Select and Insert across dblink

Question

I am having a bit of trouble with a select into insert across a dblink in oracle 10. I am using the following statement:

INSERT INTO LOCAL.TABLE_1 ( COL1, COL2) 
SELECT  COL1, COL2
FROM REMOTE.TABLE1@dblink s
WHERE COL1 IN ( SELECT COL1 FROM WORKING_TABLE)

When I run the statement the following is what gets run against the remote server on the DB Link:

SELECT /*+ OPAQUE_TRANSFORM */ "COL1", "COL2"
FROM "REMOTE"."TABLE1" "S"

If I run the select only and do not do the insert into the following is run:

SELECT /*+ */ "A1"."COL1"
     , "A1"."COL2"
  FROM "REMOTE"."TABLE1" "A1"
 WHERE "A1"."COL1" =
   ANY ( SELECT "A2"."COL1"
       FROM "LOCAL"."TABLE1"@! "A2")

The issue is in the insert case the enitre table is being pulled across the dblink and then limited localy which takes a fair bit of time given the table size. Is there any reason adding the insert would change the behavior in this manner?

Answer 1

You may want to use the driving_site hint. There is a good explanation here: http://www.dba-oracle.com/t_sql_dblink_performance.htm