Re: LEFT JOIN taking a very long time

ruslan00_at_yahoo.com (Ruslan Kogan) wrote in message 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!!!!

Ruslan, run an explain plan on both versions of the query. When you outer join you pretty much force Oracle to drive on the table that should always return a row so the driving order is likely to be different between the two queries. It may be that the driving order could be changed for the outer join version to be closer to the non-outer join version or that by comparing the plans you can determine the optimal join order. If the outer join plan is not using that order then by rewriting the query and using hints you can try to force what you believe to be a better join order and compare the results. Sometimes adding the requirement to outer join means you have to accept a slower run time to get the information you want. But only by looking at the plans will you be able to determine if the CBO is using the wrong plan, or that an index that would help is missing, etc...

HTH -- Mark D Powell -- Received on Wed Oct 13 2004 - 11:02:37 CDT