不可見索引(invisible index)在表DML操作過程中依然被維護

atlantisholic發表於2012-07-03
索引Index
 在文章《【INDEX】11g中利用不可見索引降低索引維護時對系統的衝擊》（http://space.itpub.net/519536/viewspace-662238）中介紹了關於11g的新特性“不可見索引”，這項新技術給出了在不刪除索引的情況下來判斷有無索引對SQL語句執行計劃的影響，以便輔助判斷索引是否可以被真正的刪除。在使用這項技術的過程中，需要注意一點：雖然索引被標識為“不可見”，這僅僅指的是索引本身在生成SQL執行計劃時不會考慮使用相應索引，但是，索引本身還是會隨表的變化而進行進行維護，這裡給出實驗來說明。

1.演示不可見索引使用方法
1）當使用普通索引時的SQL執行計劃
sys@ora11g> conn sec/sec
Connected.
sec@ora11g> create table t as select * from all_objects;

Table created.

sec@ora11g> create index i_t on t(object_id);

Index created.

sec@ora11g> set autot traceonly exp
sec@ora11g> select * from t where OBJECT_ID = 666;

Execution Plan
----------------------------------------------------------
Plan hash value: 2928007915

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |   158 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |   158 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_T  |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=666)

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

可見，此時SQL語句使用的是I_T這個索引構造的SQL執行計劃。

2）將索引調整為不可見索引後檢視SQL語句執行計劃
sec@ora11g> alter index i_t invisible;

Index altered.

sec@ora11g> select * from t where OBJECT_ID = 666;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    11 |  1738 |   285   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |    11 |  1738 |   285   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=666)

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

此時，雖然索引依然存在，但是構造SQL語句的執行計劃的時候不再考慮索引，這裡使用的是T表全表掃描方式獲取資料。

2.檢視索引的使用情況
sec@ora11g> analyze index I_T validate structure;

Index analyzed.

sec@ora11g> set autot off
sec@ora11g> select height, lf_blks, br_blks, btree_space from index_stats;

    HEIGHT    LF_BLKS    BR_BLKS BTREE_SPACE
---------- ---------- ---------- -----------
         2        158          1     1271396

此時索引使用了158個葉子塊。

3.向T表插入資料
sec@ora11g> insert into t select * from all_objects;

71463 rows created.

這裡先不提交事務。

4.重新獲取索引的使用情況
sec@ora11g> analyze index I_T validate structure;

Index analyzed.

sec@ora11g> select height, lf_blks, br_blks, btree_space from index_stats;

    HEIGHT    LF_BLKS    BR_BLKS BTREE_SPACE
---------- ---------- ---------- -----------
         2        321          1     2574744

可見，此時索引佔用了321個葉子塊，幾乎是原先的的2倍。

5.回滾事務後再次查詢索引使用情況
sec@ora11g> rollback;

Rollback complete.

sec@ora11g> analyze index I_T validate structure;

Index analyzed.

sec@ora11g> select height, lf_blks, br_blks, btree_space from index_stats;

    HEIGHT    LF_BLKS    BR_BLKS BTREE_SPACE
---------- ---------- ---------- -----------
         2        321          1     2574744

顯然，曾經被分配的索引塊和空間沒有被釋放（這與索引特點相關）。

6.小結
  本文通過實驗給出了在表內容發生變化的過程中，不可見索引是隨著表的內容變化而變化的。這一點在使用不可見索引的過程中一定要多加註意。當使用不可見索引技術判斷一個索引的確不再需要的時候，請儘快找到合適的維護視窗將其刪除掉，否則會因多餘的索引導致DML操作效能低下。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23071790/viewspace-734457/，如需轉載，請註明出處，否則將追究法律責任。

相關文章