Oracle virtual index

Oracle virtual index does not consume CPU, IO, and storage space. It is used to determine whether the created index can be used. The following example shows: SQL> create table tb1 as select owner, object_name, object_id from dba_objects; -- create the test Table created. SQL> select count (*) from tb1; COUNT (*) ---------- 50518 SQL> explain plan for select * from tb1 where object_id = 108; explain. SQL> select * from table (dbms_xplan.display); --- execution Plan PLAN_TABLE_OUTPUT explain Plan hash value before creating a virtual index: 3226679318 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Bytes | 0 | select statement | 4 | 384 | 78 (2) | 00:00:01 | * 1 | table access full | TB1 | 4 | 384 | 78 (2) | 00:00:01 | identified Predicate Information (identified by operation id ): ----------------------------------------------- PLAN_TABLE_OUTPUT partition 1-filter ("OBJECT_ID" = 108) Note ------dynamic sampling used for this statement 17 rows selected. SQL> alter session set "_ use_nosegment_indexes" = true; -- modify the relevant parameter Session altered. SQL> create index tb1_object_id_idx on tb1 (object_id) nosegment; -- create virtual Index index created. SQL> explain plan for select * from tb1 where object_id = 108; -- create the execution plan of the virtual index Hou explain. SQL> select * from table (dbms_xplan.display); PLAN_TABLE_OUTPUT tables -------------------------------------------------- Plan hash value: 959502086 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | period | 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 | identified Predicate Information (identified by operation id ): PLAN_TABLE_OUTPUT 2-access ("OBJECT_ID" = 108) Note ------dynamic sampling used for this statement 18 rows selected. note: This index cannot be found in dba_indexes. You need to query dba_ind_columnsSQL> create index TB1_OBJECT_ID_IDX on tb1 (object_id); -- although it is a virtual index, you still cannot create an index with the same name, need to delete change virtual index -- create real index 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 contains metadata SYS TB1_OBJECT_ID_IDX SQL> drop index indexes; --- locate and delete the virtual Index index dropped.