O'Reilly logo

Oracle Database Administration: The Essential Refe by Brian Laskey, David C. Kreines

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required