Missing indexes in Oracle

Some little details cause trouble entirely disproportionate to their importance. One such thing is a little quirk in how Oracle handles indexes.

I have some code which gets a list of tables using a particular criteria and then gets all of the indexes associated with those tables. Under normal circumstances, the code works fine. However, when testing in a clean database, it will completely fail to find any of the associated indexes, even though they’ve been verified to exist.

…or have they? It turns out that when the corresponding table does not contain any data, the index may still be associated with the table but it doesn’t really exist. So when you run a query like this:

select * from dba_indexes ind
join dba_segments s
on ind.index_name=s.segment_name
where ind.table_name=’MYTABLE’

It returns no results. Cue confusion. Adding a row to the table causes the index to actually be created and allows my script to start working correctly.

Leave a Reply

Your email address will not be published.