索引裡的NULL值與排序小記

table wxh_tbd1 ( a varchar2(100),b varchar2(100) ,c varchar2(200));

Table created.

into wxh_tbd1 select object_type ,status ,NAMESPACE from dba_objects;

index w_i on wxh_tbd1(a,b,c);

Index created.

dbms_stats.gather_table_stats(user,'wxh_tbd1');

PL/SQL procedure successfully completed.

* from wxh_tbd1 where a='TABLE' AND B IS NULL AND C='c' order by c; 

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1269858568

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    15 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| W_I  |     1 |    15 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

* from wxh_tbd1 where a='TABLE' AND B IS NULL AND C>'c' order by c;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 1380261624

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    15 |     2  (50)| 00:00:01 |
|   1 |  SORT ORDER BY    |      |     1 |    15 |     2  (50)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN| W_I  |     1 |    15 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

第二種情況出現了排序。原因是ORACLE傻B的不知道 （NULL,1) 和(NULL,2）誰大誰小，因此第二種查詢就會出現排序，DUMP出索引，索引裡也是亂序的