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

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

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

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