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:08:15 CDT
