您当前的位置:首页 > oracle

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

官方文档:Making an Index InvisiblerhQlinux系统宝典

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.rhQlinux系统宝典

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

测试:rhQlinux系统宝典

1. 创建测试表ti,根据dba_objects表。rhQlinux系统宝典

scott@ORCL>create table ti as select * from dba_objects;rhQlinux系统宝典

Table created.rhQlinux系统宝典

scott@ORCL>select count(*) from ti;rhQlinux系统宝典

  COUNT(*)rhQlinux系统宝典

----------rhQlinux系统宝典

    72799rhQlinux系统宝典

2. 根据object_id列创建索引ind_tirhQlinux系统宝典

scott@ORCL>create index ind_ti on ti(object_id);rhQlinux系统宝典

Index created.rhQlinux系统宝典

3. 调整为查看执行计划rhQlinux系统宝典

scott@ORCL>set autot trace exp rhQlinux系统宝典
 rhQlinux系统宝典

4. 测试索引是否生效被使用,结果索引被正常应用rhQlinux系统宝典

scott@ORCL>select * from ti where object_id=20;rhQlinux系统宝典

Execution PlanrhQlinux系统宝典

----------------------------------------------------------rhQlinux系统宝典

Plan hash value: 1655810896rhQlinux系统宝典

--------------------------------------------------------------------------------------rhQlinux系统宝典

| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time    |rhQlinux系统宝典

--------------------------------------------------------------------------------------rhQlinux系统宝典

|  0 | SELECT STATEMENT            |        |    1 |  207 |    2  (0)| 00:00:01 |rhQlinux系统宝典

|  1 |  TABLE ACCESS BY INDEX ROWID| TI    |    1 |  207 |    2  (0)| 00:00:01 |rhQlinux系统宝典

|*  2 |  INDEX RANGE SCAN          | IND_TI |    1 |      |    1  (0)| 00:00:01 |rhQlinux系统宝典

--------------------------------------------------------------------------------------rhQlinux系统宝典

Predicate Information (identified by operation id):rhQlinux系统宝典

---------------------------------------------------rhQlinux系统宝典

  2 - access("OBJECT_ID"=20)rhQlinux系统宝典

NoterhQlinux系统宝典

-----rhQlinux系统宝典

  - dynamic sampling used for this statement (level=2)rhQlinux系统宝典

 rhQlinux系统宝典

5. 将ind_ti索引改为不可见rhQlinux系统宝典

scott@ORCL>alter index ind_ti invisible; rhQlinux系统宝典
 rhQlinux系统宝典

6. 再次测试时,索引没有被使用rhQlinux系统宝典

scott@ORCL>select * from ti where object_id=20;rhQlinux系统宝典

Execution PlanrhQlinux系统宝典

----------------------------------------------------------rhQlinux系统宝典

Plan hash value: 798420002rhQlinux系统宝典

--------------------------------------------------------------------------rhQlinux系统宝典

| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time    |rhQlinux系统宝典

--------------------------------------------------------------------------rhQlinux系统宝典

|  0 | SELECT STATEMENT  |      |    12 |  2484 |  291  (1)| 00:00:04 |rhQlinux系统宝典

|*  1 |  TABLE ACCESS FULL| TI  |    12 |  2484 |  291  (1)| 00:00:04 |rhQlinux系统宝典

--------------------------------------------------------------------------rhQlinux系统宝典

Predicate Information (identified by operation id):rhQlinux系统宝典

---------------------------------------------------rhQlinux系统宝典

  1 - filter("OBJECT_ID"=20)rhQlinux系统宝典

NoterhQlinux系统宝典

-----rhQlinux系统宝典

  - dynamic sampling used for this statement (level=2)rhQlinux系统宝典

7. 数据修改rhQlinux系统宝典

scott@ORCL>insert into ti select * from ti;rhQlinux系统宝典

72799 rows created.rhQlinux系统宝典

scott@ORCL>insert into ti select * from ti;rhQlinux系统宝典

145598 rows created.rhQlinux系统宝典

scott@ORCL>select count(*) from ti;rhQlinux系统宝典

  COUNT(*)rhQlinux系统宝典

----------rhQlinux系统宝典

    291196rhQlinux系统宝典

8. 将索引改为可见状态rhQlinux系统宝典

scott@ORCL>alter index ind_ti visible;rhQlinux系统宝典

Index altered.rhQlinux系统宝典

9. 测试索引是否生效被使用,结果索引被正常应用rhQlinux系统宝典

scott@ORCL>set autot trace exprhQlinux系统宝典

scott@ORCL>select * from ti where object_id=20;rhQlinux系统宝典

Execution PlanrhQlinux系统宝典

----------------------------------------------------------rhQlinux系统宝典

Plan hash value: 1655810896rhQlinux系统宝典

--------------------------------------------------------------------------------------rhQlinux系统宝典

| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time    |rhQlinux系统宝典

--------------------------------------------------------------------------------------rhQlinux系统宝典

|  0 | SELECT STATEMENT            |        |    4 |  828 |    2  (0)| 00:00:01 |rhQlinux系统宝典

|  1 |  TABLE ACCESS BY INDEX ROWID| TI    |    4 |  828 |    2  (0)| 00:00:01 |rhQlinux系统宝典

|*  2 |  INDEX RANGE SCAN          | IND_TI |    4 |      |    1  (0)| 00:00:01 |rhQlinux系统宝典

--------------------------------------------------------------------------------------rhQlinux系统宝典

Predicate Information (identified by operation id):rhQlinux系统宝典

---------------------------------------------------rhQlinux系统宝典

  2 - access("OBJECT_ID"=20)rhQlinux系统宝典

NoterhQlinux系统宝典

-----rhQlinux系统宝典

  - dynamic sampling used for this statement (level=2)rhQlinux系统宝典

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

更多相关信息见 专题页面 rhQlinux系统宝典



沪ICP备10206494号-4