Merge accross a DB Link

From: Steve J <sejohnson_25_at_yahoo.com>
Date: 15 Mar 2004 16:55:14 -0800
Message-ID: <f35c8b00.0403151655.25c84dac@posting.google.com>

I'm trying to run a merge process where the source is a view which resides on Oracle 8.1.7.3. The target database and the database where I'm initiating the query is 9.2.0.2.

The source table has about 11 million rows in it and I would like to refresh our table daily/weekly with a merge script.

There are a couple of hundred columns in the table but here's the merge script condensed:

ALTER SESSION ENABLE PARALLEL DML; MERGE INTO S_EVT_ACT TAR
USING ( SELECT /*+ parallel(a,4) */
A.ACCNT_OBJCTV_ID
, A.ACCNT_SRC_ID
, A.ACD_CALL_DURATION
, A.ACD_DISC_FLG
, A.ACD_INBOUND_FLG
, A.ROW_ID

FROM S_EVT_ACT_at_NGDPRD01.NGD.COM A
WHERE LAST_UPD BETWEEN NEW_TIME(TRUNC(SYSDATE - &1), 'EST', 'GMT') AND NEW_TIME(TRUNC(SYSDATE), 'EST', 'GMT')) SRC ON ( TAR.ROW_ID = SRC.ROW_ID )
WHEN MATCHED THEN
UPDATE SET
TAR.ACCNT_OBJCTV_ID = SRC.ACCNT_OBJCTV_ID
, TAR.ACCNT_SRC_ID = SRC.ACCNT_SRC_ID
, TAR.ACD_CALL_DURATION = SRC.ACD_CALL_DURATION
, TAR.ACD_DISC_FLG = SRC.ACD_DISC_FLG

WHEN NOT MATCHED THEN
INSERT

( TAR.ACCNT_OBJCTV_ID 


, TAR.ACCNT_SRC_ID 

, TAR.ACD_CALL_DURATION 

, TAR.ACD_DISC_FLG 

, TAR.ACD_INBOUND_FLG
, TAR.ROW_ID

)
VALUES

( SRC.ACCNT_OBJCTV_ID 


, SRC.ACCNT_SRC_ID 

, SRC.ACD_CALL_DURATION 

, SRC.ACD_DISC_FLG 

, SRC.ACD_INBOUND_FLG
, SRC.ROW_ID

)
/

The Last_Upd column is indexed but the plan I get is:

QUERY_PLAN

---

MERGE STATEMENT Cost = 1007676088024

2.1 MERGE S_EVT_ACT 
3.1 VIEW 
4.1 FILTER 
5.1 HASH JOIN OUTER 
6.1 FILTER 
7.1 REMOTE 
6.2 TABLE ACCESS FULL S_EVT_ACT 

The source tables are Siebel tables and cannot be analyzed.

I understand that the merge process works by outer joining to the target table but thought it would start by doing an index range scan using the Last_Update index.

When I run an explain plan on just the SELECT statement it uses the index like I thought it would.

Are there any issues using merge between 8.1.7 & 9.2 tables, should I switch to doing insert and update statements or is there something else I can take a look at to speed this up?

Any help would be appreciated.

Thanks... Received on Mon Mar 15 2004 - 18:55:14 CST