验证索引列NULL不走索引

今天遇到问题，同事问我，order by 主键列 desc 与order by 日期列(加了索引) desc ，怎么执行时间差距这么大;于是查看执行计划不一样：
order by 主键列 desc 对应 INDEX FULL SCAN DESCENDING
order by 日期列(加了索引) desc 对应 TABLE ACCESS FUL
我加hint /*+ index(table index_name) */ 强制使用索引，计划还是使用的全表扫描，很奇怪为什么没有使用索引呢？我加了hint就不应该是数据问题了吧。
最后明白了原因：主键 not null而日期列可为null,如果将日期列改为not null，执行计划会一致。

总结：Oracle可能因为索引字段列可以为Null而放弃使用索引。以下为INDEX FULL SCAN

SQL> create table null_valid(id int primary key,sid int null,sdate date); Table created. SQL> alter table null_valid drop column sdate; Table altered. SQL> desc dba_objects Name Null? Type ----------------------------------------- -------- ---------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) SQL> insert into null_valid select object_id,object_id from dba_objects where rownum < 100; 99 rows created. SQL> create index idx_sid on null_valid(sid); Index created. SQL> set autot trace explain SQL> select * from null_valid order by id desc; Execution Plan ---------------------------------------------------------- Plan hash value: 1230549944 -------------------------------------------------------------------------------- - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- - | 0 | SELECT STATEMENT | | 99 | 2574 | 4 (25)| 00:00:01 | | 1 | SORT ORDER BY | | 99 | 2574 | 4 (25)| 00:00:01 | | 2 | TABLE ACCESS FULL| NULL_VALID | 99 | 2574 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------- - Note ----- - dynamic sampling used for this statement SQL> exec dbms_stats.gather_table_stats(user,'NULL_VALID'); PL/SQL procedure successfully completed. SQL> select * from null_valid order by id desc; Execution Plan ---------------------------------------------------------- Plan hash value: 600338710 -------------------------------------------------------------------------------- ----------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- ----------- | 0 | SELECT STATEMENT | | 99 | 594 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| NULL_VALID | 99 | 594 | 2 (0)| 00:00:01 | | 2 | INDEX FULL SCAN DESCENDING| SYS_C005257 | 99 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------- ----------- SQL> select * from null_valid order by sid desc; Execution Plan ---------------------------------------------------------- Plan hash value: 1230549944 -------------------------------------------------------------------------------- - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- - | 0 | SELECT STATEMENT | | 99 | 594 | 4 (25)| 00:00:01 | | 1 | SORT ORDER BY | | 99 | 594 | 4 (25)| 00:00:01 | | 2 | TABLE ACCESS FULL| NULL_VALID | 99 | 594 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------- - SQL> select /*+ index(null_valid idx_sid) */ * from null_valid order by sid desc; Execution Plan ---------------------------------------------------------- Plan hash value: 1230549944 -------------------------------------------------------------------------------- - | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- - | 0 | SELECT STATEMENT | | 99 | 594 | 4 (25)| 00:00:01 | | 1 | SORT ORDER BY | | 99 | 594 | 4 (25)| 00:00:01 | | 2 | TABLE ACCESS FULL| NULL_VALID | 99 | 594 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------以上sid字段（可以为null)加了索引，使用hint强制使用索引，计划依然使用全表扫描。

SQL> alter table null_valid modify sid not null; Table altered. SQL> select * from null_valid order by sid desc; Execution Plan ---------------------------------------------------------- Plan hash value: 2266710330 -------------------------------------------------------------------------------- ---------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- ---------- | 0 | SELECT STATEMENT | | 99 | 594 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| NULL_VALID | 99 | 594 | 2 (0)| 00:00:01 | | 2 | INDEX FULL SCAN DESCENDING| IDX_SID | 99 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------以上将sid列改为not null,计划选择使用了INDEX FULL SCAN DESCENDING。

SQL> set linesize 120 SQL> alter table null_valid modify sid null; Table altered. SQL> select * from null_valid order by sid desc; Execution Plan ---------------------------------------------------------- Plan hash value: 1230549944 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 99 | 594 | 4 (25)| 00:00:01 | | 1 | SORT ORDER BY | | 99 | 594 | 4 (25)| 00:00:01 | | 2 | TABLE ACCESS FULL| NULL_VALID | 99 | 594 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------- SQL> select * from null_valid where sid is not null order by sid desc; Execution Plan ---------------------------------------------------------- Plan hash value: 2266710330 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 99 | 594 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| NULL_VALID | 99 | 594 | 2 (0)| 00:00:01 | |* 2 | INDEX FULL SCAN DESCENDING| IDX_SID | 99 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("SID" IS NOT NULL)
以上将sid字段重新改为null，在sql索引字段上添加 is not null WHERE条件,计划选择INDEX FULL SCAN DESCENDING。


总结：索引列为null在有些情况下会影响oracle选择使用该索引，而选择使用全表扫描。在很多情况下应该注意null。