Re: How can I re-write this piece of code for better performance!?

lou_nyc wrote:
> select b.damage_inspection_date,
> b.damage_inspection_by
> ,b.status
> ,NVL(a.cnt,0) CNT
> from
> (select aa.damage_inspection_date,
> aa.damage_inspection_by,
> bb.status
> from (select distinct trunc(gc.damage_inspection_date)
> damage_inspection_date, gc.damage_inspection_by
> from gate_damages gd, gate_containers gc
> where gd.gate_id = gc.gate_id
> ) aa,
> (select *
> from (select 'MAJOR' STATUS from dual
> union all
> select 'MINOR' STATUS from dual
> union all
> select 'TOTAL' STATUS from dual
> )
> ) bb
> )b,
> ((SELECT damage_inspection_date,
> damage_inspection_by,
> Status,
> cnt
> FROM (select trunc(c.damage_inspection_date)
> damage_inspection_date,
> c.damage_inspection_by,
> 'MAJOR' STATUS,
> count(distinct c.gate_id) cnt
> from gate_containers c,
> gate_damages d
> where c.gate_id = d.gate_id and
> d.damage_type_code = 'A'
> group by
> trunc(c.damage_inspection_date),c.damage_inspection_by
> UNION ALL
> select trunc(g.damage_inspection_date)
> damage_inspection_date,
> g.damage_inspection_by,
> 'MINOR' STATUS,
> count(distinct g.gate_id) cnt
> from gate_containers g,
> gate_damages z
> where g.gate_id = z.gate_id and
> z.damage_type_code = 'F'
> group by
> trunc(g.damage_inspection_date),g.damage_inspection_by
> UNION ALL
> select trunc(ab.damage_inspection_date)
> damage_inspection_date,
> ab.damage_inspection_by,
> 'TOTAL' STATUS,
> count(distinct ab.gate_id) cnt
> from gate_containers ab,
> gate_damages ac
> where ab.gate_id = ac.gate_id
> group by
> trunc(ab.damage_inspection_date),ab.damage_inspection_by
> )
> group by damage_inspection_date, damage_inspection_by,
> status, cnt
> )
> ) a
> where b.damage_inspection_by = a.damage_inspection_by(+)
> and b.damage_inspection_date = a.damage_inspection_date(+)
> and b.status = a.status(+);

Amazed that no one has tried to help you?

1. Oracle version?
2. Explain Plan or Autotrace?
3. What indexes exist?
4. Did you create current statistics with DBMS_STATS?

Might be part of the reason.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)