验证索引列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。