Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Sincere Advice on Sql Plan - Thanks

Re: Sincere Advice on Sql Plan - Thanks

From: Ganesh Raja <ganesh.raja_at_gmail.com>
Date: Wed, 13 Oct 2004 19:28:34 +0100
Message-ID: <f754edf04101311286d7b7ac1@mail.gmail.com>


Lex,
Not sure If Oracle-L Accepts Attachments ..

Here are the Queries and the Plan.

Thanks for the help.

Rgds,
Ganesh

On Wed, 13 Oct 2004 19:58:55 +0200, Lex de Haan <lex.de.haan_at_naturaljoin.nl> wrote:
> you might want to provide the two statements you are talking about?
> and maybe even the two corresponding execution plans?
>
> Kind regards,
> Lex.
>
> -------------------------------
> visit http://www.naturaljoin.nl
> -------------------------------
> skype me <callto://lexdehaan>
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Ganesh Raja
> Sent: Wednesday, October 13, 2004 18:54
> To: jkstill_at_gmail.com
> Cc: smishra_97_at_yahoo.com; oracle-l_at_freelists.org
> Subject: Re: Sincere Advice on Sql Plan - Thanks
>
> Okay I am Going to Piggy Back on this ..
>
> I have an ANSI SQL which when Rewriiten Using Oracle Syntax it seems
> to take a Better plan to execute it .. Any ideas why this is
> happening.
>
> There is a Total Change in the plan and it works much faster than the
> ANSI Counterpart
>
> Any help is appreciated.
>
> Thanks.
>
> Cheers
> Ganesh R
>
> --
> http://www.freelists.org/webpage/oracle-l
>

Explain Plan for
SELECT REVIEW_ID rid
FROM KDD_REVIEW_STATUS_DESC left outer join KDD_CENTRICITY left outer join KDD_SCNRO inner join (KDD_REVIEW_SCNRO left outer join (KDD_REVIEW
left outer join KDD_REVIEW_OWNER using (OWNER_SEQ_ID)) using (REVIEW_ID) left outer join KDD_REVIEW_FINANCIAL using (REVIEW_ID)) on (KDD_REVIEW_SCNRO.SCNRO_ID = KDD_SCNRO.SCNRO_ID) on (KDD_REVIEW.CNTRY_ID = KDD_CENTRICITY.CNTRY_ID) on (KDD_REVIEW.STATUS_CD = KDD_REVIEW_STATUS_DESC.STATUS_CD) WHERE KDD_REVIEW.PRCSNG_BATCH_CMPLT_FL != 'N' AND OVERLAYS('abf', KDD_REVIEW.BUS_DMN_ST) != 'N'

AND KDD_REVIEW.STATUS_CD in ('OP', 'NW', 'RO', 'FL', 'RA')
AND KDD_REVIEW.SCNRO_CLASS_CD in ('TC', 'BEX', 'IML')
AND KDD_REVIEW_SCNRO.SCNRO_ID in (114000022)
AND KDD_REVIEW.CNTRY_ID in (113000011,113000009,113000006,113000004,113000002,113000001)
AND OWNER_SEQ_ID in (50,39,34,32,19,49,48,47,16,15,46,14,45,13,12,43,11,42,10,40,9,8,7,6,5,4,3,24,2,23,22,52,21,51)
AND KDD_REVIEW.JRSDCN_CD in ('DFLT','MUM','SNG','BOK','SEO','LDN','APC','SYD','NYK','TOK','HKG') ORDER BY KDD_REVIEW.SCORE_CT DESC, KDD_REVIEW.CREAT_TS DESC, REVIEW_ID DESC /

SQL> @plan


| Id  | Operation                            |  Name               | Rows  | Bytes |TempSpc| Cost (%CPU)|
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                     |   297K|    36M|       |   720K  (1)|
|   1 |  SORT ORDER BY                       |                     |   297K|    36M|    77M|   720K  (1)|
|   2 |   NESTED LOOPS                       |                     |   297K|    36M|       |   714K  (1)|
|*  3 |    FILTER                            |                     |       |       |       |         |
|   4 |     NESTED LOOPS OUTER               |                     |       |       |       |         |
|   5 |      INDEX FULL SCAN                 | PK_KDD_CENTRICITY   |    38 |   266 |       |     1   (0)|
|   6 |      VIEW                            |                     |  9395 |  1100K|       |         |
|*  7 |       FILTER                         |                     |       |       |       |         |
|   8 |        NESTED LOOPS OUTER            |                     |       |       |       |         |
|   9 |         NESTED LOOPS OUTER           |                     |  9395 |   211K|       |     6  (17)|
|  10 |          NESTED LOOPS                |                     |  9395 |   174K|       |     5  (20)|
|  11 |           TABLE ACCESS FULL          | KDD_REVIEW_SCNRO    |  9395 |   110K|       |     3   (0)|
|* 12 |           INDEX UNIQUE SCAN          | PK_KDD_SCNRO        |     1 |     7 |       |         |
|* 13 |          INDEX UNIQUE SCAN           | PK_KDD_REVIEW_FNCL  |     1 |     4 |       |         |
|  14 |         VIEW                         |                     |     1 |    94 |       |         |
|  15 |          NESTED LOOPS OUTER          |                     |     1 |    46 |       |     3  (34)|
|  16 |           TABLE ACCESS BY INDEX ROWID| KDD_REVIEW          |     1 |    43 |       |     2  (50)|
|* 17 |            INDEX UNIQUE SCAN         | PK_KDD_REVIEW       |     1 |       |       |     1   (0)|
|* 18 |           INDEX UNIQUE SCAN          | PK_K_RVW_OWNER      |     1 |     3 |       |         |
|* 19 |    INDEX RANGE SCAN                  | IFK3_K_RVW_STUS_CD  |     1 |     3 |       |         |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):


   3 - filter("from$_subquery$_017"."PRCSNG_BATCH_CMPLT_FL_60"<>'N' AND

              ("from$_subquery$_017"."STATUS_CD_18"='FL' OR "from$_subquery$_017"."STATUS_CD_18"='NW' OR
              "from$_subquery$_017"."STATUS_CD_18"='OP' OR "from$_subquery$_017"."STATUS_CD_18"='RA' OR
              "from$_subquery$_017"."STATUS_CD_18"='RO') AND ("from$_subquery$_017"."SCNRO_CLASS_CD_24"='BEX' OR
              "from$_subquery$_017"."SCNRO_CLASS_CD_24"='IML' OR "from$_subquery$_017"."SCNRO_CLASS_CD_24"='TC') AND
              "from$_subquery$_017"."SCNRO_ID_11"=114000022 AND ("from$_subquery$_017"."CNTRY_ID_20"=113000001 OR
              "from$_subquery$_017"."CNTRY_ID_20"=113000002 OR "from$_subquery$_017"."CNTRY_ID_20"=113000004 OR
              "from$_subquery$_017"."CNTRY_ID_20"=113000006 OR "from$_subquery$_017"."CNTRY_ID_20"=113000009 OR
              "from$_subquery$_017"."CNTRY_ID_20"=113000011) AND ("from$_subquery$_017"."OWNER_SEQ_ID_13"=2 OR
              "from$_subquery$_017"."OWNER_SEQ_ID_13"=3 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=4 OR
              "from$_subquery$_017"."OWNER_SEQ_ID_13"=5 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=6 OR
              "from$_subquery$_017"."OWNER_SEQ_ID_13"=7 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=8 OR
              "from$_subquery$_017"."OWNER_SEQ_ID_13"=9 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=10 OR
              "from$_subquery$_017"."OWNER_SEQ_ID_13"=11 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=12 OR
              "from$_subquery$_017"."OWNER_SEQ_ID_13"=13 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=14 OR
              "from$_subquery$_017"."OWNER_SEQ_ID_13"=15 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=16 OR
              "from$_subquery$_017"."OWNER_SEQ_ID_13"=19 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=21 OR
              "from$_subquery$_017"."OWNER_SEQ_ID_13"=22 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=23 OR
              "from$_subquery$_017"."OWNER_SEQ_ID_13"=24 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=32 OR
              "from$_subquery$_017"."OWNER_SEQ_ID_13"=34 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=39 OR
              "from$_subquery$_017"."OWNER_SEQ_ID_13"=40 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=42 OR
              "from$_subquery$_017"."OWNER_SEQ_ID_13"=43 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=45 OR
              "from$_subquery$_017"."OWNER_SEQ_ID_13"=46 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=47 OR
              "from$_subquery$_017"."OWNER_SEQ_ID_13"=48 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=49 OR
              "from$_subquery$_017"."OWNER_SEQ_ID_13"=50 OR "from$_subquery$_017"."OWNER_SEQ_ID_13"=51 OR
              "from$_subquery$_017"."OWNER_SEQ_ID_13"=52) AND ("from$_subquery$_017"."JRSDCN_CD_58"='APC' OR
              "from$_subquery$_017"."JRSDCN_CD_58"='BOK' OR "from$_subquery$_017"."JRSDCN_CD_58"='DFLT' OR
              "from$_subquery$_017"."JRSDCN_CD_58"='HKG' OR "from$_subquery$_017"."JRSDCN_CD_58"='LDN' OR
              "from$_subquery$_017"."JRSDCN_CD_58"='MUM' OR "from$_subquery$_017"."JRSDCN_CD_58"='NYK' OR
              "from$_subquery$_017"."JRSDCN_CD_58"='SEO' OR "from$_subquery$_017"."JRSDCN_CD_58"='SNG' OR
              "from$_subquery$_017"."JRSDCN_CD_58"='SYD' OR "from$_subquery$_017"."JRSDCN_CD_58"='TOK') AND
              "JOE"."OVERLAYS"('abf',"from$_subquery$_017"."BUS_DMN_ST_56")<>'N')
   7 - filter("from$_subquery$_015"."CNTRY_ID_8"="KDD_CENTRICITY"."CNTRY_ID")
  12 - access("KDD_REVIEW_SCNRO"."SCNRO_ID"="KDD_SCNRO"."SCNRO_ID")
  13 - access("KDD_REVIEW_SCNRO"."REVIEW_ID"="KDD_REVIEW_FINANCIAL"."REVIEW_ID"(+))
  17 - access("KDD_REVIEW_SCNRO"."REVIEW_ID"="KDD_REVIEW"."REVIEW_ID")
  18 - access("KDD_REVIEW"."OWNER_SEQ_ID"="KDD_REVIEW_OWNER"."OWNER_SEQ_ID"(+))
  19 - access("from$_subquery$_017"."STATUS_CD_18"="KDD_REVIEW_STATUS_DESC"."STATUS_CD")
       filter("KDD_REVIEW_STATUS_DESC"."STATUS_CD"='FL' OR "KDD_REVIEW_STATUS_DESC"."STATUS_CD"='NW' OR
              "KDD_REVIEW_STATUS_DESC"."STATUS_CD"='OP' OR "KDD_REVIEW_STATUS_DESC"."STATUS_CD"='RA' OR
              "KDD_REVIEW_STATUS_DESC"."STATUS_CD"='RO')

70 rows selected.


Explain Plan for
Select KR.Review_Id
From

	KDD_REVIEW		KR,
	KDD_REVIEW_OWNER		KRO,
	KDD_REVIEW_FINANCIAL	KRF,
	KDD_REVIEW_STATUS_DESC	KRS,
	KDD_REVIEW_SCNRO		KRC,	
	KDD_CENTRICITY		KC,
	KDD_SCNRO		KS
Where	KR.Owner_Seq_Id = KRO.Owner_Seq_Id (+)
And   KR.Review_Id = KRF.REVIEW_ID (+)
And   KR.Status_Cd = KRS.Status_Cd (+)
And   KR.Review_Id  = KRC.REVIEW_ID(+)

And KR.Cntry_Id = KC.CNTRY_ID (+)
And KR.Scnro_Id = KS.Scnro_Id (+)
AND KR.PRCSNG_BATCH_CMPLT_FL != 'N'
AND OVERLAYS('abf', KR.BUS_DMN_ST) != 'N' AND KR.STATUS_CD in ('OP', 'NW', 'RO', 'FL', 'RA') AND KR.SCNRO_CLASS_CD in ('TC', 'BEX', 'IML') AND KRC.SCNRO_ID in (114000022)
AND KR.CNTRY_ID in (113000011,113000009,113000006,113000004,113000002,113000001)
AND KR.OWNER_SEQ_ID in (50,39,34,32,19,49,48,47,16,15,46,14,45,13,12,43,11,42,10,40,9,8,7,6,5,4,3,24,2,23,22,52,21,51)
AND KR.JRSDCN_CD in ('DFLT','MUM','SNG','BOK','SEO','LDN','APC','SYD','NYK','TOK','HKG')
/

SQL> @plan



| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
|   0 | SELECT STATEMENT        |                     |   391 | 29325 |    38   (3)|
|   1 |  NESTED LOOPS OUTER     |                     |   391 | 29325 |    38   (3)|
|   2 |   NESTED LOOPS OUTER    |                     |   391 | 27761 |    37   (3)|
|   3 |    NESTED LOOPS OUTER   |                     |   391 | 26588 |    36   (3)|
|*  4 |     HASH JOIN           |                     |   391 | 23851 |    35   (3)|
|   5 |      NESTED LOOPS OUTER |                     |   391 | 19159 |    30   (4)|
|   6 |       NESTED LOOPS OUTER|                     |   391 | 16422 |    29   (4)|
|*  7 |        TABLE ACCESS FULL| KDD_REVIEW          |   391 | 15249 |    27   (0)|
|*  8 |        INDEX RANGE SCAN | IFK3_K_RVW_STUS_CD  |     1 |     3 |            |
|*  9 |       INDEX UNIQUE SCAN | PK_KDD_SCNRO        |     1 |     7 |            |
|* 10 |      TABLE ACCESS FULL  | KDD_REVIEW_SCNRO    |  1342 | 16104 |     3   (0)|
|* 11 |     INDEX UNIQUE SCAN   | PK_KDD_CENTRICITY   |     1 |     7 |            |
|* 12 |    INDEX UNIQUE SCAN    | PK_K_RVW_OWNER      |     1 |     3 |            |
|* 13 |   INDEX UNIQUE SCAN     | PK_KDD_REVIEW_FNCL  |     1 |     4 |            |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):


   4 - access("KR"."REVIEW_ID"="KRC"."REVIEW_ID")
   7 - filter("KR"."PRCSNG_BATCH_CMPLT_FL"<>'N' AND ("KR"."STATUS_CD"='FL' OR
              "KR"."STATUS_CD"='NW' OR "KR"."STATUS_CD"='OP' OR "KR"."STATUS_CD"='RA' OR
              "KR"."STATUS_CD"='RO') AND ("KR"."SCNRO_CLASS_CD"='BEX' OR
              "KR"."SCNRO_CLASS_CD"='IML' OR "KR"."SCNRO_CLASS_CD"='TC') AND
              ("KR"."CNTRY_ID"=113000001 OR "KR"."CNTRY_ID"=113000002 OR
              "KR"."CNTRY_ID"=113000004 OR "KR"."CNTRY_ID"=113000006 OR "KR"."CNTRY_ID"=113000009
              OR "KR"."CNTRY_ID"=113000011) AND ("KR"."OWNER_SEQ_ID"=2 OR "KR"."OWNER_SEQ_ID"=3
              OR "KR"."OWNER_SEQ_ID"=4 OR "KR"."OWNER_SEQ_ID"=5 OR "KR"."OWNER_SEQ_ID"=6 OR
              "KR"."OWNER_SEQ_ID"=7 OR "KR"."OWNER_SEQ_ID"=8 OR "KR"."OWNER_SEQ_ID"=9 OR
              "KR"."OWNER_SEQ_ID"=10 OR "KR"."OWNER_SEQ_ID"=11 OR "KR"."OWNER_SEQ_ID"=12 OR
              "KR"."OWNER_SEQ_ID"=13 OR "KR"."OWNER_SEQ_ID"=14 OR "KR"."OWNER_SEQ_ID"=15 OR
              "KR"."OWNER_SEQ_ID"=16 OR "KR"."OWNER_SEQ_ID"=19 OR "KR"."OWNER_SEQ_ID"=21 OR
              "KR"."OWNER_SEQ_ID"=22 OR "KR"."OWNER_SEQ_ID"=23 OR "KR"."OWNER_SEQ_ID"=24 OR
              "KR"."OWNER_SEQ_ID"=32 OR "KR"."OWNER_SEQ_ID"=34 OR "KR"."OWNER_SEQ_ID"=39 OR
              "KR"."OWNER_SEQ_ID"=40 OR "KR"."OWNER_SEQ_ID"=42 OR "KR"."OWNER_SEQ_ID"=43 OR
              "KR"."OWNER_SEQ_ID"=45 OR "KR"."OWNER_SEQ_ID"=46 OR "KR"."OWNER_SEQ_ID"=47 OR
              "KR"."OWNER_SEQ_ID"=48 OR "KR"."OWNER_SEQ_ID"=49 OR "KR"."OWNER_SEQ_ID"=50 OR
              "KR"."OWNER_SEQ_ID"=51 OR "KR"."OWNER_SEQ_ID"=52) AND ("KR"."JRSDCN_CD"='APC' OR
              "KR"."JRSDCN_CD"='BOK' OR "KR"."JRSDCN_CD"='DFLT' OR "KR"."JRSDCN_CD"='HKG' OR
              "KR"."JRSDCN_CD"='LDN' OR "KR"."JRSDCN_CD"='MUM' OR "KR"."JRSDCN_CD"='NYK' OR
              "KR"."JRSDCN_CD"='SEO' OR "KR"."JRSDCN_CD"='SNG' OR "KR"."JRSDCN_CD"='SYD' OR
              "KR"."JRSDCN_CD"='TOK') AND "JOE"."OVERLAYS"('abf',"KR"."BUS_DMN_ST")<>'N')
   8 - access("KR"."STATUS_CD"="KRS"."STATUS_CD"(+))
   9 - access("KR"."SCNRO_ID"="KS"."SCNRO_ID"(+))
  10 - filter("KRC"."SCNRO_ID"=114000022)
  11 - access("KR"."CNTRY_ID"="KC"."CNTRY_ID"(+))
  12 - access("KR"."OWNER_SEQ_ID"="KRO"."OWNER_SEQ_ID"(+))   13 - access("KR"."REVIEW_ID"="KRF"."REVIEW_ID"(+))

52 rows selected.

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 13 2004 - 13:24:07 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US