OraFAQ Forum: SQL & PL/SQL » Materialized View - How to capture/convert MV log deltas to real rows

Home » SQL & PL/SQL » SQL & PL/SQL » Materialized View - How to capture/convert MV log deltas to real rows (Oracle 11.2.0.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Materialized View - How to capture/convert MV log deltas to real rows [message #588737]

Thu, 27 June 2013 16:29

lkngstr82is
Messages: 33
Registered: January 2010
Location: USA

Member

We have a requirement where we need to publish only the delta/changes made to an existing materialized view. This MV is fast refreshed over a db link. Since, MV will have all the data, we won't

1] Created MV Log


CREATE MATERIALIZED VIEW LOG ON SHRTS_FLNG_PRCSD
WITH PRIMARY KEY, SEQUENCE
INCLUDING NEW VALUES;

2] Create MV

CREATE MATERIALIZED VIEW MV_SHRTS_FLNG_PRCSD (ITRTN_NB,UNQ_ID,XCHNG_ORG_ID,FLNG_ST,MAX_PRCSD_ITRTN_NB,SBMTD_SCRTS_AM,SBMTD_PSTN_AM)
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
AS 
SELECT ITRTN_NB,
       UNQ_ID,
       XCHNG_ORG_ID,
       FLNG_ST,
       MAX_PRCSD_ITRTN_NB,
       SBMTD_SCRTS_AM,
       SBMTD_PSTN_AM
  FROM SHRTS_FLNG_PRCSD@FFS

3) Inserted records to source table

Insert into SHRTS_FLNG_PRCSD
   (ITRTN_NB, UNQ_ID, XCHNG_ORG_ID, FLNG_ST, MAX_PRCSD_ITRTN_NB, 
    SBMTD_SCRTS_AM, SBMTD_PSTN_AM)
 Values
   (12, '89951671-c1dc-44ea-89a1-4f3dc610dfbf  ', 1, 'Submitted', 1, 
    7674, 8870680842);
Insert into SHRTS_FLNG_PRCSD
   (ITRTN_NB, UNQ_ID, XCHNG_ORG_ID, FLNG_ST, MAX_PRCSD_ITRTN_NB, 
    SBMTD_SCRTS_AM, SBMTD_PSTN_AM)
 Values
   (71, '2d405b47-fb6f-4a4b-8cba-9b019537ce82  ', 1, 'Submitted', 6, 
    4061, 457259670);
Insert into SHRTS_FLNG_PRCSD
   (ITRTN_NB, UNQ_ID, XCHNG_ORG_ID, FLNG_ST, MAX_PRCSD_ITRTN_NB, 
    SBMTD_SCRTS_AM, SBMTD_PSTN_AM)
 Values
   (19, '{7CE3ECED-7CE6-42A5-9046-7ADA08B3C394}', 50106, 'Submitted', 0, 
    10, 1449132);

3) I can see deltas on MV logs.

UNQ_ID	ITRTN_NB	SEQUENCE$$	SNAPTIME$$	DMLTYPE$$	OLD_NEW$$	CHANGE_VECTOR$$	XID$$
{FDE35AAC-E749-4507-972F-F26F5982E046}	1	2	1/1/4000	U	U	2800	2.81485714140869E15
{FDE35AAC-E749-4507-972F-F26F5982E046}	1	3	1/1/4000	U	N	2800	2.81485714140869E15
{FDE35AAC-E749-4507-972F-F26F5982E046}	1	8	1/1/4000	D	O	0000	2.81491297598354E15

4) I have created a temp table to capture the diff between MV and newly inserted Deltas. I have added SEQ_NB column using Oracle sequence to capture the diff

CREATE TABLE MKT_CMPLC.TMP_SHRTS_FLNG_PRCSD
(
  ITRTN_NB            NUMBER(2)                 NOT NULL,
  UNQ_ID              CHAR(38 BYTE)             NOT NULL,
  XCHNG_ORG_ID        NUMBER(8)                 NOT NULL,
  FLNG_ST             VARCHAR2(9 BYTE),
  MAX_PRCSD_ITRTN_NB  NUMBER(2),
  SBMTD_SCRTS_AM      NUMBER(8),
  SBMTD_PSTN_AM       NUMBER(16),
  SEQ_NB              NUMBER
)

5) I have refreshed the MV

 exec dbms_mview.refresh('MV_SHRTS_FLNG_PRCSD');

6) In order to capture the new inserts, I am using following DML

INSERT INTO tmp_SHRTS_FLNG_PRCSD SELECT ITRTN_NB,
UNQ_ID,
XCHNG_ORG_ID,
FLNG_ST,
MAX_PRCSD_ITRTN_NB,
SBMTD_SCRTS_AM,
SBMTD_PSTN_AM,
MV_SHRTS_FLNG_PRCSD_SEQ.NEXTVAL  FROM MV_SHRTS_FLNG_PRCSD WHERE (UNQ_ID, ITRTN_NB, XCHNG_ORG_ID) NOT IN  (SELECT UNQ_ID, ITRTN_NB, XCHNG_ORG_ID FROM tmp_SHRTS_FLNG_PRCSD)

My question is - Are there any better methods to achieve this. We need only delta records. Is there any way to extract actual rows from MV logs.

Any thoughts, let me know

Thanks
Harshad

Report message to a moderator

Re: Materialized View - How to capture/convert MV log deltas to real rows [message #588750 is a reply to message #588737]

Fri, 28 June 2013 00:22

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You have all the necessary information in MVIEW LOG, so yes it is possible.

Regards
Michel

Report message to a moderator

Re: Materialized View - How to capture/convert MV log deltas to real rows [message #588808 is a reply to message #588750]

Fri, 28 June 2013 09:33

lkngstr82is
Messages: 33
Registered: January 2010
Location: USA

Member

Thanks for your feedback.

Can you please provide example where I can use MV log to update/refresh the temp table, which has same structure as source.

Report message to a moderator

Re: Materialized View - How to capture/convert MV log deltas to real rows [message #588817 is a reply to message #588808]

Fri, 28 June 2013 10:55

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Create a basic test case: a table, a mview log on it, make some DML on it, see the rows in the mview log, compare with the statements you executed, I don't see any difficulties to find the later from the former.
If you have any problem to do it, post what you have tried (including the test case) and explain where you are stuck, we will help you to go further.

Regards
Michel

Report message to a moderator

Re: Materialized View - How to capture/convert MV log deltas to real rows [message #588829 is a reply to message #588817]

Fri, 28 June 2013 12:53

lkngstr82is
Messages: 33
Registered: January 2010
Location: USA

Member

Are there any SQLs/script to reconstruct these DMLs.

Report message to a moderator

Re: Materialized View - How to capture/convert MV log deltas to real rows [message #588830 is a reply to message #588829]

Fri, 28 June 2013 12:57

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You can create one and post it here for future readers.
Note that if you prove us you made a REAL effort solving your problem and not waiting we do it for you, I'm ready to post you what you need but I don't want to do it for a lazy or a parasite.
So do what I said, post I what I said and then I'll give you what you need.

Regards
Michel

[Updated on: Fri, 28 June 2013 12:58]

Report message to a moderator

Re: Materialized View - How to capture/convert MV log deltas to real rows [message #588838 is a reply to message #588830]

Fri, 28 June 2013 14:27

lkngstr82is
Messages: 33
Registered: January 2010
Location: USA

Member

I can construct the INSERT statement using MV LOG, but it doesn't have values for all columns.


insert into MKT_CMPLC.SHRTS_FLNG_PRCSD    (ITRTN_NB, UNQ_ID, XCHNG_ORG_ID, FLNG_ST, MAX_PRCSD_ITRTN_NB,    SBMTD_SCRTS_AM, SBMTD_PSTN_AM) Values   (14, '89951671-c1dc-44ea-89a1-4f3dc610dfbf  ', 1, 'Submitted', 1,    7674, 8870680842);

UNQ_ID	ITRTN_NB	XCHNG_ORG_ID	SNAPTIME$$	DMLTYPE$$	OLD_NEW$$	CHANGE_VECTOR$$	XID$$
-------------------------------------------------------------------------------------------------------------------
"89951671-c1dc-44ea-89a1-4f3dc610dfbf"	14	1	1/1/4000	"I"	"N"	"FE"	2.81474977113453E15

Report message to a moderator

Re: Materialized View - How to capture/convert MV log deltas to real rows [message #588839 is a reply to message #588838]

Fri, 28 June 2013 14:48

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

How can I reproduce what you see?

Regards
Michel

Report message to a moderator

Re: Materialized View - How to capture/convert MV log deltas to real rows [message #588840 is a reply to message #588839]

Fri, 28 June 2013 15:07

lkngstr82is
Messages: 33
Registered: January 2010
Location: USA

Member

This is already describe in original post - 1]Create MV log
2] Create MV
3] Execute insert from last post

Report message to a moderator

Re: Materialized View - How to capture/convert MV log deltas to real rows [message #588843 is a reply to message #588840]

Fri, 28 June 2013 15:24

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Do you mean this:

SQL> CREATE MATERIALIZED VIEW LOG ON SHRTS_FLNG_PRCSD
  2  WITH PRIMARY KEY, SEQUENCE
  3  INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON SHRTS_FLNG_PRCSD
*
ERROR at line 1:
ORA-00942: table or view does not exist

Regards
Michel

Report message to a moderator