Oracle语句优化规则汇总(二)

1. 用UNION替换OR (适用于索引列)

　　通常情况下， 用UNION替换WHERE子句中的OR将会起到较好的效果。 对索引列使用OR将造成全表扫描。注意， 以上规则只针对多个索引列有效。 如果有column没有被索引， 查询效率可能会因为你没有选择OR而降低。

　　在下面的例子中， LOC_ID 和REGION上都建有索引。

　　高效：

　　SELECT LOC_ID ， LOC_DESC ， REGION
　　FROM LOCATION
　　WHERE LOC_ID = 10
　　UNION
　　SELECT LOC_ID ， LOC_DESC ， REGION
　　FROM LOCATION
　　WHERE REGION = “MELBOURNE”

　　低效：

　　SELECT LOC_ID ， LOC_DESC ， REGION
　　FROM LOCATION
　　WHERE LOC_ID = 10 OR REGION = “MELBOURNE”

　　如果你坚持要用OR， 那就需要返回记录最少的索引列写在最前面。

　　注意：

　　WHERE KEY1 = 10 (返回最少记录)

　　OR KEY2 = 20 (返回最多记录)

　　ORACLE 内部将以上转换为

　　WHERE KEY1 = 10 AND((NOT KEY1 = 10) AND KEY2 = 20)

　　：

　　下面的测试数据仅供参考： (a = 1003 返回一条记录 ， b = 1 返回1003条记录)

SQL> select * from unionvsor /*1st test*/
　2   where a = 1003 or b = 1；
　1003 rows selected.
　Execution Plan
　----------------------------------------------------------
    0      SELECT STATEMENT Optimizer=CHOOSE
　1    0   CONCATENATION
　2    1     TABLE ACCESS （BY INDEX ROWID） OF 'UNIONVSOR'
　3    2       INDEX （RANGE SCAN） OF 'UB' （NON-UNIQUE）
    4    1     TABLE ACCESS （BY INDEX ROWID） OF 'UNIONVSOR'
　5    4       INDEX （RANGE SCAN） OF 'UA' （NON-UNIQUE）
    Statistics
　----------------------------------------------------------
    0 recursive calls
　0 db block gets
　144 consistent gets
　0 physical reads
　0 redo size
　63749 bytes sent via SQL*Net to client
　7751 bytes received via SQL*Net from client
　68 SQL*Net roundtrips to/from client
　0 sorts （memory）
    0 sorts （disk）
    1003 rows processed
　SQL> select * from unionvsor /*2nd test*/
　2 where b = 1 or a = 1003 ；
　1003 rows selected.
　Execution Plan
　----------------------------------------------------------
    0      SELECT STATEMENT Optimizer=CHOOSE
　1    0   CONCATENATION
　2    1     TABLE ACCESS （BY INDEX ROWID） OF 'UNIONVSOR'
　3    2       INDEX （RANGE SCAN） OF 'UA' （NON-UNIQUE）
    4    1     TABLE ACCESS （BY INDEX ROWID） OF 'UNIONVSOR'
　5    4       INDEX （RANGE SCAN） OF 'UB' （NON-UNIQUE）
    Statistics
　----------------------------------------------------------
    0 recursive calls
　0 db block gets
　143 consistent gets
　0 physical reads
　0 redo size
　63749 bytes sent via SQL*Net to client
　7751 bytes received via SQL*Net from client
　68 SQL*Net roundtrips to/from client 0 sorts （memory）
    0 sorts （disk）
    1003 rows processed
    SQL> select * from unionvsor /*3rd test*/
　2 where a = 1003
　3 union
　4   select * from unionvsor
　5   where b = 1；
　1003 rows selected. Execution Plan
　----------------------------------------------------------
    0      SELECT STATEMENT Optimizer=CHOOSE
　1    0   SORT （UNIQUE）
    2    1     UNION-ALL
　3    2       TABLE ACCESS （BY INDEX ROWID） OF 'UNIONVSOR'
　4    3         INDEX （RANGE SCAN） OF 'UA' （NON-UNIQUE）
    5    2       TABLE ACCESS （BY INDEX ROWID） OF 'UNIONVSOR'
　6    5         INDEX （RANGE SCAN） OF 'UB' （NON-UNIQUE）
    Statistics
　----------------------------------------------------------
    0 recursive calls
　0 db block gets
　10 consistent gets
　0 physical reads
　0 redo size
　63735 bytes sent via SQL*Net to client
　7751 bytes received via SQL*Net from client
　68 SQL*Net roundtrips to/from client 1 sorts （memory）
    0 sorts （disk）
    1003 rows processed

　　用UNION的效果可以从consistent gets和 SQL*NET的数据交换量的减少看出

　2. 用IN来替换OR

　　下面的查询可以被更有效率的语句替换：

　　低效：

　　SELECT…
　　FROM LOCATION
　　WHERE LOC_ID = 10
　　OR LOC_ID = 20
　　OR LOC_ID = 30

　　高效：

　　SELECT…
　　FROM LOCATION
　　WHERE LOC_IN IN (10，20，30);

　　：这是一条简单易记的规则，但是实际的执行效果还须检验，在ORACLE8i下，两者的执行路径似乎是相同的。

　　3. 避免在索引列上使用IS NULL和IS NOT NULL　

　　避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引。对于单列索引，如果列包含空值，索引中将不存在此记录。 对于复合索引，如果每个列都为空，索引中同样不存在此记录。　如果至少有一个列不为空，则记录存在于索引中。

　　举例：

　　如果唯一性索引建立在表的A列和B列上， 并且表中存在一条记录的A，B值为(123，null) ， ORACLE将不接受下一条具有相同A，B值(123，null)的记录(插入)。 然而如果所有的索引列都为空，ORACLE将认为整个键值为空而空不等于空。 因此你可以插入1000条具有相同键值的记录，当然它们都是空!

　　因为空值不存在于索引列中，所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引。

　　举例：

　　低效： (索引失效)

　　SELECT …
　　FROM DEPARTMENT
　　WHERE DEPT_CODE IS NOT NULL;

　　高效： (索引有效)

　　SELECT …
　　FROM DEPARTMENT
　　WHERE DEPT_CODE >=0;

 

　　4. 总是使用索引的第一个列

　　如果索引是建立在多个列上， 只有在它的第一个列(leading column)被where子句引用时，优化器才会选择使用该索引。

　　：这也是一条简单而重要的规则。 见以下实例。

    SQL> create table multiindexusage （ inda number ， indb number ， descr varchar2（10））；
　Table created.
　SQL> create index multindex on multiindexusage（inda，indb）；
　Index created.
　SQL> set autotrace traceonly
    SQL> select * from multiindexusage where inda = 1；
　Execution Plan
　----------------------------------------------------------
    0      SELECT STATEMENT Optimizer=CHOOSE
　1    0   TABLE ACCESS （BY INDEX ROWID） OF 'MULTIINDEXUSAGE'
　2    1     INDEX （RANGE SCAN） OF 'MULTINDEX' （NON-UNIQUE）
    SQL> select * from multiindexusage where indb = 1；
　Execution Plan
　----------------------------------------------------------
    0      SELECT STATEMENT Optimizer=CHOOSE
　1    0   TABLE ACCESS （FULL） OF 'MULTIINDEXUSAGE'

　　很明显， 当仅引用索引的第二个列时，优化器使用了全表扫描而忽略了索引

　　5. ORACLE内部操作

　　当执行查询时，ORACLE采用了内部的操作。 下表显示了几种重要的内部操作。

　　6. 用UNION-ALL 替换UNION ( 如果有可能的话)

　　当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并， 然后在输出最终结果前进行排序。

　　如果用UNION ALL替代UNION， 这样排序就不是必要了。 效率就会因此得到提高。

　　举例：

　　低效：

　　SELECT ACCT_NUM， BALANCE_AMT
　　FROM DEBIT_TRANSACTIONS
　　WHERE TRAN_DATE = ‘31-DEC-95’
　　UNION
　　SELECT ACCT_NUM， BALANCE_AMT
　　FROM DEBIT_TRANSACTIONS
　　WHERE TRAN_DATE = ‘31-DEC-95’

　　高效：

　　SELECT ACCT_NUM， BALANCE_AMT
　　FROM DEBIT_TRANSACTIONS
　　WHERE TRAN_DATE = ‘31-DEC-95’
　　UNION ALL
　　SELECT ACCT_NUM， BALANCE_AMT
　　FROM DEBIT_TRANSACTIONS
　　WHERE TRAN_DATE = ‘31-DEC-95’

　　：需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录。 因此各位还是要从业务需求分析使用UNION ALL的可行性。

　　UNION 将对结果集合排序，这个操作会使用到SORT_AREA_SIZE这块内存。 对于这块内存的优化也是相当重要的。 下面的SQL可以用来查询排序的消耗量

    Select substr（name，1，25） "Sort Area Name"，
　substr（value，1，15）   "Value"
　from v$sysstat
　where name like 'sort%'

1. 使用提示(Hints)

　　对于表的访问，可以使用两种Hints：FULL 和 ROWID

　　FULL hint 告诉ORACLE使用全表扫描的方式访问指定表。

　　例如：

　　SELECT /*+ FULL(EMP) */ *
　　FROM EMP
　　WHERE EMPNO = 7893;

　　ROWID hint 告诉ORACLE使用TABLE ACCESS BY ROWID的操作访问表。

　　通常， 你需要采用TABLE ACCESS BY ROWID的方式特别是当访问大表的时候， 使用这种方式， 你需要知道ROIWD的值或者使用索引。

　　如果一个大表没有被设定为缓存(CACHED)表而你希望它的数据在查询结束是仍然停留在SGA中，你就可以使用CACHE hint 来告诉优化器把数据保留在SGA中。 通常CACHE hint 和 FULL hint 一起使用。

　　例如：

　　SELECT /*+ FULL(WORKER) CACHE(WORKER)*/ *
　　FROM WORK;

　　索引hint 告诉ORACLE使用基于索引的扫描方式。 你不必说明具体的索引名称

　　例如：

　　SELECT /*+ INDEX(LODGING) */ LODGING
　　FROM LODGING
　　WHERE MANAGER = ‘BILL GATES’;

　　在不使用hint的情况下， 以上的查询应该也会使用索引，然而，如果该索引的重复值过多而你的优化器是CBO， 优化器就可能忽略索引。 在这种情况下， 你可以用INDEX hint强制ORACLE使用该索引。

　　ORACLE hints 还包括ALL_ROWS， FIRST_ROWS， RULE，USE_NL， USE_MERGE， USE_HASH 等等。

　　：使用hint ， 表示我们对ORACLE优化器缺省的执行路径不满意，需要手工修改。这是一个很有技巧性的工作。 我建议只针对特定的，少数的SQL进行hint的优化。对ORACLE的优化器还是要有信心(特别是CBO)

　2. 用WHERE替代ORDER BY

• 　　ORDER BY 子句只在两种严格的条件下使用索引。
• 　　ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序。
• 　　ORDER BY中所有的列必须定义为非空。
• 　　WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列。

　　例如：

　　表DEPT包含以下列：

　　DEPT_CODE PK NOT NULL
　　DEPT_DESC NOT NULL
　　DEPT_TYPE NULL

　　非唯一性的索引(DEPT_TYPE)

　　低效： (索引不被使用)

　　SELECT DEPT_CODE
　　FROM DEPT
　　ORDER BY DEPT_TYPE
　　EXPLAIN PLAN：
　　SORT ORDER BY
　　TABLE ACCESS FULL

　　高效： (使用索引)

　　SELECT DEPT_CODE
　　FROM DEPT
　　WHERE DEPT_TYPE > 0
　　EXPLAIN PLAN：
　　TABLE ACCESS BY ROWID ON EMP
　　INDEX RANGE SCAN ON DEPT_IDX

　　：ORDER BY 也能使用索引! 这的确是个容易被忽视的知识点。 我们来验证一下：

SQL> select * from emp order by empno；
Execution Plan
----------------------------------------------------------
    0      SELECT STATEMENT Optimizer=CHOOSE
　1    0   TABLE ACCESS （BY INDEX ROWID） OF 'EMP'
　2    1     INDEX （FULL SCAN） OF 'EMPNO' （UNIQUE）

 

　3. 避免改变索引列的类型

　　当比较不同数据类型的数据时， ORACLE自动对列进行简单的类型转换。

　　假设 EMPNO是一个数值类型的索引列。

　　SELECT …
　　FROM EMP
　　WHERE EMPNO = ‘123’

　　实际上，经过ORACLE类型转换， 语句转化为：

　　SELECT …
　　FROM EMP
　　WHERE EMPNO = TO_NUMBER(‘123’)

　　幸运的是，类型转换没有发生在索引列上，索引的用途没有被改变。

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

　　SELECT …
　　FROM EMP
　　WHERE EMP_TYPE = 123

　　这个语句被ORACLE转换为：

　　SELECT …
　　FROM EMP
　　WHERE TO_NUMBER(EMP_TYPE)=123

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

　　：为了避免ORACLE对你的SQL进行隐式的类型转换， 最好把类型转换用显式表现出来。 注意当字符和数值比较时， ORACLE会优先转换数值类型到字符类型。

 

1. 需要当心的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. 连接多个扫描

　　如果你对一个列和一组有限的值进行比较， 优化器可能执行多次扫描并对结果进行合并连接。

　　举例：

　　SELECT *
　　FROM LODGING
　　WHERE MANAGER IN (‘BILL GATES’，‘KEN MULLER’);

　　优化器可能将它转换成以下形式

　SELECT *
　　FROM LODGING
　　WHERE MANAGER = ‘BILL GATES’OR MANAGER = ‘KEN MULLER’;

　　当选择执行路径时， 优化器可能对每个条件采用LODGING$MANAGER上的索引范围扫描。 返回的ROWID用来访问LODGING表的记录 (通过TABLE ACCESS BY ROWID 的方式)。 最后两组记录以连接(CONCATENATION)的形式被组合成一个单一的集合。

　　Explain Plan ：

　　SELECT STATEMENT Optimizer=CHOOSE
　　CONCATENATION
　　TABLE ACCESS (BY INDEX ROWID) OF LODGING
　　INDEX (RANGE SCAN ) OF LODGING$MANAGER (NON-UNIQUE)
　　TABLE ACCESS (BY INDEX ROWID) OF LODGING
　　INDEX (RANGE SCAN ) OF LODGING$MANAGER (NON-UNIQUE)

　　：本节和第37节似乎有矛盾之处。

　　3. CBO下使用更具选择性的索引

　　基于成本的优化器(CBO， Cost-Based Optimizer)对索引的选择性进行判断来决定索引的使用是否能提高效率。

　　如果索引有很高的选择性， 那就是说对于每个不重复的索引键值，只对应数量很少的记录。

　　比如， 表中共有100条记录而其中有80个不重复的索引键值。 这个索引的选择性就是80/100 = 0.8 . 选择性越高， 通过索引键值检索出的记录就越少。

　　如果索引的选择性很低， 检索数据就需要大量的索引范围查询操作和ROWID 访问表的操作。 也许会比全表扫描的效率更低。

　　：下列经验请参阅：

　　a. 如果检索数据量超过30%的表中记录数。使用索引将没有显著的效率提高。

　　b. 在特定情况下， 使用索引也许会比全表扫描慢， 但这是同一个数量级上的区别。 而通常情况下，使用索引比全表扫描要快几倍乃至几千倍!

　　4. 避免使用耗费资源的操作

　　带有DISTINCT，UNION，MINUS，INTERSECT，ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序(SORT)功能。 DISTINCT需要一次排序操作， 而其他的至少需要执行两次排序。

　　例如，一个UNION查询，其中每个查询都带有GROUP BY子句， GROUP BY会触发嵌入排序(NESTED SORT) ; 这样， 每个查询需要执行一次排序， 然后在执行UNION时， 又一个唯一排序(SORT UNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行。 嵌入的排序的深度会大大影响查询的效率。

　　通常， 带有UNION， MINUS ， INTERSECT的SQL语句都可以用其他方式重写。

　　：如果你的数据库的SORT_AREA_SIZE调配得好， 使用UNION ， MINUS， INTERSECT也是可以考虑的， 毕竟它们的可读性很强

　　5. 优化GROUP BY

　　提高GROUP BY 语句的效率， 可以通过将不需要的记录在GROUP BY 之前过滤掉。下面两个查询返回相同结果但第二个明显就快了许多。

　　低效：

　　SELECT JOB ， AVG(SAL)
　　FROM EMP
　　GROUP by JOB
　　HAVING JOB = ‘PRESIDENT’
　　OR JOB = ‘MANAGER’

　　高效：

　　SELECT JOB ， AVG(SAL)
　　FROM EMP
　　WHERE JOB = ‘PRESIDENT’
　　OR JOB = ‘MANAGER’GROUP by JOB

　　6. 使用日期当

　　使用日期是，需要注意如果有超过5位小数加到日期上， 这个日期会进到下一天!

　　例如：

　　1.

　　SELECT TO_DATE(‘01-JAN-93’+.99999)
　　FROM DUAL;
　　Returns：“01-JAN-93 23：59：59‘

　　2.

　　SELECT TO_DATE(’01-JAN-93‘+.999999)
　　FROM DUAL;
　　Returns：“02-JAN-93 00：00：00‘

　　：虽然本节和SQL性能优化没有关系， 但是作者的功力可见一斑。

　　7. 使用显式的游标(CURSORs)

　　使用隐式的游标，将会执行两次操作。 第一次检索记录， 第二次检查TOO MANY ROWS 这个exception . 而显式游标不执行第二次操作。

　　8. 优化EXPORT和IMPORT

　　使用较大的BUFFER(比如10MB ， 10，240，000)可以提高EXPORT和IMPORT的速度。

　　ORACLE将尽可能地获取你所指定的内存大小，即使在内存不满足，也不会报错。这个值至少要和表中最大的列相当，否则列值会被截断。

　　：可以肯定的是， 增加BUFFER会大大提高EXPORT ， IMPORT的效率。 (曾经碰到过一个CASE， 增加BUFFER后，IMPORT/EXPORT快了10倍!)

　　作者可能犯了一个错误： “这个值至少要和表中最大的列相当，否则列值会被截断。 ”其中最大的列也许是指最大的记录大小。

　　关于EXPORT/IMPORT的优化，CSDN论坛中有一些总结性的贴子，比如关于BUFFER参数， COMMIT参数等等， 详情请查。

　　9. 分离表和索引

　　总是将你的表和索引建立在不同的表空间内(TABLESPACES)。 决不要将不属于ORACLE内部系统的对象存放到SYSTEM表空间里。 同时，确保数据表空间和索引表空间置于不同的硬盘上。

　　：“同时，确保数据表空间和索引表空间置与不同的硬盘上。”可能改为如下更为准确 “同时，确保数据表空间和索引表空间置与不同的硬盘控制卡控制的硬盘上。”