An additional comment on RBO vs. CBO

Oracle itself actually continues to user RBO. Oracle
does not support (at least this used to be the case)
analyzing the system tables.

Typically in an OLTP environment you want to use
first_rows because it optimizes to return the first
row as quickly as possible, while it may take longer
to retrieve the rest of the rows. It is typical in these
environments for a user to want to see only a single
row.

In a DSS environment you typically are returning
many rows during a query and very rarely are only
returning a single row. Most of the queries involve
full table scans vs. indexes (one of the reasons Oracle
put in the CBO was to deal with the fact that with
queries that are returning a large percentage of table
rows it is better to do a full table scan than using an
index - in RBO, a full table scan is the absolute last
resort). In the case of these types of queries, you
don't care about how quickly the first row is returned
(best response time) but rather how much resource
is used to return the entire data set (best throughput).

Just as a little extra....

Access Paths
1. Single row by ROWID
2. Single row by cluster join
3. Single row by hash cluster key w/ unique or primary key
4. Single row by unique or primary key
5. Cluster join
6. Hash cluster key
7. Index 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. MAX or MIN of indexed column
14. ORDER BY on indexed columns
15. Full table scan

The RBO will choose the lowest numbered access path possible.

There are also several unranked access paths (can't be used with RBO)

Fast full index scan
Bitmap index scan

Joe