Oracle索引的基本原理（二）不可见索引

原创大柏树 2023-01-04

714

决定对哪些列创建索引往往是很难的。主键是被自动索引的，外键也应该创建索引，那其他的呢？更困难的是决定删除某个可能不好的索引。每插入一条记录，所有索引都必须更新。如果一个被索引的列被更新，该索引也要被更新。在不引起用户查询出现一系列全表扫描或笛卡尔积连接的情况下，决定哪些索引需要被删除是很困难的，尤其是在需要调优的第三方应用程序代码不能改变的情况下。
不可视索引是该问题一个可能的解决方案，Oracle允许隐藏索引（使其不可见），但是索引上的维护工作还会继续（在任何DML操作：INSERT/UPDATE/DELETE期间），因为可能需要快速重新启用它。
可以通过下面的方法使得索引可见或不可见；

ALTER INDEX idx1 INVISIBLE;
ALTER INDEX idx1 VISIBLE;
CREATE INDEX … INVISIBLE;

下面的查询显示了在表emp的deptno列上新建一个不可见索引，在接下来的查询中，我们看不到该索引：

SQL> conn scott/oracle
SQL> set line 200
SQL> select index_name,table_name,column_name from user_ind_columns;

INDEX_NAME           TABLE_NAME                     COLUMN_NAME
-------------------- ------------------------------ ----------------------------------------
PK_DEPT              DEPT                           DEPTNO
PK_EMP               EMP                            EMPNO
SQL> create index emp_deptno_index on emp(deptno) invisible;
索引已创建。
SQL> select index_name,table_name,column_name from user_ind_columns;
INDEX_NAME           TABLE_NAME                     COLUMN_NAME
-------------------- ------------------------------ ----------------------------------------
EMP_DEPTNO_INDEX     EMP                            DEPTNO
PK_DEPT              DEPT                           DEPTNO
PK_EMP               EMP                            EMPNO
SQL>
SQL> set autot trace
SQL> select count(*) from scott.emp where deptno=30;
执行计划
----------------------------------------------------------
Plan hash value: 2083865914
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     5 |    15 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("DEPTNO"=30)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL>
复制

虽然这时候索引是不可见的，但是可以使用提示来强制使用这个索引。从Oracle 11.2 版本开始就可以使用USE_INVISIBLE_INDEXES提示。

SQL> select /*+ use_invisible_indexes */ count(*) from scott.emp where deptno=30;
执行计划
----------------------------------------------------------
Plan hash value: 467672213
--------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |     1 |     3 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                  |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| EMP_DEPTNO_INDEX |     5 |    15 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPTNO"=30)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL>
复制

如果把索引设置为可见状态，就不需要INDEX提示了。

SQL> alter index scott.EMP_DEPTNO_INDEX visible;
索引已更改。
SQL> select count(*) from scott.emp where deptno=30;
执行计划
----------------------------------------------------------
Plan hash value: 467672213
---------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |     1 |     3 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                  |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| EMP_DEPTNO_INDEX |     5 |    15 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPTNO"=30)
统计信息
----------------------------------------------------------
        222  recursive calls
          0  db block gets
         44  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL>
复制

在不将其变成不可见的前提下，也可以使用NO_INDEX提示关闭某个索引，看看除此之外是否会使用其他的索引（或者不使用索引）。换句话说，使用除了NO_INDEX中指定的索引之外的任何其他索引。

SQL> select /*+ no_index(e EMP_DEPTNO_INDEX) */ count(*) from scott.emp e where deptno=30;
执行计划
----------------------------------------------------------
Plan hash value: 2083865914
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     3 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     3 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     5 |    15 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("DEPTNO"=30)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        527  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL>
复制

可以随时将这个索引设为不可见：

SQL> alter index scott.EMP_DEPTNO_INDEX invisible;
索引已更改。
SQL>
复制

可以通过USER_INDEXES或DBA_INDEXES视图来查看索引的可见性：

SQL> select index_name,visibility from dba_indexes where index_name='EMP_DEPTNO_INDEX';

INDEX_NAME           VISIBILIT
-------------------- ---------
EMP_DEPTNO_INDEX     INVISIBLE

SQL>
复制

总结
通过使用不可见索引，可以通过使用索引不可见的方法，临时“隐藏”它们来查询在没有它们时的性能。由于不可见索引依然被维护，因此如果需要的话，可以重新变为可见状态。