Insert into multiple tables from external table

From: Lewis C <lewisc_at_excite.com>
Date: Wed, 11 May 2005 22:49:42 GMT
Message-ID: <vjs4815qsbmnc3g2ipu0863dsa0qiccnvb@4ax.com>

Hi. I have a couple of possibilities but I'm looking for alternatives. Performance is important here.

I'm using Oracle 10g. I have 4 tables.

Table IDs
PK ID_NO number
Column Date_Loaded

Table SRC
PK SRC_ID number
Column Date_Loaded

Table ID_SRC
PK ID_SRC_ID Generated from a sequence. Column Date_Associated
ID_NO May Be null
SRC_ID May Be Null

Table Trans
PK TRAN_ID number
Column TRAN_DT
Column AMT
Column ID_SRC

IDs is a parent to ID_SRC
SRC is a parent to ID_SRC

ID_SRC is a parent to TRANS

I also have an external table:

EXT_TAB
Column Tran_ID Will not be null number
Column ID_NO Might be null number
Column SRC_ID Might be null number
AMT Will not be null number
Tran_DT Will not be null date

I will always get either an ID_NO or a SRC_ID or both from the external table. They will never both be null.

I need to insert and populate the above 4 tables from the external table.

IDs and SRCs in the external table will have dupes.

ID_SRC will have about 200,000,000.

TRAN is partitioned by day and as new days roll in, old days roll off. There's about 900,000,000 rows in TRAN.

EXT_TAB will deliver about 10 million or so rows.

What I would like is a merge all, a combination of insert all and merge. That doesn't exist.

The best I have come up with so far is either a two pass through the external table or resorting to pl/sql and using bulk collect and forall. I don't like either but can't think of anything better.

Bascially the rules are:

Must insert into IDs if ID_NO is present in external table and is not present in ID table.

Must insert into SRCs if SRC_ID is present in external table and is not present in SRC table.

Must insert into ID_SRC table if combination of ID_NO and SRC_ID do not exist in _ID_SRC table.

Must insert into TRAN is TRAN_ID does not exist, must update TRAN is TRAN_ID does exist.

Any ideas?

Thanks,

Lewis

Lewis R Cunningham

Author, ItToolBox Blog: An Expert's Guide to Oracle http://blogs.ittoolbox.com/oracle/guide/

Topic Editor, Suite101.com: Oracle Database http://www.suite101.com/welcome.cfm/oracle

Received on Wed May 11 2005 - 17:49:42 CDT