Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Improving on EXPLAIN PLAN Results

If you don't like the results you get from EXPLAIN PLAN, you can change how Oracle executes your query. Generally speaking, these things fall into the following three categories:

• Restating the query

• Creating or modifying indexes

• Using hints

First, however, you have to be sure the default execution path is a bad one. This isn't as easy as you may think.

Knowing Good Results from Bad

Knowing a good execution plan from a bad one requires some degree of experience and judgment. It helps to understand your data. In many cases, it may not be enough to look at the plan. You may have to do some benchmarking as well.

Consider the issue of doing a full table scan, reading all the rows in the table, to find rows for a query. On the surface, reading the entire table to find the desired rows seems like an inefficient approach. Many people avoid it, thinking that an indexed retrieval is better. But this isn't necessarily the case. If you have a reasonably large table and are searching for one or two rows, then a full table scan is an inefficient approach. However, if you are retrieving or summarizing a large percentage of the rows in the table, then a full table scan will likely outperform an indexed retrieval. The problem is that somewhere between these two extremes lies a large gray area. That's where you have to do some benchmarking and use some judgment based on your expectations of what the query will be asked to do when it is in production.

Here are some questions ...