July 15th, 2011, 04: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.
CREATE TABLE owner.mytable
BATCHID INTEGER NOT NULL,
INFO VARCHAR2(255 BYTE),
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, 06:41 AM
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.
Originally Posted by Wombat1800
July 15th, 2011, 08: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);