Re: Merge accross a DB Link

Steve J wrote:

> 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...

Merge did not exist in 8i.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)