Oracle11新特性——在线操作功能增强之新增的不可见索引

不在正式环境中进行添加、删除操作，很难了解索引对执行计划的影响。而在正式环境中添加、删除索引，又很容易影响其他SQL的执行计划，从而导致系统出现性能问题。 Oracle11g新增了INVISIBLE INDEX功能，可以新增一个不可见的索引，或者将目前的索引变为不可见。除非指定了参数，否则优化器不会使用这种状态的索引。

11g测试：

CREATE INDEX ptest1_IDX1 ON ptest1 (vl_1);

select * from ptest1 where vl_1=1

 

SELECT STATEMENT, GOAL = ALL_ROWS   2 1 7
 TABLE ACCESS BY INDEX ROWID YUZH PTEST1 2 1 7
  INDEX RANGE SCAN YUZH PTEST1_IDX1 1 1 

 

ALTER INDEX ptest1_IDX1 INVISIBLE; 

 

select * from ptest1 where vl_1=1

 

SELECT STATEMENT, GOAL = ALL_ROWS   3 1 7
 TABLE ACCESS FULL YUZH PTEST1 3 1 7

ALTER INDEX ptest1_IDX1 VISIBLE; 

 

select * from ptest1 where vl_1=1

 

SELECT STATEMENT, GOAL = ALL_ROWS   2 1 7
 TABLE ACCESS BY INDEX ROWID YUZH PTEST1 2 1 7
  INDEX RANGE SCAN YUZH PTEST1_IDX1 1 1 

 

由上例看出在设置索引状态为INVISIBLE后，执行计划变更，修改为VISIBLE后执行计划又使用了改索引

当然也有强制所用索引的参数OPTIMIZER_USE_INVISIBLE_INDEXES，不管是否可见索引都会被执行计划考虑

 

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;
 
Session altered
 
SQL> ALTER INDEX ptest1_IDX1 INVISIBLE;
 
Index altered
 
SQL> explain plan for select * from ptest1 where vl_1=1;
 
Explained
 
SQL> select * from table(dbms_xplan.display);

 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3585511124
--------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |     7 |     2   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID| PTEST1      |     1 |     7 |     2   (0)|
|*  2 |   INDEX RANGE SCAN          | PTEST1_IDX1 |     1 |       |     1   (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("VL_1"=1)
 
14 rows selected
 
USER_INDEXES的新增字段VISIBILITY字段来查看索引的可见性

 

SQL> select INDEX_NAME, VISIBILITY  from user_indexes;
 
INDEX_NAME                     VISIBILITY
------------------------------ ----------
PTEST1_IDX1                    INVISIBLE

 

 

 

利用不可见索引可以在不影响其他会话的情况下查看索引建立后的效果。
在删除索引之前可以将索引先至于不可见状态，这样一旦发现索引不应该被删除，索引的恢复将会十分迅速。