Thursday, December 19, 2019

How to merge two large tables? (Oracle)

Scenario:
There are two large tables, table A has 3 billion records and table B has 80 million records. How to merge them into one table?

Solutions:
################
# Solution 1:
################
Use MERGE sql statement with parallel.
--pros: simple and easy to understand
--cons: the requirement to hardware is relatively high

################
# Solution 2:
################
Hash partition both tables by ID, then use MERGE with parallel. This will use Oracle's new feature PWJ -- Partition Wise Join. 


################
# Solution 3:
################
Create a new table C, then insert into C using full outer job of table A and B. Then rename table C, rebuild indexes...etc.
--PWJ + parallel + nologging + compress

################
# Solution 4:
################
11g + version only
Use DBMS_PARALLEL_EXECUTE to separate table B(which has less records) by ROWID. Then use cursor/plsql to loop thru the smaller tables.
--pros: less rollback/UNDO
--cons: very slow using cursor .

################
# Solution 5:
################
Compare records in both A and B and remove duplicate ones. Then do a direct INSERT.
--pros: no MERGE, less expensive
--cons: more DML than other solutions



No comments:

Post a Comment

My own Mind Map program in Java script and Python

I had been searching online mindmap apps for my study for a while and never got one that I am really happy with.  Then I asked myself what I...