Re: Advice For Efficient Update

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Tue, 21 Jan 2003 08:32:39 +0000
Message-ID: <b0ipi8$k5k$1@ctb-nnrp2.saix.net>

daecc wrote:

> I have a table with 3 million records. The field "group" starts out
> null and I need to update it based on the combination of 7 other large
> text fields (I used 3 small fields for illustration purposes).
>
> rec_num field1 field2 field3 group
> ------- ------ ------ ------ -----
> 1 AA JJ XX 1
> 2 AA JJ XX 1
> 3 BB JJ XX 2
> 4 CC JJ XX 3
> 5 AA JJ XX 1
> 6 CC KK ZZ 4
> 7 CC KK ZZ 4
>
> The data is already loaded in the table and I must do an update. What
> would be the most time efficient way to accomplish this?

The Wrong Way (tm). Writing a PL/SQL proc that opens a cursor. Reading a row. Using several IF statements to determine the group value. Then updating the row.

The Wrong Way (tm) is slow. It is individual row processing. It risks a snapshot too old error. It dates back to what we did in the 70's and 80's with with tape merges in COBOL on IBM mainframes.

The SQL Way (tm):

UPDATE foo
  SET group = 1

WHERE field1 = 'AA' 
AND   field2 = 'JJ'
AND   field3 = 'XX'
  

Commit. Repeat for other values. To increase performance, look at parallel DML and indexing.

If the coding of the group field is complex, then better do that up front on a smaller distinct set, instead of the full table.

E.g.

Create a distinct list:
CREATE TABLE foobar
NOLOGGING AS
SELECT DISTINCT field1, field2, field3 FROM foo

Add the group column:
ALTER TABLE foobar ADD ( group NUMBER(6) )

Calculated the group value on the smaller distinct data set: UPDATE foobar
  SET group = DECODE( field1||field2||field3,

                         'value1' , 1,
                         'value2',  2,
                                   -666 )

(or use PL/SQL if you have no other choice due to the complexity of the decoding of the fields to determine the grouping)

Now update the master table with the calculated results: UPDATE foo f
  SET group = (SELECT x.group FROM foobar x

               WHERE x.field1 = f.field1
               AND   x.field2 = f.field2
               AND   x.field3 = f.field3 )

I'm sure this cat can be turned into roadkill with several other cunningly crafted SQL statements too.

Have fun.

--
Billy

Received on Tue Jan 21 2003 - 02:32:39 CST