Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 11 2009
Added on Dec 13 2008
10 comments
963 views