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

"lou_nyc" <lnlx_at_aol.com> wrote in message news:1119465959.232854.316900_at_f14g2000cwb.googlegroups.com...
> This piece of code is taking a long time. Any help will be appreicate
> it.
>
>
> select *
> from (
> select ssl_user_code,
> ssl_user_code ssl,
> i.container,
> out_date,
> cl.code length_code,
> out_trucker_code,
> decode ((select ih.in_date
> from his_containers ih
> where ih.container = i.container
> and ih.container_id > i.container_id
> and ih.container_id = (select min(ihh.container_id)
> from his_containers ihh
> where ihh.container_id >
> i.container_id
> and ihh.container =
> i.container)), null,
> (select ic.in_date
> from inv_containers ic
> where ic.container = i.container)) in_date,
> decode ((select ih.in_trucker_code
> from his_containers ih
> where ih.container = i.container
> and ih.container_id > i.container_id
> and ih.container_id = (select min(ihh.container_id)
> from his_containers ihh
> where ihh.container_id >
> i.container_id
> and ihh.container =
> i.container)), null,
> (select ic.in_trucker_code
> from inv_containers ic
> where ic.container = i.container)) in_trucker_code,
> decode ((select ih.chassis
> from his_containers ih
> where ih.container = i.container
> and ih.container_id > i.container_id
> and ih.container_id = (select min(ihh.container_id)
> from his_containers ihh
> where ihh.container_id >
> i.container_id
> and ihh.container =
> i.container)), null,
> (select ic.chassis
> from inv_containers ic
> where ic.container = i.container)) in_chassis
> from his_containers i,
> container_masters cm,
> tml_container_lhts clht,
> tml_container_lengths cl
> where i.chassis is null and
> i.out_mode = 'T'
> and cm.container = i.container and
> cm.lht_code = clht.code and
> clht.length_code = cl.code and
> decode ((select ih.container_id
> from his_containers ih
> where ih.container = i.container
> and ih.container_id > i.container_id
> and ih.container_id = (select min(ihh.container_id)
> from his_containers ihh
> where ihh.container_id >
> i.container_id
> and ihh.container =
> i.container)), null,
> (select ic.container_id
> from inv_containers ic
> where ic.container = i.container)) is not null
> )
> where ssl_user_code = 'ACL'
> and ((out_date between to_date('01-MAR-05 00:00:00', 'DD-MON-RR
> HH24:MI:SS')
> and to_date('31-MAR-05 23:59:59', 'DD-MON-RR HH24:MI:SS'))
> or (in_date between to_date('01-MAR-05 00:00:00', 'DD-MON-RR
> HH24:MI:SS')
> and to_date('31-MAR-05 23:59:59', 'DD-MON-RR HH24:MI:SS')))
> and in_date is null
>

too long - do a 10046 level 8 trace, see what you are waiting - learn how to read an explain plan Received on Wed Jun 22 2005 - 13:54:47 CDT