merge data deliveries with main table; report I/U's
484086Dec 13 2008 — edited Dec 14 2008==@admin: i just see this should probably be in 'SQL and PL/SQL'. Can you please move it there? Thanks==
Hi all,
I was hoping to find some help in solving the following problem in an efficient manner.
Basically i would like to merge records into an existing table, but also have a result dataset containing all the updates/inserts.
Table T1:
a set of 40,000,000 records.
Incoming dataset (delivered up to 100 times daily) T2:
a total daily number of around 100,000,000 records (e.g., 100 files containing 1,000,000 records each). In light of the main table, some of these are new accno's, some are relevant change records (different product id). However by far, most contain unchanged and/or old data (from timestamp) and can be discarded. Therefore we can say the main table is relatively stable (>>indexing?).
Both tables hold 1 row per account_no.
Two Requirements:
1) a refreshed table containing:
- all 'old' T1 recs that are not in T2
- all 'new' T2 recs that are not in T1
- all 'updated' T2 recs that are in T1 but have different attr1 field and the timestamp fields are at least <n> days newer than the timestamp for the record in T1
2) an output dataset containing:
- all new and updated records. (
As the delivery may happen up to 100 times per day, i am not so sure if and how to put indexes; join into a temp table, etc...
any help is greatly appreciated!!
many thanks
PS oracle version is 10g.
Edited by: user481083 on 13-dec-2008 15:09
Edited by: user481083 on 13-dec-2008 15:17
Edited by: user481083 on 13-dec-2008 15:30
- date difference on timestamp should be >n days
- attri_1 fields should be different