July 15th, 2011, 03:56 AM
-
Serial Number query
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?
July 15th, 2011, 05:41 AM
-
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.
July 15th, 2011, 07:19 AM
-
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);