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

In article <368811B3.2B48_at_teleport.com>,   GHouck <hksys_at_teleport.com> wrote:
> 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
>

Given the number of rows in both tables I believe this would be a bit faster:

UPDATE OUTTABLE SET OUTTABLE.TOCOLUMN=
 (SELECT /*+ full(inptable) index(outtable indexname) */

    INPTABLE.FROMCOLUMN FROM INPTABLE
    WHERE OUTTABLE.TSTCOLUMN=INPTABLE.TSTCOLUMN); It all depends on version of Oracle RDBMS (this would work in 7.3.4.3 and higher) and CBO features.
The other solution would be to use RBO and then we have two cases:

1. TSTCOLUMN is "numeric" (number or date)

  UPDATE OUTTABLE SET OUTTABLE.TOCOLUMN=   (SELECT INPTABLE.FROMCOLUMN FROM INPTABLE     WHERE OUTTABLE.TSTCOLUMN=INPTABLE.TSTCOLUMN+0); b) TSTCOLUMN is "character" column (CHAR, VARCHAR2, ROWID)

  UPDATE OUTTABLE SET OUTTABLE.TOCOLUMN=   (SELECT INPTABLE.FROMCOLUMN FROM INPTABLE    WHERE OUTTABLE.TSTCOLUMN=INPTABLE.TSTCOLUMN||'');

--
Mladen Gogala

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own