thanks for the exceptionally fast reply!!
So, there is no way in 8.0.6 to get rid of table scans...that is what I
thought, but I figured I would ask the experts first! {grin}
As of now, this query takes about 9 seconds to run because of the table
scans...if I were to upgrade oracle, would the performance increase?
And, if I upgraded oracle at the server, all clients would also need to
upgrade, yes?
BTW - the 8.0.6 is on Windows NT 4.0 box.
Jerry
"Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message
news:zh4Ba.493357$Si4.422969_at_rwcrnsc51.ops.asp.att.net...
> There are several reasons why it is scanning.
> 1.upsh.sh_seqnum is NULL will not use an index; nulls are not indexed
> 2. upsh.sh_seqnum(+) outer join assumes there is a null also
> 3. 'A' || afsh.sh_moohnumber you have concatenated something and hence
this
> won't use an index.
> (could use a function based index, but you are on an ancient version of
> Oracle that might not support it - Oracle doesn't support it anymore.)
> Jim
>
> --
> Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
> with family. Remove the negative part, keep the minus sign. You can
figure
> it out.
> "Jerry Dubuke" <jdubuke_at_not.gpdservices.com> wrote in message
> news:3ed4ce76$0$29105$4c41069e_at_reader0.ash.ops.us.uu.net...
> > Hi all,
> > I have an 8.0.6 Oracle installation. Effectively, I have 2 database
with
> > identical schemas - one is called AF and one is called UP. I am trying
to
> > set up a synchronization on a couple of the tables (must be manual for
> > multiple reasons...). I have the following query, which uses table
scans
> > according to the explain plan. Can anyone see a way that I can speed
this
> > up by forcing it to use indexes? I have indexes set up on sh_seqnum and
> > oh_number.
> >
> > Select
> > afsh.sh_moohtype f1,
> > upoh.oh_number f2,
> > afsh.sh_morefno f3,
> > afsh.sh_date f4,
> > afsh.sh_qty f5,
> > afsh.sh_item f6,
> > afsh.sh_seqnum f7,
> > afsh.sh_adcukey f8,
> > afsh.sh_txseqnum f9,
> > afsh.sh_moohnumber f10,
> > afsh.sh_rqseqnum f11
> > from
> > af.sh afsh,
> > up.sh upsh,
> > up.oh upoh
> > where
> > (upsh.sh_seqnum(+) = afsh.sh_seqnum and upsh.sh_seqnum is NULL) and
> > (upoh.oh_number = 'A' || afsh.sh_moohnumber) and
> > afsh.sh_qty > 0
> > order by
> > afsh.sh_moohnumber,
> > afsh.sh_morefno;
> >
> > Thanks in advance for any help/assistance you can give!!
> > Jerry D
> >
> >
>
>
Received on Wed May 28 2003 - 10:17:38 CDT
