Re: sql explain plan issue

From: <Jared.Still_at_radisys.com>
Date: Thu, 5 Feb 2004 09:22:30 -0800
Message-ID: <OF8BCD221D.B764E860-ON88256E31.005F5415-88256E31.005F60BF@radisys.com>

Other than the fact that the table access step appears in a different location,
these plans appear identical.
Why it is in a different order I can't explain.

I'm sure someone here knows why, and whether or not it matters.

Jared

Sai Selvaganesan <ssaisundar_at_sbcglobal.net> Sent by: oracle-l-bounce_at_freelists.org
02/04/2004 11:13 AM
Please respond to oracle-l

        To:     oracle-l_at_freelists.org
        cc: 
        Subject:        sql explain plan issue

hi
the below is a sql code and explain plan on two environments the first one is 81.7.3 and the next one is 9i. can someone please point out why there is a diff

SELECT SQ1_T2.ORG_ID
FROM S_PARTY SQ1_T1, S_ORG_BU SQ1_T2, S_ORG_EXT SQ1_T3 WHERE

(SQ1_T3.INT_ORG_FLG = 'Y') AND 
(SQ1_T1.ROW_ID = SQ1_T3.PAR_ROW_ID AND SQ1_T2.BU_ID = SQ1_T1.ROW_ID) AND 
(SQ1_T3.PAR_BU_ID = '0-R9NH')

Execution Plan

0 SELECT STATEMENT Optimizer=RULE
1 0 NESTED LOOPS
2 1 NESTED LOOPS
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_EXT' 4 3 INDEX (RANGE SCAN) OF 'S_ORG_EXT_F13' (NON-UNIQUE) 5 2 INDEX (UNIQUE SCAN) OF 'S_PARTY_P1' (UNIQUE) 6 1 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_BU' 7 6 INDEX (RANGE SCAN) OF 'S_ORG_BU_M4' (NON-UNIQUE) Execution Plan

0 SELECT STATEMENT Optimizer=RULE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_BU' 2 1 NESTED LOOPS
3 2 NESTED LOOPS
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'S_ORG_EXT'

5 4 INDEX (RANGE SCAN) OF 'S_ORG_EXT_F13' (NON-UNIQUE) 
6 3 INDEX (UNIQUE SCAN) OF 'S_PARTY_P1' (UNIQUE) 
7 2 INDEX (RANGE SCAN) OF 'S_ORG_BU_M4' (NON-UNIQUE)

the objects are the same but the plan is different. this is a third party software, hence rule based.

thanks
sai

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Please see the official ORACLE-L FAQ: http://www.orafaq.com

Received on Thu Feb 05 2004 - 11:22:30 CST