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 processed

2.语句: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 processed

SQL> set autot off

#优化方法2,增加case when的函数索引,改写语句,同语句2,略

相关推荐