ITPub博客

首页 > 数据库 > Oracle > Oracle 索引的可见与隐藏(visible/invisible)

Oracle 索引的可见与隐藏(visible/invisible)

原创 Oracle 作者:yhdmy 时间:2015-06-09 17:00:24 0 删除 编辑
官方文档: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)


总结:早期版本没有该特性,如果想去掉索引进行测试,之后又想恢复索引,只能先将索引置为不能用或删除。过后在rebuild index,需要花费不少时间,而且有可能会影响到业务工作效率。学习了该特性对以后优化SQL测试有很大帮助。

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

请登录后发表评论 登录
全部评论
曾就职于铁道科学研究院,太极计算机公司,合力中税。负责过国家电网数据库部署、迁移、升级、灾备等实施规划工作。大唐发电厂、北京市公安局数据库运维及优化工作。现任金融工场高级DBA岗位,负责oracle、mysql数据库相关工作。拥有Oracle技术10g/11g的 OCP与OCM认证,Oracle YEP成员,OCM联盟成员。

注册时间:2013-04-22

  • 博文量
    106
  • 访问量
    1573903