sql 过滤条件IS NULL 的优化随笔

来源：这里教程网时间：2026-03-03 16:04:31 作者：

有些时候在写语句是经常用 IS Null 为过滤条件，导致对表全表扫面，如果此表很大的话，导致消耗很多资源，下面通过实验说明怎么对 Is Null 优化

创建测试表如下：

select count(*) from TB_INFO;

COUNT(*)

----------

226803

SQL>

SQL> explain plan for SELECT * FROM TB_INFO A WHERE A.TB_NO IS NULL;

SQL> SELECT * FROMTABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2467334803

-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------

| 0 |SELECT STATEMENT | | 186K| 24M| 11573 (1)| 00:02:19 |

|* 1| TABLE ACCESS FULL| TB_INFO | 186K| 24M| 11573 (1)| 00:02:19 |

-----------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------

1 -filter("A"."TB_NO" IS NOT NULL)

13 rows selected.

优化方法 1

增加函数索引，只让 TB_NO IS NULL 保存在索引中

SQL> create index tb_ind01 on TB_INFO(decode(TB_NO,null,1));

Index created.

SQL> execdbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'TB_INFO',estimate_percent=> 10,method_opt=> 'for all indexed columns') ;

PL/SQL procedure successfullycompleted.

语句改写为：

SELECT*

FROM TB_INFO A

WHERE decode(A.TB_NO ,null,1)=1;

decode(object_name,null,1)=1;

SQL> SELECT *FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value:4138696740

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 10 | 1400 | 6 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TB_INFO | 10 | 1400 | 6 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN |TB_IND01 | 10 | | 3 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

PredicateInformation (identified by operation id):

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------

2 - access("A"."TB_NO"IS NULL)

14 rows selected.

优化方法 2:

增加索引，让 TB_NO IS NULL 的也保存在索引中

Drop index tb_ind01;

SQL> create index tb_ind02 on TB_INFO (TB_NO,1);

Index created.

此方法不需要改语句，执行计划如下：

Explained.

SQL> SELECT *FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------

Plan hash value:4138696740

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 10| 1400 | 6 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TB_INFO | 10 | 1400 | 6 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN |TB_IND02 | 10 | | 3 (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

PredicateInformation (identified by operation id):

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------

2 - access("A"."TB_NO"IS NULL)

14 rows selected.

最后建义

Where 子句中的 IS NULL 和 ISNOT NULL 将不会使用索引而是进行全表搜索。因此需要通过改变查询方式 , 或者通过建函数索引来实现走索引的目的，这样操作会影响 DML(INSERT UPDATEDELETE ) 性能，建议还是从初期设计表时对其处理。