Re: LEFT JOIN taking a very long time

ruslan00_at_yahoo.com (Ruslan Kogan) wrote in news:d562b81c.0410121720.2f071f80_at_posting.google.com:

> Hi,
>
> I am using the following SQL statement in oracle and the query is
> taking about 5 minutes to run (and correctly returns 6 rows):
>
> select
> ch.wave_nbr,
> ph.store_nbr,
> ph.whse,
> ph.co,
> ph.div,
> sm.name,
> ch.carton_nbr,
> lh.dsp_locn,
> fn_rep_GetLocationDisplay(lh.whse, lh.locn_class, lh.area, lh.zone,
> lh.aisle, lh.bay, lh.lvl, lh.posn),
> sc.code_desc,
> im.size_desc,
> im.sku_desc,
> im.color_desc,
> cd.units_pakd,
> cd.user_id
> from
> carton_hdr ch,
> pkt_hdr ph,
> store_master sm,
> item_master im,
> carton_dtl cd,
> task_dtl td,
> sys_code sc,
> locn_hdr lh
> where
> ch.pkt_ctrl_nbr = ph.pkt_ctrl_nbr and
> ph.store_nbr = sm.store_nbr and
> cd.sku_id = im.sku_id and
> ch.carton_nbr = cd.carton_nbr and
> cd.carton_nbr = td.carton_nbr and
> cd.carton_seq_nbr = td.carton_seq_nbr and
> sc.rec_type = 'S' and
> sc.code_type = '502' and
> ch.stat_code = sc.code_id and
> ch.curr_locn_id(+) = lh.locn_id
>
> If I change the last line to:
> ch.curr_locn_id = lh.locn_id (no left join)
> the query runs in a fraction of a second. The left join is required.
>
> Is there a way I can re-construct this SQL query to make it run
> quicker?
>
> Thanks heaps for all the help!!!!
>

Try by removing
> task_dtl td,
> sys_code sc,
> locn_hdr lh

from the FROM clause because they contribute ZERO items to the SELECT Convert them to EXISTS within the WHERE clause Received on Wed Oct 13 2004 - 08:59:07 CDT