But first, let’s start at the beginning…
The rule-based optimizer (RBO) has only a small amount of information to use in deciding upon an execution plan for a SQL statement:
• The text of the SQL statement itself
• Basic information about the objects in the SQL statement, such as the tables, clusters, and views in the FROM clause and the data type of the columns referenced in the other clauses
• Basic information about indexes associated with the tables referenced by the SQL statement
• Data dictionary information is only available for the local database. If you’re referencing a remote database, the remote dictionary information is not available to the RBO…
In order to determine the execution plan, the RBO first examines the WHERE clause of the statement, separating each predicate from one another for evaluation, starting from the bottom of the statement. It applies a score for each predicate, using the fifteen access methods ordered by their alleged merit:
1. Single row by ROWID
2. Single row by cluster join
3. Single row by hash cluster key with unique key
4. Single row by unique index
5. Cluster join
6. Hash cluster key
7. Indexed cluster key
8. Composite key
9. Single-column non-unique index
10. Bounded range search on indexed columns
11. Unbounded range search on indexed columns
12. Sort-merge join
13. MAX or MIN of indexed column
14. ORDER BY on indexed columns
15. Full table-scan
Suggest you check out:
www.evdbt.com/SearchIntelligenceCBO.doc
which is a pretty good paper on this topic.
