Re: LEFT JOIN taking a very long time

Try adding a combination join in the same order as your query for sc.rec_type, sc.code_type, sc.code_id

maybe switching it to code_id, rec_type, code_type in your where clause so the index will look like

index column 1, code_id

         column 2, rec_type
         column 3 code_type

the index will only be used if the code_id is in the first position in the where clause then the second and third in sequencial positions. It will also pick up for queries using on the first or first and second columns. Make sure also that all the foreign keys and primary keys are active on your database sometimes they can become inactive depending on the skill of the dba transfering data from one schema to another (somethings do get forgotten) TOAD is a good tool to see this.

Depending on your database if it is 8i or 9i it maybe using the rule base or statistics .. see that you know which .. talk to the DBA maybe the stats have not been updated.

Cheers.

"Ruslan Kogan" <ruslan00_at_yahoo.com> 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!!!!
Received on Thu Oct 14 2004 - 15:50:21 CDT