Re: Join Table Across Database

Shinya Koizumi wrote:
> Is there any service in oracle to join the remote table realtime( each table
> located in the different DB and different network)?
>
> Location A has information about the customer information, Location B has
> information about the purchase info( number of
> books how much they spend ), and Location C has information about books(
> author, title, opinions on the book ).
> We want to join these three tables and search on the joined table. Retrieved
> data should be updated each search.
>
>
>
> Shinya

The short answer is: yes, this is a simple to set-up and do using database links (which are simple to create and use):

Here's the 3-minute solution, connected to location "B" where your purchase info is (I chose that location assuming the purchase [transactional] data is probably the source of the largest amount of data, and assuming that the purchase info connects directly to both the product and customer type data.):

CREATE DATABASE LINK link_a
CONNECT TO user_a IDENTIFIED BY user_a_password USING 'database_a';

CREATE DATABASE LINK link_c
CONNECT TO user_c IDENTIFIED BY user_c_password USING 'database_c';

SELECT c.name
      ,c.address
      ,b.title
      ,b.author
      ,p.quantity
      ,p.price
FROM   purchase          p
      ,cust_at_link_a       c
      ,book_at_link_c       b
WHERE  c.cust_id       = p.cust_id
AND    b.cust_id       = p.cust_id;

Presto -- instant 3-database queries, all with current info.

(This assumes database_a and database_c are in database_b's ORACLE_HOME TNSNAMES -- if not, you want to substitute DB connect info.)

The long answer is: be VERY CAREFUL with this! This is NOT the way Amazon.com does it, I guarantee you :-). Although this solution will function reasonably well with modest data volumes and/or very carefully selective SQL (unlike my un-selective example above), you do not want to choose this solution if you have any other choice. It is NOT a good choice for scalable OLTP application building. This type of solution is best utilized for occasional selective cross-database queries.

Why are the three types of info, which sound very closely related, in three different databases? Why not just 3 different schemas in one database?

-Greg Received on Thu Apr 27 2006 - 01:10:50 CDT