Oracle11新特性——線上操作功能增強（四）

打算寫一系列的文章介紹11g的新特性和變化。

11g中線上處理功能得到了很大增強，其中包括線上修改表結構，線上建立或重建索引，建立不可見索引，表增加非空欄位，線上DDL以及物件依賴性細化等。

這一篇介紹Oracle11g新增的不可見索引。

Oracle11新特性——線上操作功能增強（一）：http://yangtingkun.itpub.net/post/468/400430

Oracle11新特性——線上操作功能增強（二）：http://yangtingkun.itpub.net/post/468/401293

Oracle11新特性——線上操作功能增強（三）：http://yangtingkun.itpub.net/post/468/401641

新增、刪除索引一直是一個比較頭痛的問題。不在正式環境中進行新增、刪除操作，很難了解索引對執行計劃的影響。而在正式環境中新增、刪除索引，又很容易影響其他SQL的執行計劃，從而導致系統出現效能問題。

Oracle11g新增了INVISIBLE INDEX功能，可以新增一個不可見的索引，或者將目前的索引變為不可見。除非指定了引數，否則最佳化器不會使用這種狀態的索引。

SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;

表已建立。

SQL> CREATE INDEX IND_T_OWNER ON T (OWNER);

索引已建立。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')

PL/SQL 過程已成功完成。

SQL> SET AUTOT ON EXP
SQL> SELECT COUNT(*) FROM T WHERE OWNER = 'YANGTK';

COUNT(*)
----------
24

執行計劃
----------------------------------------------------------
Plan hash value: 225622394

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 8 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX RANGE SCAN| IND_T_OWNER | 3108 | 18648 | 8 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

2 - access("OWNER"='YANGTK')

SQL> ALTER INDEX IND_T_OWNER INVISIBLE;

索引已更改。

SQL> SELECT COUNT(*) FROM T WHERE OWNER = 'YANGTK';

COUNT(*)
----------
24

執行計劃
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 284 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | TABLE ACCESS FULL| T | 3108 | 18648 | 284 (1)| 00:00:04 |
---------------------------------------------------------------------------

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

2 - filter("OWNER"='YANGTK')

如果希望最佳化器考慮不可見索引，可以在系統級或會話級設定初始化引數：OPTIMIZER_USE_INVISIBLE_INDEXES設定為TRUE：

SQL> SHOW PARAMETER OPTIMIZER_USE_INVISIBLE_INDEXES

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes boolean FALSE
SQL> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;

會話已更改。

SQL> SELECT COUNT(*) FROM T WHERE OWNER = 'YANGTK';

COUNT(*)
----------
24

執行計劃
----------------------------------------------------------
Plan hash value: 225622394

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 8 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX RANGE SCAN| IND_T_OWNER | 3108 | 18648 | 8 (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

2 - access("OWNER"='YANGTK')

可以透過USER_INDEXES的新增欄位VISIBILITY欄位來檢視索引的可見性：

SQL> SET AUTOT OFF
SQL> CREATE INDEX IND_T_NAME ON T (OBJECT_NAME);

索引已建立。

SQL> SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES WHERE TABLE_NAME = 'T';

INDEX_NAME VISIBILIT
------------------------------ ---------
IND_T_NAME VISIBLE
IND_T_OWNER INVISIBLE

利用不可見索引可以在不影響其他會話的情況下檢視索引建立後的效果。

在刪除索引之前可以將索引先至於不可見狀態，這樣一旦發現索引不應該被刪除，索引的恢復將會十分迅速。