Ask the Expert

Determining which execution plan is better

How do you determine that one execution plan is better than another?

    Requires Free Membership to View

The truth is that you cannot look at one execution plan and compare it to another and make a determination of which is better. To me, the "better" execution plan is the plan that returns the results the quickest. So the only way to know for sure which plan produces results the quickest, you have to run the SQL statement with both plans and time them. For example, assume that I have the following query:
SELECT * FROM orders WHERE customer_agent='Bob109';
I have one execution plan that says a FULL TABLE SCAN will be performed on ORDERS. I have another execution plan that says that an INDEX SCAN will be performed on ORDERS. Which is better? Just looking at this information, we cannot tell. Many people will initially say that the INDEX SCAN will perform faster than a FULL TABLE SCAN, but this is not always the case. I can easily construct a test case where the opposite is true. The FULL TABLE SCAN will be preferrable over the INDEX SCAN if the value 'Bob109' returns more than 5% (some will say as high as 10%) of the rows of the table. If the CUSTOMER_AGENT column is guaranteed to be unique, then a full table scan will be slower . . . unless the table is only one block in size. Hopefully, you can see that there is a lot here to determine which execution plan is "better." Oracle's Cost Based Optimizer (CBO) uses many statistics to help make these determinations.

This was first published in November 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: