Click here to Skip to main content
13,048,027 members (73,117 online)
Click here to Skip to main content
Add your own
alternative version


5 bookmarked
Posted 1 Apr 2013

Optimized way to join tables residing across multiple databases in Oracle

, 1 Apr 2013
Rate this:
Please Sign up or sign in to vote.
Querying data from tables across multiple databases when we need data from all those tables in a single query.


With a huge enterprise level datasystems these days, we might have some requirements where we would need to get data from different databases residing on different servers (sometimes residing at different locations), Here we might need to get data joining two tables residing on different databases

For example there exists a table_a under schema_a in database_a and table_b under schema_b in database_b with below columns:

table_a columns

table_b columns

Let's say we need to write a query to get top 9 employee details with there names and emails.

Here this might look simple, thinking that we can join two tables by refering tables with dblinks pointing to needed database just like below queries.


select * from table_a x, y where where rownum > 10; 


The above query works perfect to get data needed, but if we learn how the above query works, we will get to know how this would create problems when those tables have thousands of records and when we are expecting only a couple from those. When we run the query, actually oracle will fetch all the records in the table_B from database_B into database_a and then try to filter the matching records in both table_a and table_b So now we can imagine how much data traffic it would cause to move thousands of records across databases just to fetch a couple from those. 

So the other alternative we can think about is, we need to get only the required records from table_b in database_b to database_a and then try joining both to get the columns needed from both the tables but unfortunately oracle allows only primitive data types (Number, Varchar..etc..) to return using a function across databases but here we need to get a table type (user defined type) in order to be able to join both.

Using The Code 

Here I came with a thought of how we can use an old technique/ predefined procedure in oracle which is used to execute dynamic SQL queries called DBMS_SQL. The main advantage or reason to use this is DBMS_SQL is a procedure and as all know oracle supports RPC (Remote Procedure Calls) and this procedure can be executed across databases, Let us see how this works (Refer the link to know more on DBMS_SQL ).  

  1. Define a function in database_b which would execute a query to return only the needed records from table_b with the help of its input parameter which would be sent while calling this function from database_a
  2. PROCEDURE DefineQueryCursor (c IN OUT NUMBER, p_emp_ids VARCHAR)
       rc   NUMBER;
       c := DBMS_SQL.open_cursor;
       DBMS_SQL.parse (c,
                       'SELECT *
     FROM table_b Where employee_id in (' || p_emp_ids || ')',
       rc := DBMS_SQL.Execute (c);
    END DefineQueryCursor;

    We would be calling the above function from database_a to get only the required employee details. Observe the first parameter in function is a out param that sends out the cursor reference in database_b to database_a on which the query would get executed and the results would be saved from that point of cursor where as the second parameter is used to filter out the needed records from table_b. Here I would send a comma separated employee ids from database_a that are needed for us.

  3. Declare a record type and table type variables in database_a with the columns needed from table_b to catch the records/result set we get from query declared in the above function.
  4. TYPE rec_employee IS RECORD
          EMPLOYEE_ID             VARCHAR (10),
          EMPLOYEE_MAIL     VARCHAR2 (4000)
    TYPE tab_employee IS TABLE OF rec_employee;
  5. Define a function in database_a to call the function in database_b and then wrap the data in a table type, we use pipelined functions here that helps in returning table types, please go through the link for more information on pipelined functions
  6. FUNCTION get_employee_info
          RETURN tab_employee
          v_result        rec_employee;
          cmfs            VARCHAR (4000);
          c               NUMBER;
          v_employee_id   ;
          indx            NUMBER := 0;
          d               NUMBER;
          fetch_count     NUMBER := 10;
       /*Below Query gives us top 9 employee ids each separated by a comma in a single record 
         which would be sent to function defined in database_b to get the details of only those 9 employees*/
          SELECT RTRIM (
                    XMLAGG (XMLELEMENT (e, employee_id || '|')).EXTRACT (
            INTO emp_ids
            FROM (SELECT t1.employee_id
                    FROM table_a t1
                   WHERE rownum < 10);
          SELECT COUNT (t1.employee_id) + 5
            INTO fetch_count
            FROM table_a t1
           WHERE rownum < 10;
        /* Below we are calling the function defined in database_b with a OUT param c that holds 
           the cusrsor reference at database_b and a second param which have all the ids that we need*/
 (c, emp_ids); 
        /* Below are array definations which we need to define based on the results of query in database_b,
        C is the cursor reference,1 is the column index in the query in database_b,
        employee_id is the column name corresponding to the column index 1 in the query result at database_b,
        fetch_count is a variable which says how many records does the procedure need to fetch at one stretch 
        here we declared it with the no of records we are expecting so as to get all the records in a single stretch */
             d := (c);
         /*Below we save all the record values into a table variable that we already declared */
    (c, 1, v_employee_id);
    (c, 2, v_employee_name);
             EXIT WHEN d != fetch_count;
          END LOOP;
          IF v_employee_id.COUNT > 0
        /* Below we save all the values in local table variable 
           and then pipe it to the record type variables we declared earlier */
             FOR i IN v_employee_id.FIRST .. v_employee_id.LAST
                v_result.EMPLOYEE_ID := v_employee_id (i);
                v_result.EMPLOYEE_MAIL := v_employee_name (i);
                PIPE ROW (v_result);
             END LOOP;
          END IF;
          WHEN OTHERS
             IF (c)
             END IF;
             raise_application_error (
                'func-getempdetails-dbms_sql: - ' || SQLERRM);
       END get_employee_info; 

    Please go through the comments for a detailed explanation at each step

  7. Now we can get only the needed records from table_b in database_b by querying the above function as below in.............................. of course database_a
    select * from table(get_employee_info());

    So now we can easily join the both the above query and table_a and that would give us the required/expected result.

    SELECT t1.employee_id, t1.employee_name, t2.employee_email
      FROM table_a t1, TABLE (get_employee_info ()) t2
     WHERE t1.employee_id = t2.employee_id

Points of Interest

I know all these looks a bit cumbersome or heavy to maintain but if we can organize all these functions in a single package in two databases, that would give us a lot of performance to the application using these tables, Below are some sample timestamps which we in our team experienced (though table/database names are not real, all the times are facts which we experienced)

Time elapsed When used the query as mentioned in my introduction is 3Min 10 Sec, check the below explain plan


Time elapsed when used the query using DBMS_SQL is 900ms, check the below explain plan

<img src="Best_Time.png">

we can observe in the above explain plans that though the cost is less in both the cases, Bytes transferred is very high in the first case which was greatly reduced with out approach.


  • Version created on 4/1/2013.
  • Added images to show the performance on 4/2/2013


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Karthik Narahari
Software Developer (Senior) ADP
India India
No Biography provided

You may also be interested in...

Comments and Discussions

GeneralThanks for your great post Pin
Utkarsh D Shah25-Jun-13 19:38
memberUtkarsh D Shah25-Jun-13 19:38 
GeneralMy vote of 5 Pin
SRSHINDE8-Apr-13 0:12
memberSRSHINDE8-Apr-13 0:12 
GeneralRe: My vote of 5 Pin
Karthik Narahari8-Apr-13 21:13
memberKarthik Narahari8-Apr-13 21:13 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170713.1 | Last Updated 2 Apr 2013
Article Copyright 2013 by Karthik Narahari
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid