CertPal

I ran into a weird little error while trying to tune the performance of a query in the oracle database. A table had an index on a VARCHAR2 column. After a fair number of inserts were made the population of this table grew to 1.5 million records. A query that did an join on the VARCHAR2 column was talking ages to get the results. Well, it was taking 900ms actually but the SLA for the API call is way below that.

An “explain plan for QUERY” statement was run followed by a select from plan_details. It turns out the index is never used and a full table scan was used to get the data. Hence the problem.