Check for a missing index

If you think Oracle should be using an index to resolve your query and it is not doing so, then make sure the index exists. Here are two ways to check:

Do any indexes exist? If so, is there one where the leading columns of the index match the columns of your WHERE clause?

If the answer is NO to either of these questions, then the problem is that there is no index that covers your SQL. Perhaps the index has been accidently dropped, or it never existed at all. You have four choices:

  1. If the index has been dropped accidently, get the DBA to re-create it.
  2. Restructure your query. Perhaps the structure of your database permits you to retrieve the information in another way that does contain an index.
  3. How bad is the performance really? Is it unacceptably slow? If not, then doing nothing is a valid course of action. But consider that performance will deteriorate if the table is growing.
  4. Discuss with the DBA the prospect of adding a new index. Providing the index is efficient, and you have genuinely considered and rejected the options above, the DBA sould have no objections. However if the index is inefficient, then it will not help your query. You could discuss with the DBA the merits of partitioning, or a hash cluster.

©Copyright 2003