一個查詢不走索引的例子

-- 查詢不走索引的例子，因為不能對NULL進行索引，所有存在null的情況下，查詢不走索引。當指定了索引的欄位為not null的時候，查詢才會走索引。

-- 建立測試用表，並建立索引

SYS@test>create table t11 as select * from dba_objects where object_id is not null;

Table created.

SYS@test>create index idx_object_id11 on t11(object_id);

Index created.

-- 檢視執行計劃，發現是全表掃描，雖然有索引

SYS@test>set autotrace traceonly

SYS@test>select count(*) from t11;


Execution Plan
----------------------------------------------------------
Plan hash value: 4132580237

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   340   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T11  | 98612 |   340   (1)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)
   - SQL plan baseline "SQL_PLAN_5b92zmgvh38s16d6f1a89" used for this statement


Statistics
----------------------------------------------------------
          6  recursive calls
          0  db block gets
       1329  consistent gets
       1245  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

將索引所在的列，修改為not null。 這樣執行計劃就走索引了。

SYS@test>alter table t11 modify object_id not null;

Table altered.

SYS@test>select count(*) from t11;


Execution Plan
----------------------------------------------------------
Plan hash value: 767390840

---------------------------------------------------------------------------------
| Id  | Operation             | Name            | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |     1 |    54   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |                 |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID11 | 98612 |    54   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         31  recursive calls
          4  db block gets
        298  consistent gets
        193  physical reads
        712  redo size
        528  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          1  rows processed

SYS@test>

END。