UPDATE PRICE p
SET  p.L_PRICE =
(SELECT  S.L_PRICE
FROM LOAD S
WHERE '0000006666'||S.D_NAME||S.S_ID     = p.p_id
   AND S. L_PRICE !=  p.L_PRICE
   AND S. L_PRICE != 0)
WHERE EXISTS(SELECT  S.L_PRICE
FROM LOAD S
WHERE  p.p_id = '0000006666'||S.D_NAME||S.S_ID
   AND S.L_PRICE !=  p.L_PRICE
   AND S.L_PRICE != 0)

[U]My indexes are[/U]
 INDEX P_IDX2 ON PRICE
(P_LIST, S_ID)

INDEX P_IDX3 ON PRICE
(P_ID, L_PRICE)

[U]Explain plan[/U]

Execution Plan
----------------------------------------------------------
   0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=100416 Card=10017 By
          tes=250425)

   1    0   UPDATE OF 'PRICE'
   2    1     FILTER
   3    2       TABLE ACCESS (FULL) OF 'PRICE' (Cost=246 Card=1001
          7 Bytes=250425)

   4    2       TABLE ACCESS (FULL) OF 'LOAD'
          (Cost=10 Card=13 Bytes=143)

   5    1     TABLE ACCESS (FULL) OF 'LOAD' (C
          ost=10 Card=13 Bytes=195)





Statistics
----------------------------------------------------------
          0  recursive calls
        613  db block gets
   11040073  consistent gets
       1593  physical reads
     137700  redo size
        366  bytes sent via SQL*Net to client
        756  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        544  rows processed