I'm trying to do an insert as select to a table via dblink. The query looks like this:
Insert into MARS_SUPERVISORS_PUB@EDHPUB
select
{...}
from edhper a, edhper b
where {...
a.idn not in (select s.tarcfsra idn from cfsra s where s.srtcfsra = 'MAPSSPA' and s.stscfsra = 'A')
...}
The select itself works quite fast here is its execution plan shows all the joins, but the insert takes very very long and the plan consists only of "REMOTE" steps :
INSERT STATEMENT REMOTE, GOAL = ALL_ROWS
LOAD TABLE CONVENTIONAL
SORT AGGREGATE
FILTER
REMOTE
REMOTE
FILTER
HASH JOIN
REMOTE
REMOTE
REMOTE
I wonder if Oracle tries to push both tables a and b through the database link and then joins it there.
Can I instruct the optimizer to do a select inside the current DB and pass only the results to @EDHPUB?
P.S/
If I create a test table locally, insert this select there and then do an insert like:
Insert into MARS_SUPERVISORS_PUB@EDHPUB
select * from TEMP_TBL
it works fast. For me it looks like an evidence that in my previous example Oracle really tries to push unjoined tables thtough dblink.
UPDATE/
I figured out that the insert takes long because of a a.idn not in ...
condition.
without it the plan looks like this:
INSERT STATEMENT REMOTE, GOAL = ALL_ROWS
LOAD TABLE CONVENTIONAL
SORT AGGREGATE
FILTER
REMOTE
REMOTE
HASH JOIN
REMOTE
REMOTE
Best Answer
The
driving_site
hint, as suggested by Alex Poole would be an interesting solution to consider/investigate. I'm not sure how/if it works for remote inserts though.If the hint doesn't work, you can use a local temporary table. Insert the data locally into your temporary table. Then
INSERT INTO tab@remote FROM temp
should perform well.