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