*** First, create our table from dba_tables SQL> create table virtual_index_tab as select * from dba_tables; Table created. *** Now let's create our fake / virtual index ... SQL> create index virtual_idx_tab_i on virtual_index_tab(table_name) NOSEGMENT; Index created. *** Let's collect statistics SQL> exec dbms_stats.gather_table_stats(ownname=>'BOWIE', tabname=>'VIRTUAL_INDEX_TAB', method_opt=>'FOR ALL COLUMNS SIZE 1', cascade=> true); PL/SQL procedure successfully completed. *** Note dba_segments and dba_indexes are not populated SQL> select * from dba_segments where owner = 'BOWIE' and segment_name='VIRTUAL_IDX_TAB_I'; no rows selected SQL> select * from dba_indexes where owner = 'BOWIE' and index_name='VIRTUAL_IDX_TAB_I'; no rows selected *** But dba_objects is populated SQL> select * from dba_objects where owner = 'BOWIE' and object_name='VIRTUAL_IDX_TAB_I'; OWNER ------------------------------ OBJECT_NAME -------------------------------------------------------------------------------- SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE ------------------------------ ---------- -------------- ------------------- CREATED LAST_DDL_ TIMESTAMP STATUS T G S --------- --------- ------------------- ------- - - - BOWIE VIRTUAL_IDX_TAB_I 55481 55481 INDEX 14/JUN/07 14/JUN/07 2007-06-14:00:16:56 VALID N N N *** Let's see if the index is used ... SQL> select * from virtual_index_tab where table_name = 'TAB$'; Execution Plan ---------------------------------------------------------- Plan hash value: 4272086532 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 206 | 12 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| VIRTUAL_INDEX_TAB | 1 | 206 | 12 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("TABLE_NAME"='TAB$') Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 53 consistent gets 0 physical reads 0 redo size 3514 bytes sent via SQL*Net to client 396 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed *** NO. A full table scan is performed ... *** Let's change our session so that it can see these fake indexes SQL> alter session set "_use_nosegment_indexes" = true; Session altered. *** Now let's see if the index is used SQL> select * from virtual_index_tab where table_name = 'TAB$'; Execution Plan ---------------------------------------------------------- Plan hash value: 3115255993 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 206 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| VIRTUAL_INDEX_TAB | 1 | 206 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | VIRTUAL_IDX_TAB_I | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("TABLE_NAME"='TAB$') Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 53 consistent gets 0 physical reads 0 redo size 3514 bytes sent via SQL*Net to client 396 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed *** Well yes and no. The CBO now can see the index and has used it in its execution plan *** BUT, the statistics show that it's still using '53 consistent gets' and is really performing a full table scan behind the scenes. *** Note Oracle is still clever enough to avoid a full table scan even if a fake index gets in the way *** Let's create a second, "real" index on the table and make fake indexes not viable to the session SQL> create index virtual_idx_tab_i_2 on virtual_index_tab(num_rows); Index created. SQL> alter session set "_use_nosegment_indexes" = false; Session altered. *** Let's confirm Oracle would ordinarily use this new index with this new query based on both columns of interest SQL> select * from virtual_index_tab where table_name = 'TAB$' and num_rows=388; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 3209678585 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 206 |3 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| VIRTUAL_INDEX_TAB | 1 | 206 |3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | VIRTUAL_IDX_TAB_I_2 | 6 | |1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("TABLE_NAME"='TAB$') 2 - access("NUM_ROWS"=388) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 3172 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed *** Yes indeed, Oracle does use the new index *** But would it prefer the Fake index if it existed ? SQL> alter session set "_use_nosegment_indexes" = true; Session altered. SQL> select * from virtual_index_tab where table_name = 'TAB$' and num_rows=388; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 3115255993 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 206 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| VIRTUAL_INDEX_TAB | 1 | 206 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | VIRTUAL_IDX_TAB_I | 1 | | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("NUM_ROWS"=388) 2 - access("TABLE_NAME"='TAB$') Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 3172 bytes sent via SQL*Net to client 385 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed *** Yes it would prefer the Fake index if it really existed but uses the other one instead behind the scenes as highlighted by the 3 consistent gets *** I have this urge to rebuild this fake index ... SQL> alter index virtual_idx_tab_i rebuild; alter index virtual_idx_tab_i rebuild * ERROR at line 1: ORA-08114: can not alter a fake index *** But you can't as it's only a fake index ...