Thursday, February 13, 2014

update a single column in a table with billion rows

Question:
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