Remote Join Optimization Example

The following is an example of the remote join optimization capability of the Oracle database.

Note:

The explain plan that uses tables from a non-Oracle system can differ from similar statements with local or remote Oracle table scans. This is because of the limitation on the statistics available to Oracle for non-Oracle tables. Most importantly, column selectivity is not available for non-unique indexes of non-Oracle tables. Because of the limitation of the statistics available, the following example is not necessarily what you encounter when doing remote joins and is intended for illustration only.

Consider the following example:

EXPLAIN PLAN FOR
SELECT e.ename, d.dname, f.ename, f.deptno FROM
   dept d,
   emp@remote_db e,
   emp@remote_db f
 WHERE e.mgr = f.empno
  AND e.deptno = d.deptno 
  AND e.empno = f.empno;
  
@utlxpls

You should see output similar to the following:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

---------------------------------------------------------------------------
| Id    | Operation                           | Name  | Rows  | Bytes  | Cost
| Inst  |IN-OUT|
---------------------------------------------------------------------------
|   0   | SELECT STATEMENT   |                |  2000   |   197K|   205 |
|*  1   | HASH JOIN          |                |  2000   |   197K|
205 |
|   2   | TABLE ACCESS FULL  | DEPT           |  21     |   462 |     2 |
|*  3   | HASH JOIN          |                |  2000   |   154K|
201 |
|   4   | REMOTE             |                |  2000   | 66000 
|    52 |
|   5   | REMOTE             |                |  2000   | 92000
|    52 |
---------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Query Block Name / Hint Alias (identified by operation id):
-----------------------------------------------------------

   1 - sel$1 / D
   2 - sel$1 / D
   3 - sel$1 / F
   4 - sel$1 / F
   5 - sel$1 / E

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

---------------------------------------------------

   1 - access("E"."DEPTNO"="D"."DEPTNO")
   3 - access("E"."MGR"="F"."EMPNO" AND "E"."EMPNO"="F"."EMPNO")

Issue the following statement:

SET long 300
SELECT other FROM plan_table WHERE operation = 'REMOTE';

You should see output similar to the following:

OTHER
--------------------------------------------------------------------------------

SELECT "EMPNO","ENAME","DEPTNO" FROM "EMP"
SELECT "EMPNO","ENAME","MGR","DEPTNO" FROM "EMP"
SELECT "EMPNO","ENAME","DEPTNO" FROM "EMP"
SELECT "EMPNO","ENAME","MGR","DEPTNO" FROM "EMP"