Re: Re: sql explain plan issue
- From: <ryan.gaffuri@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 5 Feb 2004 13:33:54 -0500
someone just asked this question on asktom and got a very interesting response.
its up on the main page now.
>
> From: Jared.Still@xxxxxxxxxxx
> Date: 2004/02/05 Thu PM 12:22:30 EST
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Re: sql explain plan issue
>
> 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@xxxxxxxxxxxxx>
> Sent by: oracle-l-bounce@xxxxxxxxxxxxx
> 02/04/2004 11:13 AM
> Please respond to oracle-l
>
>
> To: oracle-l@xxxxxxxxxxxxx
> 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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
>
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
- » Re: Re: sql explain plan issue
