Select and Insert across dblink
- by Domtar
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?
