Monthly Archives: January 2014

Why wouldn’t Oracle use a perfectly valid index?

I’ve been working on a project where Oracle suddenly stopped using a perfectly valid index. It is a unique index, so if you run a query for 20 different key values, there will be (at most) 20 rows. But instead of using this index, Oracle started doing a full table scan on the table, but it has a few hundred million rows, so it was game over for the application and it was totally incomprehensible. I am 95% developer and 5% DBA, I know my way around Oracle, SQL and simple optimizations, but I couldn’t find any explanation for this issue, so I had to start digging deeper and deeper. I learned a lot about Oracle’s internals until I finally found the solution. Instead of just writing about the end result, I decided to tell my whole story from the very beginning. Not only was it a very interesting investigation for me, but it can be useful to anyone in a similar situation. Continue reading