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

Sybrand Bakker wrote:

Thank you for the suggestions. I added the WHERE clause to the UPDATE portion of the statement, and it completed in very quick time. For the benefit of others as dense as myself:

UPDATE OUTTABLE SET OUTTABLE.TOCOLUMN=
 (SELECT INPTABLE.FROMCOLUMN FROM INPTABLE     WHERE OUTTABLE.TSTCOLUMN=INPTABLE.TSTCOLUMN)   WHERE OUTTABLE.TSTCOLUMN IN (SELECT INPTABLE.TSTCOLUMN FROM INPTABLE) (I have qualified all the tables only for clarity)

Thanks to the other respondents as well; I appreciate it.

Yours,

Geoff Houck

>
> Hi Geoff,
>
> As there is no where clause on outtable, this will ALWAYS result in a
> full table scan, regardless of optimizer mode. The subquery you have is
> a correlated subquery between outtable and inptable, and the optimizer
> is correctly choosing the index there. Based on your sql and your
> description of the tables as far as I can see no other index can be
> used.
> If you need to limit the rows to be updated in outtable to the rows
> occurring in inptable, you will need to add a where clause with the same
> correlated subquery. In that case an index will be used.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
> GHouck 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
>
> ---------------------------------------------------------------
>
> Name: postbus.vcf
> Part 1.2 Type: text/x-vcard
> Encoding: 7bit
> Description: Card for Sybrand Bakker
 

-- 
Geoff Houck
systems hk
hksys_at_teleport.com
http://www.teleport.com/~hksys