oracle virtual index

虚拟索引不消耗CPU以及IO,还有存储空间，用来判断创建的索引是否可被用，下面举例说明：

SQL> create table tb1 as select owner,object_name,object_id from dba_objects; --创建测试表

Table created.

SQL> select count(*) from tb1;

COUNT(*)

----------

50518

SQL> explain plan for select * from tb1 where object_id=108;

Explained.

SQL> select * from table(dbms_xplan.display); ---未创建虚拟索引前的执行计划

PLAN_TABLE_OUTPUT

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

Plan hash value: 3226679318

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

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

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

| 0 | SELECT STATEMENT | | 4 | 384 | 78 (2)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| TB1 | 4 | 384 | 78 (2)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

1 - filter("OBJECT_ID"=108)

Note

-----

- dynamic sampling used for this statement

17 rows selected.

SQL> alter session set "_use_nosegment_indexes"=true; --修改相关参数

Session altered.

SQL> create index tb1_object_id_idx on tb1(object_id) nosegment; --创建虚拟索引

Index created.

SQL> explain plan for select * from tb1 where object_id=108; --创建虚拟索引侯的执行计划

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 959502086

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

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

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

| 0 | SELECT STATEMENT | | 4 | 384 | 5 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| TB1 | 4 | 384 | 5 (0)| 00:00:01 |

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

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

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT

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

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

2 - access("OBJECT_ID"=108)

Note

-----

- dynamic sampling used for this statement

18 rows selected.

注意：此索引在dba_indexes中是无法查到的，需要查询dba_ind_columns

SQL> create index TB1_OBJECT_ID_IDX on tb1(object_id); --虽然是虚拟索引，但是还是不能创建同名的索引，需要删除改虚拟索引 --创建真实索引

create index TB1_OBJECT_ID_IDX on tb1(object_id)

*

ERROR at line 1:

ORA-00955: name is already used by an existing object

SQL> select index_owner,index_name from dba_ind_columns where lower(index_name)='tb1_object_id_idx';

INDEX_OWNER INDEX_NAME

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

SYS TB1_OBJECT_ID_IDX

SQL> drop index tb1_object_id_idx; ---找出并删除该虚拟索引

Index dropped.