NOSEGMENT INDEX OR FAKE INDEX IN ORACLE
Invisible index is the index which is not visible for optimizer in 11g and later versions.
Previously, the same invisible index was called as fake or no segment or virtual index.
This index can be logically created in memory without any segments in the disk. Fake index cannot be eligible for any maintenance operations like unusable, rebuild etc..
To create a virtual index, use nosegment clause in syntax
kIsH@xHydra<>CREATE INDEX logicalidx on rp(r_name,r_cost) local (partition R_JUN2022, partition R_MAY2022, partition R_APR2022, partition R_FEB2022) nosegment;
Index created.
Fake index are not visible or stored in dba_indexes view but has to be checked under dba_ind_partitions.
kIsH@xHydra<>SELECT index_name FROM dba_indexes WHERE index_name='LOGICALIDX';
no rows selected
kIsH@xHydra<>SELECT index_name FROM dba_ind_partitions WHERE index_name not in (SELECT index_name FROM dba_indexes);
INDEX_NAME
--------------------------------------------------------------------------------
BIN$7+HiycqkhSXgU8c4qMD3Bw==$0
BIN$7+HiycqkhSXgU8c4qMD3Bw==$0
BIN$7+HiycqkhSXgU8c4qMD3Bw==$0
BIN$7+HiycqkhSXgU8c4qMD3Bw==$0
BIN$7+HiycqkhSXgU8c4qMD3Bw==$0
BIN$7+HiycqkhSXgU8c4qMD3Bw==$0
LOGICALIDX
LOGICALIDX
LOGICALIDX
LOGICALIDX
10 rows selected.
_use_nosegment_indexes is a hidden parameter to enable the virtual index at optimizer level.
kIsH@xHydra<>alter session set "_use_nosegment_indexes" = true;
Session altered.
kIsH@xHydra<>set autot traceonly explain
kIsH@xHydra<>set lines 200 pages 1000
kIsH@xHydra<>SELECT r_name FROM RP where r_cost < 100;
Execution Plan
----------------------------------------------------------
Plan hash value: 1602089251
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 3 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| RP | 1 | 16 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | LOGICALIDX | 1 | | 2 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("R_COST"<100)
Any maintenance operations throw ORA-08114
kIsH@xHydra<>ALTER INDEX LOGICALIDX unusable;
ALTER INDEX LOGICALIDX unusable
*
ERROR at line 1:
ORA-08114: can not alter a fake index