Oracle in a Nutshell

Rule-based Optimizer

The rule-based optimizer, as the name implies, uses a set of predefined rules to determine query optimization.

The rule-based optimizer uses the set of rules summarized in Table 17-1, in the order shown in the table, to select an execution path.

Table 17-1. Rule precedence for the rule-based optimizer

Rule	Meaning
Single row by ROWID	Use ROWID in the WHERE clause, or CURRENT OF CURSOR
Single row by cluster join	Use cluster key in the WHERE clause; query returns only one row
Single row by hash cluster key with a unique or primary key	Use all columns in hash cluster key in the WHERE clause; query returns only one row
Single row by unique or primary key	Use unique or primary key in the WHERE clause; query returns only single row
Clustered join	Use when all tables are in cluster and all columns in the cluster key are in the WHERE clause with equality condition
Hash cluster key	Use when all columns in hash cluster key are in the WHERE clause with equality condition
Indexed cluster key	Use when all columns in indexed cluster key are in the WHERE clause with equality condition
Composite index	Use when all columns in composite index are in the WHERE clause with equality condition
Single-column index	Use when all columns in single-column index are in the WHERE clause with equality condition
Bounded range search on an indexed column	Use when indexed column is in the WHERE clause with bounded values specified
Unbounded range search on an indexed ...