Merging tables from different databases for report

I need to find a method to essentially `merge` tables in different databases. Here is the situation:

We use an ERP system that runs on an Oracle 8.06 database. Last year management decided to `re-implement` the system on a new database to accomplish some very fundamental changes in the way the inventory and costing are handled. This was the only way to do it and it worked out fine. The challenge now is historical reports since we have all historical data prior to June 1, 2000 in the HISTORY database and everything since then in the CURRENT database. There are some records that overlap because they were in an open status as of 6/1/00 (ie. They exist in both databases). Now if I need to write a report that spans both databases, I first have to merge the tables, favoring the CURRENT database in cases where a record exists in both since that would be more complete information.

I tried to accomplish this by linking the tables from one database to the other and then creating a view to do the merging. Here’s the basic idea of what I tried:

I have a link to the CUSTOMER_ORDER table in the HISTORY DATABASE.

SELECT ORDER_ID, CUSTOMER_ID, ORDER_DATE, ORDER_TOTAL FROM CUSTOMER_ORDER
UNION
SELECT ORDER_ID, CUSTOMER_ID, ORDER_DATE, ORDER_TOTAL FROM CUSTOMER_ORDER_at_HISTORY
WHERE ORDER_ID NOT IN (SELECT ORDER_ID FROM CUSTOMER_ORDER); The good news is it worked. The bad news is it takes forever to run (over 10 minutes) and kills the processor while it is running. Apparently this is not the right way to do this. Any suggestions on the best way to accomplish what I’m trying to do would be appreciated. There are potentially over a hundred tables I need to merge so I would like to avoid importing them into the live database if possible. Received on Mon Nov 05 2001 - 10:21:38 CST