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.
 

C:\sql>sqlplus test/test
SQL*Plus: Release 10.2.0.2.0 - Production on Tue Oct 10 2006Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - ProdWith the Partitioning, OLAP and Data Mining options

test@SVTEST> set autotrace traceonlytest@SVTEST> alter session set optimizer_mode=rule;
Session altered.
Elapsed: 00:00:00.01
test@SVTEST> 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.
                    




Thank you Eric.