Last week I talked
about adding a user index on JOB.EMPL_STATUS that resulted in
an 80% reduction in run time for a query. Well, bad news on that
one—I have now dropped the new index. It did, in fact, save 80%
of the run time for that query, but only for that one day!
The person testing these queries sent me another similar one
that was now running seemingly forever. The query plan showed
that it was using the new index. I dropped the index and the
query plan showed a major reshuffling of the various access paths.
Now the SELECT was running slowly, but at least it was finishing
within a reasonable period of time. I recreated the index and
now the query ran quickly. What was going on? This situation
should have been the same as what I started with,
but instead of taking "forever", the query was
now finishing within seconds.
Updating the statistics on JOB revealed the problem. We update
the statistics in our database every night. During this
operation, the database (Oracle, in this case) analyzes each
table and index to understand the types of data it contains.
This theoretically helps in developing the best query plan.
When I created the index, Oracle didn't look closely at the
data and just made some generic assumptions about the index. But when
the statistics were eventually updated, Oracle noticed that
this index contains very few unique values (mostly "A" and "T"),
and this changed its opinion of how the index should be used.
That, unfortunately, destroyed its usefulness as an index
for our queries. This is why this particular index was a good idea—for one day!