Re: LEFT JOIN taking a very long time

"Ana C. Dent" <anacedent_at_hotmail.com> wrote in message news:<Xns9581470F13210SunnySD_at_68.6.19.6>...
> 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

Ana, you must have looked at the post too quickly as two of the three tables you listed DO provide columns to the select list. In the case of the third adding a table to the FROM clause which does not provide a column to the select list can nevertheless sometimes make an index on that table availble to the optimizer to use in creating an indexed access path into another table in the FROM clause. Without an explain plan and information on the available indexes there is no way to be sure if the table hurts or helps the query performance.

IMHO -- Mark D Powell -- Received on Wed Oct 13 2004 - 20:02:49 CDT