invisible index

Havent blogged in a long while (thanks to my busy schedule and interesting projects assignments)..

anyway, just thought I would post about this really cool 11g feature...There are countless situations wherein a tuning specialist has to work straight in production environment. But you wont be allowed to create new indexes because thought it may make your current query/sql being analyzed to go faster, there is always a concern that it might affect the other existing queries and potentially generate a new plan which may be worse than the current plan.

11g introduces something called "invisible indexes" to solve this issue (only theoretical knowledge from what I have read..no idea on how to implement as I haven't gotten my hands on or haven't read docs yet).. Now basically what you do is create an invisible index..and this index would be considered by optimizer only when hinted..

so until we fix the performance problem of say a single sql..we can simply have many invisible indexes to make the query go faster..and simply edit the statement to add a hint to use the new indexes and that wouldnt affect anything else which are running smooth in prod already..

Cool & simple ..& I could see its use already :)