不可视索引和相同列上的多个索引

在删除一个索引之前，建议先将索引设置为不可视(invisible)状态，不可视状态相当于将索引隐藏起来。但是索引上的维护工作还会继续(DML操作的同时维护索引上的数据)。因为可能需要快速启用这个不可视索引。

使索引不可视的方法：

alter index idx1 invisible;
create index ... invisible;

使索引可视的方法：

alter index idx1 visible;

测试

SQL> @ind scott.dept
Display indexes where table or index name matches %scott.dept%...

TABLE_OWNER  TABLE_NAME  INDEX_NAME    POS# COLUMN_NAME                    DSC
------------ ----------- ------------- ---- ------------------------------ ----
SCOTT        DEPT        PK_DEPT          1 DEPTNO

INDEX_OWNER  TABLE_NAME  INDEX_NAME    IDXTYPE    UNIQ STATUS   PART TEMP  H     LFBLKS           NDK   NUM_ROWS       CLUF LAST_ANALYZED     DEGREE VISIBILIT
------------ ----------- ------------- ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ----------------- ------ ---------
SCOTT        DEPT        PK_DEPT       NORMAL     YES  VALID    NO   N                                                                        1      VISIBLE

SQL> select count(*) from scott.dept where deptno = 30;

Execution Plan
----------------------------------------------------------
Plan hash value: 2236899148

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

当前scott.dept表有一个PK_DEPT索引，将其设置为不可视

SQL> alter index scott.pk_dept invisible;

SQL> @ind scott.dept
Display indexes where table or index name matches %scott.dept%...

TABLE_OWNER  TABLE_NAME  INDEX_NAME    POS# COLUMN_NAME                    DSC
------------ ----------- ------------- ---- ------------------------------ ----
SCOTT        DEPT        PK_DEPT          1 DEPTNO

INDEX_OWNER  TABLE_NAME  INDEX_NAME    IDXTYPE    UNIQ STATUS   PART TEMP  H     LFBLKS           NDK   NUM_ROWS       CLUF LAST_ANALYZED     DEGREE VISIBILIT
------------ ----------- ------------- ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ----------------- ------ ---------
SCOTT        DEPT        PK_DEPT       NORMAL     YES  VALID    NO   N                                                                        1      INVISIBLE

SQL> select count(*) from scott.dept where deptno = 30;

Execution Plan
----------------------------------------------------------
Plan hash value: 315352865

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

使用Hint强制使用这个不可视索引

SQL> select /*+ index(d pk_dept)*/count(*) from scott.dept d where deptno = 30;

Execution Plan
----------------------------------------------------------
Plan hash value: 315352865

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

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DEPTNO"=30)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------

   2 -  SEL$1 / D@SEL$1
         U -  index(d pk_dept)

使用 Hint index 不走不可视索引，可以使用Hint use_invisible_indexes

SQL> select /*+ use_invisible_indexes */count(*) from scott.dept d where deptno = 30;

Execution Plan
----------------------------------------------------------
Plan hash value: 2236899148

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

将PK_DEPT索引设置为可视

SQL> alter index scott.pk_dept visible;

创建索引时可以指定索引不可视

SQL> create index scott.idx_dept_deptno on scott.dept(deptno) invisible;

SQL> @ind scott.dept
Display indexes where table or index name matches %scott.dept%...

TABLE_OWNER  TABLE_NAME  INDEX_NAME        POS# COLUMN_NAME                    DSC
------------ ----------- ----------------- ---- ------------------------------ ----
SCOTT        DEPT        IDX_DEPT_DEPTNO      1 DEPTNO
                         PK_DEPT              1 DEPTNO

INDEX_OWNER  TABLE_NAME  INDEX_NAME        IDXTYPE    UNIQ STATUS   PART TEMP  H     LFBLKS           NDK   NUM_ROWS       CLUF LAST_ANALYZED     DEGREE VISIBILIT
------------ ----------- ----------------- ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ----------------- ------ ---------
SCOTT        DEPT        IDX_DEPT_DEPTNO   NORMAL     NO   VALID    NO   N     1          1             4          4          1 20200104 08:33:52 1      INVISIBLE
             DEPT        PK_DEPT           NORMAL     YES  VALID    NO   N                                                                        1      VISIBLE

此处验证了12C的新特性，一个表的相同列上可以创建多个索引，但是某一时刻只能有一个设置为可见。

SQL> alter index scott.idx_dept_deptno visible;
ORA-14147: There is an existing VISIBLE index defined on the same set of columns.