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 

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