在Oracle中，虚拟索引的作用有哪些？

DB宝 2019-05-06
728
♣题目部分在Oracle中，虚拟索引的作用有哪些？

     
♣答案部分

在数据库优化中，索引的重要性是不言而喻的。但是，在性能调整过程中，一个索引是否能被查询用到，在索引创建之前是无法确定的，而创建索引是一个代价比较高的操作，尤其是当数据量较大的时候。这种情况下，创建虚拟索引是一个很好的选择。
虚拟索引（Virtual Index）是定义在数据字典中的伪索引，但没有相关的索引段。虚拟索引的目的是模拟索引的存在而不用真实的创建一个完整索引。这允许开发者创建虚拟索引来查看相关执行计划而不用等到真实创建完索引才能查看索引对执行计划的影响，并且不会增加存储空间的使用。需要确保创建的索引将不会对数据库中的其它查询产生负面影响，这些都可以使用虚拟索引来完成测试。
虚拟索引与不可见索引的不同之处在于不可见索引是有与之相关的存储的，只是优化器不能选择它们。而虚拟索引没有与之相关的存储空间。由于这个原因，虚拟索引也被称为无段索引。
Oracle文档中并没有提到虚拟索引的创建语法，实际上就是普通索引语法后面加一个NOSEGMENT关键字即可，B-Tree索引和BITMAP索引都可以被创建成虚拟索引。
需要注意的是，必须设置隐含参数“_USE_NOSEGMENT_INDEXES”为TRUE（默认为FALSE）后，CBO（Cost Based Optimization，基于代价的优化器）模式才能使用虚拟索引，而RBO（Rule Based Optimization，基于规则的优化器）模式无法使用虚拟索引。
可以使用如下的SQL语句查找系统中已经存在的虚拟索引：
1SELECT INDEX_OWNER, INDEX_NAME
2  FROM DBA_IND_COLUMNS
3 WHERE INDEX_NAME NOT LIKE 'BIN$%'
4MINUS
5SELECT OWNER, INDEX_NAME
6  FROM DBA_INDEXES;


关于虚拟索引需要注意以下几点：
①　虚拟索引无法执行ALTER INDEX操作。
1SQL> ALTER INDEX IX_T_ID REBUILD;
2ALTER INDEX IX_T_ID REBUILD*
3第 1 行出现错误:
4ORA-08114: 无法变更假索引


②　使用回收站特性的时候，虚拟索引必须显式DROP，才能创建同名的索引。
 1SQL> CREATE INDEX IND_STATUS ON T(STATUS);
 2索引已创建。
 3SQL> DROP TABLE T;
 4表已删除。
 5SQL> FLASHBACK TABLE T TO BEFORE DROP;
 6闪回完成。
 7SQL> SELECT TABLE_NAME,INDEX_NAME,STATUS FROM USER_INDEXES WHERE TABLE_NAME='T';
 8TABLE_NAME               INDEX_NAME              STATUS
 9------------------------------ ------------------------------ --------
10T                   BIN$7jAFlUG6b1zgQAB/AQAPyw==$0 VALID
11SQL> CREATE INDEX IND_OBJECT_ID ON T(OBJECT_ID);
12索引已创建。
13SQL> CREATE INDEX INDS_STATUS ON T(STATUS);CREATE INDEX INDS_STATUS ON T(STATUS);
14                              *
15第 1 行出现错误:
16ORA-01408: 此列列表已索引


③　不能创建和虚拟索引同名的实际索引。
④　可以创建和虚拟索引包含相同列但不同名的实际索引。
⑤　虚拟索引可以被分析并且有效，但是数据字典里查不到结果。
下面给出虚拟索引的一个示例：
  1SYS@lhrdb> SELECT * FROM V$VERSION WHERE ROWNUM<=2;
  2BANNER
  3--------------------------------------------------------------------------------
  4Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  5PL/SQL Release 11.2.0.4.0 - Production
  6SYS@lhrdb> CREATE TABLE T_VI_20160818_01_LHR AS SELECT * FROM DBA_OBJECTS;
  7Table created.
  8
  9
 10--虚拟索引的创建语法比较简单，实际上就是普通索引语法后面加一个NOSEGMENT关键字：
 11SYS@lhrdb> CREATE INDEX IX_VI01_ID ON T_VI_20160818_01_LHR(OBJECT_ID) NOSEGMENT;
 12Index created.
 13
 14
 15--从数据字典DBA_INDEXES中是无法找到这个索引的，但是DBA_OBJECTS的确存在：
 16SYS@lhrdb> SELECT INDEX_NAME,STATUS FROM DBA_INDEXES WHERE TABLE_NAME='T_VI_20160818_01_LHR';
 17no rows selected
 18SYS@lhrdb> COL OBJECT_NAME FORMAT A10
 19SYS@lhrdb> SELECT D.OWNER,D.OBJECT_NAME,D.OBJECT_TYPE FROM DBA_OBJECTS D WHERE D.OBJECT_NAME='IX_VI01_ID';
 20OWNER                          OBJECT_NAM OBJECT_TYPE
 21------------------------------ ---------- -------------------
 22SYS                            IX_VI01_ID INDEX
 23SYS@lhrdb> SELECT TO_CHAR(DBMS_METADATA.GET_DDL('INDEX','IX_VI01_ID')) FROM DUAL;
 24TO_CHAR(DBMS_METADATA.GET_DDL('INDEX','IX_VI01_ID'))
 25-------------------------------------------------------------------
 26  CREATE INDEX "SYS"."IX_VI01_ID" ON "SYS"."T_VI_20160818_01_LHR" ("OBJECT_ID")
 27  PCTFREE 10 INITRANS 2 MAXTRANS 255  NOSEGMENT
 28
 29
 30--使用虚拟索引，首先要将隐含参数“_USE_NOSEGMENT_INDEXES”设置为TRUE：
 31SYS@lhrdb> ALTER SESSION SET "_USE_NOSEGMENT_INDEXES"=TRUE;
 32Session altered.
 33SYS@lhrdb> SHOW PARAMETER OPTIMIZER_MODE
 34NAME                                 TYPE        VALUE
 35------------------------------------ ----------- ------------------------------
 36optimizer_mode                       string      ALL_ROWS
 37SYS@lhrdb> SET AUTOTRACE TRACEONLY
 38SYS@lhrdb> SET LINE 9999
 39SYS@lhrdb> SELECT * FROM T_VI_20160818_01_LHR WHERE OBJECT_ID=1;
 40no rows selected
 41Execution Plan
 42----------------------------------------------------------
 43Plan hash value: 3209519479
 44----------------------------------------------------------------------------------------------------
 45| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
 46----------------------------------------------------------------------------------------------------
 47|   0 | SELECT STATEMENT            |                      |    14 |  2898 |     5   (0)| 00:00:01 |
 48|   1 |  TABLE ACCESS BY INDEX ROWID| T_VI_20160818_01_LHR |    14 |  2898 |     5   (0)| 00:00:01 |
 49|*  2 |   INDEX RANGE SCAN          | IX_VI01_ID           |   312 |       |     1   (0)| 00:00:01 |
 50----------------------------------------------------------------------------------------------------
 51Predicate Information (identified by operation id):
 52---------------------------------------------------
 53   2 - access("OBJECT_ID"=1)
 54Note
 55-----
 56   - dynamic sampling used for this statement (level=2)
 57Statistics
 58----------------------------------------------------------
 59          0  recursive calls
 60          0  db block gets
 61       1249  consistent gets
 62          0  physical reads
 63          0  redo size
 64       1343  bytes sent via SQL*Net to client
 65        509  bytes received via SQL*Net from client
 66          1  SQL*Net roundtrips to/from client
 67          0  sorts (memory)
 68          0  sorts (disk)
 69          0  rows processed
 70
 71
 72
 73--以下看的是真实执行计划，显然是用不到索引。
 74SYS@lhrdb> SET AUTOTRACE OFF
 75SYS@lhrdb> ALTER SESSION SET STATISTICS_LEVEL=ALL;
 76Session altered.
 77SYS@lhrdb> SELECT * FROM T_VI_20160818_01_LHR WHERE OBJECT_ID=1;
 78no rows selected
 79SYS@lhrdb> SELECT SQL_ID,CHILD_NUMBER,SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%SELECT * FROM T_VI_20160818_01_LHR WHERE OBJECT_ID=1%';
 80SQL_ID        CHILD_NUMBER SQL_TEXT
 81------------- ------------ ---------------------------------------------------
 82d5v59m8vyyz7d            0 SELECT * FROM T_VI_20160818_01_LHR WHERE OBJECT_ID=1
 83SYS@lhrdb> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('d5v59m8vyyz7d',0,'ALLSTATS LAST'));
 84PLAN_TABLE_OUTPUT
 85------------------------------------------------------------------------------------
 86SQL_ID  d5v59m8vyyz7d, child number 0
 87-------------------------------------
 88SELECT * FROM T_VI_20160818_01_LHR WHERE OBJECT_ID=1
 89Plan hash value: 847945500
 90----------------------------------------------------------------------------------------------------
 91| Id  | Operation         | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
 92----------------------------------------------------------------------------------------------------
 93|   0 | SELECT STATEMENT  |                      |      1 |        |      0 |00:00:00.01 |    1249 |
 94|*  1 |  TABLE ACCESS FULL| T_VI_20160818_01_LHR |      1 |     14 |      0 |00:00:00.01 |    1249 |
 95----------------------------------------------------------------------------------------------------
 96Predicate Information (identified by operation id):
 97---------------------------------------------------
 98   1 - filter("OBJECT_ID"=1)
 99Note
100-----
101   - dynamic sampling used for this statement (level=2)
10222 rows selected.
103
104
105--查找系统中已经存在的虚拟索引：
106SYS@lhrdb> SELECT INDEX_OWNER, INDEX_NAME
107  2    FROM DBA_IND_COLUMNS
108  3   WHERE INDEX_NAME NOT LIKE 'BIN$%'
109  4  MINUS
110  5  SELECT OWNER, INDEX_NAME
111  6    FROM DBA_INDEXES;
112INDEX_OWNER                    INDEX_NAME
113------------------------------ ------------------------------
114SYS                            IX_VI01_ID

下面是一个常见的面试题，“若现在生产库不允许创建索引，但是需要测试创建索引后对SQL性能的影响，该怎么办？”，那么答案就是要么在测试库创建索引来测试，要么使用虚拟索引来测试性能。
& 说明：
有关虚拟索引的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2123687/

本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。