How to accelerate UPDATE SQL?

From: Dino Hsu <dino1.nospam_at_ms1.hinet.net>
Date: Mon, 04 Feb 2002 10:55:36 +0800
Message-ID: <5vsr5u47dmego0foatsrh1ih7jv1o7g410@4ax.com>

Dear all,

I have been bothered for months by the poor performance of UPDATE, I have tried this on several databases with the same result. The following is a typical example: the scenario is to update the INVENTORY table, when the corresponding records don't exist, do UPDATE, when not, do INSERT. The INSERT will take like forever, even when the affected number of rows is small whereas INSERT... SELECT comes out almost instantly. If this problem cannot be worked out, I am forced to use CREATE TABLE AS SELECT + DELETE + INSERT... SELECT instead of UPDATE... SELECT, which is really ugly.

SQL> desc inventory

 Name                          Null?    Type


 ----------------------------- -------- --------------------

 DEF_DATE                      NOT NULL DATE
 SRCODE                        NOT NULL CHAR(2)
 FSC                           NOT NULL CHAR(7)
 OHQTY                                  NUMBER(7)
 CTDQTY                                 NUMBER(7)
 TYPE                                   CHAR(1)

SQL> desc fsrdrp
 Name                          Null?    Type


 ----------------------------- -------- --------------------

 DEF_DATE                               DATE
 SRCODE                                 CHAR(2)
 FSC                                    CHAR(7)
 SALESQTY                               NUMBER(7)
 EXCHANGEQTY                            NUMBER(7)
 COUNTQTY                               NUMBER(7)

SQL> desc ohctd
 Name                          Null?    Type


 ----------------------------- -------- --------------------

 DEF_DATE                      NOT NULL DATE
 SRCODE                        NOT NULL CHAR(2)
 FSC                           NOT NULL CHAR(7)
 OHQTY                                  NUMBER(7)
 CTDQTY                                 NUMBER(7)

insert into INVENTORY

    (select

        a.DEF_DATE,
        a.SRCODE,
        a.FSC,
        b.SALESQTY + b.EXCHANGEQTY - b.COUNTQTY OHQty,
        b.SALESQTY CTDQty,
        '0' TYPE

    from OHCTD a, FSRDRP b, INVENTORY c
    where

        a.SRCODE = b.SRCODE and 
        a.FSC = b.FSC and
        a.SRCODE = c.SRCODE (+) and 
        a.FSC = c.FSC (+) and
        c.SRCODE is null);

update INVENTORY c
set (OHQty, CTDQty)=

    (select

        b.SALESQTY + b.EXCHANGEQTY - b.COUNTQTY OHQty,
        b.SALESQTY CTDQty

    from OHCTD a, FSRDRP b
    where

        a.SRCODE = b.SRCODE and 
        a.FSC = b.FSC and
        a.SRCODE = c.SRCODE and 
        a.FSC = c.FSC);

Any comments are highly appreciated.

Regards,
Dino Received on Sun Feb 03 2002 - 20:55:36 CST