【DB笔试面试556】在Oracle中，虚拟索引的作用有哪些？

  • 2019 年 10 月 10 日
  • 筆記

♣

题目部分

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

♣

答案部分

在数据库优化中，索引的重要性是不言而喻的。但是，在性能调整过程中，一个索引是否能被查询用到，在索引创建之前是无法确定的，而创建索引是一个代价比较高的操作，尤其是当数据量较大的时候。这种情况下，创建虚拟索引是一个很好的选择。

虚拟索引（Virtual Index）是定义在数据字典中的伪索引，但没有相关的索引段。虚拟索引的目的是模拟索引的存在而不用真实的创建一个完整索引。这允许开发者创建虚拟索引来查看相关执行计划而不用等到真实创建完索引才能查看索引对执行计划的影响，并且不会增加存储空间的使用。需要确保创建的索引将不会对数据库中的其它查询产生负面影响，这些都可以使用虚拟索引来完成测试。

虚拟索引与不可见索引的不同之处在于不可见索引是有与之相关的存储的，只是优化器不能选择它们。而虚拟索引没有与之相关的存储空间。由于这个原因，虚拟索引也被称为无段索引。

Oracle文档中并没有提到虚拟索引的创建语法，实际上就是普通索引语法后面加一个NOSEGMENT关键字即可，B-Tree索引和BITMAP索引都可以被创建成虚拟索引。

需要注意的是，必须设置隐含参数“_USE_NOSEGMENT_INDEXES”为TRUE（默认为FALSE）后，CBO（Cost Based Optimization，基于代价的优化器）模式才能使用虚拟索引，而RBO（Rule Based Optimization，基于规则的优化器）模式无法使用虚拟索引。

可以使用如下的SQL语句查找系统中已经存在的虚拟索引：

SELECT INDEX_OWNER, INDEX_NAME    FROM DBA_IND_COLUMNS   WHERE INDEX_NAME NOT LIKE 'BIN$%'  MINUS  SELECT OWNER, INDEX_NAME    FROM DBA_INDEXES;

关于虚拟索引需要注意以下几点：

①　虚拟索引无法执行ALTER INDEX操作。

SQL> ALTER INDEX IX_T_ID REBUILD;  ALTER INDEX IX_T_ID REBUILD*  第 1 行出现错误:  ORA-08114: 无法变更假索引

②　使用回收站特性的时候，虚拟索引必须显式DROP，才能创建同名的索引。

SQL> CREATE INDEX IND_STATUS ON T(STATUS);  索引已创建。  SQL> DROP TABLE T;  表已删除。  SQL> FLASHBACK TABLE T TO BEFORE DROP;  闪回完成。  SQL> SELECT TABLE_NAME,INDEX_NAME,STATUS FROM USER_INDEXES WHERE TABLE_NAME='T';  TABLE_NAME               INDEX_NAME              STATUS  ------------------------------ ------------------------------ --------  T                   BIN$7jAFlUG6b1zgQAB/AQAPyw==$0 VALID  SQL> CREATE INDEX IND_OBJECT_ID ON T(OBJECT_ID);  索引已创建。  SQL> CREATE INDEX INDS_STATUS ON T(STATUS);CREATE INDEX INDS_STATUS ON T(STATUS);                                *  第 1 行出现错误:  ORA-01408: 此列列表已索引

③　不能创建和虚拟索引同名的实际索引。

④　可以创建和虚拟索引包含相同列但不同名的实际索引。

⑤　虚拟索引可以被分析并且有效，但是数据字典里查不到结果。

下面给出虚拟索引的一个示例：

SYS@lhrdb> SELECT * FROM V$VERSION WHERE ROWNUM<=2;  BANNER  --------------------------------------------------------------------------------  Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production  PL/SQL Release 11.2.0.4.0 - Production  SYS@lhrdb> CREATE TABLE T_VI_20160818_01_LHR AS SELECT * FROM DBA_OBJECTS;  Table created.      --虚拟索引的创建语法比较简单，实际上就是普通索引语法后面加一个NOSEGMENT关键字：  SYS@lhrdb> CREATE INDEX IX_VI01_ID ON T_VI_20160818_01_LHR(OBJECT_ID) NOSEGMENT;  Index created.      --从数据字典DBA_INDEXES中是无法找到这个索引的，但是DBA_OBJECTS的确存在：  SYS@lhrdb> SELECT INDEX_NAME,STATUS FROM DBA_INDEXES WHERE TABLE_NAME='T_VI_20160818_01_LHR';  no rows selected  SYS@lhrdb> COL OBJECT_NAME FORMAT A10  SYS@lhrdb> SELECT D.OWNER,D.OBJECT_NAME,D.OBJECT_TYPE FROM DBA_OBJECTS D WHERE D.OBJECT_NAME='IX_VI01_ID';  OWNER                          OBJECT_NAM OBJECT_TYPE  ------------------------------ ---------- -------------------  SYS                            IX_VI01_ID INDEX  SYS@lhrdb> SELECT TO_CHAR(DBMS_METADATA.GET_DDL('INDEX','IX_VI01_ID')) FROM DUAL;  TO_CHAR(DBMS_METADATA.GET_DDL('INDEX','IX_VI01_ID'))  -------------------------------------------------------------------    CREATE INDEX "SYS"."IX_VI01_ID" ON "SYS"."T_VI_20160818_01_LHR" ("OBJECT_ID")    PCTFREE 10 INITRANS 2 MAXTRANS 255  NOSEGMENT      --使用虚拟索引，首先要将隐含参数“_USE_NOSEGMENT_INDEXES”设置为TRUE：  SYS@lhrdb> ALTER SESSION SET "_USE_NOSEGMENT_INDEXES"=TRUE;  Session altered.  SYS@lhrdb> SHOW PARAMETER OPTIMIZER_MODE  NAME                                 TYPE        VALUE  ------------------------------------ ----------- ------------------------------  optimizer_mode                       string      ALL_ROWS  SYS@lhrdb> SET AUTOTRACE TRACEONLY  SYS@lhrdb> SET LINE 9999  SYS@lhrdb> SELECT * FROM T_VI_20160818_01_LHR WHERE OBJECT_ID=1;  no rows selected  Execution Plan  ----------------------------------------------------------  Plan hash value: 3209519479  ----------------------------------------------------------------------------------------------------  | Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |  ----------------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT            |                      |    14 |  2898 |     5   (0)| 00:00:01 |  |   1 |  TABLE ACCESS BY INDEX ROWID| T_VI_20160818_01_LHR |    14 |  2898 |     5   (0)| 00:00:01 |  |*  2 |   INDEX RANGE SCAN          | IX_VI01_ID           |   312 |       |     1   (0)| 00:00:01 |  ----------------------------------------------------------------------------------------------------  Predicate Information (identified by operation id):  ---------------------------------------------------     2 - access("OBJECT_ID"=1)  Note  -----     - dynamic sampling used for this statement (level=2)  Statistics  ----------------------------------------------------------            0  recursive calls            0  db block gets         1249  consistent gets            0  physical reads            0  redo size         1343  bytes sent via SQL*Net to client          509  bytes received via SQL*Net from client            1  SQL*Net roundtrips to/from client            0  sorts (memory)            0  sorts (disk)            0  rows processed        --以下看的是真实执行计划，显然是用不到索引。  SYS@lhrdb> SET AUTOTRACE OFF  SYS@lhrdb> ALTER SESSION SET STATISTICS_LEVEL=ALL;  Session altered.  SYS@lhrdb> SELECT * FROM T_VI_20160818_01_LHR WHERE OBJECT_ID=1;  no rows selected  SYS@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%';  SQL_ID        CHILD_NUMBER SQL_TEXT  ------------- ------------ ---------------------------------------------------  d5v59m8vyyz7d            0 SELECT * FROM T_VI_20160818_01_LHR WHERE OBJECT_ID=1  SYS@lhrdb> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('d5v59m8vyyz7d',0,'ALLSTATS LAST'));  PLAN_TABLE_OUTPUT  ------------------------------------------------------------------------------------  SQL_ID  d5v59m8vyyz7d, child number 0  -------------------------------------  SELECT * FROM T_VI_20160818_01_LHR WHERE OBJECT_ID=1  Plan hash value: 847945500  ----------------------------------------------------------------------------------------------------  | Id  | Operation         | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  ----------------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT  |                      |      1 |        |      0 |00:00:00.01 |    1249 |  |*  1 |  TABLE ACCESS FULL| T_VI_20160818_01_LHR |      1 |     14 |      0 |00:00:00.01 |    1249 |  ----------------------------------------------------------------------------------------------------  Predicate Information (identified by operation id):  ---------------------------------------------------     1 - filter("OBJECT_ID"=1)  Note  -----     - dynamic sampling used for this statement (level=2)  22 rows selected.      --查找系统中已经存在的虚拟索引：  SYS@lhrdb> SELECT INDEX_OWNER, INDEX_NAME    2    FROM DBA_IND_COLUMNS    3   WHERE INDEX_NAME NOT LIKE 'BIN$%'    4  MINUS    5  SELECT OWNER, INDEX_NAME    6    FROM DBA_INDEXES;  INDEX_OWNER                    INDEX_NAME  ------------------------------ ------------------------------  SYS                            IX_VI01_ID

下面是一个常见的面试题，“若现在生产库不允许创建索引，但是需要测试创建索引后对SQL性能的影响，该怎么办？”，那么答案就是要么在测试库创建索引来测试，要么使用虚拟索引来测试性能。

& 说明：

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

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

VirMach 便宜 VPS

QNews

QNews