有时开发进行表结构设计，对表字段是否为空过于随意，出现诸如id1=id2，如果允许字段为空，因为oracle中空值并不等于空值，有可能得到意料之外的结果。除此之外，最关键的是，NULL会影响oracle的执行计划。

以下为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 |