Re: Single Complex Query Vs Temoprary Table
Date: Thu, 17 Jul 2003 08:35:04 GMT
Message-ID: <XctRa.80274$ye4.60537_at_sccrnsc01>
get rid of the distinct and replace:
> WHERE TRUNC(A.CANDATE) BETWEEN '01-jan-2003' AND '30-jan-2003'
with
> WHERE A.CANDATE BETWEEN to_date('01-jan-2003','dd-mmm-yyyy') AND
to_date('30-jan-2003','dd-mmm-yyyy')
(don't rely on the implicit sting to date conversion, it will bite you
later.)The reson it is full scanning table A is the trunc on the column.
With the between clause you don't need it.
See if those things help.
Jim
--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Mahesh Hardikar" <hardikarm_at_yahoo.com> wrote in message
news:4a1c57c2.0307170028.25048548_at_posting.google.com...
> Hi ,
>
> Thanks for your inputs.
>
> Here goes the query :
> /***********************************************************
> SELECT DISTINCT
> A.ORGNSTNCODE AS OULCODE,
> K.DESCRIPTION AS OUL,
> K1.DESCRIPTION AS LOCCODE,
> A.SALESMANCODE AS SALESMANCODE,
> B.DESCRIPTION AS SALESPERSON,
> A.AGNTCODE AS AGENTCODE,
> D.PRINTDESCRIP AS AGENT,
> A.AWBNO AS MAWBNO,
> A.WONO AS WONO,
> A.WOKEY AS WOKEY,
> A.MODECODE ,
> A.HAWBNO AS HAWBNO,
> A.HAWBDATE AS HAWBDATE,
> N.INVKEY ,
> N.INVNO AS BILLNO,
> N.INVDATE AS BILLDATE,
> A.CARRIERCODE AS CARRIERCODE,
> E.DESCRIPTION AS CARRIER,
> A.ORIPORTCODE AS ORIGINPORTCODE,
> P1.DESCRIPTION AS ORIGIN,
> A.DESTPORTCODE AS DESTPORTCODE,
> P2.DESCRIPTION AS DESTINATION,
> A.ORGNSTNCODE REVENUESTN,
> A.CUSTOMERCODE AS CUSTOMERCODE,
> DECODE(A.CUSTOMERDESC, NULL, A1.DESCRIPTION,
> A.CUSTOMERDESC) AS CUSTOMER,
> A.PONO AS PONO,
> H.AFLCMDTY AS COMMCODE,
> I.DESCRIPTION AS COMMODITY,
> H.NOPKGS AS PKG,
> H.AFLGROSSWT AS GROSSWT,
> A.GROSSUOM AS GROSSUOMCODE,
> J.DESCRIPTION AS GROSSUOM,
> H.AFLCHRGWT AS CHRGQTY,
> H.CONTTYPE AS CONTTYPE,
> H.CONTSIZE AS CONTSIZE,
> M.PPCC AS PPCC,
> A.FFCURR AS FFCURR,
> NVL(M.FRTAMT,0) AS FRTAMT,
> A.FFEXCHRATE ,
> A.ORIEXCHRATE ,
> DECODE(A.RTDBYCODE, '1', 'AGENT', '2', 'AFL', '') AS RTDBYCODE
> FROM
> ICWOIMP A,
> COCOPHDR A1,
> COSALESMAN B,
> ICAGENTHDR D,
> ICCARRIERHEADER E,
> ICCONTDTLS H,
> ICCOMMODITY I,
> ICPORT P1,
> ICPORT P2,
> COUOM J,
> COOUL K,
> COPRODUCT L,
> ICWOFRTDTL M,
> ICINVHDR N,
> COOUL K1
> WHERE TRUNC(A.CANDATE) BETWEEN '01-jan-2003' AND '30-jan-2003'
> AND A.STATUS = 'C'
> AND A.STAGE NOT IN ('S','C')
> AND A.ORGNSTNCODE = K.OULCODE
> AND A.LOCCODE = K1.OULCODE
> AND A.CUSTOMERCODE = A1.CUSTOMERCODE
> AND A.SALESMANCODE = B.SALESMANCODE
> AND A.AGNTCODE = D.AGENTCODE
> AND A.CARRIERCODE = E.CARRIERCODE
> AND A.WOKEY = H.WOKEY(+)
> AND H.AFLCMDTY = I.COMMCODE(+)
> AND A.GROSSUOM = J.UOMCODE(+)
> AND A.ORIPORTCODE = P1.PORTCODE
> AND A.DESTPORTCODE = P2.PORTCODE
> AND A.PRODUCTCODE = L.PRODUCTCODE
> AND L.PARENT = 'IMP'
> AND A.WOKEY = M.WOKEY(+)
> AND A.WOKEY = N.LINKREF1KEY(+)
> AND N.TRANTYPE(+) = 'ICCAN'
> ORDER BY A.WONO
> ******************************************************/
>
> Here is the execution plan :
>
> /*******************************
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1989 Card=24 Bytes=1
> 1088)
>
> 1 0 SORT (UNIQUE) (Cost=1984 Card=24 Bytes=11088)
> 2 1 HASH JOIN (OUTER) (Cost=1978 Card=24 Bytes=11088)
> 3 2 NESTED LOOPS (OUTER) (Cost=1976 Card=24 Bytes=10416)
> 4 3 NESTED LOOPS (Cost=1930 Card=23 Bytes=9407)
> 5 4 HASH JOIN (OUTER) (Cost=1907 Card=23 Bytes=8671)
> 6 5 HASH JOIN (OUTER) (Cost=1611 Card=23 Bytes=7751)
> 7 6 NESTED LOOPS (OUTER) (Cost=1297 Card=23 Bytes=
> 7498)
>
> 8 7 HASH JOIN (Cost=1274 Card=23 Bytes=6785)
> 9 8 HASH JOIN (Cost=1268 Card=23 Bytes=6486)
> 10 9 HASH JOIN (Cost=1262 Card=23 Bytes=6187)
> 11 10 HASH JOIN (Cost=1258 Card=23
> Bytes=5267)
>
> 12 11 HASH JOIN (Cost=1255 Card=23
> Bytes=4669)
>
> 13 12 HASH JOIN (Cost=1251 Card=23
> Bytes=4370)
>
> 14 13 HASH JOIN (Cost=1247 Card=23
> Bytes=4071)
>
> 15 14 HASH JOIN (Cost=1245 Card=23
> Bytes=3634)
>
> 16 15 TABLE ACCESS (FULL) OF
> 'COPRODUCT' (Cost=2 Card=2 Bytes=12)
>
> 17 15 TABLE ACCESS (FULL) OF
> 'ICWOIMP' (Cost=1242 Card=23 Bytes=3496)
>
> 18 14 TABLE ACCESS (FULL) OF
> 'COSALESMAN' (Cost=1 Card=72 Bytes=1368)
>
> 19 13 TABLE ACCESS (FULL) OF 'COOUL'
> (Cost=3 Card=237 Bytes=3081)
>
> 20 12 TABLE ACCESS (FULL) OF 'COOUL'
> (Cost=3 Card=237 Bytes=3081)
>
> 21 11 TABLE ACCESS (FULL) OF
> 'ICCARRIERHEADER' (Cost=2 Card=263 Bytes=6838)
>
> 22 10 TABLE ACCESS (FULL) OF 'ICAGENTHDR'
> (Cost=3 Card=267 Bytes=10680)
>
> 23 9 TABLE ACCESS (FULL) OF 'ICPORT' (Cost=5
> Card=1160 Bytes=15080)
>
> 24 8 TABLE ACCESS (FULL) OF 'ICPORT' (Cost=5
> Card=1160 Bytes=15080)
>
> 25 7 TABLE ACCESS (BY INDEX ROWID) OF 'COUOM'
> (Cost=1 Card=1245 Bytes=38595)
>
> 26 25 INDEX (UNIQUE SCAN) OF 'SYS_C003223'
> (UNIQUE)
>
> 27 6 TABLE ACCESS (FULL) OF 'ICWOFRTDTL'
> (Cost=311Card=43971 Bytes=483681)
>
> 28 5 TABLE ACCESS (FULL) OF 'ICINVHDR' (Cost=295
> Card=6572 Bytes=262880)
>
> 29 4 TABLE ACCESS (BY INDEX ROWID) OF 'COCOPHDR'
> (Cost=1 Card=11027 Bytes=352864)
>
> 30 29 INDEX (UNIQUE SCAN) OF 'UK_COCOPHDR' (UNIQUE)
>
> 31 3 TABLE ACCESS (BY INDEX ROWID) OF 'ICCONTDTLS'
> (Cost=2 Card=51807 Bytes=1295175)
>
> 32 31 INDEX (RANGE SCAN) OF 'INDX_ICCONTDTLS_WOKEY'
> (NON-UNIQUE) (Cost=1 Card=51807)
>
> 33 2 TABLE ACCESS (FULL) OF 'ICCOMMODITY' (Cost=1 Card=59
> Bytes=1652)
> **************************************************/
>
> The main table ICWOIMP (Rows 36000) has index on Column STATUS but
> execution plan is not using it . Instead it is using TABLE SCAN.
>
> Surprisingly when I ran the query thru SQL-Plus , it was pretty fast.
> Gave me o/p in a min. But many times I see this report getting
> cancelled after 30 Mins theu 9iAS Report Server Jobs.
>
> I will also talk to developer if they are doing something at Report
> level which is killing the process.
>
> Regards,
> Mahesh
>
> "Jim Kennedy" <kennedy-down_with_spammers_at_no_spam.comcast.net> wrote in
message news:<cnbRa.79047$H17.22579_at_sccrnsc02>...
> > What are the explain plan and the tkprof results? It is rare to have to
put
> > results to a temp table to get better performance. My guess is that
there
> > is a function on a column and that forces a full table scan. But I have
no
> > way of knowing without seeing the query and the explain plan results.
> > Jim
> >
> > --
> > Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
> > with family. Remove the negative part, keep the minus sign. You can
figure
> > it out.
> > "Mahesh Hardikar" <hardikarm_at_yahoo.com> wrote in message
> > news:4a1c57c2.0307160331.58838584_at_posting.google.com...
Received on Thu Jul 17 2003 - 10:35:04 CEST