Oracle SQL过滤条件是IS NULL or ！=的优化

通常情况下is null或者！=这些条件如果不是具有很强的过滤性，可以先关注其它的过滤条件。但有些SQL这两种条件具有很强的过滤性，就可以考虑用以下方法。下面先讨论is null的优化，再讨论！=的优化，最后讨论is null or ！=一起使用的优化。以下测试：
Oracle version:11.2.0.4#新建测试表
create table scott.tb_sj01 as select * from dba_objects;#处理测试表中的数据
update scott.tb_sj01 set object_name=null where object_id<=10;update scott.tb_sj01 set object_name="SJ" where object_id>20;commit;#收集表的统计信息
begin
dbms_stats.gather_table_stats（"scott","TB_SJ01"）;
end;#查看测试表中object_name字段的数据分布
#可以看出object_name字段IS NULL的有9笔记录;object_name ！="SJ"有11笔记录。
select nvl（object_name,"NULL"） object_name,count（1） cnt
from scott.tb_sj01
group by nvl（object_name,"NULL"）
order by 2 ;
/*
OBJECT_NAME CNT
ICOL$ 1
TS$ 1
OBJ$ 1
FILE$ 1
STDBY_LINK_CT6601SB 1
UNDO$ 1
FET$ 1
I_USER# 1
UET$ 1
SEG$ 1
IND$ 1
NULL 9
SJ 86880
*/1.语句：select * from scott.tb_sj01 where object_name is null的优化
SQL> set autot trace
#优化前,COST=346,consistent gets=1246
SQL> select * from scott.tb_sj01 where object_name is null;9 rows selected.Execution Plan
----------------------------------------------------------
Plan hash value: 283620643-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost （%CPU）| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 684 | 346 （1）| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TB_SJ01 | 9 | 684 | 346 （1）| 00:00:05 |
-----------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 1 - filter（"OBJECT_NAME" IS NULL）Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1246 consistent gets
0 physical reads
0 redo size
1850 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts （memory）
0 sorts （disk）
9 rows processed

#优化方法1:
#增加索引，让object_name IS NULL的也保存在索引中
SQL> create index scott.idx_tb_sj01_01 on scott.tb_sj01（object_name,1）;#优化后，COST=3,consistent gets=5
SQL> select * from scott.tb_sj01 where object_name is null;9 rows selected.Execution Plan
----------------------------------------------------------
Plan hash value: 1042936765----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 9 | 684 | 3 （0）| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| TB_SJ01 | 9 | 684 | 3 （0）| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_TB_SJ01_01 | 9 | | 2 （0）| 00:00:01 |----------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 2 - access（"OBJECT_NAME" IS NULL）Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1850 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts （memory）
0 sorts （disk）
9 rows processed#优化方法2:
#增加函数索引，只让object_name IS NULL的保存在索引中
create index scott.idx_tb_sj01_02 on scott.tb_sj01（decode（object_name,null,1））;#原语句改写为：
select * from scott.tb_sj01 where decode（object_name,null,1）=1;#优化后，COST=2，consistent gets=4
SQL> select * from scott.tb_sj01 where decode（object_name,null,1）=1;9 rows selected.Execution Plan
----------------------------------------------------------
Plan hash value: 612345449----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 869 | 66044 | 2 （0）| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| TB_SJ01 | 869 | 66044 | 2 （0）| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_TB_SJ01_02 | 9 | | 1 （0）| 00:00:01 |----------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 2 - access（DECODE（"OBJECT_NAME",NULL,1）=1）Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1850 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts （memory）
0 sorts （disk）
9 rows processed2.语句:select * from scott.tb_sj01 where object_name！="SJ"的优化#优化前,COST=346，consistent gets=1246
SQL> select * from scott.tb_sj01 where object_name！="SJ";11 rows selected.Execution Plan
----------------------------------------------------------
Plan hash value: 283620643-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 79650 | 5911K| 346 （1）| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TB_SJ01 | 79650 | 5911K| 346 （1）| 00:00:05 |
-----------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 1 - filter（"OBJECT_NAME"<>"SJ"）Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1246 consistent gets
0 physical reads
0 redo size
1979 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts （memory）
0 sorts （disk）
11 rows processed#优化方法1：
#增加函数索引，将object_name！="SJ"和object_name IS NULL的保存在索引中
create index scott.idx_tb_sj01_04 on scott.tb_sj01（decode（object_name,null,1,"SJ",null,2））;
drop index scott.idx_tb_sj01_04;#原语句改写为：
select * from scott.tb_sj01 t where decode（object_name,null,1,"SJ",null,2）=2;#优化后，COST=4，consistent gets=4
SQL> select * from scott.tb_sj01 t where decode（object_name,null,1,"SJ",null,2）=2;11 rows selected.Execution Plan
----------------------------------------------------------
Plan hash value: 3453712045----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 869 | 71258 | 4 （0）| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| TB_SJ01 | 869 | 71258 | 4 （0）| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_TB_SJ01_04 | 20 | | 1 （0）| 00:00:01 |----------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 2 - access（DECODE（"OBJECT_NAME",NULL,1,"SJ",NULL,2）=2）Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1938 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts （memory）
0 sorts （disk）
11 rows processed#优化方法2：
#增加函数索引，只让object_name！="SJ"的保存在索引中
create index scott.idx_tb_sj01_05 on scott.tb_sj01（case when object_name！="SJ" then 1 else null end）;#原语句改写为：
select * from scott.tb_sj01 t where （case when object_name！="SJ" then 1 else null end）=1;#优化后，COST=3，consistent gets=4
SQL> select * from scott.tb_sj01 t where （case when object_name！="SJ" then 1 else null end）=1;11 rows selected.Execution Plan
----------------------------------------------------------
Plan hash value: 376302892----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 869 | 71258 | 3 （0）| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| TB_SJ01 | 869 | 71258 | 3 （0）| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_TB_SJ01_05 | 11 | | 1 （0）| 00:00:01 |----------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 2 - access（CASE WHEN "OBJECT_NAME"<>"SJ" THEN 1 ELSE NULL END =1）Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1938 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts （memory）
0 sorts （disk）
11 rows processed
3.语句：select * from scott.tb_sj01 where object_name is null or object_name ！="SJ"的优化#优化前,COST=346，consistent gets=1247
SQL> select * from scott.tb_sj01 where object_name is null or object_name ！="SJ";20 rows selected.Execution Plan
----------------------------------------------------------
Plan hash value: 283620643-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 3280 | 346 （1）| 00:00:05 |
|* 1 | TABLE ACCESS FULL| TB_SJ01 | 40 | 3280 | 346 （1）| 00:00:05 |
-----------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 1 - filter（"OBJECT_NAME"<>"SJ" OR "OBJECT_NAME" IS NULL）Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1247 consistent gets
0 physical reads
0 redo size
2403 bytes sent via SQL*Net to client
534 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts （memory）
0 sorts （disk）
20 rows processed
#优化方法1：
#增加索引，将object_name！="SJ"和object_name IS NULL的保存在索引中
create index scott.idx_tb_sj01_06 on scott.tb_sj01（decode（object_name,null,1,"SJ",null,1））;#语句修改为：
select * from scott.tb_sj01 t where decode（object_name,null,1,"SJ",null,1）=1;#优化后,COST=3，consistent gets=6
SQL> select * from scott.tb_sj01 where decode（object_name,null,1,"SJ",null,1）=1;20 rows selected.Execution Plan
----------------------------------------------------------
Plan hash value: 356892721----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CP
U）| Time |----------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 869 | 71258 | 3 （0）| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| TB_SJ01 | 869 | 71258 | 3 （0）| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_TB_SJ01_06 | 20 | | 1 （0）| 00:00:01 |----------------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 2 - access（DECODE（"OBJECT_NAME",NULL,1,"SJ",NULL,1）=1）Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
2362 bytes sent via SQL*Net to client
534 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts （memory）
0 sorts （disk）
20 rows processedSQL> set autot off#优化方法2，增加case when的函数索引，改写语句，同语句2，略更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址