Re: update takes to long

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Fri, 16 Feb 2007 21:19:55 +0100
Message-ID: <45D611EB.8000902@gmail.com>

Mladen Gogala schrieb:
> On Fri, 16 Feb 2007 18:51:16 +0100, Maxim Demenko wrote:
>

>> 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

>
>
> Slightly faster version which will avoid repeated counting:
> 1 merge into a
> 2 using (select rowid as row_id,
> 3 row_number() over (order by path) as rnk
> 4 from a) b
> 5 on (a.rowid=b.row_id)
> 6* when matched then update set sortorder=b.rnk
> SQL> /
>
> 100000 rows merged.
>
>

Mladen, you still miss my point - both updates are not the same if SORTORDER is nullable (and only with COUNT is adequate to 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> update a set sortorder=NULL where mod(path,1000)=0;

20 rows updated.

SQL> commit;

Commit complete.

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.

SQL> select * from a where mod(path,1000)=0;

       PATH SORTORDER
---------- ----------

       1000        999
       2000       1998
       3000       2997
       4000       3996
       5000       4995
       6000       5994
       7000       6993
       8000       7992
       9000       8991
      10000       9990
      11000      10989
      12000      11988
      13000      12987
      14000      13986
      15000      14985
      16000      15984
      17000      16983
      18000      17982
      19000      18981
      20000      19980

20 rows selected.

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.

SQL> select * from a where mod(path,1000)=0;

       PATH SORTORDER
---------- ----------

       1000       1000
       2000       2000
       3000       3000
       4000       4000
       5000       5000
       6000       6000
       7000       7000
       8000       8000
       9000       9000
      10000      10000
      11000      11000
      12000      12000
      13000      13000
      14000      14000
      15000      15000
      16000      16000
      17000      17000
      18000      18000
      19000      19000
      20000      20000

20 rows selected.

Best regards

Maxim Received on Fri Feb 16 2007 - 14:19:55 CST