Oracle虚拟索引

SQL> select TABLE_NAME,TABLESPACE_NAME,STATUS from user_tables;

TABLE_NAME       TABLESPACE_NAME      STATUS

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

DEPT       USERS      VALID

EMP       USERS      VALID

BONUS       USERS      VALID

SALGRADE       USERS      VALID

T       USERS      VALID

SQL> select count(*) from t;

  COUNT(*)

----------

      1002

SQL> explain plan for

  2  select * from t where id > 900

  3  /

Explained.

SQL> select * from table (dbms_xplan.display (null,null,'basic +cost'))

  2  /

PLAN_TABLE_OUTPUT

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

Plan hash value: 1601196873

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

| Id  | Operation  | Name | Cost (%CPU)|

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

|   0 | SELECT STATEMENT  | |     4   (0)|

|   1 |  TABLE ACCESS FULL| T |     4   (0)|

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

8 rows selected.

为了能使用所创建的虚拟索引，需要将_USE_NOSEGMENT_INDEXES设置为true：

SQL> alter session set "_use_nosegment_indexes"=TRUE;

Session altered.

创建虚拟索引必须在create index语句中指定nosegment子句，不会创建索引段：

SQL> create index t_v on t(id) nosegment;

Index created.

验证：

SQL> select index_name from dba_indexes where table_name = 'T' and index_name = 'T_V';
no rows selected

SQL> select object_name, object_type from dba_objects where object_name = 'T_V';
OBJECT_NAME   OBJECT_TYPE
------------- -------------------
T_V           INDEX

从上面的结果可以看到索引对象已经创建，但没有创建索引段。

重新查看执行计划，优化器会使用虚拟索引：

SQL> explain plan for

  2  select * from t where id >900

  3  /

Explained.

SQL> select * from table(dbms_xplan.display (null,null,'basic +cost'))

  2  /

PLAN_TABLE_OUTPUT

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

Plan hash value: 1737097152

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

| Id  | Operation    | Name | Cost (%CPU)|

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

|   0 | SELECT STATEMENT    |   | 3   (0)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T    | 3   (0)|

|   2 |   INDEX RANGE SCAN    | T_V  | 2   (0)|

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

9 rows selected.

TIPS：

使用虚拟索引需要注意，我们可以分析虚拟索引，但不能重建虚拟索引，如果重建虚拟索引会收到ORA-8114: "User attempted to alter a fake index"错误提示，可以删除虚拟索引。