Richard Foote's Oracle Blog

The Fake Index Trap (“Nowhere Now”) May 2, 2023

In a recent correspondence, I was alerted to an issue in relation to the use of Virtual/Fake/Nosegment Indexes that I wasn’t aware of previously. Having a play, it appears this issue is still present in at least Oracle Database 21c, so I thought it worth a mention in case anyone else happens to fall into this trap.

I’ve discussed Virtual/Fake/Nosegment Indexes a number of times previously. These are indexes that do not exist as a physical segment (and so can be created almost immediately without consuming any storage), that can be used to determine if an index could potentially be used by the CBO if it were to be actually created.

Although such Fake Indexes don’t physically exist, they can cause issues if forgotten…

To illustrate this issue, I’ll start by creating a new tablespace:

SQL> create tablespace BOWIE_TS datafile 'C:\ORADATA\ZIGGY\ZIGGYPDB1\BOWIE_TS.DBF' size 100M;

Tablespace created.

Next, I’ll create and populate a table in this BOWIE_TS tablespace:

SQL> create table bowie_test (id number, name varchar2(42)) tablespace bowie_ts;

Table created.

SQL> insert into bowie_test select rownum, 'DAVID BOWIE' from dual connect by level <=10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE_TEST');

PL/SQL procedure successfully completed.

I’ll next create a Virtual/Fake index, using the NOSEGMENT option:

SQL> create index bowie_test_id_i on bowie_test(id) nosegment tablespace bowie_ts;

Index created.

We note this Fake Index is NOT listed in either USER_INDEXES or USER_SEGMENTS:

SQL> select index_name, tablespace_name from user_indexes where table_name='BOWIE_TEST';

no rows selected

SQL> select segment_name, segment_type, tablespace_name from user_segments
     where segment_name='BOWIE_TEST_ID_I';

no rows selected

If we run a basic, highly selective query on this table:

SQL> select * from bowie_test where id=42;

Execution Plan
----------------------------------------------------------
Plan hash value: 65548668

--------------------------------------------------------------------------------
| Id | Operation          | Name       | Rows | Bytes | Cost (%CPU) | Time     |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |            |    1 |    16 |      11 (0) | 00:00:01 |
|* 1 |  TABLE ACCESS FULL | BOWIE_TEST |    1 |    16 |      11 (0) | 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

     1 - filter("ID"=42)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
         38 consistent gets
          0 physical reads
          0 redo size
        648 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

We notice the CBO uses a FTS. The Fake Index is NOT considered by default.

However, if we set the session as follows and re-run the query:

SQL> alter session set "_use_nosegment_indexes" = true;

Session altered.

SQL> select * from bowie_test where id=42;

Execution Plan
----------------------------------------------------------
Plan hash value: 1280686875

-------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name            | Rows | Bytes | Cost (%CPU) | Time     |
-------------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |                 |    1 |    16 |       2 (0) | 00:00:01 |
|  1 |  TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE_TEST      |    1 |    16 |       2 (0) | 00:00:01 |
|* 2 |   INDEX RANGE SCAN                   | BOWIE_TEST_ID_I |    1 |       |       1 (0) | 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

     2 - access("ID"=42)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
         38 consistent gets
          0 physical reads
          0 redo size
        648 bytes sent via SQL*Net to client
         52 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed

We can see the CBO appears to now use the Fake Index, but as it doesn’t actually physically exist, actually uses a FTS behind the scenes (the number of consistent gets is evidence of this). But at least we now know the CBO would at least consider such an index if it physically existed.

We now decide to drop the tablespace and so first try to MOVE the table to another tablespace using the ONLINE option:

SQL> alter table bowie_test move online tablespace users;
alter table bowie_test move online tablespace users
*
ERROR at line 1:
ORA-14808: table does not support ONLINE MOVE TABLE because of the presence of nosegment index

The error message clearly states we can’t move the table ONLINE if such a Fake/Nosegment Index exists. This is our official warning of the potential danger to come…

We try to move the table using the default OFFLINE method:

SQL> alter table bowie_test move tablespace users;

Table altered.

We have now successfully moved the table to another tablespace.

If we check to see if we have any other segments within the tablespace yto be dropped:

SQL> select segment_name from dba_segments where tablespace_name='BOWIE_TS';

no rows selected

Oracle tells us that no, we do NOT have any current segments in this tablespace.

So it’s now safe to purge and drop this tablespace (or so we think):

SQL> purge tablespace bowie_ts;

Tablespace purged.

SQL> drop tablespace bowie_ts;

Tablespace dropped.

The tablespace has been successfully dropped.

However, if we now re-run the query on this table:

SQL> select * from bowie_test where id=42;
select * from bowie_test where id=42
*
ERROR at line 1:
ORA-00959: tablespace 'BOWIE_TS' does not exist

We get this unexpected error that the tablespace BOWIE_TS does not exist.

BUT, we already know the tablespace doesn’t exist, we’ve just dropped it !!!

So why are we getting this error?

It’s all due to the damn Fake Index we created previously.

Although there is no physical index segment for our Fake Index, there are still some internal Data Dictionary links between the Fake Index and the tablespace it was associated with. The tablespace is gone, but NOT the Fake Index.

The only place where fake indexes can be easily found within Oracle, is within the USER_OBJECTS view:

SQL> select o.object_name, o.object_type, o.status
from user_objects o left join user_indexes i
on o.object_name=i.index_name
where o.object_type='INDEX' and i.index_name is null;

OBJECT_NAME                    OBJECT_TYPE             STATUS
------------------------------ ----------------------- -------
BOWIE_TEST_ID_I                INDEX                   VALID

To eliminate this error, we have to first drop the Fake Index associated with the dropped tablespace:

SQL> drop index bowie_test_id_i;

Index dropped.

We can now safely run the query without error:

SQL> select * from bowie_test where id=42;

        ID NAME
---------- ------------------------------------------
42         DAVID BOWIE

So if you do ever create Fake Indexes, don’t forget to drop them once you’ve finished experimenting with them.

ESPECIALLY if you ever decide to drop the tablespace into which they were associated. This is explained in part in Oracle Support Doc ID 1361049.1.