SQL: Is there a Faster Way to do this Update?

I am updating a column 'tocolumn' in 'outtable' with the value in column 'fromcolumn' in table 'inptable', based on the equality of the columns 'tstcolumn'.

Column 'tstcolumn' in both tables is indexed.

I was wondering if there is a faster way to perform this update?

UPDATE OUTTABLE SET OUTTABLE.TOCOLUMN=
(SELECT INPTABLE.FROMCOLUMN FROM INPTABLE    WHERE OUTTABLE.TSTCOLUMN=INPTABLE.TSTCOLUMN); The Explain Plan (from TOAD) states:

UPDATE STATEMENT Optimizer=CHOOSE
  TABLE ACCESS (FULL) OF OUTTABLE
  TABLE ACCESS (BY ROWID) OF INPTABLE
    INDEX (RANGE SCAN) OF INPTABLE_TSTCOLUMN_X (NON-UNIQUE) The OUTTABLE is relatively large (100,000) when compared to INPTABLE (700), but considering neither is excessively large, it would seem the update could be accomplished without running out of rollback space.

I do not understand why a FULL table scan is being performed on the OUTTABLE, given the index. Could it perhaps be the optimizer mode?

Any enlightenment would be appreciated,

Geoff Houck
systems hk
hksys_at_teleport.com
http://www.teleport.com/~hksys Received on Mon Dec 28 1998 - 00:00:00 CST