Re: Single Complex Query Vs Temoprary Table

Hi ,

I tried eliminating trunc . but still it is doing FULL Scan of Table ICWOIMP.
We are anyway trying to eliminate SUbtotals & SUm at Report level . I have asked them to put this into main query in Oracle.

Thanks for the inputs ....
Mahesh

"Jim Kennedy" <kennedy-down_with_spammers_at_no_spam.comcast.net> wrote in message news:<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 Fri Jul 18 2003 - 06:06:49 CEST