Supposed we have huge table called bigTable, we want to update col3's 'old val' to 'new val'.
Solution:
1. if there is enough db space
INSERT into temp
SELECT pk, col1, col2, col 4, col5
CASE col3 WHEN 'old val' THEN 'new val' ELSE col END
FROM bigTable;
Create indices;
Drop related FKs;
Drop bigTable;
Rename Table temp To bigTable;
Create FKs;
Update: This technique is called CTAS in the link:
http://www.dba-oracle.com/t_fast_batch_updates_ctas.htm
create table
new_mytab
NOLOGGING as
select /*+ full parallel(mytab,35)*/
decode (status,'new','old',status,
col2, col3, col4
from mytab;
-- rebuild indexes, triggers and constraints to new_mytab
rename mytab to bkup_mytab;
rename new_mytab to mytab;
2. if there is limited db space
INSERT into temp
SELECT pk, 'new val' as col
FROM bigTable
Where col3 = 'old val';
MERGE INTO bonuses D USING temp S ON (D.pk = S.pk) WHEN MATCHED THEN UPDATE SET D.col3 = S.col ;
No comments:
Post a Comment