RBO changes plan from 8i to 9i

From: amonte <ax.mount_at_gmail.com>
Date: Wed, 12 Jul 2006 08:17:39 +0200
Message-ID: <85c1fb130607112317q280dc1e6y327f7b2bec06c9ca@mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jul 12 2006 - 01:17:39 CDT