Re: SQL Update from Select problem

I am using Oracle 9i. Here is the explain plan/autotrace:

UPDATE R_VEHICLE rv1
SET (RANK, VPCT) = ( select rank,vpct
 from (SELECT w_vhc_id,
  DENSE_RANK() OVER(ORDER BY VPSUM DESC) AS RANK,   PERCENT_RANK() OVER(ORDER BY VPSUM ASC) AS VPCT   FROM R_VEHICLE rv2
  WHERE VERSION_ID = 33
  and vpsum > 0) v1
 where rv1.w_vhc_id = v1.w_vhc_id)
where VERSION_ID = 33
and vpsum > 0
/  

98 rows updated.

Execution Plan

   0 UPDATE STATEMENT Optimizer=CHOOSE    1 0 UPDATE OF 'R_VEHICLE'

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'R_VEHICLE'
   3    2       INDEX (RANGE SCAN) OF 'I_RVEHICLE_VERSIONID' (NON-UNIQ
          UE)

   4    1     VIEW
   5    4       WINDOW (SORT)
   6    5         WINDOW (SORT)
   7    6           TABLE ACCESS (BY INDEX ROWID) OF 'R_VEHICLE'
   8    7             INDEX (RANGE SCAN) OF 'I_RVEHICLE_VERSIONID' (NO
          N-UNIQUE)

Statistics

          0  recursive calls
        199  db block gets
        202  consistent gets
          0  physical reads
      44160  redo size
        613  bytes sent via SQL*Net to client
        824  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
        197  sorts (memory)
          0  sorts (disk)
         98  rows processed

"Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message news:<ofeM9.394048$WL3.117197_at_rwcrnsc54>...
> What Oracle version, what is the explain plan, and what does tkprof say?
> Jim
> "Steve" <steve_ackermann_at_hotmail.com> wrote in message
> news:42733b5.0212182244.57fa0561_at_posting.google.com...
> > This SQL runs indefinitely and takes 95% CPU. However, if I create a
> > temporary table (say R_VEHICLE_TEMP) and modify the SQL slightly to
> > update that instead of R_VEHICLE, the SQL only takes 5 seconds to run
> > correctly. Why can't I select and update from/to the same table?
> >
> > UPDATE R_VEHICLE rv1
> > SET (RANK, VPCT) = ( select rank,vpct
> > from (SELECT w_vhc_id,
> > DENSE_RANK() OVER(ORDER BY VPSUM DESC) AS RANK,
> > PERCENT_RANK() OVER(ORDER BY VPSUM ASC) AS VPCT
> > FROM R_VEHICLE rv2
> > WHERE VERSION_ID = 19
> > and vpsum > 0) v1
> > where rv1.w_vhc_id = v1.w_vhc_id)
> > where VERSION_ID = 19
> > and vpsum > 0
> > /
> >
> > Thanks for any help,
> > Steve
Received on Fri Jan 03 2003 - 23:10:23 CET