以下为NULL影响执行计划的测试示例。
/*1.构建test表，其中create table方式建立的test表结构object_id非空*，走索引/
SELECT Count(*) FROM all_objects WHERE object_id IS NOT NULL; --41790笔
DROP TABLE test;
CREATE TABLE test AS SELECT * FROM all_objects WHERE object_id IS NOT NULL; ----41791笔
CREATE INDEX idx_test ON test(object_id);
ANALYZE TABLE test compute STATISTICS FOR TABLE FOR ALL indexes FOR ALL indexed COLUMNS;
EXPLAIN PLAN FOR SELECT Count(*) FROM test;
SELECT * FROM TABLE(dbms_xplan.display);
Plan hash value: 3508397080
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 (5)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_TEST | 41791 | 23 (5)| 00:00:01 |
--------------------------------------------------------------------------
/*2.改变test表结构，使得object_id字段为NULL,并更新一笔资料为NULL*，走全表/
ALTER TABLE test MODIFY object_id NUMBER NULL;
UPDATE test SET object_id=NULL WHERE ROWNUM=1;
COMMIT;
EXPLAIN PLAN FOR SELECT Count(*) FROM test;
SELECT * FROM TABLE(dbms_xplan.display);
Plan hash value: 1950795681
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 135 (2)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST | 41791 | 135 (2)| 00:00:02 |
-------------------------------------------------------------------
/*3.对SQL指令增加条件过滤NULL之资料*，走索引/
EXPLAIN PLAN FOR SELECT Count(*) FROM test WHERE object_id IS NOT NULL;
SELECT * FROM TABLE(dbms_xplan.display);
Plan hash value: 3508397080
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 23 (5)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX FAST FULL SCAN| IDX_TEST | 41791 | 163K| 23 (5)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID" IS NOT NULL)
/*4.将上面改的那笔object_