Hi All,
Can you explain me how to improve this sql statement. The "top"
command showed this SQL program took about 50% CPU resource. When it
run system kind of halt and nobody can run anything until it done. Any
help you could give me would be greatly appreciated.

--Wayne

Here is the out put from "set explain on"


QUERY:
------
select f.t_cofc, a.t_orno, a.t_pono, a.t_sqnb, a.t_cprj, b.t_item,
b.t_dsca, a.t_shpm, a.t_oqua, a.t_dqua, a.t_pric, a.t_oamt,
ldate(a.t_odat), ldate(a.t_dldt), ldate(a.t_prdt),
ldate(a.t_invd),
b.t_kitm, b.t_cpcl, b.t_cpln, a.t_cpcl, a.t_cpln, b.t_ctyp,
c.t_prog,
c.t_cont, d.t_nama, e1.t_nama, e2.t_nama, e3.t_nama,
e4.t_nama,
c.t_per1, a.t_ttyp, a.t_invn, f.t_corn, f.t_refa, g.t_dsca,
trim(h.t_name) || ', ' || h.t_cste || ' ' || trim(h.t_pstc),
decode(c.t_bpos, 1, 'SoleSource', 2, 'Competetive', 'NA'),
decode(c.t_btyp, 1, 'N', 2, 'F', 'X') from ttdsls401100 a,
ttcibd001100
b, tltsls400100 c, ttccom100100 d, ttccom001100 e1, outer
ttccom001100
e2, outer ttccom001100 e3, outer ttccom001100 e4, ttdsls400100
f,
outer ttcmcs045100 g, ttccom130100 h where f.t_futo not in
(2,3) and
a.t_item = b.t_item and a.t_orno = c.t_orno and a.t_ofbp =
d.t_bpid
and c.t_cadm = e1.t_emno and c.t_fina = e2.t_emno and c.t_rep1 =
e3.t_emno and c.t_bdev = e4.t_emno and a.t_orno = f.t_orno and
a.t_item
= b.t_item and f.t_creg = g.t_creg and a.t_stad = h.t_cadr and
a.t_oltp
<> 1 and a.t_clyn <> 1 and decode(f.t_cofc, 110, 101, 100)
between 100
and 999 and a.t_orno between '000000000' and 'ZZZZZZZZZ' and
exists
(select b.t_orno from ttdsls401100 b where b.t_orno = a.t_orno and
b.t_pono = a.t_pono and ldate(b.t_dldt) between '9/16/2003' and
'9/16/2003') order by a.t_orno, a.t_pono, a.t_sqnb

Estimated Cost: 10504182
Estimated # of Rows Returned: 1
Temporary Files Required For: Order By

1) bsp.a: SEQUENTIAL SCAN

Filters: ((((bsp.a.t_oltp != 1 AND bsp.a.t_clyn != 1 ) AND
EXISTS <subquery> ) AND bsp.a.t_orno <= 'ZZZZZZZZZ' ) AND bsp.a.t_orno
>= '000000000' )

2) bsp.f: SEQUENTIAL SCAN

Filters:
Table Scan Filters: ((CASE WHEN bsp.f.t_cofc = 110 THEN 101
ELSE 100 END<= 999 AND CASE WHEN bsp.f.t_cofc = 110 THEN 101 ELSE
100 END>= 100 ) AND bsp.f.t_futo NOT IN (2 , 3 ))

DYNAMIC HASH JOIN
Dynamic Hash Filters: bsp.a.t_orno = bsp.f.t_orno

3) bsp.b: INDEX PATH

Filters: bsp.a.t_item = bsp.b.t_item

(1) Index Keys: t_item (Serial, fragments: ALL)
Lower Index Filter: bsp.a.t_item = bsp.b.t_item
NESTED LOOP JOIN

4) bsp.h: SEQUENTIAL SCAN

DYNAMIC HASH JOIN (Build Outer)
Dynamic Hash Filters: bsp.a.t_stad = bsp.h.t_cadr

5) bsp.c: INDEX PATH

(1) Index Keys: t_orno (Serial, fragments: ALL)
Lower Index Filter: bsp.a.t_orno = bsp.c.t_orno
NESTED LOOP JOIN

6) bsp.e1: INDEX PATH

(1) Index Keys: t_emno (Serial, fragments: ALL)
Lower Index Filter: bsp.c.t_cadm = bsp.e1.t_emno
NESTED LOOP JOIN

Hi,
You have a join select on 11 (!) tables. I'm not surprised your system
hangs. I would suggest you to break your script down into several temporary
tables. That will help a lot.
Regards,
OM

"Wayne T" <ntrieu@yahoo.com> a écrit dans le message news:
e6623a40.0309161548.270a4252@posting.google.com...
> Hi All,
> Can you explain me how to improve this sql statement. The "top"
> command showed this SQL program took about 50% CPU resource. When it
> run system kind of halt and nobody can run anything until it done. Any
> help you could give me would be greatly appreciated.
>
> --Wayne
>
> Here is the out put from "set explain on"
>
>
> QUERY:
> ------
> select f.t_cofc, a.t_orno, a.t_pono, a.t_sqnb, a.t_cprj, b.t_item,
> b.t_dsca, a.t_shpm, a.t_oqua, a.t_dqua, a.t_pric, a.t_oamt,
> ldate(a.t_odat), ldate(a.t_dldt), ldate(a.t_prdt),
> ldate(a.t_invd),
> b.t_kitm, b.t_cpcl, b.t_cpln, a.t_cpcl, a.t_cpln, b.t_ctyp,
> c.t_prog,
> c.t_cont, d.t_nama, e1.t_nama, e2.t_nama, e3.t_nama,
> e4.t_nama,
> c.t_per1, a.t_ttyp, a.t_invn, f.t_corn, f.t_refa, g.t_dsca,
> trim(h.t_name) || ', ' || h.t_cste || ' ' || trim(h.t_pstc),
> decode(c.t_bpos, 1, 'SoleSource', 2, 'Competetive', 'NA'),
> decode(c.t_btyp, 1, 'N', 2, 'F', 'X') from ttdsls401100 a,
> ttcibd001100
> b, tltsls400100 c, ttccom100100 d, ttccom001100 e1, outer
> ttccom001100
> e2, outer ttccom001100 e3, outer ttccom001100 e4, ttdsls400100
> f,
> outer ttcmcs045100 g, ttccom130100 h where f.t_futo not in
> (2,3) and
> a.t_item = b.t_item and a.t_orno = c.t_orno and a.t_ofbp =
> d.t_bpid
> and c.t_cadm = e1.t_emno and c.t_fina = e2.t_emno and c.t_rep1 =
> e3.t_emno and c.t_bdev = e4.t_emno and a.t_orno = f.t_orno and
> a.t_item
> = b.t_item and f.t_creg = g.t_creg and a.t_stad = h.t_cadr and
> a.t_oltp
> <> 1 and a.t_clyn <> 1 and decode(f.t_cofc, 110, 101, 100)
> between 100
> and 999 and a.t_orno between '000000000' and 'ZZZZZZZZZ' and
> exists
> (select b.t_orno from ttdsls401100 b where b.t_orno = a.t_orno and
> b.t_pono = a.t_pono and ldate(b.t_dldt) between '9/16/2003' and
> '9/16/2003') order by a.t_orno, a.t_pono, a.t_sqnb
>
> Estimated Cost: 10504182
> Estimated # of Rows Returned: 1
> Temporary Files Required For: Order By
>
> 1) bsp.a: SEQUENTIAL SCAN
>
> Filters: ((((bsp.a.t_oltp != 1 AND bsp.a.t_clyn != 1 ) AND
> EXISTS <subquery> ) AND bsp.a.t_orno <= 'ZZZZZZZZZ' ) AND bsp.a.t_orno
> >= '000000000' )
>
> 2) bsp.f: SEQUENTIAL SCAN
>
> Filters:
> Table Scan Filters: ((CASE WHEN bsp.f.t_cofc = 110 THEN 101
> ELSE 100 END<= 999 AND CASE WHEN bsp.f.t_cofc = 110 THEN 101 ELSE
> 100 END>= 100 ) AND bsp.f.t_futo NOT IN (2 , 3 ))
>
> DYNAMIC HASH JOIN
> Dynamic Hash Filters: bsp.a.t_orno = bsp.f.t_orno
>
> 3) bsp.b: INDEX PATH
>
> Filters: bsp.a.t_item = bsp.b.t_item
>
> (1) Index Keys: t_item (Serial, fragments: ALL)
> Lower Index Filter: bsp.a.t_item = bsp.b.t_item
> NESTED LOOP JOIN
>
> 4) bsp.h: SEQUENTIAL SCAN
>
> DYNAMIC HASH JOIN (Build Outer)
> Dynamic Hash Filters: bsp.a.t_stad = bsp.h.t_cadr
>
> 5) bsp.c: INDEX PATH
>
> (1) Index Keys: t_orno (Serial, fragments: ALL)
> Lower Index Filter: bsp.a.t_orno = bsp.c.t_orno
> NESTED LOOP JOIN
>
> 6) bsp.e1: INDEX PATH
>
> (1) Index Keys: t_emno (Serial, fragments: ALL)
> Lower Index Filter: bsp.c.t_cadm = bsp.e1.t_emno
> NESTED LOOP JOIN

On Tue, 16 Sep 2003 19:48:24 -0400, Wayne T wrote:

With an eleven table join, running under default optimization rules, the
optimizer must calculate the cost of every one of the 11! or 33,916,800
possible query paths. Unless this query is returning a HUGE number of rows the
cost of these optimization calculations is MUCH more than the difference
between the best query plan and the worst. Try running the same query under
SET OPTIMIZATION LOW;

Art S. Kagel

> Hi All,
> Can you explain me how to improve this sql statement. The "top" command showed
> this SQL program took about 50% CPU resource. When it run system kind of halt
> and nobody can run anything until it done. Any help you could give me would be
> greatly appreciated.
>
> --Wayne
>
> Here is the out put from "set explain on"
>
>
> QUERY:
> ------
> select f.t_cofc, a.t_orno, a.t_pono, a.t_sqnb, a.t_cprj, b.t_item,
> b.t_dsca, a.t_shpm, a.t_oqua, a.t_dqua, a.t_pric, a.t_oamt,
> ldate(a.t_odat), ldate(a.t_dldt), ldate(a.t_prdt),
> ldate(a.t_invd),
> b.t_kitm, b.t_cpcl, b.t_cpln, a.t_cpcl, a.t_cpln, b.t_ctyp,
> c.t_prog,
> c.t_cont, d.t_nama, e1.t_nama, e2.t_nama, e3.t_nama,
> e4.t_nama,
> c.t_per1, a.t_ttyp, a.t_invn, f.t_corn, f.t_refa, g.t_dsca,
> trim(h.t_name) || ', ' || h.t_cste || ' ' || trim(h.t_pstc),
> decode(c.t_bpos, 1, 'SoleSource', 2, 'Competetive', 'NA'),
> decode(c.t_btyp, 1, 'N', 2, 'F', 'X') from ttdsls401100 a,
> ttcibd001100
> b, tltsls400100 c, ttccom100100 d, ttccom001100 e1, outer
> ttccom001100
> e2, outer ttccom001100 e3, outer ttccom001100 e4, ttdsls400100
> f,
> outer ttcmcs045100 g, ttccom130100 h where f.t_futo not in
> (2,3) and
> a.t_item = b.t_item and a.t_orno = c.t_orno and a.t_ofbp =
> d.t_bpid
> and c.t_cadm = e1.t_emno and c.t_fina = e2.t_emno and c.t_rep1 =
> e3.t_emno and c.t_bdev = e4.t_emno and a.t_orno = f.t_orno and
> a.t_item
> = b.t_item and f.t_creg = g.t_creg and a.t_stad = h.t_cadr and
> a.t_oltp
> <> 1 and a.t_clyn <> 1 and decode(f.t_cofc, 110, 101, 100)
> between 100
> and 999 and a.t_orno between '000000000' and 'ZZZZZZZZZ' and
> exists
> (select b.t_orno from ttdsls401100 b where b.t_orno = a.t_orno and
> b.t_pono = a.t_pono and ldate(b.t_dldt) between '9/16/2003' and
> '9/16/2003') order by a.t_orno, a.t_pono, a.t_sqnb
>
> Estimated Cost: 10504182
> Estimated # of Rows Returned: 1
> Temporary Files Required For: Order By
>
> 1) bsp.a: SEQUENTIAL SCAN
>
> Filters: ((((bsp.a.t_oltp != 1 AND bsp.a.t_clyn != 1 ) AND
> EXISTS <subquery> ) AND bsp.a.t_orno <= 'ZZZZZZZZZ' ) AND bsp.a.t_orno
>>= '000000000' )
>
> 2) bsp.f: SEQUENTIAL SCAN
>
> Filters:
> Table Scan Filters: ((CASE WHEN bsp.f.t_cofc = 110 THEN 101
> ELSE 100 END<= 999 AND CASE WHEN bsp.f.t_cofc = 110 THEN 101 ELSE 100
> END>= 100 ) AND bsp.f.t_futo NOT IN (2 , 3 ))
>
> DYNAMIC HASH JOIN
> Dynamic Hash Filters: bsp.a.t_orno = bsp.f.t_orno
>
> 3) bsp.b: INDEX PATH
>
> Filters: bsp.a.t_item = bsp.b.t_item
>
> (1) Index Keys: t_item (Serial, fragments: ALL)
> Lower Index Filter: bsp.a.t_item = bsp.b.t_item
> NESTED LOOP JOIN
>
> 4) bsp.h: SEQUENTIAL SCAN
>
> DYNAMIC HASH JOIN (Build Outer)
> Dynamic Hash Filters: bsp.a.t_stad = bsp.h.t_cadr
>
> 5) bsp.c: INDEX PATH
>
> (1) Index Keys: t_orno (Serial, fragments: ALL)
> Lower Index Filter: bsp.a.t_orno = bsp.c.t_orno
> NESTED LOOP JOIN
>
> 6) bsp.e1: INDEX PATH
>
> (1) Index Keys: t_emno (Serial, fragments: ALL)
> Lower Index Filter: bsp.c.t_cadm = bsp.e1.t_emno
> NESTED LOOP JOIN