VMCD.ORG » 11g new feature Index Invisible

11g为我们提供了Index Invisible特性，使用这个特性可以轻松的将index置为不可用状态，对比之前的将索引置为unusable
最大的改进是，当index设置为unusable时，索引将不再随着表数据的更新而更新，导致如果重新使用index 需要rebuild等操作
这对大型的OLTP系统是极大的挑战，而invisible则不然

下面做个测试：

SQL> create table t_test1 as select * from all_objects;

Table created.

SQL> create index t_test1_idx on t_test1(object_name)invisible ;

Index created.

SQL> analyze table t_test1 compute statistics for table for all indexes;

Table analyzed.

SQL> set autot trace exp

SQL> select * from t_test1 where object_name like ‘T_%’;

Execution Plan
———————————————————-
Plan hash value: 1883417357

—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1805 | 170K| 137 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T_TEST1 | 1805 | 170K| 137 (1)| 00:00:02 |
—————————————————————————–

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

1 – filter(“OBJECT_NAME” LIKE ‘T_%’)

SQL>

此时CBO走了全表扫描

SQL> alter index t_test1_idx visible;

Index altered.

将index置为 visible

SQL> select * from t_test1 where object_name like ‘T_%’;

Execution Plan
———————————————————-
Plan hash value: 3466041839

——————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————————-
| 0 | SELECT STATEMENT | | 1805 | 170K| 135 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST1 | 1805 | 170K| 135 (0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | T_TEST1_IDX | 325 | | 3 (0)| 00:00:01 |
——————————————————————————————-

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

2 – access(“OBJECT_NAME” LIKE ‘T_%’)
filter(“OBJECT_NAME” LIKE ‘T_%’)

还有一种方法可以使CBO 使用到index

SQL> alter index t_test1_idx invisible;

Index altered.

SQL> alter session set optimizer_use_invisible_indexes=true;

Session altered.

SQL> select * from t_test1 where object_name like ‘T_%’;

Execution Plan
———————————————————-
Plan hash value: 3466041839

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

2 – access(“OBJECT_NAME” LIKE ‘T_%’)
filter(“OBJECT_NAME” LIKE ‘T_%’)

通过修改 optimizer_use_invisible_indexes同样可以强制使用 invisible index,同样需要注意的是即使使用hint 如果index 为 invisible hint将无法生效。

Beginning with Release 11g, you can create invisible indexes. An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level. Making an index invisible is an alternative to making it unusable or dropping it. Using invisible indexes, you can do the following:

Test the removal of an index before dropping it.

Use temporary index structures for certain operations or modules of an application without affecting the overall application.

Unlike unusable indexes, an invisible index is maintained during DML statements.