Driving_site hint can be used to advise Oracle to process a join at a remote site. (Incidentally, this is the only hint other than RULE that can be used for RBO.) But if the intended query involves the local site and only one remote site, creating a view at the remote site as select from the local table and the remote table achieves the same goal, as long as the FROM clause of the query only contains the remote view and optionally other remote tables, but nothing local. In that case, the following rule applies (from "Oracle9i Database Performance Tuning Guide and Reference", Chapter 2 "Optimizer Operations"): * If all the tables accessed by a SQL statement are located together on the same remote database, then Oracle sends the SQL statement to that remote database. The remote Oracle instance executes the statement and sends only the results back to the local database. Here's my test. Local DB RCAT (9.2.0.1.0) has tables employees and departments (CTAS from scott.emp and scott.dept). Remote DB ORCL (10.1.0.2.0) also has them. Table stats are gathered on all four. Links are created in both DBs pointing to each other. Queries are run after set autotrace traceonly All queries are run twice and the second one is shown here to avoid unnecessary physical read and redo. Inline comments are in square brackets. Test 1: In RCAT, [Interestingly, select * is OK in spite of duplicated column names, deptno] SQL> select * from employees, departments@orcl where employees.deptno = departments.deptno; 14 rows selected. Execution Plan [Note on line 0, there's no plan_table option for this SELECT. So data in departments are sent to local RCAT, consistent with last line SERIAL_FROM_REMOTE..."DEPARTMENTS"] ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=14 Bytes=798) 1 0 MERGE JOIN (Cost=8 Card=14 Bytes=798) 2 1 SORT (JOIN) (Cost=5 Card=4 Bytes=80) 3 2 REMOTE* (Cost=3 Card=4 Bytes=80) ORCL.US.ORACLE.COM 4 1 SORT (JOIN) (Cost=4 Card=14 Bytes=518) 5 4 TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=2 Card=14 Bytes=518) 3 SERIAL_FROM_REMOTE SELECT "DEPTNO","DNAME","LOC" FROM "DEPARTMENTS" "DEPARTMENTS" Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 1500 bytes sent via SQL*Net to client 499 bytes received via SQL*Net from client ... Test 2: SQL> select /*+ driving_site(departments) */ * from employees, departments@orcl where employees.deptno = departments.deptno; 14 rows selected. Execution Plan [Note REMOTE option for the SELECT. So data in employees are sent to ORCL and join there. Also, the plan changes from merge join to hash join and optimizer goal changes from CHOOSE to ALL_ROWS.] ---------------------------------------------------------- 0 SELECT STATEMENT (REMOTE) Optimizer=ALL_ROWS (Cost=6 Card=14 Bytes=798) 1 0 HASH JOIN (Cost=6 Card=14 Bytes=798) 2 1 TABLE ACCESS (FULL) OF 'DEPARTMENTS' (TABLE) (Cost=3 Card=4 Bytes=80) ORCL 3 1 REMOTE* (Cost=2 Card=14 Bytes=518) ! 3 SERIAL_FROM_REMOTE SELECT "EMPNO","ENAME","JOB","MGR","HIREDATE ","SAL","COMM","DEPTNO" FROM "EMPLOY Statistics ---------------------------------------------------------- 6 recursive calls [6 more recursive calls] 0 db block gets 4 consistent gets [1 more here] 0 physical reads 0 redo size 1710 bytes sent via SQL*Net to client [210 bytes are sent] 499 bytes received via SQL*Net from client ... Test 3: Still in RCAT. This test is not needed for our study but shown here for completeness. [Have to list columns in SELECT to avoid ORA-957 "duplicate column name"] SQL> create view v as select empno, ename, job, mgr, hiredate, sal, comm, employees.deptno from employees, departments@orcl where employees.deptno = departments.deptno; View created. SQL> select * from v; 14 rows selected. Execution Plan [Understandably, no REMOTE option. The same output as running the query that defines the view.] ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=14 Bytes=700) ... Test 4: In remote database ORCL, create view v as shown above. Then back to RCAT, we run a query against v@orcl *only*, *without* the driving_site hint, SQL> select * from v@orcl; 14 rows selected. Execution Plan [Note the REMOTE option and 'SERIAL_FROM_REMOTE...FROM "EMPLOY'. So this achieves the same as the driving_site hint (see Test 1).] ---------------------------------------------------------- 0 SELECT STATEMENT (REMOTE) Optimizer=ALL_ROWS (Cost=6 Card=14 Bytes=560) 1 0 HASH JOIN (Cost=6 Card=14 Bytes=560) 2 1 TABLE ACCESS (FULL) OF 'DEPARTMENTS' (TABLE) (Cost=3 Card=4 Bytes=12) ORCL 3 1 REMOTE* (Cost=2 Card=14 Bytes=518) RCAT 3 SERIAL_FROM_REMOTE SELECT "EMPNO","ENAME","JOB","MGR","HIREDATE ","SAL","COMM","DEPTNO" FROM "EMPLOY Statistics [Interestingly, recursive calls and consistent gets become 0.] ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 1307 bytes sent via SQL*Net to client 499 bytes received via SQL*Net from client ... Test 5: However, when there's a mixture of remote and local tables/views, REMOTE option disappears again (unless driving_site hint is added). SQL> select * from v@orcl, dual; 14 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=71 Card=16288 Bytes=358336) ... SQL> select /*+ driving_site(v) */ * from v@orcl, dual; 14 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT (REMOTE) Optimizer=ALL_ROWS (Cost=74 Card=76011 Bytes=3192462) ... [Update 2009-12] Test in 10.2.0.1 for both DBs: I have a local table called LOCAL_TAB and a remote table in database oracd1 called REMOTE_TAB accessed through DB link oracd1. (1) Guarantee join on the local side: SQL> set autotrace on explain SQL> select /*+ driving_site(lc) */ * from local_tab lc, remote_tab@oracd1 rm where lc.x = rm.x; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 2216230941 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT| ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10500 | 225K| 24 (9)| 00:00:01 | | | <- top SQL is local |* 1 | HASH JOIN | | 10500 | 225K| 24 (9)| 00:00:01 | | | | 2 | REMOTE | REMOTE_TAB | 10000 | 136K| 11 (0)| 00:00:01 | ORACD1 | R->S | <- remote_tab has remote operation | 3 | TABLE ACCESS FULL| LOCAL_TAB | 21000 | 164K| 11 (0)| 00:00:01 | | | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("LC"."X"="RM"."X") Remote SQL Information (identified by operation id): ---------------------------------------------------- 2 - SELECT "X","Y" FROM "REMOTE_TAB" "RM" (accessing 'ORACD1' ) (2) Guarantee join on the remote side: SQL> select /*+ driving_site(rm) */ * from local_tab lc, remote_tab@oracd1 rm where lc.x = rm.x; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 1266698748 ----------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT| ----------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT REMOTE| | 10500 | 246K| 23 (5)| 00:00:01 | | | <- top SQL is remote |* 1 | HASH JOIN | | 10500 | 246K| 23 (5)| 00:00:01 | | | | 2 | TABLE ACCESS FULL | REMOTE_TAB | 10000 | 136K| 11 (0)| 00:00:01 | ORACD1 | | | 3 | REMOTE | LOCAL_TAB | 21000 | 205K| 11 (0)| 00:00:01 | ! | R->S | <- local_tab has remote operation ----------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A2"."X"="A1"."X") Remote SQL Information (identified by operation id): ---------------------------------------------------- 3 - SELECT "X" FROM "LOCAL_TAB" "A2" (accessing '!' ) Note ----- - fully remote statement