Oracle索引的可见与隐藏（visible/invisible）

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Oracle索引的可见与隐藏（visible/invisible），小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4337字，纯文字阅读大概需要7分钟。

内容图文

一个不可见的索引在优化器中被忽视，除非你主动地在会话或系统级别中设置OPTIMIZER_USE_INVISIBLE_INDEXES初始化参数为TRUE。标记

官方文档：Making an Index Invisible

An invisible index 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. You cannot make an individual index partition invisible. Attempting to do so produces an error.

解释：一个不可见的索引在优化器中被忽视，除非你主动地在会话或系统级别中设置OPTIMIZER_USE_INVISIBLE_INDEXES初始化参数为TRUE。标记不可见索引可以替代不可用索引或删除索引。你不能让分区索引不可见。试图这么做会产生一个错误。

测试：

1. 创建测试表ti，根据dba_objects表。

scott@ORCL>create table ti as select * from dba_objects;

Table created.

scott@ORCL>select count(*) from ti;

COUNT(*)

----------

72799

2. 根据object_id列创建索引ind_ti

scott@ORCL>create index ind_ti on ti(object_id);

Index created.

3. 调整为查看执行计划

scott@ORCL>set autot trace exp

4. 测试索引是否生效被使用，结果索引被正常应用

scott@ORCL>select * from ti where object_id=20;

Execution Plan

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

Plan hash value: 1655810896

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

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

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

| 0 | SELECT STATEMENT | | 1 | 207 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TI | 1 | 207 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IND_TI | 1 | | 1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID"=20)

Note

-----

- dynamic sampling used for this statement (level=2)

5. 将ind_ti索引改为不可见

scott@ORCL>alter index ind_ti invisible;

6. 再次测试时，索引没有被使用

scott@ORCL>select * from ti where object_id=20;

Execution Plan

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

Plan hash value: 798420002

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

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

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

| 0 | SELECT STATEMENT | | 12 | 2484 | 291 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| TI | 12 | 2484 | 291 (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

1 - filter("OBJECT_ID"=20)

Note

-----

- dynamic sampling used for this statement (level=2)

7. 数据修改

scott@ORCL>insert into ti select * from ti;

72799 rows created.

scott@ORCL>insert into ti select * from ti;

145598 rows created.

scott@ORCL>select count(*) from ti;

COUNT(*)

----------

291196

8. 将索引改为可见状态

scott@ORCL>alter index ind_ti visible;

Index altered.

9. 测试索引是否生效被使用，，结果索引被正常应用

scott@ORCL>set autot trace exp

scott@ORCL>select * from ti where object_id=20;

Execution Plan

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

Plan hash value: 1655810896

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

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

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

| 0 | SELECT STATEMENT | | 4 | 828 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TI | 4 | 828 | 2 (0)| 00:00:01 |

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

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

Predicate Information (identified by operation id):

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

2 - access("OBJECT_ID"=20)

Note

-----

- dynamic sampling used for this statement (level=2)

内容总结

以上是互联网集市为您收集整理的Oracle索引的可见与隐藏（visible/invisible）全部内容，希望文章能够帮你解决Oracle索引的可见与隐藏（visible/invisible）所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---