Re: update takes to long

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

SQL> SELECT COUNT(*)
   2 FROM gv$reserved_words
   3 WHERE keyword = 'PATH';

   COUNT(*)

          1

SQL> And "FROM a b" is invalid so this is not your actual code.

I see multiple problems with what you have posted, and think you could do yourself a substantial favor if you reposted and included the following:

1. Oracle version
2. DDL for table a
3. DDL for table b
4. Number of rows in table a
5. Number of rows in table b
6. DDL of all indexes on a.path and b.path
7. Explain plan for the real statement when it is run

But by way of observation ... you are asking for a Cartesian product so the number of actual rows is huge. What is the business case that supports your WHERE clause?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org