Oracle11g新特性：在线操作功能增强-Oracle11g新增的不可见索引 （转载）

 添加、删除索引一直是一个比较头痛的问题。不在正式环境中进行添加、删除操作，很难了解索引对执行计划的影响。而在正式环境中添加、删除索引，又很容易影响其他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 

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