Re: Single Complex Query Vs Temoprary Table
Date: 17 Jul 2003 01:28:05 -0700
Message-ID: <4a1c57c2.0307170028.25048548_at_posting.google.com>
Hi ,
Thanks for your inputs.
Here goes the query :
Here is the execution plan :
28 5 TABLE ACCESS (FULL) OF 'ICINVHDR' (Cost=295
Card=6572 Bytes=262880)
29 4 TABLE ACCESS (BY INDEX ROWID) OF 'COCOPHDR'
30 29 INDEX (UNIQUE SCAN) OF 'UK_COCOPHDR' (UNIQUE)
31 3 TABLE ACCESS (BY INDEX ROWID) OF 'ICCONTDTLS'
/***********************************************************
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
******************************************************/
/*******************************
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)
(Cost=1 Card=11027 Bytes=352864)
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:28:05 CEST