INDEX受到NULL值的影响
假设现在有如下一张表：CREATE TABLE TBL( USERID NUMBER(20), NAME VARCHAR2(20), DEPTNO NUMBER(20));--模拟数据BEGIN FOR I IN 0..1000 LOOP INSERT INTO TBL VALUES( I,CONCAT('TBL',I),MOD(I,2)); END LOOP;END;/SELECT * FROM TBL;在这张表的数据上面建立了如下的索引：CREATE INDEX INDEX_TBL_NAME_DEPTNO ON TBL(NAME,DEPTNO);在这张表上，修改了name和deptno两列为not null：alter table tbl modify(name not null);alter table tbl modify(deptno not null);
我们在这张表上执行如下查询：ChenZw> select name,deptno from tbl order by name,deptno;已选择1001行。已用时间: 00: 00: 00.03执行计划----------------------Plan hash value: 2449324302------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------| 0 | SELECT STATEMENT | | 1001 | 8008 | 4 (0)| 00:00:01 || 1 | INDEX FULL SCAN | INDEX_TBL_NAME_DEPTNO | 1001 | 8008 | 4 (0)| 00:00:01 |------------------------------------------------------我们看到，因为我们要查询的所有的数据都是在索引中可以提取，因此优化器选择了INDEX FULL SCAN的方式来获取数据，这里，如果没有排序操作的话，Oracle就会直接从索引中进行提取数据了，没有排序操作的INDEX FULL SCAN就是INDEX FAST FULL SCAN，如下面的执行计划：ChenZw> select name,deptno from tbl;已选择1001行。已用时间: 00: 00: 00.03执行计划----------------------Plan hash value: 3591538465----------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------| 0 | SELECT STATEMENT | | 1001 | 8008 | 3 (0)| 00:00:01 || 1 | INDEX FAST FULL SCAN| INDEX_TBL_NAME_DEPTNO | 1001 | 8008 | 3 (0)| 00:00:01 |---------------------------------------------------------上面的结果就是没有排序，并且所有的数据都是可以直接从索引中获取的执行计划。
删除现在的表，然后再来一遍：drop table tbl;
CREATE TABLE TBL( USERID NUMBER(20), NAME VARCHAR2(20), DEPTNO NUMBER(20));--模拟数据BEGIN FOR I IN 0..1000 LOOP INSERT INTO TBL VALUES( I,CONCAT('TBL',I),MOD(I,2)); END LOOP;END;/SELECT * FROM TBL;
CREATE INDEX INDEX_TBL_NAME_DEPTNO ON TBL(NAME,DEPTNO);
注意,在这里我们没有对name和deptno设置not null属性,我们看看现在的执行计划,为了保证这个表的索引是正确的,我们执行下面的语句,对表进行表分析:analyze table tbl compute statistics for table for all indexed columns;执行之后,执行一下语句,然后看一下执行计划:ChenZw> select name,deptno from tbl;已选择1001行。已用时间: 00: 00: 00.03执行计划----------------------Plan hash value: 2144214008--------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------| 0 | SELECT STATEMENT | | 1001 | 8008 | 3 (0)| 00:00:01 || 1 | TABLE ACCESS FULL| TBL | 1001 | 8008 | 3 (0)| 00:00:01 |--------------------------------------看到上面是没有用到索引的,然后看一下排序的结果呢?ChenZw> select name,deptno from tbl order by name,deptno;已选择1001行。已用时间: 00: 00: 00.03执行计划----------------------Plan hash value: 2469592408---------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------| 0 | SELECT STATEMENT | | 1001 | 8008 | 4 (25)| 00:00:01 || 1 | SORT ORDER BY | | 1001 | 8008 | 4 (25)| 00:00:01 || 2 | TABLE ACCESS FULL| TBL | 1001 | 8008 | 3 (0)| 00:00:01 |---------------------------------------上面也没有用到索引!这个是为什么呢?因为如果索引项建立在属性为空的列,或者可能为空的列上,优化器是用不到这个索引的,这个也是为什么明明建立了索引,但是却没有产生效果的原因之一。让我们来验证一下吧：将name和deptno的列修改为非空属性的：alter table tbl modify（name not null);alter table tbl modify (deptno not null);这个时候,继续执行上面的两个SQL看一下执行计划:ChenZw> select name,deptno from tbl;已选择1001行。已用时间: 00: 00: 00.03执行计划----------------------Plan hash value: 3591538465
----------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------| 0 | SELECT STATEMENT | | 1001 | 8008 | 3 (0)| 00:00:01 || 1 | INDEX FAST FULL SCAN| INDEX_TBL_NAME_DEPTNO | 1001 | 8008 | 3 (0)| 00:00:01 |----------------------------------------------------------和下面的这个SQL的结果:ChenZw> select name,deptno from tbl order by name,deptno;已选择1001行。已用时间: 00: 00: 00.03执行计划----------------------Plan hash value: 2449324302
------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------| 0 | SELECT STATEMENT | | 1001 | 8008 | 4 (0)| 00:00:01 || 1 | INDEX FULL SCAN | INDEX_TBL_NAME_DEPTNO | 1001 | 8008 | 4 (0)| 00:00:01 |------------------------------------------------------现在可以看到，上面的执行计划中，已经正确的使用了索引了，因此上述给出的一个提议就是：在建立索引来解决效率问题的时候，一定要检查一下建立索引的列应该是NOT NULL的。
