Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> LEFT JOIN taking a very long time
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!!!! Received on Tue Oct 12 2004 - 20:20:54 CDT