在删除一个索引之前,建议先将索引设置为不可视(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.