Rule based in Oracle 10g

Apparently it is still out there, the RBO in 10g and even Oracle, as demonstrated by Eric, and i thought that it was already had gone. Especially in Oracle 10gR2. Maybe not internally, but untouchable by us mere mortals.

I saw the following post of Eric Jenkinson in an OTN forum(

The rule based optimizer is most definitely present in 10gR2. It might not be in the documentation, but it is still there (By the way, it also works in Oracle

C:\sql>sqlplus test/test
SQL*Plus: Release - Production ON Tue Oct 10 2006
Copyright (c) 1982, 2005, Oracle.  ALL Rights Reserved.
Connected TO:
Oracle DATABASE 10g Enterprise Edition Release - Prod
WITH the Partitioning, OLAP AND DATA Mining options
SQL> SET autotrace traceonly
SQL> ALTER SESSION SET optimizer_mode=rule;
SESSION altered.
Elapsed: 00:00:00.01
Elapsed: 00:00:00.03
Execution Plan
Plan hash VALUE: 272002086
| Id  | Operation         | Name |
|   0 | SELECT STATEMENT  |      |
- rule based optimizer used (consider USING cbo)
          1  recursive calls
          0  db block gets
          3  consistent gets
          2  physical reads
          0  redo SIZE
        407  bytes sent via SQL*Net TO client
        381  bytes received via SQL*Net FROM client
          2  SQL*Net roundtrips TO/FROM client
          0  sorts (memory)
          0  sorts (disk)
          1  ROWS processed

The other remark he makes is also, I think, a valid one (and one that could be expected):

Also, if perform 10046 trace on SQL statements that generate recursive SQL you will see Oracle still uses the RULE hint on some of the queries.

It is not supported, but it is still there.

Marco Gralike Written by: