Oracle索引列NULL值引发执行计划该表的测试示例

有时开发进行表结构设计，对表字段是否为空过于随意，出现诸如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 |   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_id is NULL的资料delete掉，再查看plan，依然走全表*/DELETE FROM test WHERE object_id IS NULL;
COMMIT;
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: 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 | 41790 |   135   （2）| 00:00:02 |
-------------------------------------------------------------------
综上，看起来，假如索引列的表结构非空，则会走索引；若索引列表结构可空，则无论是否存在null资料，都会走全表；可以用where 索引列 is not null过滤空资料，则还是会走索引。Oracle Library Cache Lock 解决思路效率测试小工具runstats学习及应用示例相关资讯 Oracle索引

Oracle跳跃式索引扫描测试（08月09日）
Oracle组合索引与回表（08/07/2015 18:11:53）
Oracle 索引基本原理（04/12/2015 18:03:58）

关于Oracle位图索引内部浅论（09/17/2015 19:23:59）
Oracle 索引的可见与隐藏（visible （07/18/2015 09:41:42）
Oracle索引合并coalesce操作（04/01/2015 20:21:34）

本文评论查看全部评论（0）