Re: How to improve SQL
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