Oracle SQL*Plus: The Definitive Guide

Improving on EXPLAIN PLAN Results

If you don’t like the results you get from EXPLAIN PLAN, there are some things you can do to 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, though, 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 also 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 a very inefficient approach. Many people avoid it out of hand, thinking that an indexed retrieval is always better. If you have a reasonably large table and are searching for only one or two rows, then a full table scan is not an efficient 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 in between these two extremes lies a large grey 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 ...