关于oracle 11g中的索引.

最新推荐文章于 2024-09-11 08:43:55 发布

quanhj

最新推荐文章于 2024-09-11 08:43:55 发布

阅读量3k

点赞数

CC 4.0 BY-SA版权

文章标签： oracle user sql session access table

本文链接：https://blog.youkuaiyun.com/quanhj/article/details/6169499

本文介绍了Oracle数据库中Invisible索引的概念及其使用方法。通过设置索引为不可见状态，可以在不影响现有查询的情况下禁用索引，同时避免了重建或删除索引带来的数据迁移成本。文章还展示了如何通过SQL命令使索引变为不可见或重新变为可见，并讨论了Invisible索引对执行计划的影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

如果我们一个表在设计上存在问题如: 当某个索引在当初设计时就存在着缺陷或者不合理时,现在已经产生了几千条几万条时,我们不能直接把他删除掉, 在11g 之前我们只能先把数据转移到一个新表,然后在来删除索引,然而在11g后,oracle新增了一个功能,那就是------invisible index.

当你想要一个索引不可用,但是又不能删除,你可以这样做:

alter index index_name invisible;

把一个不可用索引修改为可用 :
alter index index_name visible;

查看索引是否可用:

SELECT INDEX_NAME, VISIBILITY
FROM USER_INDEXES where index_name = Index_name1 ;

INDEX_NAME     VISIBILITY
------------------    -------------
index_name1        VISIBLE

下面我们来看看 invisible index 对执行计划的影响.

创建 INVISIBLE 索引

注意新的关键字

SQL> CREATE INDEX emp_ename ON emp(ename)
  2        TABLESPACE users
  3        INVISIBLE;
Index created

USER_INDEXES 视图的新列 VISIBILITY

SQL> select INDEX_NAME ,VISIBILITY from user_indexes;
INDEX_NAME                                                   VISIBILITY
------------------------------------------------------------ ------------------
PK_DEPT                                                      VISIBLE
PK_EMP                                                       VISIBLE
EMP_ENAME                                                    INVISIBLE

观察执行计划的影响

SQL> select count(*) from emp where ename='ADAMS';

  COUNT(*)
----------
         1

Execution Plan
----------------------------------------------------------

Plan hash value: 2083865914

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     7 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |     7 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

告诉优化器使用 INVISIBLE 索引

SQL> alter session set OPTIMIZER_USE_INVISIBLE_INDEXES=true;

Session altered.

SQL> select count(*) from emp where ename='ADAMS';

  COUNT(*)
----------
         1

Execution Plan
----------------------------------------------------------
Plan hash value: 1569421590

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |     7 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |           |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| EMP_ENAME |     1 |     7 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

值得注意的是 VISIBILITY 索引在 Rebuild 后会变成可见索引：

SQL> select INDEX_NAME ,VISIBILITY from user_indexes;
INDEX_NAME                                                   VISIBILITY
------------------------------------------------------------ ------------------
PK_DEPT                                                      VISIBLE
PK_EMP                                                       VISIBLE
EMP_ENAME                                                    INVISIBLE

SQL> alter index EMP_ENAME rebuild;
=Index altered.

SQL> select INDEX_NAME ,VISIBILITY from user_indexes;

INDEX_NAME                                                   VISIBILITY
------------------------------------------------------------ ------------------
PK_DEPT                                                      VISIBLE
PK_EMP                                                       VISIBLE
EMP_ENAME                                                    VISIBLE
SQL>