Rule based in Oracle 10g

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 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

test@SVTEST> set autotrace traceonly
test@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.

4 Comments

  1. CSS Compliant October 12, 2006
  2. Marco Gralike October 12, 2006
  3. Marco Gralike October 12, 2006
  4. Howard J. Rogers October 12, 2006