Chapter 17. Cost-Based Optimizers
Supposedly, a rule-based optimizer (RBO) differs from a cost-based optimizer (CBO). Consider this SQL statement:
SELECT * FROM Table1 WHERE column2 = 55
column2 is an indexed, non-unique column. A rule-based optimizer will find
column2 in the system catalog and discover that it is indexed, but not uniquely indexed. The RBO then combines this data with the information that the query uses the equals operator. A common assumption in the field of optimization is that “
= <literal>” search conditions will retrieve 5% of all rows. (In contrast, the assumption for greater-than conditions is that they will retrieve 25% of all rows.) That is a narrow search, and usually it's faster to perform a narrow search ...