Updating Tables in Faster Way...........

[shailesh]

Hello,

We have 'MYROWID' column in all tables of our database which will have unique value. There are 1000 tables I need to update where records are ranging upto 30,00,000. Altough I have written procedure to do this which takes around 4 to 5 hours to update MYROWID column in all tables. Please guide me in making this procedure faster. Can I use multiple session in procedure, If yes how? or any other method. Ultimately I want to reduce this time.

CREATE OR REPLACE PROCEDURE UpdateRowId IS
CURSOR C1 IS
SELECT TNAME
FROM TAB
WHERE TABTYPE = 'TABLE' ;

Row_Rec C1%ROWTYPE ;
nCursorId INTEGER ;
nRowsProcessed INTEGER ;

BEGIN
nCursorId := DBMS_SQL.OPEN_CURSOR ;
OPEN C1 ;
LOOP
FETCH C1 INTO Row_Rec ;
EXIT WHEN C1%NOTFOUND ;

DBMS_SQL.PARSE ( nCursorId, 'UPDATE ' || Row_Rec.TNAME
|| ' SET MYROWID = SEQMYROWID.NEXTVAL ', dbms_sql.v7 );
nRowsProcessed := DBMS_SQL.EXECUTE ( nCursorId ) ;
COMMIT;
END LOOP ;
CLOSE C1 ;
DBMS_SQL.CLOSE_CURSOR ( nCursorId ) ;
END UpdateRowId ;
/


Thanks & Regards
Shailesh

[marist89]

jeff@dev815nt.us> create table test1 (keyval number(10), dataval varchar2(20));

Table created.

jeff@dev815nt.us> insert into test1 values (NULL, '1');

1 row created.

jeff@dev815nt.us> insert into test1 values (NULL, '2');

1 row created.

jeff@dev815nt.us> insert into test1 values (NULL, '3');

1 row created.

jeff@dev815nt.us> commit;

Commit complete.

jeff@dev815nt.us> select * from test1;

KEYVAL DATAVAL
---------- --------------------
1
2
3

jeff@dev815nt.us> update test1 set keyval = rownum;

3 rows updated.

jeff@dev815nt.us> commit;

Commit complete.

jeff@dev815nt.us> select * from test1;

KEYVAL DATAVAL
---------- --------------------
1 1
2 2
3 3

[mber]

Commit complete.

jeff@dev815nt.us> select * from test1;

KEYVAL DATAVAL
---------- --------------------
1
2
3

jeff@dev815nt.us> update test1 set keyval = rownum;

it should be:

KEYVAL DATAVAL
------------ ------------------
UUUUUUUU 1
UUUUUUUU 2
UUUUUUUU 3

Last will be:

KEYVAL DATAVAL
------------ ------------------
1UUUUUUU 1
2UUUUUUU 2
3UUUUUUU 3

"U" represents for spaces.

And or U can do update test1 set keyval = rowid based on what U really want, and of course U shoul change the keyval datatype to VARCHAR2.

Which version are U using? If under ORACLE 8i then
use execute immediate new feature.

Take care

[Edited by mber on 01-23-2001 at 04:29 PM]