Rules of Precedence
The rule-based optimizer has 15 rules that it uses to determine how to parse a query. Each rule has a rank. The optimizer looks at all the combinations it can find, then chooses the access path with the lowest rank. Table 8.2 lists the 15 rules in order.
|
Rank |
Access Path |
|
1 |
Single row by ROWID |
|
2 |
Single row by cluster join |
|
3 |
Single row by hash cluster key with unique or primary key |
|
4 |
Single row by unique or primary key |
|
5 |
Cluster join |
|
6 |
Hash cluster key |
|
7 |
Indexed cluster key |
|
8 |
Composite key |
|
9 |
Single-column indexes |
|
10 |
Bounded range search on indexed columns |
|
11 |
Unbounded range search on indexed columns |
|
12 |
Sort-merge join |
|
13 |
Maximum or minimum of indexed column |
|
14 |
Order by an indexed column |
|
15 |
Full table scan |
If the rule-based optimizer finds a situation where it can apply a rule with a ranking of 11 or less, it will perform a nested loop join. For example, assume that you are joining two tables, one without an index and the other with a unique index. The unique index has a rank of 4. Therefore, the rule-based optimizer will perform a full table scan on the table without an index, using a nested loop join to query the rows out of the second table.