Bring the history database onto the new server under a different schema, and
onto different drives than the current database. This will solve several
problems. The main problem is that Oracle will not use an index in the
"WHERE ORDER_ID NOT IN (SELECT ORDER_ID FROM CUSTOMER_ORDER);" portion of
your query because it is on another server. This means a full table scan.
The only way to solve this is to have both tables on the same server. So,
bring it over (it's history, so it's static), and create an index on
order_id in both tables. By placing the data and indexes on drives other
than the ones your current database is on, you'll be avoiding contention
with the OLTP system.
"Eric Bowman" <ebowman_at_systec.com> wrote in message
news:3a1c1b63.0111050821.7f249aae_at_posting.google.com...
> 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 - 11:31:43 CST
