RBO changes plan from 8i to 9i

Hi all

I am migrating a Siebel 7.x CRM database from 8i to 9i under HPUX. This
version of Siebel only supports RBO therefore we will be using RBO in 9i as
well.

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)


tia

Other related posts: