473,562 Members | 2,587 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Single Complex Query Vs Temoprary Table

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
Jul 19 '05 #1
6 4500
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: ke************* ***********@att bi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Mahesh Hardikar" <ha*******@yaho o.com> wrote in message
news:4a******** *************** ***@posting.goo gle.com...
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

Jul 19 '05 #2
ha*******@yahoo .com (Mahesh Hardikar) wrote in message news:<4a******* *************** ****@posting.go ogle.com>...
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 ?

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.
Any help in this regards is highly appreciated ....

Thanks & Regards,
Mahesh Hardikar

Jul 19 '05 #3
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>...
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: ke************* ***********@att bi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Mahesh Hardikar" <ha*******@yaho o.com> wrote in message
news:4a******** *************** ***@posting.goo gle.com...

Jul 19 '05 #4
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: ke************* ***********@att bi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Mahesh Hardikar" <ha*******@yaho o.com> wrote in message
news:4a******** *************** ***@posting.goo gle.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.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>...
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: ke************* ***********@att bi.com
with family. Remove the negative part, keep the minus sign. You can figure it out.
"Mahesh Hardikar" <ha*******@yaho o.com> wrote in message
news:4a******** *************** ***@posting.goo gle.com...

Jul 19 '05 #5
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>...
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: ke************* ***********@att bi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Mahesh Hardikar" <ha*******@yaho o.com> wrote in message
news:4a******** *************** ***@posting.goo gle.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.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>...
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: ke************* ***********@att bi.com
with family. Remove the negative part, keep the minus sign. You can figure it out.
"Mahesh Hardikar" <ha*******@yaho o.com> wrote in message
news:4a******** *************** ***@posting.goo gle.com...

Jul 19 '05 #6
was it faster?

--
Replace part of the email address: ke************* ***********@att bi.com
with family. Remove the negative part, keep the minus sign. You can figure
it out.
"Mahesh Hardikar" <ha*******@yaho o.com> wrote in message
news:4a******** *************** ***@posting.goo gle.com...
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>...
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: ke************* ***********@att bi.com
with family. Remove the negative part, keep the minus sign. You can figure it out.
"Mahesh Hardikar" <ha*******@yaho o.com> wrote in message
news:4a******** *************** ***@posting.goo gle.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.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>...
> 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: ke************* ***********@att bi.com > with family. Remove the negative part, keep the minus sign. You

can figure
> it out.
> "Mahesh Hardikar" <ha*******@yaho o.com> wrote in message
> news:4a******** *************** ***@posting.goo gle.com...

Jul 19 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
5886
by: Marcus | last post by:
Hi I have a very complex sql query and a explain plan. I found there is a full table scan in ID=9 9 8 TABLE ACCESS (FULL) OF 'F_LOTTXNHIST' (Cost=84573 Card=185892 Bytes=7063896) How can I correlate which part of the SQL statement is running on full table scan. Please see below for the code and explain plan SQL Code
4
8960
by: Starbuck | last post by:
OK, first let me say that I am no DB person. But I have a user here who keeps getting this error whenever she does, whatever it is she does, with databases... A google search takes me to various forums where I am forced to sign up before I can read any answers. Interesting note here is that the guy in the office next
2
6064
by: Mikel | last post by:
I am trying to get around the problem "The expression you have entered is too complex" for a select query. (The example below is not the expression that is giving me headaches.) So I am thinking that I just need to do the parsing and calculating in an event procedure for an "On Click" event. My question is: If I have a query field in...
4
2011
by: ED | last post by:
I am attempting to to write a query that has a numerous nested IIf statements. The problem that I am having is that it is to long of a query to be built in design mode and when I build it in sql mode after a certain point it give me the error message that the expression is to complex. Below is the sql code that I am using (this works so far,...
5
3318
by: Ross A. Finlayson | last post by:
Hi, I'm scratching together an Access database. The development box is Office 95, the deployment box Office 2003. So anyways I am griping about forms and global variables. Say for example I'm adding a customer. The Customer fields are mostly foreign keys that refer to primary keys in other tables, left join instead of junction tables...
1
2938
by: phlype.johnson | last post by:
Suppose we have to design a database for a recruitment agency. There will be a table "candidates" with fields "candidateid","last name","first name" ; the languages mastered by a candidate as well as the skills are separated into different tables. Eg. The skills are stored in the table "skills" with fields "skillid", "skillname","candidateid"...
3
2438
by: Slower Than You | last post by:
I am trying to write an SQL UPDATE statement for an MSAccess table and am having some problems getting my head around it. Can anyone help? TableName: CustTransactions TransactionKey AutoNumber (Primary Key) CustomerID Long Integer (Non-unique index) AmountSpent Double CustSelected Boolean What I would like to do is, for...
2
370
by: Mahesh Hardikar | last post by:
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.
1
15910
by: Rudolf Bargholz | last post by:
Hi, We have created triggers to log modifications to tables in our application. The triggers work fine, just on one of the tables in our database the triggers fail with the error message <SQL0437W Performance of this complex query may be sub-optimal. Reason code: "1". SQLSTATE=01602>. The same trigger on other tables works fine (the...
0
7874
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7630
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7940
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6228
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5198
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3626
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3609
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1192
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
907
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.