Re: RBO changes plan from 8i to 9i

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_at_gmail.com>:
> 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
--
http://www.freelists.org/webpage/oracle-l