Oracle 11g 新特性——隐式索引（Invisible Index）

Oracle 11g 开始，可以创建隐式索引。优化器会忽略隐式索引，除非在系统或者会话级别设置初始化参数OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE ，该参数默认是FALSE。隐式索引用来替代使索引不可用或者删除索引。通过隐式索引，可以完成如下操作：

（1）       在删除索引之前测试要删除的索引。

（2）       对运用程序特定的操作或模块使用临时索引结构，从而不影响整个运用。

与不可用索引不同，隐式索引在执行DML期间仍然会维护。

创建隐式索引：

CREATE   INDEX    index_name     ON  TABLE_NAME ( column_name )      INVISIBLE;

 

或者通过使用ALTER      INDEX命令修改：

ALTER       INDEX       index_name            INVISIBLE;

ALTER       INDEX       index_name            VISIBLE;

 

通过*_INDEXES数据字典视图的VISIBILITY         列来确定索引是VISIBLE还是INVISIBLE。

 

 

 

示例：

----创建测试表：

SQL> create  table ii_test (

  2  i  number )

  3  ;

 

Table created.

----插入测试数据：

SQL> begin

  2  for  i in 1.. 10000 loop

  3    insert into ii_test values (i);

  4  end loop;

  5  commit;

  6  end;

  7  /

 

PL/SQL procedure successfully completed.

 

----创建隐式索引：

SQL>  create index ii_test_id on ii_test(i) invisible;

 

Index created.

 

----收集统计信息：

SQL>EXEC         DBMS_STATS.GATHER_TABLE_STATS(ownname=>'test',tabname=>'ii_test',cascade=>true);

 

PL/SQL procedure successfully completed.

 

----where条件查询：

SQL> set autot trace exp

SQL> select * from ii_test where i=580;

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 4113430834

 

-----------------------------------------------------------------------------

| Id  | Operation      | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |          |         1 |           4 |           7   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| II_TEST |          1 |           4 |           7   (0)| 00:00:01 |

-----------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("I"=580)

 

----在会话级修改OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE 并在次测试：

SQL> alter session set OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;

 

Session altered.

 

SQL> select * from ii_test where i=580;

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3143506905

 

-------------------------------------------------------------------------------

| Id  | Operation    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |                |     1 |     4 |     1 (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| II_TEST_ID |     1 |     4 |     1        (0)| 00:00:01 |

-------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - access("I"=580)

 

----设置OPTIMIZER_USE_INVISIBLE_INDEXES = FALSE，并使用hint测试：

SQL> alter session set OPTIMIZER_USE_INVISIBLE_INDEXES = FALSE ;

 

Session altered.

 

SQL> select /*+index(test  ii_test_id)*/ * from ii_test where i=580;

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 4113430834

 

-----------------------------------------------------------------------------

| Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |      1 |     4 |     7   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| II_TEST |      1 |     4 |     7   (0)| 00:00:01 |

-----------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("I"=580)

由此可见对于invisible 的索引，hint 无效；

----将索引改为visible测试：

SQL> alter index ii_test_id visible;

 

Index altered.

 

SQL>  select * from ii_test where i=580;

 

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3143506905

 

-------------------------------------------------------------------------------

| Id  | Operation   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |        |     1 |     4 |     1  (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| II_TEST_ID |     1 |     4 |     1  (0)| 00:00:01 |

-------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - access("I"=580)

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23161469/viewspace-751274/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23161469/viewspace-751274/