DRIVING_SITE SQL Hint

We all are know about Oracle Data Base Link and I called it dblink in short throughout this article. Usisg dblink you can easily access remote Database Object. Now Please think the following situation:

Situation:

Suppose you have 2 tables: 1. table1 in local DB 2. table2 in remote DB.The table1 size is 10 MB and table2 size is 100 MB. We need to join those two tables and we access only in local DB.

If you now think about performance, it is important to where the sql query executed and the join perform. For above situation, it is better to bring table1 from local DB to Remote DB and execute the sql in remote server and finally bring back the result in local DB.

Lets do the job

The DRIVING_SITE hint forces query execution to be done at a different site than that selected by Oracle. This hint can be used with either rule-based or cost-based optimization. The syntax of this hint is:

/*+DRIVING_SITE(table)*/

where table is the name or alias for the table at which site the execution should take place.

Example:

SELECT /*+DRIVING_SITE(table2)*/ * FROM tabl1, table2@remote_db
WHERE table1.DEPTNO = table2.DEPTNO;

If this query is executed without the hint, rows from table2 will be sent to the local site and the join will be executed there. With the hint, the rows from table1 will be sent to the remote site and the query will be executed there, returning the result to the local DB.