Oracle中含常數的複合索引

原來對於索引的認識只知道索引可以基於一個或者多個列，B-Tree索引不包含null，但有些情況下我們又需要通過where 列名 is null來尋找一些資料，這時候資料庫由於沒辦法使用索引就會使用全表掃描，導致執行效率低下，這時候我們可以通過使用含常數的複合索引來解決這個問題。

下面開始進行實驗：

首先建立測試表

SYS@ORCL>create table test_objects nologging as select rownum id,a.* from dba_objects a where 1=2;

Table created.

插入500萬條資料：

SYS@ORCL>declare

 l_cnt number;

 l_rows number:=&1;

begin

 insert /*+ append */ into test_objects select rownum,a.* from dba_objects a;

 l_cnt:=sql%rowcount;

 commit;

 while(l_cnt<l_rows)

 loop

   insert /*+ append */ into test_objects select rownum+l_cnt,

          owner,object_name,subobject_name,

          object_id,data_object_id,

          object_type,created,last_ddl_time,

          timestamp,status,temporary,

          generated,secondary

          from test_objects

          where rownum<=l_rows-l_cnt;

          l_cnt:=l_cnt+sql%rowcount;

    commit;

  end loop;

end;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21  

22  /

Enter value for 1: 5000000

old   3:   l_rows number:=&1;

new   3:   l_rows number:=5000000;

PL/SQL procedure successfully completed.

在object_id列上建立一般的B-Tree索引：

SYS@ORCL>create index idx_oid_test_objects on test_objects(object_id);

Index created.

收集表資訊：

SYS@ORCL>exec dbms_stats.gather_table_stats('SYS','TEST_OBJECTS');    

PL/SQL procedure successfully completed.

測試一下索引能否正常使用：

SYS@ORCL>select count(*) from test_objects where object_id=52457;

 COUNT(*)

----------

       99

Execution Plan

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

Plan hash value: 3877533889

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

----------

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

Time     |

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

----------

|   0 | SELECT STATEMENT  |                      |     1 |     5 |     3   (0)|

00:00:01 |

|   1 |  SORT AGGREGATE   |                      |     1 |     5 |            |

        |

|*  2 |   INDEX RANGE SCAN| IDX_OID_TEST_OBJECTS |    99 |   495 |     3   (0)|

00:00:01 |

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

----------

Predicate Information (identified by operation id):

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

  2 - access("OBJECT_ID"=52457)

Statistics

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

         1  recursive calls

         0  db block gets

         3  consistent gets

         0  physical reads

         0  redo size

       411  bytes sent via SQL*Net to client

       385  bytes received via SQL*Net from client

         2  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

         1  rows processed

可以看到索引可以正常使用，下面我們來進入正題，當where條件中為object_id is null的時候會如何：

SYS@ORCL>select count(*) from test_objects where object_id is null;

 COUNT(*)

----------

        0

Execution Plan

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

Plan hash value: 3799704240

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

---

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

 |

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

---

|   0 | SELECT STATEMENT   |              |     1 |     5 | 16612   (2)| 00:03:2

0 |

|   1 |  SORT AGGREGATE    |              |     1 |     5 |            |

 |

|*  2 |   TABLE ACCESS FULL| TEST_OBJECTS |     1 |     5 | 16612   (2)| 00:03:2

0 |

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

---

Predicate Information (identified by operation id):

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

  2 - filter("OBJECT_ID" IS NULL)

Statistics

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

         1  recursive calls

         0  db block gets

     74808  consistent gets

     74730  physical reads

       904  redo size

       410  bytes sent via SQL*Net to client

       385  bytes received via SQL*Net from client

         2  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

         1  rows processed

因為B-Tree索引不會記錄null所以只能使用全表掃描。產生了大量的consistent gets下面我們來建立含常數的複合索引，並查詢執行：

SYS@ORCL>create index idx_oid2_test_objects on test_objects(object_id,1);

Index created.

SYS@ORCL>select count(*) from test_objects where object_id is null;

 COUNT(*)

----------