Oracle优化笔记1

Oracle优化笔记
 
 
    摘自《Oracle 10g SQL 开发指南》
        

1. 使用WHERE而不是HAVING
2. 使用UNION ALL 而不是UNION
3. 使用EXISTS而不是IN
4. 使用EXISTS而不是 DISTINCT ；(因为DISTINCT在禁止重复行之前要排序检索到的行)

 

   
        查看解析计划：

    

1. explain plan for YOUR_SQL
2. select * from table(dbms_xplan.display());  

 

1. 用EXISTS替换DISTINCT
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换
 例如:
低效:
    SELECT DISTINCT DEPT_NO,DEPT_NAME
    FROM DEPT D,EMP E
    WHERE D.DEPT_NO = E.DEPT_NO
高效:
    SELECT DEPT_NO,DEPT_NAME
    FROM DEPT D
    WHERE EXISTS ( SELECT 1
                    FROM EMP E
                    WHERE E.DEPT_NO = D.DEPT_NO);
  EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.

 

2. 最高效的删除重复记录方法 ( 因为使用了ROWID)
DELETE FROM EMP a
WHERE a.ROWID > (SELECT MIN(b.ROWID)
                   FROM EMP b  WHERE a.EMP_NO = b.EMP_NO);
-- 对于select语句中 过滤重复记录，则可以使用类似

SELECT * FROM EMP a
WHERE a.ROWID = (SELECT MIN(b.ROWID) FROM EMP b  WHERE a.EMP_NO = b.EMP_NO);

 

3. 尽量多使用COMMIT
只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
 COMMIT所释放的资源:
a.       回滚段上用于恢复数据的信息.
b.       被程序语句获得的锁
c.       redo log buffer 中的空间
d.       ORACLE为管理上述3种资源中的内部花费

 

    ·表索引一些注意的地方：

1. WHERE语句将决定是否有使用索引。

 

* 需要当心的WHERE子句

某些SELECT 语句中的WHERE子句不使用索引. 这里有一些例子.

在下面的例子里, ‘!=’ 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中.

不使用索引:

SELECT ACCOUNT_NAME

FROM TRANSACTION

WHERE AMOUNT !=0;

使用索引:

SELECT ACCOUNT_NAME

FROM TRANSACTION

WHERE AMOUNT >0;

下面的例子中, ‘||’是字符连接函数. 就象其他函数那样, 停用了索引.

不使用索引:

SELECT ACCOUNT_NAME,AMOUNT

FROM TRANSACTION

WHERE ACCOUNT_NAME||ACCOUNT_TYPE=’AMEXA’;

使用索引:

SELECT ACCOUNT_NAME,AMOUNT

FROM TRANSACTION

WHERE ACCOUNT_NAME = ‘AMEX’

AND  ACCOUNT_TYPE=’ A’;

下面的例子中, ‘+’是数学函数. 就象其他数学函数那样, 停用了索引.

不使用索引:

SELECT ACCOUNT_NAME, AMOUNT

FROM TRANSACTION

WHERE AMOUNT + 3000 >5000;

使用索引:

SELECT ACCOUNT_NAME, AMOUNT

FROM TRANSACTION

WHERE AMOUNT > 2000 ;

下面的例子中,相同的索引列不能互相比较,这将会启用全表扫描.

不使用索引:

SELECT ACCOUNT_NAME, AMOUNT

FROM TRANSACTION

WHERE ACCOUNT_NAME = NVL(:ACC_NAME,ACCOUNT_NAME);

使用索引:

SELECT ACCOUNT_NAME, AMOUNT

FROM TRANSACTION

WHERE ACCOUNT_NAME LIKE NVL(:ACC_NAME,’%’);

译者按:

如果一定要对使用函数的列启用索引, ORACLE新的功能: 基于函数的索引(Function-Based Index) 也许是一个较好的方案.

 CREATE INDEX EMP_I ON EMP (UPPER(ename)); /*建立基于函数的索引*/

 SELECT * FROM emp WHERE UPPER(ename) = ‘BLACKSNAIL’; /*将使用索引*/

   

2. 数值类型转换将可能不会使用索引。所以尽可能将索引的列定义为NUMBER类型。

 

现在,假设EMP_TYPE是一个字符类型的索引列.

SELECT …
FROM EMP

WHERE EMP_TYPE = 123

这个语句被ORACLE转换为:

SELECT …

FROM EMP

WHERE TO_NUMBER(EMP_TYPE)=123

因为内部发生的类型转换, 这个索引将不会被用到!

   

3. 避免在索引列上使用计算．

WHERE子句中，如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．

 

举例:

 

 

低效：

 

SELECT …

FROM DEPT

WHERE SAL * 12 > 25000;

 

高效:

SELECT …

FROM DEPT

WHERE SAL  > 25000/12;

 

译者按:
这是一个非常实用的规则，请务必牢记

   

4. DECODE的使用．

DECODE compares expr to each search value one by one. If expr is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle returns default. If default is omitted, then Oracle returns null.

 

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
例如:
   SELECT COUNT(*)，SUM(SAL)
   FROM　EMP
   WHERE DEPT_NO = 0020
   AND ENAME LIKE　‘SMITH%’;
   SELECT COUNT(*)，SUM(SAL)
   FROM　EMP
   WHERE DEPT_NO = 0030
   AND ENAME LIKE　‘SMITH%’;
你可以用DECODE函数高效地得到相同结果
SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,
        COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,
        SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
        SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
FROM EMP WHERE ENAME LIKE ‘SMITH%’;
类似的,DECODE函数也可以运用于GROUP BY 和ORDER BY子句中.

 

5. row_number和over partition by的使用．

下面语句的作用是根据msisdn分组，并查询出create_time最新的记录

select msisdn,imsi,user_type,1  reg_type,city_id,prov_id,create_time

            from(

                  select msisdn,imsi,user_type ,city_id,prov_id,create_time,

                         row_number()over(partition by msisdn order by create_time desc) r

                  from   UDPAIR_HIS

                  where  udpair_id between id and MIDDLE_ID

                )

            where r=1;