[Wombat1800]

I am trying to run a query from a supplier to upgrade an application which is attempting to add a URN to a simple table of around 135,000 rows.

mytable

CREATE TABLE owner.mytable
(
BATCHID INTEGER NOT NULL,
INFO VARCHAR2(255 BYTE),
ID NUMBER
)

The script, which runs for hours then gives an 'end of communication channel' error, is as follows:

update owner.mytable v set v.id = (
select aRowNum from (
select a.rowid aRowID,rownum aRowNum from owner.mytable a order by rowid asc) b
where b.aRowID = v.rowid)

Can anyone suggest anything that might make this script run more efficiently, an alternative query which will do the same thing more quickly or even just a means of stopping the database connection from dropping out?

[Wombat1800]

Originally Posted by Wombat1800

Can anyone suggest anything that might make this script run more efficiently, an alternative query which will do the same thing more quickly or even just a means of stopping the database connection from dropping out?

I should add that there is an index on column BATCHID but since the values in this column are repeated anything from 1 to 40 times I'm not sure how much help this is.

[Wombat1800]

Solved my own problem

use of a WITH update statement and a /*+ materialize */ hint has the query running in about 30 minutes.

UPDATE cme.tbllaptopbookininfo v
set v.ID =
(with subquery AS
(select /*+ materialize */ a.ROWID arowid, ROWNUM arownum
FROM cme.tbllaptopbookininfo a order by ROWID asc)
select arownum from subquery where arowid = v.RowId);