2)避免在索引列上使用NOT运算或不等于运算(<>,!=)
通常,我们要避免在索引列上使用NOT或<>,两者会产生在和在索引列上使用函数相同的影响。 当ORACLE遇到NOT或不等运算时,他就会停止使用索引转而执行全表扫描。
-->低效:
SELECT * FROM emp WHERE NOT ( deptno = 20 ); -->实际上NOT ( deptno = 20 )等同于deptno <> 20,即deptno <>同样会限制索引 |
-->高效:
SELECT * FROM emp WHERE deptno > 20 OR deptno < 20; |
-->尽管此方式可以替换且实现上述结果,但依然走全表扫描,如果是单纯的 > 或 < 运算,则此时为索引范围扫描
需要注意的是,在某些时候, ORACLE优化器会自动将NOT转化成相对应的关系操作符
其次如果是下列运算符进行NOT运算,依然有可能选择走索引, 仅仅除了NOT = 之外,因为 NOT = 等价于 <>
“NOT >” to <= “NOT >=” to < “NOT <” to >= “NOT <=” to > |
来看一个实际的例子
hr@CNMMBO> SELECT * 2 FROM employees 3 where not employee_id<100; -->索引列上使用了not,但是该查询返回了所有的记录,即107条,因此此时选择走全表扫描
107 rows selected.
Execution Plan ---------------------------------------------------------- Plan hash value: 1445457117 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 107 | 7276 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMPLOYEES | 107 | 7276 | 3 (0)| 00:00:01 | -->执行计划中使用了走全表扫描方式 ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("EMPLOYEE_ID">=100) -->查看这里的谓词信息被自动转换为 >= 运算符
hr@CNMMBO> SELECT * 2 FROM employees 3 where not employee_id<140; -->此例与上面的语句相同,仅仅是查询范围不同返回67条记录,而此时选择了索引范围扫描
67 rows selected.
Execution Plan ---------------------------------------------------------- Plan hash value: 603312277
--------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 68 | 4624 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 68 | 4624 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | EMP_EMP_ID_PK | 68 | | 1 (0)| 00:00:01 | -->索引范围扫描方式 --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("EMPLOYEE_ID">=140) |
3)用UNION 替换OR(适用于索引列)
通常情况下,使用UNION 替换WHERE子句中的OR将会起到较好的效果.基于索引列使用OR使得优化器倾向于使用全表扫描,而不是扫描索引。
注意,以上规则仅适用于多个索引列有效。 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低。
-->低效:
SELECT deptno, dname FROM dept WHERE loc = 'DALLAS' OR deptno = 20; |
-->高效:
SELECT deptno, dname FROM dept WHERE loc = 'DALLAS' UNION SELECT deptno, dname FROM dept WHERE deptno = 30 |
-->经测试,由于数据量较少,此时where子句中的谓词上都存在索引列时,两者性能相当。
-->假定where子句中存在两列
scott@CNMMBO> create table t6 as select object_id,owner,object_name from dba_objects where owner='SYS' and rownum<1001; scott@CNMMBO> insert into t6 select object_id,owner,object_name from dba_objects where owner='SCOTT' and rownum<6; scott@CNMMBO> create index i_t6_object_id on t6(object_id); scott@CNMMBO> create index i_t6_owner on t6(owner); scott@CNMMBO> insert into t6 select object_id,owner,object_name from dba_objects where owner='SYSTEM' and rownum<=300; scott@CNMMBO> commit; scott@CNMMBO> exec dbms_stats.gather_table_stats('SCOTT','T6',cascade=>true); scott@CNMMBO> select owner,count(*) from t6 group by owner;
OWNER COUNT(*) -------------------- ---------- SCOTT 5 SYSTEM 300 SYS 1000
scott@CNMMBO> select * from t6 where owner='SCOTT' and rownum<2;
OBJECT_ID OWNER OBJECT_NAME ---------- -------------------- -------------------- 69450 SCOTT T_TEST
scott@CNMMBO> select * from t6 where object_id=69450 or owner='SYSTEM';
301 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 238853296 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 300 | 7200 | 5 (0)| 00:00:01 | | 1 | CONCATENATION | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| T6 | 1 | 24 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | I_T6_OBJECT_ID | 1 | | 1 (0)| 00:00:01 | |* 4 | TABLE ACCESS BY INDEX ROWID| T6 | 299 | 7176 | 3 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | I_T6_OWNER | 300 | | 1 (0)| 00:00:01 | -----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("OBJECT_ID"=69450) 4 - filter(LNNVL("OBJECT_ID"=69450)) 5 - access("OWNER"='SYSTEM')
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 46 consistent gets 0 physical reads 0 redo size 11383 bytes sent via SQL*Net to client 712 bytes received via SQL*Net from client 22 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 301 rows processed
scott@CNMMBO> select * from t6 where owner='SYSTEM' or object_id=69450;
301 rows selected.
Execution Plan ---------------------------------------------------------- Plan hash value: 238853296 ----------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 300 | 7200 | 5 (0)| 00:00:01 | | 1 | CONCATENATION | | | | | | | 2 | TABLE ACCESS BY INDEX ROWID| T6 | 1 | 24 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | I_T6_OBJECT_ID | 1 | | 1 (0)| 00:00:01 | |* 4 | TABLE ACCESS BY INDEX ROWID| T6 | 299 | 7176 | 3 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | I_T6_OWNER | 300 | | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- |