oracle11g 新特性之不可见索引

最新推荐文章于 2024-02-29 21:02:35 发布

cmcwzce729371

最新推荐文章于 2024-02-29 21:02:35 发布

阅读量267

点赞数

文章标签：数据库

不可见索引是11g引入的新特性，所谓的索引不可见，是语句生成的计划不会使用该索引，但是在物理上时可见的，也存在的，DML语句发生时候照常会进行维护，也就是说处于INVISIBLE状态下的索引并不失效，只是优化器不选择它而已。

在11g之前，也可以先不删除索引，而将其修改为unusable状态。这样的话，索引的定义并未删除，只是索引不能再被使用也不会随着表数据的更新而更新。当需要重新使用该索引时，需要用rebuild语句重建、然后更新统计信息。对于一些大表来说，这个过程可能很长，需要消耗过多的资源。

　在使用不可见索引时可以降低直接删除索引或者禁用索引的风险。

不可见索引有关的参数：

optimizer_use_invisible_indexes

创建不可见索引语句

CREATE INDEXindex_name ON table_name(column_name) INVISIBLE;

修改索引是否可见

ALTER INDEXindex_name INVISIBLE;

ALTER INDEXindex_name VISIBLE;

如下：

SQL>

SQL> create index ACCT_idx on tt_info(ACCT_NO)invisible;

Index created.

SQL> execdbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'TT_INFO',estimate_percent=> 10,method_opt=> 'for all indexed columns') ;

PL/SQL proceduresuccessfully completed.

SQL>

SQL> explain planfor select * from tt_info where ACCT_NO=':no'

2 ;

Explained.

SQL> SELECT *FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT

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

Plan hash value:2467334803

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

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

| 0 | SELECT STATEMENT | | 5 | 685 | 1726 (1)| 00:00:21 |

|* 1 | TABLE ACCESS FULL| TT_INFO | 5| 685 | 1726 (1)| 00:00:21 |

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

PredicateInformation (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

1 - filter("ACCT_NO"=':no')

13 rows selected.

这里使用了全表扫描，

为什么没有走索引那？

索引状态如下：

SQL> select visibility from dba_indexes whereindex_name='ACCT_IDX';

VISIBILIT

---------

INVISIBLE

原来如此。

修改OPTIMIZER_USE_INVISIBLE_INDEXES参数，再次查询：

SQL> alter session set optimizer_use_invisible_indexes=TRUE;(对当前session起作用)

Session altered.

SQL> explain plan forselect * from tt_info where ACCT_NO=':no';

Explained.

SQL> SELECT * FROMTABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT

Plan hash value: 4131614804

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

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

| 0 | SELECT STATEMENT | | 5 | 685 | 8 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TT_INFO| 5 | 685 | 8 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | ACCT_IDX | 5 | | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------------------------PredicateInformation (identified by operation id):

PLAN_TABLE_OUTPUT

2 - access("ACCT_NO"=':no')

14 rows selected.

居然走了索引。

NAME TYPE VALUE

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

optimizer_use_invisible_indexes boolean FALSE

SQL> alter system set optimizer_use_invisible_indexes=true; --全局生效

System altered.

SQL> explain plan for select * from tt_infowhere ACCT_NO=':no';

Explained.

SQL> SELECT * FROMTABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT

Plan hash value: 4131614804

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

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

| 0 | SELECT STATEMENT | | 5 | 685 | 8 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TT_INFO| 5 | 685 | 8 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | ACCT_IDX | 5 | | 3 (0)| 00:00:01 |

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

Predicate Information (identified byoperation id):

PLAN_TABLE_OUTPUT

2 - access("ACCT_NO"=':no')

14 rows selected.

SQL>

SQL> show parameteroptimizer_use_invisible_indexes

NAME TYPE VALUE

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

optimizer_use_invisible_indexes boolean TRUE

SQL>

在官方文档上对这一特性的说明中也只有提到optimizer_use_invisible_indexes 参数才起作用：

　　Beginning withRelease 11g, you can create invisible indexes. An invisible index is an indexthat is ignored by the optimizer unless you explicitly set theOPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the sessionor system level.

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

转载于:http://blog.itpub.net/10201716/viewspace-2144291/