Oracle Database Administration: The Essential Refe

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.

Table 8-2. Rule-Based Optimizer Rules of Precedence

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.