ORACLE SQL性能优化系列 (十一)

ORACLE SQL性能优化系列 (十一)

作者: black_snail

关键字 ORACLE PERFORMANCE PL/SQL TUNING  出处 http://www.dbasupport.com  36. 用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的数据交换量的减少看出  37. 用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下，两者的执行路径似乎是相同的．　  38. 避免在索引列上使用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;