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