null value index

*** Create a table based on dba_tables
SQL> CREATE TABLE test_nulls AS SELECT * FROM dba_tables;
Table created.
*** Note the PCT_FREE column conatins 50 null values
SQL> SELECT COUNT(*) FROM test_nulls WHERE PCT_FREE IS NULL;
 COUNT(*)
----------
 50
*** create an index on the PCT_FREE column
SQL> CREATE INDEX test_nulls_i ON test_nulls(pct_free);
Index created.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'BOWIE', tabname=>'TEST_NULLS', estimate_percent=>NULL, 
cascade=>true, method_opt=>'FOR ALL COLUMNS SIZE 1');
PL/SQL procedure successfully completed.
*** Perform a search for all NULL values
SQL> SELECT * FROM test_nulls WHERE pct_free IS NULL;
50 rows selected.
Execution Plan
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time 
|
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 10250 | 12 (0) | 00:00:01 |
|* 1 | TABLE ACCESS FULL | TEST_NULLS | 50 | 10250 | 12 (0) | 00:00:01 |
---------------------------------------------------------------------------------------------------
*** Note the index is ignored
SQL> SELECT /*+ INDEX(tn, test_nulls_i) */ * FROM test_nulls tn WHERE pct_free IS NULL;
50 rows selected.
Execution Plan
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time 
|
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 10250 | 12 (0) | 00:00:01 |
|* 1 | TABLE ACCESS FULL | TEST_NULLS | 50 | 10250 | 12 (0) | 00:00:01 |
---------------------------------------------------------------------------------------------------
*** Hints do no good. A block dump on the index shows that the NULL values are not indexed
*** However, create a concatenated index with the OWNER column, which has a NOT NULL constraint ...
SQL> CREATE INDEX test_nulls_i2 ON test_nulls(pct_free, owner) COMPUTE STATISTICS;
Index created.
SQL> SELECT * FROM test_nulls WHERE pct_free IS NULL;
50 rows selected.
Execution Plan
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |Cost (%CPU) | Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 10250 | 6 (0) | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST_NULLS | 50 | 10250 | 6 (0) | 00:00:01 |
|* 2 | INDEX RANGE SCAN | TEST_NULLS_I2 | 50 | | 2 (0) | 00:00:01 |
------------------------------------------------------------------------------------------------------
*** And we see the index is now used 

*** However, create another index with just a space ' ' tagged 

这里再补充一个， CREATE INDEX test_nulls_i2 ON test_nulls(,owner,pct_free);

SELECT COUNT(*) FROM test_nulls WHERE PCT_FREE IS NULL;

默认是不会走这个Index：

SQL>  SELECT  * FROM sajet.test_nulls tn WHERE pct_free IS NULL;
50 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4225836326
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |    50 | 10500 |    18   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST_NULLS |    50 | 10500 |    18   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("PCT_FREE" IS NULL)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         76  consistent gets
          0  physical reads
          0  redo size
       6593  bytes sent via SQL*Net to client
        525  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         50  rows processed

可以强制走index，消耗的资源会比FULL Table小：

SQL>  SELECT /*+ INDEX(tn, test_null_i2) */ * FROM sajet.test_nulls tn WHERE pct_free IS NULL
50 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1164047204

--------------------------------------------------------------------------------
------------


| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)
| Time     |


--------------------------------------------------------------------------------
------------


|   0 | SELECT STATEMENT            |              |    50 | 10500 |    12   (0)
| 00:00:01 |


|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_NULLS   |    50 | 10500 |    12   (0)
| 00:00:01 |


|*  2 |   INDEX FULL SCAN           | TEST_NULL_I2 |    50 |       |     8   (0)
| 00:00:01 |


--------------------------------------------------------------------------------
------------




Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("PCT_FREE" IS NULL)
       filter("PCT_FREE" IS NULL)




Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         19  consistent gets
          0  physical reads
          0  redo size
       6582  bytes sent via SQL*Net to client
        525  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         50  rows processed