索引的不可视

最新推荐文章于 2024-01-26 12:40:15 发布

dick198244

最新推荐文章于 2024-01-26 12:40:15 发布

阅读量323

点赞数

本文链接：https://blog.csdn.net/dick198244/article/details/40858677

版权

使用alter index index_name invisible可以将索引设置成为不可视（失效），但索引的维护工作还会继续。

例子：

--创建试验表

create table t as select * from dba_objects;

--创建索引(不可视索引)

create index t_ind on t(object_id) invisible;

--查看执行计划

set autotrace on

select * from t where object_id = 2;

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 115 | 426 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 115 | 426 (1)| 00:00:01 |
--------------------------------------------------------------------------

--将索引恢复为可视

alter index t_ind visible;

select * from t where object_id = 2;

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 115 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 115 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IND | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

--也可以使用no_index的方法屏蔽索引

select /*+ no_index(t t_ind) */ * from t where object_id = 2;