ll these years I’ve been faking it and no one has ever caught me. This short tutorial will teach how to fake it too.

An error I found this morning, while look at a DataPump import, was the following:

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-08114: can not alter a fake index
Failing sql is:
ALTER INDEX “ASAP”.”QUEST_SX_IDX8812BA0BC1CC2074CC” NOPARALLEL

Most DBAs, even DBAs with 20+ years of experience have never heard of a fake index and wouldn’t know one if they saw it. Here’s the history lesson in how they came into being.

Oracle’s ADDM, introduced in version 10.1, gives tuning advice. And one of the hidden techniques it uses to do this is to build fake indexes, run hidden explain plans, and see if the optimizer will use the index. NOSEGMENT indexes were the indexing trick and the DBMS_XPLAN package was how the plan was created behind the scenes. And if the index was used and the plan cost low ADDM recommends that you build the index: Simple.

A real index is a complex thing. It is an index object (IND$ and OBJ$), consists of a segment (SEG$), composed of extents (UET$), is owned by someone (USER$), the segment is in a tablespace (TS$), that consists of one or more datafiles (FILE$). And I’m just getting started. Once you issue the DDL command Oracle does lots of latching, recursive SQL, space allocation, a full table scan of the table, etc. etc. and has to build it which is I/O intensive. Now assume a table with millions of rows and it gets ugly. So what was Oracle to do? How could it try 10, 20, 50 different index possibilities and report which one was best on a running production database? The answer was NOSEGMENT indexes, also known as FAKE indexes. Fake in the sense that no index is really built. What happens is that two different entries are made into the data dictionary sufficient to fool the optimizer into thinking there is an index which is not actually built. So the index “build” takes 20 milliseconds or so. The optimizer only knows about two things ... objects and index columns ... so the two inserts are into OBJ$ and INDCOL$.

Now ADDM, after its analysis  is supposed to drop the NOSEGMENT indexes it creates. But occasionally, as happened at SF, it fails to do so for some reason. In the above case it appears the fake index was created by a Quest product so likely the user session crashed or they ended their session in some other way that aborted the session. Alternatively Quest may not use ADDM since they wouldn’t know a client’s licensing status and probably wrote their own engine to create, test, and drop them.

So the way you find these things, if you are looking for them is with the following SQL statement. What it does is look for index objects (OBJ$) that are not in dba_indexes (IND$) ... remember fake index creation only hit INDCOL$.

SELECT owner, object_name
FROM dba_objects
WHERE object_type = 'INDEX'
MINUS
SELECT owner, index_name
FROM dba_indexes;