| Faster way to update large amount of data [message #411141] |
Thu, 02 July 2009 02:50  |
carillpower
Messages: 40
Registered: April 2009
Location: malaysia |
Member |
|
|
Hi all guru's...i got doubt here..
I have a proc which will change data in all table which hav certain column...its like this..
CREATE OR REPLACE
PROCEDURE process_A IS
cursor c1 is
select distinct(table_name) t_name from ...;
BEGIN
for rec in c1 loop
execute immediate '
update '||rec.t_name||' set data_A = translate(data_A,''0123456789'',''1234567890'')';
end loop;
END SCRAMBLING_JOB;
The process is quite simple but because of the data is too many...the time it takes was so long...do anyone of u could give me any tips how to make the proc execute more faster
Thanks a lot
|
|
|
|
|
| Re: Faster way to update large amount of data [message #411176 is a reply to message #411141] |
Thu, 02 July 2009 05:03  |
_jum
Messages: 117
Registered: February 2008 |
Senior Member |
|
|
If there is an INDEX on data_A the UPDATE could be faster without or with marked as UNUSABLE IINDEX. See the sample below. I simple see the time difference, may be the gurus have something to remark, esp. in production ?
DROP TABLE test_107 CASCADE CONSTRAINTS;
CREATE TABLE test_107 AS
SELECT TRUNC(SYSDATE - DBMS_RANDOM.VALUE * 365) datum,
DBMS_RANDOM.STRING('x', 20) col1
FROM DUAL
CONNECT BY LEVEL <= 100000;
SET TIMING ON;
UPDATE test_107
SET col1 = TRANSLATE(col1, '0123456789', '1234567890');
CREATE INDEX indx_test_107 ON test_107(col1);
UPDATE test_107
SET col1 = TRANSLATE(col1, '0123456789', '1234567890');
ALTER INDEX enviam.indx_test_107 UNUSABLE;
UPDATE test_107
SET col1 = TRANSLATE(col1, '0123456789', '1234567890');
ALTER INDEX enviam.indx_test_107 REBUILD;
UPDATE test_107
SET col1 = TRANSLATE(col1, '0123456789', '1234567890');
Table dropped.
Table created.
100000 rows updated.
Elapsed: 00:00:01:46
Index created.
Elapsed: 00:00:00:18
100000 rows updated.
Elapsed: 00:00:07:12
Index altered.
Elapsed: 00:00:00:00
100000 rows updated.
Elapsed: 00:00:01:70
Index altered.
Elapsed: 00:00:01:29
100000 rows updated.
Elapsed: 00:00:07:31
|
|
|
| Re: Faster way to update large amount of data [message #411182 is a reply to message #411176] |
Thu, 02 July 2009 05:26 |
JRowbottom
Messages: 5254
Registered: June 2006
Location: Sunny North Yorkshire, ho... |
Senior Member |
|
|
The big disadvantage with this method is that it affects other users.
If you just performa an update and commit the changes when they're finished, then the table remains completely usable to other users at all times.
If you make the indexes unusable, you run the risk of severely impacting other peoples sessions.
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
