【DB笔试面试553】在Oracle中，什么是不可见索引？

2023-01-06 08:28:02

♣ 题目部分

在Oracle中，什么是不可见索引？

♣ 答案部分

索引维护是DBA的一项重要工作。当一个系统运行很长一段时间，经过需求变更、结构设计变化后，系统中就可能会存在一些不再被使用的索引，或者使用效率很低的索引。这些索引的存在，不仅占用系统空间，而且会降低事务效率，增加系统的负载。因此，需要找出那些无用或低效的索引，并删除它们（找出无用索引可以通过索引监控的方法）。但是，直接删除索引还是存在一定风险的。例如，某些索引可能只是在一些周期的作业中被使用到，而如果监控周期没有覆盖到这些作业的触发点，那么就会认为索引是无用的，从而将其删除。当作业启动后，可能就会对系统性能造成冲击。这时，可能就会手忙脚乱地去找回索引定义语句、重建索引。在Oracle 11g里，Oracle提供了一个新的特性来降低直接删除索引或者禁用索引的风险，那就是不可见索引（Invisible Indexes）。

从Oracle 11g开始，可以创建不可见索引。优化程序会忽略不可见索引，除非在会话或系统级别上将OPTIMIZER_USE_INVISIBLE_INDEXES初始化参数显式设置为TRUE，此参数的默认值是FALSE。

使索引不可见是使索引不可用或被删除的一种替代方法。使用不可见索引，可以完成以下操作：

（1）在删除索引之前测试对索引删除后对系统性能的影响。

（2）对应用程序的特定操作或模块使用临时索引结构，这样就不会影响整个应用程序了。

当索引不可见时，优化程序生成的计划不会使用该索引。如果未发现性能下降，那么可以删除该索引。还可以创建最初不可见索引，执行测试，然后确定是否使该索引可见。可以查询DBA_INDEXES数据字典视图的VISIBILITY列来确定该索引是VISIBLE还是INVISIBLE。

创建不可见索引的方式如下所示：

1CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME) INVISIBLE;

修改索引是否可见的方式如下所示：

1ALTER INDEX INDEX_NAME INVISIBLE; --修改索引不可见
2ALTER INDEX INDEX_NAME VISIBLE; --修改索引可见

不可见索引的特点主要有以下几点：

（1）当索引变更为不可见的时候，只是对Oracle的优化器不可见。

（2）不可见索引在DML操作的时候也会被维护。

（3）加HNIT对不可见索引无效。

（4）可以通过修改SYSTEM级别和SESSION级别参数来使用不可见索引。

不可见索引是从Oracle 11g开始出现的，所以，在Oracle 11g之前的版本中索引没有INVISIBLE的功能，那么应该如何处理呢？有2种办法，第一，让索引变为UNUSABLE；第二，修改索引的统计信息。

在Oracle 11g之前，可以先不删除索引，而将其修改为UNUSABLE。这样的话，索引的定义并未删除，只是索引不能再被使用，也不会随着表数据的更新而更新。当需要重新使用该索引时，需要用REBUILD语句重建、然后更新统计信息。对于一些大表来说，这个时间可能就非常长。

现在Oracle数据库一般都采用基于成本的优化器来生成执行计划，只要索引的成本更低，Oracle就会选择使用索引，所以，只要告诉Oracle使用索引成本很高，它就不会使用这个索引，这样就达到了暂时让索引不可用的效果。Oracle提供了DBMS_STATS包来管理对象的统计信息，通过DBMS_STATS.SET_INDEX_STATS函数可以强制设置索引的统计信息，现在只要把索引的成本设置成非常大即可。

设置非常离谱的统计信息，让Oracle认为使用索引的成本很高：

 1SYS@lhrdb> SELECT A.OWNER,A.INDEX_NAME,A.BLEVEL,A.LEAF_BLOCKS,A.NUM_ROWS FROM DBA_INDEXES A WHERE INDEX_NAME='IDX_II_20160819';
 2OWNER                          INDEX_NAME                         BLEVEL LEAF_BLOCKS   NUM_ROWS
 3------------------------------ ------------------------------ ---------- ----------- ----------
 4SYS                            IDX_II_20160819                         1         193      87133
 5SYS@lhrdb> EXEC DBMS_STATS.SET_INDEX_STATS(OWNNAME => user,INDNAME => 'IDX_II_20160819',INDLEVEL => 10,NUMLBLKS => 1000000000,NUMROWS => 100000000000,NO_INVALIDATE => FALSE );
 6PL/SQL procedure successfully completed.
 7SYS@lhrdb> col NUM_ROWS format 999999999999999
 8SYS@lhrdb> SELECT A.OWNER,A.INDEX_NAME,A.BLEVEL,A.LEAF_BLOCKS,A.NUM_ROWS FROM DBA_INDEXES A WHERE INDEX_NAME='IDX_II_20160819';
 9OWNER                          INDEX_NAME                         BLEVEL LEAF_BLOCKS         NUM_ROWS
10------------------------------ ------------------------------ ---------- ----------- ----------------
11SYS                            IDX_II_20160819                        10  1000000000     100000000000

其中，NO_INVALIDATE=FALSE表示让Library Cache中的执行计划立即失效，重新按现在的统计信息生成SQL执行计划。

虚拟索引和不可见索引的区别如表 3-18所示。

表 3-18 虚拟索引和不可见索引的区别

比较项目	不可见索引（Invisible Indexes）	虚拟索引（Virtual Indexes，无段索引）
出现版本	Oracle 11g	Oracle 9i
有无索引段	有索引段，占用一定的存储空间	无索引段，不占用存储空间
是否可以通过ALTER直接切换其属性	可以通过ALTER直接修改索引是否可见： ALTER INDEX INDEX_NAME INVISIBLE; ALTER INDEX INDEX_NAME VISIBLE;	不能通过ALTER修改属性，也不能通过ALTER重建虚拟索引
视图DBA_INDEXES是否可以查询到	是	否
视图DBA_OBJECTS是否可以查询到	是	是
启用参数	OPTIMIZER_USE_INVISIBLE_INDEXES（默认为FALSE）	_USE_NOSEGMENT_INDEXES（默认为FALSE）
创建语法	CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME) INVISIBLE;	CREATE INDEX INDEX_NAME ON TABLE_NAME(COLUMN_NAME) NOSEGMENT;
查询系统中存在的所有不可见或虚拟索引的SQL	SELECT OWNER, INDEX_NAME FROM DBA_INDEXES WHERE VISIBILITY='INVISIBLE';	SELECT INDEX_OWNER, INDEX_NAME FROM DBA_IND_COLUMNS WHERE INDEX_NAME NOT LIKE 'BIN$%' MINUS SELECT OWNER, INDEX_NAME FROM DBA_INDEXES;
作用	当索引不可见时，优化程序生成的计划不会使用该索引。如果未发现性能下降，那么可以删除该索引。还可以创建最初不可见索引，执行测试，然后确定是否使该索引可见	模拟索引的存在而不用真实的创建一个完整索引。这允许开发者创建虚拟索引来查看相关执行计划而不用等到真实创建完索引才能查看索引对执行计划的影响，并且不会增加存储空间的使用
共同点	都可以通过参数在SESSION和SYSTEM级别进行设置

下面给出不可见索引的使用示例：

创建表、不可见索引，并收集统计信息：

 1SYS@lhrdb> CREATE TABLE T_II_20160819_01_LHR AS SELECT * FROM DBA_OBJECTS;
 2Table created.
 3SYS@lhrdb> CREATE INDEX IDX_II_20160819 ON T_II_20160819_01_LHR(OBJECT_ID) INVISIBLE;
 4Index created.
 5SYS@lhrdb> SELECT VISIBILITY FROM DBA_INDEXES WHERE INDEX_NAME='IDX_II_20160819';
 6VISIBILIT
 7---------
 8INVISIBLE
 9SYS@lhrdb> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>USER,TABNAME=>'T_II_20160819_01_LHR',DEGREE=>2,CASCADE => TRUE);
10PL/SQL procedure successfully completed.
11
12--带WHERE条件查询：
13SYS@lhrdb> SHOW PARAMETER OPTIMIZER_USE_INVISIBLE_INDEXES
14NAME                                 TYPE        VALUE
15------------------------------------ ----------- ------------------------------
16optimizer_use_invisible_indexes      boolean     FALSE
17SYS@lhrdb> set line 9999
18SYS@lhrdb> set autot traceonly exp
19SYS@lhrdb> SELECT * FROM T_II_20160819_01_LHR WHERE OBJECT_ID=1;
20Execution Plan
21----------------------------------------------------------
22Plan hash value: 700947541
23------------------------------------------------------------------------------------------
24| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
25------------------------------------------------------------------------------------------
26|   0 | SELECT STATEMENT  |                      |     1 |    98 |   343   (2)| 00:00:05 |
27|*  1 |  TABLE ACCESS FULL| T_II_20160819_01_LHR |     1 |    98 |   343   (2)| 00:00:05 |
28------------------------------------------------------------------------------------------
29Predicate Information (identified by operation id):
30---------------------------------------------------
31   1 - filter("OBJECT_ID"=1)
32
33
34--这里使用了全表扫描，根据唯一性，这里应该走索引的，加上Hint试试：
35SYS@lhrdb> SELECT /*+ index(T IDX_II_20160819)*/ * FROM T_II_20160819_01_LHR T WHERE OBJECT_ID=1;
36Execution Plan
37----------------------------------------------------------
38Plan hash value: 700947541
39------------------------------------------------------------------------------------------
40| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
41------------------------------------------------------------------------------------------
42|   0 | SELECT STATEMENT  |                      |     1 |    98 |   343   (2)| 00:00:05 |
43|*  1 |  TABLE ACCESS FULL| T_II_20160819_01_LHR |     1 |    98 |   343   (2)| 00:00:05 |
44------------------------------------------------------------------------------------------
45Predicate Information (identified by operation id):
46---------------------------------------------------
47   1 - filter("OBJECT_ID"=1)
48
49
50--对于INVISIBLE的INDEX，使用Hint也没有用。修改OPTIMIZER_USE_INVISIBLE_INDEXES参数为TRUE，再次查询：
51SYS@lhrdb> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
52Session altered.
53SYS@lhrdb> SELECT * FROM T_II_20160819_01_LHR WHERE OBJECT_ID=1;
54Execution Plan
55----------------------------------------------------------
56Plan hash value: 2544197461
57----------------------------------------------------------------------------------------------------
58| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
59----------------------------------------------------------------------------------------------------
60|   0 | SELECT STATEMENT            |                      |     1 |    98 |     2   (0)| 00:00:01 |
61|   1 |  TABLE ACCESS BY INDEX ROWID| T_II_20160819_01_LHR |     1 |    98 |     2   (0)| 00:00:01 |
62|*  2 |   INDEX RANGE SCAN          | IDX_II_20160819      |     1 |       |     1   (0)| 00:00:01 |
63----------------------------------------------------------------------------------------------------
64Predicate Information (identified by operation id):
65---------------------------------------------------
66   2 - access("OBJECT_ID"=1)
67
68
69--这次使用了索引。关闭OPTIMIZER_USE_INVISIBLE_INDEXES参数，将索引改成VISIBLE，再测试：
70SYS@lhrdb> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE;
71Session altered.
72SYS@lhrdb> ALTER INDEX IDX_II_20160819 VISIBLE;
73Index altered.
74SYS@lhrdb> SELECT * FROM T_II_20160819_01_LHR WHERE OBJECT_ID=1;
75Execution Plan
76----------------------------------------------------------
77Plan hash value: 2544197461
78----------------------------------------------------------------------------------------------------
79| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
80----------------------------------------------------------------------------------------------------
81|   0 | SELECT STATEMENT            |                      |     1 |    98 |     2   (0)| 00:00:01 |
82|   1 |  TABLE ACCESS BY INDEX ROWID| T_II_20160819_01_LHR |     1 |    98 |     2   (0)| 00:00:01 |
83|*  2 |   INDEX RANGE SCAN          | IDX_II_20160819      |     1 |       |     1   (0)| 00:00:01 |
84----------------------------------------------------------------------------------------------------
85Predicate Information (identified by operation id):
86---------------------------------------------------
87   2 - access("OBJECT_ID"=1)

索引可见，优化器就可以使用到索引。

& 说明：

有关不可见索引的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2124044/

真题1、An index called ORD_CUSTNAME_IX has been created on the CUSTNAME column in the ORDERS table using the following command:

SQL>CREATE INDEX ord_custname_ix ON orders(custname);

The ORDERS table is frequently queried using the CUSTNAME column in the WHERE clause.You want to check the impact on the performance of the queries if the index is not available.You do not want the index to be dropped or rebuilt to perform this test.

Which is the most efficient method of performing this task?

A、disabling the indexB、making the index invisible

C、aking the index unusableD、using the MONITORING USAGE clause for the index

答案：B。

题目要求在不能删除和重建的情况下来测试索引的性能。

对于选项A，索引不能被禁用。所以，选项A错误。

对于选项B，让索引不可见，为正确选项。所以，选项B正确。

对于选项C，让索引不可用之后还是得重建索引。所以，选项C错误。

对于选项D，监控索引并不能测试索引在不可用的情况下对系统的性能影响。所以，选项D错误。

所以，本题的答案为B。