Re: Single Complex Query Vs Temoprary Table
From: Jim Kennedy <kennedy-down_with_spammers_at_no_spam.comcast.net>
Date: Fri, 18 Jul 2003 05:21:19 GMT
Message-ID: <jtLRa.84772$N7.11341_at_sccrnsc03>
Date: Fri, 18 Jul 2003 05:21:19 GMT
Message-ID: <jtLRa.84772$N7.11341_at_sccrnsc03>
was it faster?
-- 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.0307172006.7ac45692_at_posting.google.com...Received on Fri Jul 18 2003 - 07:21:19 CEST
> 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...