Single Complex Query Vs Temoprary Table

[Mahesh Hardikar]

Hi ,

Oracle 8.1.7.0.0 on HP-UX 11.0

I am a newbie as far as PL-SQL is concerned . I have Sybase/MSSQL
T-SQL background.

We have a report which uses a select statement . This select statement
joins 15 tables . SOme of the tables are outer joined.
It runs much slow when parameters (From & To Date) are for a month.
Like From Date = Jun 1 , To Date = Jun 30. Report does not complete
even after 30 mins.

I was thinking to break this query in smaller parts. I am trying to
minimise no of tables used in single statement to around 6.

I want to use Global Temporary table , insert data in temp table by
joining few tables. Rest of the columns of Temporary tables will be
updated thru subsequent updates. Finally a select statement from
temporary table will populate the report .

Will it be a good approach (in Oracle PL-SQL) as far as performance is
concrened ? Are there any performance issues using Global temporary
table ?

Any help in this regards is highly appreciated ....

Thanks & Regards,
Mahesh Hardikar

[Jim Kennedy]

Re: Single Complex Query Vs Temoprary Table

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_spamm ers@attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Mahesh Hardikar" <hardikarm@yaho o.com> wrote in message
news:4a1c57c2.0 307160331.58838 584@posting.goo gle.com...[color=blue]
> Hi ,
>
> Oracle 8.1.7.0.0 on HP-UX 11.0
>
> I am a newbie as far as PL-SQL is concerned . I have Sybase/MSSQL
> T-SQL background.
>
> We have a report which uses a select statement . This select statement
> joins 15 tables . SOme of the tables are outer joined.
> It runs much slow when parameters (From & To Date) are for a month.
> Like From Date = Jun 1 , To Date = Jun 30. Report does not complete
> even after 30 mins.
>
> I was thinking to break this query in smaller parts. I am trying to
> minimise no of tables used in single statement to around 6.
>
> I want to use Global Temporary table , insert data in temp table by
> joining few tables. Rest of the columns of Temporary tables will be
> updated thru subsequent updates. Finally a select statement from
> temporary table will populate the report .
>
> Will it be a good approach (in Oracle PL-SQL) as far as performance is
> concrened ? Are there any performance issues using Global temporary
> table ?
>
> Any help in this regards is highly appreciated ....
>
> Thanks & Regards,
> Mahesh Hardikar[/color]

[Alex Filonov]

Re: Single Complex Query Vs Temoprary Table

hardikarm@yahoo .com (Mahesh Hardikar) wrote in message news:<4a1c57c2. 0307160331.5883 8584@posting.go ogle.com>...[color=blue]
> Hi ,
>
> Oracle 8.1.7.0.0 on HP-UX 11.0
>
> I am a newbie as far as PL-SQL is concerned . I have Sybase/MSSQL
> T-SQL background.
>
> We have a report which uses a select statement . This select statement
> joins 15 tables . SOme of the tables are outer joined.
> It runs much slow when parameters (From & To Date) are for a month.
> Like From Date = Jun 1 , To Date = Jun 30. Report does not complete
> even after 30 mins.
>
> I was thinking to break this query in smaller parts. I am trying to
> minimise no of tables used in single statement to around 6.
>
> I want to use Global Temporary table , insert data in temp table by
> joining few tables. Rest of the columns of Temporary tables will be
> updated thru subsequent updates. Finally a select statement from
> temporary table will populate the report .
>
> Will it be a good approach (in Oracle PL-SQL) as far as performance is
> concrened ? Are there any performance issues using Global temporary
> table ?
>[/color]

PL/SQL is wrap-up procedural language. Queries are written in SQL.

Approaches to tuning:
1. Short approach: run explain plan on the query and post results.
2. Long (and proper) approach: read 2 Oracle books at least, Concepts
and Tuning.
[color=blue]
> Any help in this regards is highly appreciated ....
>
> Thanks & Regards,
> Mahesh Hardikar[/color]

[Mahesh Hardikar]

Re: Single Complex Query Vs Temoprary Table

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.CUSTOM ERDESC, 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.RTDBYC ODE, '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=CHOOS E (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
'ICCARRIERHEADE R' (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=4 3971 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_ICCONTDTL S_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_spamm ers@no_spam.com cast.net> wrote in message news:<cnbRa.790 47$H17.22579@sc crnsc02>...[color=blue]
> 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_spamm ers@attbi.com
> with family. Remove the negative part, keep the minus sign. You can figure
> it out.
> "Mahesh Hardikar" <hardikarm@yaho o.com> wrote in message
> news:4a1c57c2.0 307160331.58838 584@posting.goo gle.com...[/color]

[Jim Kennedy]

Re: Single Complex Query Vs Temoprary Table

get rid of the distinct and replace:[color=blue]
> WHERE TRUNC(A.CANDATE ) BETWEEN '01-jan-2003' AND '30-jan-2003'[/color]
with[color=blue]
> WHERE A.CANDATE BETWEEN to_date('01-jan-2003','dd-mmm-yyyy') AND[/color]
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_spamm ers@attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Mahesh Hardikar" <hardikarm@yaho o.com> wrote in message
news:4a1c57c2.0 307170028.25048 548@posting.goo gle.com...[color=blue]
> 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.CUSTOM ERDESC, 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.RTDBYC ODE, '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=CHOOS E (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
> 'ICCARRIERHEADE R' (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=4 3971 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_ICCONTDTL S_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_spamm ers@no_spam.com cast.net> wrote in[/color]
message news:<cnbRa.790 47$H17.22579@sc crnsc02>...[color=blue][color=green]
> > What are the explain plan and the tkprof results? It is rare to have to[/color][/color]
put[color=blue][color=green]
> > results to a temp table to get better performance. My guess is that[/color][/color]
there[color=blue][color=green]
> > is a function on a column and that forces a full table scan. But I have[/color][/color]
no[color=blue][color=green]
> > way of knowing without seeing the query and the explain plan results.
> > Jim
> >
> > --
> > Replace part of the email address: kennedy-down_with_spamm ers@attbi.com
> > with family. Remove the negative part, keep the minus sign. You can[/color][/color]
figure[color=blue][color=green]
> > it out.
> > "Mahesh Hardikar" <hardikarm@yaho o.com> wrote in message
> > news:4a1c57c2.0 307160331.58838 584@posting.goo gle.com...[/color][/color]

[Mahesh Hardikar]

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_spamm ers@no_spam.com cast.net> wrote in message news:<XctRa.802 74$ye4.60537@sc crnsc01>...[color=blue]
> get rid of the distinct and replace:[color=green]
> > WHERE TRUNC(A.CANDATE ) BETWEEN '01-jan-2003' AND '30-jan-2003'[/color]
> with[color=green]
> > WHERE A.CANDATE BETWEEN to_date('01-jan-2003','dd-mmm-yyyy') AND[/color]
> 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_spamm ers@attbi.com
> with family. Remove the negative part, keep the minus sign. You can figure
> it out.
> "Mahesh Hardikar" <hardikarm@yaho o.com> wrote in message
> news:4a1c57c2.0 307170028.25048 548@posting.goo gle.com...[color=green]
> > 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.CUSTOM ERDESC, 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.RTDBYC ODE, '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=CHOOS E (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
> > 'ICCARRIERHEADE R' (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=4 3971 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_ICCONTDTL S_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_spamm ers@no_spam.com cast.net> wrote in[/color]
> message news:<cnbRa.790 47$H17.22579@sc crnsc02>...[color=green][color=darkred]
> > > What are the explain plan and the tkprof results? It is rare to have to[/color][/color]
> put[color=green][color=darkred]
> > > results to a temp table to get better performance. My guess is that[/color][/color]
> there[color=green][color=darkred]
> > > is a function on a column and that forces a full table scan. But I have[/color][/color]
> no[color=green][color=darkred]
> > > way of knowing without seeing the query and the explain plan results.
> > > Jim
> > >
> > > --
> > > Replace part of the email address: kennedy-down_with_spamm ers@attbi.com
> > > with family. Remove the negative part, keep the minus sign. You can[/color][/color]
> figure[color=green][color=darkred]
> > > it out.
> > > "Mahesh Hardikar" <hardikarm@yaho o.com> wrote in message
> > > news:4a1c57c2.0 307160331.58838 584@posting.goo gle.com...[/color][/color][/color]

[Jim Kennedy]

Re: Single Complex Query Vs Temoprary Table

was it faster?

--
Replace part of the email address: kennedy-down_with_spamm ers@attbi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Mahesh Hardikar" <hardikarm@yaho o.com> wrote in message
news:4a1c57c2.0 307172006.7ac45 692@posting.goo gle.com...[color=blue]
> 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_spamm ers@no_spam.com cast.net> wrote in[/color]
message news:<XctRa.802 74$ye4.60537@sc crnsc01>...[color=blue][color=green]
> > get rid of the distinct and replace:[color=darkred]
> > > WHERE TRUNC(A.CANDATE ) BETWEEN '01-jan-2003' AND '30-jan-2003'[/color]
> > with[color=darkred]
> > > WHERE A.CANDATE BETWEEN to_date('01-jan-2003','dd-mmm-yyyy') AND[/color]
> > 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_spamm ers@attbi.com
> > with family. Remove the negative part, keep the minus sign. You can[/color][/color]
figure[color=blue][color=green]
> > it out.
> > "Mahesh Hardikar" <hardikarm@yaho o.com> wrote in message
> > news:4a1c57c2.0 307170028.25048 548@posting.goo gle.com...[color=darkred]
> > > 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.CUSTOM ERDESC, 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.RTDBYC ODE, '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=CHOOS E (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
> > > 'ICCARRIERHEADE R' (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=4 3971 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_ICCONTDTL S_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_spamm ers@no_spam.com cast.net> wrote[/color][/color][/color]
in[color=blue][color=green]
> > message news:<cnbRa.790 47$H17.22579@sc crnsc02>...[color=darkred]
> > > > What are the explain plan and the tkprof results? It is rare to[/color][/color][/color]
have to[color=blue][color=green]
> > put[color=darkred]
> > > > results to a temp table to get better performance. My guess is that[/color]
> > there[color=darkred]
> > > > is a function on a column and that forces a full table scan. But I[/color][/color][/color]
have[color=blue][color=green]
> > no[color=darkred]
> > > > way of knowing without seeing the query and the explain plan[/color][/color][/color]
results.[color=blue][color=green][color=darkred]
> > > > Jim
> > > >
> > > > --
> > > > Replace part of the email address:[/color][/color][/color]
kennedy-down_with_spamm ers@attbi.com[color=blue][color=green][color=darkred]
> > > > with family. Remove the negative part, keep the minus sign. You[/color][/color][/color]
can[color=blue][color=green]
> > figure[color=darkred]
> > > > it out.
> > > > "Mahesh Hardikar" <hardikarm@yaho o.com> wrote in message
> > > > news:4a1c57c2.0 307160331.58838 584@posting.goo gle.com...[/color][/color][/color]