Skip to Main Content
Oracle in a Nutshell
book

Oracle in a Nutshell

by Rick Greenwald, David C. Kreines
December 2002
Intermediate to advanced content levelIntermediate to advanced
928 pages
85h 29m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle Internals

Oracle Internals

Donald K. Burleson
Oracle PL/SQL

Oracle PL/SQL

Lewis Cunningham
Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition

Rick Greenwald, Robert Stackowiak, Jonathan Stern

Publisher Resources

ISBN: 0596003366Errata Page