-
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
-
[email protected]> create table test1 (keyval number(10), dataval varchar2(20));
Table created.
[email protected]> insert into test1 values (NULL, '1');
1 row created.
[email protected]> insert into test1 values (NULL, '2');
1 row created.
[email protected]> insert into test1 values (NULL, '3');
1 row created.
[email protected]> commit;
Commit complete.
[email protected]> select * from test1;
KEYVAL DATAVAL
---------- --------------------
1
2
3
[email protected]> update test1 set keyval = rownum;
3 rows updated.
[email protected]> commit;
Commit complete.
[email protected]> select * from test1;
KEYVAL DATAVAL
---------- --------------------
1 1
2 2
3 3
-
Commit complete.
[email protected]> select * from test1;
KEYVAL DATAVAL
---------- --------------------
1
2
3
[email protected]> 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]
