Understand When to Use Invisible Indexes

最新推荐文章于 2025-08-31 21:07:58 发布

原创最新推荐文章于 2025-08-31 21:07:58 发布 · 195 阅读

0 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

91 篇文章

订阅专栏

最近遇到一个性能问题，发现在检索一张1TB的表消耗很多时间。仔细查看比对前后执行计划，发现Oracle 优化器选择了使用index7,以前都是选择index2. 当使用 index7执行时间很长很长。由于这张表被很多其他下游使用，索引不能删除。

所以处理操作就是让这个索引index7 invisiable for the particular session.

 ALTER INDEX index7 INVISIBLE;

EXEC DBMS_STATS.gather_table_stats(USER, 'TABLE_NAME', cascade=> TRUE);

check PLAN choose index7 or not

当某个Session需要用到index7 可以设置 set the OPTIMIZER_USE_INVISIBLE_INDEXES TRUE

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXS=TRUE;

check SQL PLAN choose index7 or not

https://docs.oracle.com/cd/E11882_01/server.112/e25494/indexes.htm#ADMIN13279

Understand When to Use Unusable or Invisible Indexes

Use unusable or invisible indexes when you want to improve the performance of bulk loads, test the effects of removing an index before dropping it, or otherwise suspend the use of an index by the optimizer.

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.

To make an index invisible:

Submit the following SQL statement:
```
ALTER INDEX index INVISIBLE;
```

To make an invisible index visible again:

Submit the following SQL statement:
```
ALTER INDEX index VISIBLE;
```

To determine whether an index is visible or invisible:

Query the dictionary views USER_INDEXES, ALL_INDEXES, or DBA_INDEXES.

For example, to determine if the index ind1 is invisible, issue the following query:
```
SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES
   WHERE INDEX_NAME = 'IND1';

INDEX_NAME   VISIBILITY
----------   ----------
IND1         VISIBLE
```

当某个子查询中需要用到invisble 索引可以使用hint 去提示优化器。避免session level 的限制或者数据库层的限制。/*+USE_INVISIBLE_INDEXES*/