Toad World® Forums

Orphaned Virtual Indexes

Hi, Has any one heard of and know a fix for SQL Optimizer leaving behind a phantom index definition in the database. I have been using the tool to tune for a few months now (against copy production databases). We have found that on occasion when the tuning activity has suggested that a new index be created (as far as I understand a ‘virtual’ index is created) on leaving the tool there is still a ‘ghost’ of that index in the database. As far as we can determine it consists of a record in the dba_objects view, but cannot be seen in the dba_views view ! I have not yet been able to get this to occure on our development databases. Any ideas though much appreciated.

You can start another Optimize Index, it will detect any left behind virtual indexes and you have option to drop those indexes at that screen.

Thank you Richard. Not yet had any succcess. As i don’t have the original query that was being tuned, I’ve not yet recreated one that suggests the specific index, but will keep trying.

You can use any SQL to start an Optimize Index action, (select * from dual where dummy =‘X’), it detects left behind virtual indexes without considering your SQL.