Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> LEFT JOIN taking a very long time

LEFT JOIN taking a very long time

From: Ruslan Kogan <ruslan00_at_yahoo.com>
Date: 12 Oct 2004 18:20:54 -0700
Message-ID: <d562b81c.0410121720.2f071f80@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!!!! Received on Tue Oct 12 2004 - 20:20:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US