Home » SQL & PL/SQL » SQL & PL/SQL » Materialized View - How to capture/convert MV log deltas to real rows (Oracle 11.2.0.3)
Materialized View - How to capture/convert MV log deltas to real rows [message #588737] Thu, 27 June 2013 16:29 Go to next message
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

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 Go to previous messageGo to next message
Michel Cadot
Messages: 68006
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
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 Go to previous messageGo to next message
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.

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 Go to previous messageGo to next message
Michel Cadot
Messages: 68006
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
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 Go to previous messageGo to next message
lkngstr82is
Messages: 33
Registered: January 2010
Location: USA
Member
Are there any SQLs/script to reconstruct these DMLs.
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68006
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 Go to previous messageGo to next message
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




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 Go to previous messageGo to next message
Michel Cadot
Messages: 68006
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How can I reproduce what you see?

Regards
Michel
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68006
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
Re: Materialized View - How to capture/convert MV log deltas to real rows [message #588844 is a reply to message #588843] Fri, 28 June 2013 15:28 Go to previous messageGo to next message
lkngstr82is
Messages: 33
Registered: January 2010
Location: USA
Member
Here are correct steps
1.Create base table in step 4]
2. Create MV log
3. Create MV
4. Insert


Re: Materialized View - How to capture/convert MV log deltas to real rows [message #589036 is a reply to message #588844] Mon, 01 July 2013 10:17 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Why not simply do a refresh on commit. Your MV will always be up to date and correct.
Previous Topic: Oracle equivalent to TSQL SELECT INTO()
Next Topic: trying to get difference in values from two subqueries
Goto Forum:
  


Current Time: Sun Oct 24 15:06:09 CDT 2021