Apparently it is still out there, the RBO in 10g and even Oracle 10.2.0.2.0, 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( http://forums.oracle.com/forums/message.jspa?messageID=1499581)
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 11.1.0.6.0)
C:\sql>sqlplus test/test
SQL*Plus: Release 10.2.0.2.0 - Production on Tue Oct 10 2006
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 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
SQL> select * from dual;
Elapsed: 00:00:00.03
Execution Plan
----------------------------------
Plan hash value: 272002086
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| DUAL |
----------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
———————————————————-
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.
