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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation