Re: update takes to long

From: Maxim Demenko <mdemenko_at_arcor.de>
Date: Fri, 16 Feb 2007 18:51:16 +0100
Message-ID: <45D5EF14.3020702@arcor.de>

Mark D Powell schrieb:
> On Feb 16, 9:12 am, "andersleffler" <andersleff..._at_hotmail.com> wrote:

>> I have a sql statement that really could use som tuning, but I do not
>> know how. It is updated by a self join, and contains appr 20000 rows.
>> The statement executes on almost 3 minutes. I have rewritten it to an
>> anonymous block, but I only reduce the time by half, which is not
>> enough. Any help would be appreciated. Thanks
>>
>> UPDATE a
>>    SET (sortorder) = (SELECT COUNT (sortorder)
>>                       FROM a b
>>                      WHERE b.PATH <= a.PATH)

>
> Where is the explain plan? Is path indexed? What pl/sql alternate
> did you use? You should also always include the Oracle version.
>
> The subquery is a coordinated sub-query so it is executed once for
> every row in the outer query. The subquery is then scanning the
> table. Because you are reading and updating the same table Oracle is
> going to have to build a lot of consistent views of the blocks
> basically undoing the changes so that the subquery can see the rows as
> the rows existed before any changes were made.
>
> You might want to consider using the row_number analytic function.
>
> HTH -- Mark D Powell --
>

I would suggest, the more appropriated windowing function (than row_number) is count - in case when NULL is allowed for sortorder, it will yield results different from the original statement.

SQL> CREATE TABLE a AS

   2 SELECT ROWNUM path,trunc(dbms_random.VALUE(1,20000)) AS sortorder    3 FROM dual CONNECT BY LEVEL <= 20000;

Table created.

SQL> set timing on
SQL> UPDATE a

   2     SET (sortorder) = (SELECT COUNT (sortorder)
   3                        FROM a b
   4                       WHERE b.PATH <= a.PATH);

20000 rows updated.

Elapsed: 00:01:00.32
SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.51
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.53
SQL> MERGE INTO a

   2 USING (SELECT t.*, COUNT(sortorder) over(ORDER BY path) new_sortorder FROM a t) b

   3 ON (a.path=b.path)
   4 WHEN MATCHED THEN UPDATE
   5 SET sortorder=new_sortorder;

20000 rows merged.

Elapsed: 00:00:00.40

Best regards

Maxim Received on Fri Feb 16 2007 - 11:51:16 CST