Re: RBO changes plan from 8i to 9i

  • From: "Alex Gorbachev" <gorbyx@xxxxxxxxx>
  • To: ax.mount@xxxxxxxxx
  • Date: Wed, 12 Jul 2006 10:16:04 +0200

The plan looks almost the same.
Perhaps, 9i got a bit smarter and avoided accessing the table
S_ESCL_STATE before the join - I guess your join columns are part of
the indexes S_ESCL_RULE_M1 and S_ESCL_RULE_M1.
On the other hand, it might be just a different output of exection plan.

2006/7/12, amonte <ax.mount@xxxxxxxxx>: 
I noticed that many execution plan changed when migrated to 9i. I thought
RBO is not affected by init.ora parameters, version changes and its
development stopped ages ago.

Any clues?

Most change are like this:

ORIGINAL:
-----------------
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   SORT (ORDER BY)
   2    1     NESTED LOOPS
   3    2       INDEX (RANGE SCAN) OF 'S_ESCL_RULE_M1' (UNIQUE)
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'S_ESCL_STATE'
   5    4         INDEX (RANGE SCAN) OF 'S_ESCL_STATE_M1' (NON-UNIQUE)

9i:
--------------------
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE
   1    0   SORT (ORDER BY)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'S_ESCL_STATE'
   3    2       NESTED LOOPS
   4    3         INDEX (RANGE SCAN) OF 'S_ESCL_RULE_M1' (UNIQUE)
   5    3         INDEX (RANGE SCAN) OF 'S_ESCL_STATE_M1' (NON-UNIQUE)



--
Best regards,
Alex Gorbachev

http://blog.oracloid.com
--
//www.freelists.org/webpage/oracle-l


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 07-2006