Limitations of DRIVING_SITE Hint

Applies to:

Oracle Server – Enterprise Edition – Version: 9.2.0.1 to 11.2.0.2
This problem can occur on any platform.

Symptoms

For optimization of distributed queries Oracle provides the DRIVING_SITE hint.
The Oracle? Database Administrator’s Guide 11g Release 1 (11.1)
(http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/ds_appdev004.htm#sthref3180)
describes it as follows:

Using the DRIVING_SITE Hint

The DRIVING_SITE hint lets you specify the site where the query execution is performed. It is best to let cost-based optimization determine where the execution should be performed, but if you prefer to override the optimizer, you can specify the execution site manually.

Following is an example of a SELECT statement with a DRIVING_SITE hint:

SELECT /*+ DRIVING_SITE(dept)*/ * FROM emp, dept@remote.com dept
WHERE emp.deptno = dept.deptno;

So when you’re using this hint you will see in the query execution plan REMOTE in the corresponding operation section, here’s an example for a SELECT. Note that we have the table example_data on both databases, i.e. we have a local table example_data and a remote table example_data. The SQL is launched from the local side:

select /*+ DRIVING_SITE(remote_example_data) */ *
from example_data local_example_data, example_data@v10203 remote_example_data
where local_example_data.x = remote_example_data.x;

—————————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
—————————————————————————————————
| 0 | SELECT STATEMENT | | 164 | 4264 | 5 (20)| 00:00:01 | | |
|* 1 | HASH JOIN | | 164 | 4264 | 5 (20)| 00:00:01 | | |
| 2 | TABLE ACCESS FULL| EXAMPLE_DATA | 2 | 26 | 2 (0)| 00:00:01 | | |
| 3 | REMOTE | EXAMPLE_DATA | 82 | 1066 | 2 (0)| 00:00:01 | V10203 | R->S |
—————————————————————————————————

Predicate Information (identified by operation id):
—————————————————

1 – access(“LOCAL_EXAMPLE_DATA”.”X”=”REMOTE_EXAMPLE_DATA”.”X”)

Remote SQL Information (identified by operation id):
—————————————————-

3 – SELECT “X” FROM “EXAMPLE_DATA” “REMOTE_EXAMPLE_DATA” (accessing ‘V10203’ )

So this works as expected as is also clearly visible in the “Remote SQL Information” section.

You might wonder what happens when you do this in a CREATE TABLE AS SELECT, i.e.:

create table remote_new_data (x,y) — need to give unique names to columns
as
select /*+ DRIVING_SITE(remote_example_data) */ *
from example_data local_example_data, example_data@v10203 remote_example_data
where local_example_data.x = remote_example_data.x;

When you check the execution plan, you’ll notice hint seems to work for the query itself, but the newly
created table is created locally. That also doesn’t change if you include the DRIVING_SITE hint directly after the CREATE TABLE portion, you’ll get an execution plan like:

———————————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
———————————————————————————————————-
| 0 | CREATE TABLE STATEMENT | | 8 | 48 | 6 (17)| 00:00:01 | | |
| 1 | LOAD AS SELECT | REMOTE_NEW_DATA | | | | | | |
|* 2| HASH JOIN | | 8 | 48 | 5 (20)| 00:00:01 | | |
| 3 | REMOTE | EXAMPLE_DATA | 2 | 6 | 2 (0)| 00:00:01 | V10203 |R->S |
| 4 | TABLE ACCESS FULL | EXAMPLE_DATA | 8 | 24 | 2 (0)| 00:00:01 | | |
———————————————————————————————————-

PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————————————————————–

Predicate Information (identified by operation id):
—————————————————

2 – access(“LOCAL_EXAMPLE_DATA”.”X”=”REMOTE_EXAMPLE_DATA”.”X”)

Remote SQL Information (identified by operation id):
—————————————————-

3 – SELECT /*+ */ “X” FROM “EXAMPLE_DATA” “REMOTE_EXAMPLE_DATA” (accessing ‘V10203’ )

Cause

What happened? That’s actually expected behaviour, a distributed DML statement must execute on the database where the DML target resides. The DRIVING_SITE hint cannot override this. DRIVING_SITE hint means that entire cursor (not a subquey) is supposed to be mapped remotely. That also means CREATE TABLE cannot be executed remotely (which is also the reason why you get ORA-2021 when you try to accomplish this with an Create Table table_name@remote_database).

Solution

So keep in mind when using the DRIVING_SITE hint this is merely for query optimization and not intended for DML or DDL.