Re: Merge accross a DB Link

Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1079409723.658672_at_yasure>...
> 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.

I was thinking that but thought I'd get a syntax error which I don't.

Thanks... Received on Sun Mar 28 2004 - 09:31:24 CST