#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2009
    Location
    Cambridge, UK
    Posts
    10
    Rep Power
    0

    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?
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2009
    Location
    Cambridge, UK
    Posts
    10
    Rep Power
    0
    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.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2009
    Location
    Cambridge, UK
    Posts
    10
    Rep Power
    0
    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);

IMN logo majestic logo threadwatch logo seochat tools logo