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