高效SQL语句必杀技
作者：网络转载 发布时间：[ 2013/6/18 10:28:10 ] 推荐标签：
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 |
-----------------------------------------------------------------------------------------------
sales@spasvo.com