oracle建立虚拟索引,ORACLE虚拟索引(Virtual Index)

概念：

虚拟索引(Virtual Indexes)是一个定义在数据字典中的假索引(fake index)，它没有相关的索引段。虚拟索引的目的是模拟索引的存在而不用真实的创建一个完整索引。创建虚拟索引来查看相关执行计划而不用等到真实创建完索引才能查看索引对执行计划的影响，并且不会增加存储空间的使用。

应用：

虚拟索引是Oracle 9.2.0.1以后开始引入的，虚拟索引的应用场景主要是在SQL优化调优当中，主要是给DBA做SQL优化使用，根据它的测试效果来判断是否需要创建实际索引。

虚拟环境测试：

SQL> selectversion from v$instance; (查看实例版本)

VERSION

-----------------

11.2.0.4.0

SQL> createtable test as select * from dba_objects;  (创建test表)

Table created.

SQL> selectcount(*) from test;

COUNT(*)

----------

86736

SQL>  insert into test select * from test;

86735 rowscreated.

SQL> selectcount(*) from test;

COUNT(*)

----------

173470

SQL> setautotrace traceonly explain;

SQL> select *from test where object_id=60;

Execution Plan

----------------------------------------------------------

Plan hash value:1357081020

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |     |    27 |  5589 |  673   (1)| 00:00:09 |

|*  1 | TABLE ACCESS FULL| TEST |    27|  5589 |   673  (1)| 00:00:09 |

--------------------------------------------------------------------------

Predicate Information(identified by operation id):

---------------------------------------------------

1 - filter("OBJECT_ID"=60)

Note

-----

- dynamic sampling used for this statement(level=2)

SQL> setautotrace off;  不生成AUTOTRACE报告

创建虚拟索引

SQL> create index idx_test_virtual ontest(object_id) nosegment;

Index created.

验证虚拟索引是否生效

SQL> set autotrace traceonly explain;

SQL> select * from test whereobject_id=60;

Execution Plan

----------------------------------------------------------

Plan hash value: 1357081020

--------------------------------------------------------------------------

| Id | Operation         | Name |Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0| SELECT STATEMENT  |      |   27 |  5589 |   673  (1)| 00:00:09 |

|*  1 | TABLE ACCESS FULL| TEST |    27|  5589 |   673  (1)| 00:00:09 |

--------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

1- filter("OBJECT_ID"=60)

Note

-----

-dynamic sampling used for this statement (level=2)

SQL>

如上所示，并没有使用虚拟索引，依旧执行的全表扫描。

设置隐含参数为true

SQL> alter session set"_USE_NOSEGMENT_INDEXES"=true;

Session altered.

SQL> set autotrace traceonly explain;

SQL> select * from test whereobject_id=60;

Execution Plan

----------------------------------------------------------

Plan hash value: 1235845473

------------------------------------------------------------------------------------------------

| Id | Operation                   |Name             | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------------------------

|   0| SELECT STATEMENT            |                  |     2 |  196 |     2   (0)| 00:00:01 |

|   1|  TABLE ACCESS BY INDEX ROWID| TEST             |     2 |  196 |     2   (0)| 00:00:01 |

|*  2|   INDEX RANGE SCAN          | IDX_TEST_VIRTUAL |     2 |      |     1   (0)| 00:00:01 |

------------------------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

2- access("OBJECT_ID"=60)

注意：

如果要使用所创建的虚拟索引，必须设置隐含参数"_USE_NOSEGMENT_INDEXES"=TRUE(默认为FALSE)后CBO优化器模式才能使用虚拟索引，RBO优化器模式无法使用虚拟索引

RBO优化模式依旧走全表扫描

SQL> set autotrace off;

SQL> select * from test whereobject_id=60;

OWNER                          OBJECT_NAME                    SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_IDOBJECT_TYPE         CREATED

------------------------------------------------------------ ------------------------------ ------------------------ --------------------

LAST_DDL_ TIMESTAMP           STATUS  T G S NAMESPACE EDITION_NAME

--------- ------------------- ------- - - ----------- ------------------------------

SYS                            OBJERROR$                                                            60             60 TABLE               25-AUG-13

25-AUG-13 2013-08-25:05:18:34 VALID   N N N          1

SYS                            OBJERROR$                                                            60             60 TABLE               25-AUG-13

25-AUG-13 2013-08-25:05:18:34 VALID   N N N         1

SQL>   selectsql_id, child_number,sql_text

2   from v$sql

3   where sql_text like '%select *from test%60%';

SQL_ID        CHILD_NUMBER SQL_TEXT

------------- ------------ --------------------------------------------------------------------------------

845p6f42gwpj3            0 select * from test where object_id=60

2m5n2ffq8hryt            0 select sql_id,child_number,sql_text from v$sql  wheresql_text like '%select *

from test%60%'

76rkkrw0j254p            0 select sql_id,child_number,sql_text from v$sql where sql_text like '%select * f

rom test%60%'

SQL> select * fromtable(dbms_xplan.display_cursor('845p6f42gwpj3'));

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID 845p6f42gwpj3, child number 0

-------------------------------------

select * from test where object_id=60

Plan hash value: 1357081020

--------------------------------------------------------------------------

| Id | Operation         | Name |Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0| SELECT STATEMENT  |      |      |       |   673 (100)|          |

|*  1 | TABLE ACCESS FULL| TEST |     2|   196 |   673  (1)| 00:00:09 |

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified byoperation id):

---------------------------------------------------

1 -filter("OBJECT_ID"=60)

18 rows selected.

查看数据库有没有创建对应的虚拟索引(2种方式)

SQL> 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                            IDX_TEST_VIRTUAL

SQL> SELECT O.OBJECT_NAME ASFAKE_INDEX_NAME

2  FROM   DBA_OBJECTS O

3  WHERE  O.OBJECT_TYPE = 'INDEX'

4         AND NOT EXISTS (SELECTNULL

5                        FROM   DBA_INDEXES I

6                        WHERE  O.OBJECT_NAME =I.INDEX_NAME

7                               AND O.OWNER = I.OWNER);

FAKE_INDEX_NAME

------------------------------

IDX_TEST_VIRTUAL