|
Virtual Index in Oracle
Virtual indexes is to simulate the existence of an index - without actually
building a full index. Using Oracle Hidden Parameter.
To build a virtual index, runs the following statement:
| SQL > CREATE unique INDEX index_name on
table_name(col_name) NOSEGMENT; |
To use the index run the following statement:
| SQL > ALTER SESSION SET \"_use_nosegment_indexes\"
= TRUE; |
To detect a virtual index in the database run the following sql (the se inde xes
don\'t have any columns in
dba_ind_columns):
SQL > SELECT index_owner, index_name FROM
dba_ind_columns
WHERE index_name NOT LIKE \'BIN$%\'
MINUS
SELECT owner, index_name
FROM dba_indexes; |
Oracle has a special hidden \"nosegment\" clause to the create index syntax that
allows you to create an index definition without actually creating the index.
| 
