ORACLE SQL性能优化系列 (十一)
2024-08-29 13:46:45
供稿：网友
Oracle SQL性能优化系列 (十一)
36. 用UNION替换OR (适用于索引列) 通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注重, 以上规则只针对多个索引列有效. 假如有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引.高效:SELECT LOC_ID , LOC_DESC , REGIONFROM LOCATIONWHERE LOC_ID = 10UNIONSELECT LOC_ID , LOC_DESC , REGIONFROM LOCATIONWHERE REGION = “MELBOURNE” 低效:SELECT LOC_ID , LOC_DESC , REGIONFROM LOCATIONWHERE 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=CHOOSE1 0 CONCATENATION2 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 calls0 db block gets144 consistent gets0 physical reads0 redo size63749 bytes sent via SQL*Net to client7751 bytes received via SQL*Net from client68 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1003 rows PRocessedSQL> select * from unionvsor /*2nd test*/2 where b = 1 or a = 1003 ; 1003 rows selected.Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE1 0 CONCATENATION2 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 calls0 db block gets143 consistent gets0 physical reads0 redo size63749 bytes sent via SQL*Net to client7751 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1003 rows processed SQL> select * from unionvsor /*3rd test*/2 where a = 10033 union 4 select * from unionvsor5 where b = 1;1003 rows selected.Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE1 0 SORT (UNIQUE)2 1 UNION-ALL3 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 calls0 db block gets10 consistent gets 0 physical reads0 redo size63735 bytes sent via SQL*Net to client7751 bytes received via SQL*Net from client68 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)1003 rows processed用UNION的效果可以从consistent gets和 SQL*NET的数据交换量的减少看出 37. 用IN来替换OR 下面的查询可以被更有效率的语句替换: 低效: SELECT….FROM LOCATIONWHERE LOC_ID = 10OR LOC_ID = 20OR LOC_ID = 30 高效SELECT…FROM LOCATIONWHERE 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 DEPARTMENTWHERE DEPT_CODE IS NOT NULL; 高效: (索引有效)SELECT …FROM DEPARTMENTWHERE DEPT_CODE >=0;
