Join Oracle tables from remote sites

Lesson 9	Overview of Oracle's topology solution
Objective	How do I join tables from remote sites in a SQL query?

We can now include any tables from these remote sites by qualifying their remote site name in the SQL query.
This example joins three tables:

a local ORDER table in Denver,
a CUSTOMER table in London, and
a ORDERLINE table in Paris.

SELECT 
   customer.customer_name, 
   order.order_date, 
   orderline.quantity_ordered
FROM 
   customer@london, 
   order, 
   orderline@paris
WHERE
 customer.cust_number = order.customer_number
 AND
 order.order_number = orderline.order_number;

The local ORDER table will access the CUSTOMER table at London, and the ORDERLINE table in Paris will also be included.
You can also hide the fact that the CUSTOMER and ORDERLINE tables are remote by creating a synonym that hides the database link name:

Create public synonym customer for customer@london;
Create public synonym orderline for orderline@paris;

The SQL could now be written as if the tables were local to the database.
The next lesson takes a look at the transparent network substrate (TNS).
Overview Toplogy - Exercise

Before moving on to the next lesson, click the Exercise link below to practice defining database links.
Overview Toplogy - Exercise