【INDEX】11g中利用不可見索引降低索引維護時對系統的衝擊

secooler發表於2010-05-07
Index索引
11gR1中推出了“不可見索引”（Invisible Indexes）技術，這個新技術為DBA進行索引維護帶來了非常大的便利。
在11gR1版本之前提供的是“不可用索引”（Unusable indexes）技術，這個技術的不便之處就是，一旦設定了索引為“不可用索引”，所有後續的DML操作將不會在索引上得到體現，也就是說，索引已經失去了存在的價值。而11g的“不可見索引”技術恰恰解決了這個問題，可以隨時設定索引的可見與否，索引仍然維護DML的變化，雖然維護索引需要一定的開銷，但是與其帶來的便利性相比，意義是重大的。

透過一個實驗，看一下“不可見索引”（Invisible Indexes）的建立和使用。

1.建立測試表T
sec@11gR2> create table t as select * from all_objects;

Table created.

sec@11gR2> desc t;
 Name                Null?    Type
 ------------------- -------- ------------------------
 OWNER               NOT NULL VARCHAR2(30)
 OBJECT_NAME         NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME               VARCHAR2(30)
 OBJECT_ID           NOT NULL NUMBER
 DATA_OBJECT_ID               NUMBER
 OBJECT_TYPE                  VARCHAR2(19)
 CREATED             NOT NULL DATE
 LAST_DDL_TIME       NOT NULL DATE
 TIMESTAMP                    VARCHAR2(19)
 STATUS                       VARCHAR2(7)
 TEMPORARY                    VARCHAR2(1)
 GENERATED                    VARCHAR2(1)
 SECONDARY                    VARCHAR2(1)
 NAMESPACE           NOT NULL NUMBER
 EDITION_NAME                 VARCHAR2(30)

2.無索引情況下執行計劃
sec@11gR2> set autot trace exp
sec@11gR2> select * from t where OBJECT_ID = 666;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    17 |  2686 |   285   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |    17 |  2686 |   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)

3.建立“不可見索引”（Invisible Indexes）
sec@11gR2> create index i_t on t(object_id) invisible;

Index created.

4.在具有不可見索引的表T上執行查詢操作
sec@11gR2> select * from t where OBJECT_ID = 666;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    17 |  2686 |   285   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |    17 |  2686 |   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語句採納。索引被忽略。

5.修改“不可見索引”為正常索引，再次檢視執行計劃
sec@11gR2> alter index i_t visible;

Index altered.

sec@11gR2> 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)

可見，此時我們建立的索引I_T已經被使用。

再次將索引修改為“不可見索引”的方法如下：
sec@11gR2> alter index i_t invisible;

Index altered.

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

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    17 |  2686 |   285   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |    17 |  2686 |   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)


6.使用optimizer_use_invisible_indexes引數控制“不可見索引”是否生效
當引數optimizer_use_invisible_indexes為“FALSE”（預設值）時，表示“不可見索引”型別的索引不被最佳化器使用；
當引數optimizer_use_invisible_indexes為“TRUE”（預設值）時，表示“不可見索引”型別的索引與正常所以無異；

關於optimizer_use_invisible_indexes引數介紹資訊請參考Oracle官方文件中的描述：
1）在session級別修改optimizer_use_invisible_indexes為“TRUE”
sec@11gR2> alter session set optimizer_use_invisible_indexes=true;

Session altered.

sec@11gR2> 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)

查詢已經使用索引。

2）在session級別修改optimizer_use_invisible_indexes為“FALSE”
sec@11gR2> alter session set optimizer_use_invisible_indexes=false;

Session altered.

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

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    17 |  2686 |   285   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |    17 |  2686 |   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)

索引不被最佳化器考慮，此時重新選擇全表掃描的方式獲取資料。

7.小結
“不可見”的概念就是，Oracle還是在不停地維護這個索引，只是在SQL執行過程中不再考慮該索引——視索引為無物。
有了這個技術，當我們可以在不同時間段啟用不同的索引，以便比秒索引被反覆刪除和新增。

Good luck.

secooler
10.05.07

-- The End --

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

相關文章