3

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

migrated from stackoverflow.com Apr 2 '13 at 9:21

This question came from our site for professional and enthusiast programmers.

  • 2
    You could see if the driving_site hint affects the plan; I'm not sure on insert... – Alex Poole Mar 27 '13 at 15:52
  • @AlexPoole:I think this will not make any difference ,fetching of data is done at local DB ,and inserting bulk data is done at remote DB ,the amount of data is the problem . – Gaurav Soni Mar 27 '13 at 15:58
  • @gaurav it could help if it worked since the plan looks like the hash join is done remotely! – Vincent Malgrat Mar 27 '13 at 16:05
  • I am curious if you see a difference by trying: Insert into MARS_SUPERVISORS_PUB@EDHPUB select * from ( select {...} from edhper a, edhper b where {...} ) – willOEM Mar 27 '13 at 16:07
  • @VincentMalgrat:Yes possible ,but one thing ,why there is a hash join with remote table ,does the select query is joining with remote table? – Gaurav Soni Mar 27 '13 at 16:10
2

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.

  • Yes, I tried the driving_site hint - didn't work. In fact, as far as I understand this hint does the opposite (when you're pulling data it joins everything on remote DB side), here I need to push data. – andreybavt Mar 27 '13 at 16:35
  • @andreybavt - it depends which table you give as an argument. If you did insert /*+ driving_site(MARS_SUPERVISORS_PUB) */ ... then yes, it would push it all to the remote side. If you gave it the local table (or one of the aliases in this case) then it might keep it local. That's if it does anything in this case, and if the aliases form the subquery are meaningful to the insert... – Alex Poole Mar 27 '13 at 16:56
  • 1
    As far as I can tell from some testing, driving_site is ignored by the insert anyway. MOS note 825677.1 suggests this too: 'a distributed DML statement must execute on the database where the DML target resides. The DRIVING_SITE hint cannot override this', as does not-a-bug 5517609. So, worth investigating, but doesn't work *8-) – Alex Poole Mar 27 '13 at 17:16
  • @andreybavt it seems that you will have to do some workaround to have the work done locally. A temporary table should work. – Vincent Malgrat Mar 28 '13 at 8:59
1

When sending data from local table to remote table across a link, the best results we have had is to save into a local temp table, then copy the local temp table to an image of it (another temp table) on the remote database, then run a script on the remote database that inserts/merges from the remote temp into the remote 'real' tables. Every time we tried accessing a 'real' table across the link we got performance hits.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy