Re: update takes to long

From: Mladen Gogala <mgogala.SPAM-ME.NOT_at_verizon.net>
Date: Fri, 16 Feb 2007 21:06:57 +0100 (CET)
Message-ID: <er52t1$rru$1@aioe.org>

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.

-- 
http://www.mladen-gogala.com

Received on Fri Feb 16 2007 - 14:06:57 CST