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采用了内部的操作。 下表显示了几种重要的内部操作。
当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表空间里。 同时,确保数据表空间和索引表空间置于不同的硬盘上。
:“同时,确保数据表空间和索引表空间置与不同的硬盘上。”可能改为如下更为准确 “同时,确保数据表空间和索引表空间置与不同的硬盘控制卡控制的硬盘上。”