Re: How to accelerate UPDATE SQL?

Dino Hsu <dino1.nospam_at_ms1.hinet.net> wrote in message news:<5vsr5u47dmego0foatsrh1ih7jv1o7g410_at_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

Hello Dino,

Here is a further question on your problem. Hope this contributes to finding a solution.

First I want to get straight that where you say ... The INSERT will take like forever, even ... you really mean the UPDATE takes forever. That is obvious from the contexts.

Looking at the UPDATE statement, it strikes that there is no WHERE clause on the table being updated (INVENTORY). So all INVENTORY rows are updated and for each the subquery is executed. This would explain the wait times, were it not that the affected number of rows is small, as you say. Do you mean that not all INVENTORY rows are affected, even though there is no WHERE clause for the UPDATE?

Is that possible? May it for instance be an Oracle feature that rows are skipped from being updated if the sub-query returns no rows?

Edzard Received on Mon Feb 04 2002 - 13:54:29 CST