Q

Determining which execution plan is better

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

How do you determine that one execution plan is better than another?
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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close