No Segment/Virtual Indexes (8i and up):
Virtual indexes allow us to simulate the existence of an index and test its impact without actually building the actual index.
Only sessions marked for Virtual Index usage will be affected by their existence. Their creation does not affect new sessions.
Virtual indexes will be used only when the initialization parameter "_use_nosegment_indexes" is set to TRUE.
The Rule based optimizer does not recognize Virtual Indexes but the CBO does recognize them.
Dictionary view DBA_SEGMENTS will not show entries for Virtual Indexes. [DBA|ALL|USER]_OBJECTS view will have an entry.
They are permanent and continue to exist unless dropped. Make sure to drop virtual indexes after analysis and tuning is completed.
Statistics can be gathered on virtual indexes in the same way as regular indexes.
Oracle will prevent us from creating another virtual index with the same column list, but it will allow us to create a real index with the same column list.
To detect a virtual index in the database run the following SQL (these indexes don't have any columns in dba_ind_columns):
SELECT index_owner, index_name
FROM dba_ind_columns
WHERE index_name NOT LIKE 'BIN$%'
MINUS
SELECT owner, index_name
FROM dba_indexes;
Invisible Indexes (11gR1 and up):
Beginning with Release 11g, you can create invisible indexes. An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level.
ALTER SESSION SET optimizer_use_invisible_indexes=TRUE;
ALTER SYSTEM SET optimizer_use_invisible_indexes=TRUE;
Using invisible indexes, you can do the following:
- Test the optimizer behave on the application before dropping an index.
- Use the index for certain operations or modules of an application without affecting the overall performance of the application.
- It's also useful for some DELETE operations in Database Machine (Oracle EXADATA).
An invisible index is maintained during DML statements.
Statistics can be gathered on an invisible indexes.
The current visibility status of an index is indicated by the VISIBILITY column of the [DBA|ALL|USER]_INDEXES views.
Using ALTER INDEX we can make the index INVISIBLE or VISIBLE.
We can REBUILD an invisible index.
INDEX hit will not work with an invisible index.
/*+ opt_param('optimizer_use_invisible_indexes','TRUE') */ -- we CAN'T use opt_param hint
We CAN'T modify index partition to Invisible, it's at Index level.
Unusable Indexes (11gR2):
Zero Sized Unusable Indexes:
In Oracle 11gR2 when the index or index partition marked as unusable, oracle automatically drop any index segment space.
This means we can release OLD(Not Active) index partition space and we can keep NEW(Active) partition indexes.
ALTER INDEX
Once we make an index unusable, we will not see the segment in [DBA|ALL|USER]_SEGMENTS view, but we will have entry in [DBA|ALL|USER]_IND_PARTITIONS view.
The column SEGMENT_CREATED(column value: YES/NO) shows whether a segment exists for that partition.
unlike previous oracle releases(< 11gR2), we can query the unusable partition data, but it uses full table scan instead of an index scan, because the corresponding index partition is unusable and cannot be used by the optimizer.
When you query against NEW(Active) partition, it uses the corresponding index scan.
If a table is truncated, the unusable index partition will become usable again and Oracle Database will re-create the segment.
