how to prevent a table scan

From: Jerry Dubuke <jdubuke_at_not.gpdservices.com>
Date: Wed, 28 May 2003 10:57:43 -0400
Message-ID: <3ed4ce76$0$29105$4c41069e@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 - 09:57:43 CDT