Oracle fake index

Recently following the document ID 456468.1, I made a verification of the integrity of the catalog. Anyway, they have appeared a couple of errors indicating a problem with an object identifier:

1703982

This object exist in dba_objects, the query:

Source   
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM dba_objects WHERE object_id=1703982

Return:

Source   
OWNER   OBJECT_NAME                   OBJECT_TYPE
-----  ---------------------------   -------------------
APP     QUEST_SX_91B74A9BF3DEF56D61 INDEX

I try to get more information with:

Source   
SELECT * FROM DBA_INDEXES I WHERE i.index_name='QUEST_SX_91B74A9BF3DEF56D61'

And in this case it returns nothing, very strange
At first it seems that there really is a problem of catalog integrity, but if you look a little Metalink can be found in the document ID 329457.1.
This document discusses the FAKE indexes, which are only indexes created by assistants of Enterprise Manager, to see the effect it would have on an implementation plan CBO the existence of a new index.
You can create an index of this type:

Source   
CREATE INDEX idx2 ON myobj (object_name) NOSEGMENT;

As you can see it is an index without segments.
Anyway, I’ve found have not been created by Enterprise Manager, but it TOAD. For that reason boombox sen detected as an integrity problem catalog.
You can see all existing fake indexes using the query:

Source   
SELECT o.object_name AS fake_index_name FROM dba_objects o
WHERE o.object_type = 'INDEX' AND NOT EXISTS
(SELECT NULL FROM dba_indexes i WHERE o.object_name = i.index_name AND o.owner = i.owner);

These indices can even analyze, but not much more. I deleted that are showing me as problematic.

Leave a Reply