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