Re: update takes to long

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 -- Received on Fri Feb 16 2007 - 10:51:26 CST