Oracle 11g新特性:索引不可见
来源:互联网 发布:中国科学院软件研究所 编辑:程序博客网 时间:2024/05/17 22:36
【IT168 技术文档】索引维护是DBA的一项重要工作。当一个系统运行很长一段时间,经过需求变更、结构设计变化后,系统中就可能会存在一些不会被使用的索引,或者使用效率很低的索引。这些索引的存在,不仅占用系统空间,而且会降低事务效率,增加系统的waits。因此,我们需要找出那些无用或低效索引的索引并删除它们(找出无用索引可以通过索引监控的方法)。
但是,直接删除索引还是存在一定风险的。例如,某些索引可能只是在一些周期的作业中被使用到,而如果监控周期没有覆盖到这些作业的触发点,就会认为索引是无用的而被删除。当作业启动后,可能就会对系统性能造成冲击。这时,可能就会手忙脚乱的去找回索引定义语句、重建索引。
11g之前,我们可以先不删除索引,而将其修改为unusable。这样的话,索引的定义并未删除,只是索引不能再被使用也不会随着表数据的更新而更新。当需要重新使用该索引时,需要用rebuild语句重建、然后更新统计信息。对于一些大表来说,这个时间可能就非常长。
在11g里,oracle提供了一个新的特性来降低直接删除索引或者禁用索引的风险,那就是索引不可见(Index Invisible)。我们可以在创建索引时指定invisible属性或者用alter语句来修改索引为invisible。
SQL代码
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
SQL> create index t_test1_idx on t_test1(table_name) invisible;
Index created.
SQL> alter index t_test1_idx invisible;
Index altered.
当索引被设为不可见后,实际上就是指该索引对于优化器不可见,而索引的正常更新并不受影响——即表在增、删、改时,索引也会被更新。只是当优化器在选择查询计划时会“无视”该索引(无论是CBO或RBO):
SQL代码
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
SQL> create table t_test1 as select * from dba_tables;
Table created.
SQL> create index t_test1_idx on t_test1(table_name);
Index created.
SQL> analyze table t_test1 compute statistics for table for allindexes;
Table analyzed.
SQL> set autot trace exp
SQL> select * from t_test1 where table_name like 'HR%';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3466041839
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 126 | 27468 | 14 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST1 | 126 | 27468 | 14 (0)|00:00:01 |
|* 2 | INDEX RANGE SCAN | T_TEST1_IDX | 23 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TABLE_NAME" LIKE 'HR%')
filter("TABLE_NAME" LIKE 'HR%')
SQL> alter index t_test1_idx invisible;
Index altered.
SQL> select * from t_test1 where table_name like 'HR%';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1883417357
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 126 | 27468 | 25 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_TEST1 | 126 | 27468 | 25 (0)| 00:00:01|
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TABLE_NAME" LIKE 'HR%')
如果我们需要重新使该所有有效,只需要再将其改为visible就可以了——这个过程不是重建,仅仅是修改索引的一个属性,非常快!
SQL代码
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
SQL> set timing on
SQL> alter index t_test1_idx visible;
Index altered.
Elapsed: 00:00:00.01
当然,当索引被设为不可见时,并非完全不可用。可以通过修改参数optimizer_use_invisible_indexes为true(默认为false,system级别和session级别都可以):
SQL代码
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
SQL> select index_name, visibility from user_indexes whereindex_name='T_TEST1_IDX';
INDEX_NAME VISIBILIT
------------------------------ ---------
T_TEST1_IDX INVISIBLE
SQL>
SQL> alter session set optimizer_use_invisible_indexes=true;
Session altered.
SQL> set autot trace exp
SQL> select * from t_test1 where table_name like 'HR%';
Execution Plan
----------------------------------------------------------
Plan hash value: 3466041839
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 126 | 27468 | 14 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_TEST1 | 126 | 27468 | 14 (0)|00:00:01 |
|* 2 | INDEX RANGE SCAN | T_TEST1_IDX | 23 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TABLE_NAME" LIKE 'HR%')
filter("TABLE_NAME" LIKE 'HR%')
这里还需要指出一点,当索引不可见时,只能通过上面的参数使之在查询计划中可用,即使通过HINT也无法改变。
SQL代码
Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/
SQL> select /*+ index(t_test1 T_TEST1_IDX) */ * from t_test1 t wheretable_name like 'HR%';
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1883417357
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 126 | 27468 | 25 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_TEST1 | 126 | 27468 | 25 (0)| 00:00:01|
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("TABLE_NAME" LIKE 'HR%')
在官方文档上对这一特性的说明中也只有提到该参数才起作用:
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.
- Oracle 11g新特性:索引不可见
- Oracle 11g 新特性 -- Invisible Indexes(不可见的索引) 说明
- Oracle 11g 新特性 -- Invisible Indexes(不可见的索引) 说明
- Oracle 11g新特性invisible index(不可见的索引)
- 11g新特性_索引的可见与不可见
- Oracle Study---Oracle 11g 不可见索引案例
- Oracle11g新特性-不可见索引
- Oracle 11g 新特性 -- 虚拟索引!
- Oracle 11g之不可见的索引
- oracle 不可见索引
- oracle 12c:新特性-不可见字段
- oracle 12c 新特性之不可见字段
- 【翻译自mos文章】OGG支持oracle db 11g中的不可见索引吗?
- Oracle 11g 新特性
- Oracle 11g新特性
- oracle 11g 新特性
- ORACLE 11G新特性
- ORACLE 11g新特性
- VB.NET FTP登录类
- ibatis配置文件中 parameterClass,resultClass,resultMap 表示的意思
- vlc-android对于通过Live555接收到音视频数据包后的处理分析
- 最大公约数
- 解决 WIN7 SMTP 安装问题
- Oracle 11g新特性:索引不可见
- HNOI2006(BZOJ1191~1197)题解
- 九度1046 求最大值
- Oracle数据库远程连接设置的四种方法
- TLD(Tracking-Learning-Detection)学习与源码理解之(四) .
- pulsonix高速布线--蛇形走线
- BP神经网络学习一
- 图像旋转的OpenCV实现
- ARM汇编align伪指令