Home » Server Options » Replication » Materialized View log Purging and Use of sequence Id (merged 3) (Oracle 11g)
Materialized View log Purging and Use of sequence Id (merged 3) [message #606085] Sun, 19 January 2014 12:06 Go to next message
kgecdeep
Messages: 8
Registered: April 2012
Location: kolkata
Junior Member
Hi,

I have designed an ETL job where I am using the Materialized View log as my source for delta records extraction by using the DML_Type ( I, U, D) and sequence Id to track the latest changes.

I am capturing all the change records in a table by using the sequence number in the MV Log . Once the capture is done for the instance , I am tracking the maximum sequence ID and then the next instance will start from there. This is how I am using the sequencenId .

My concern is:

I have seen the sequence Id is always increasing , if that is the case my design will work. id it possible that the sequence Id will get reset at any stage , if yes how

Also My MV log is getting larger , I want to purge/archive the MV log in a regular frequency based on old sequence Ids that are already loaded and not required . How I can purge the MV log from below certain sequence Ids ?
Re: Materialized View log Purging and Use of sequence Id [message #606090 is a reply to message #606085] Sun, 19 January 2014 13:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you created you rmveiw log with SEQUENCE option then this sequence is retrieved from SYS.CDC_RSID_SEQ$ which is defined with increment by 1, order, no upper limit and no cycle option, so it can't decrease.

Re: Materialized View log Purging and Use of sequence Id [message #606162 is a reply to message #606090] Mon, 20 January 2014 10:59 Go to previous messageGo to next message
kgecdeep
Messages: 8
Registered: April 2012
Location: kolkata
Junior Member
Thanks . Please let me know if I can purge/archive the data from the MV log on monthly basis by using the sequence Id ? Lets say I want to purge the old data from MV log below certain sequence id as the old records are already processed and not required any more .. pls suggest
Re: Materialized View log Purging and Use of sequence Id [message #606164 is a reply to message #606162] Mon, 20 January 2014 11:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You don't use any Oracle feature with this mview log then you can delete the rows as you want.
If you want to do it in a month basis maybe it is better to use the SNAPTIME$$ column than the SEQUENCE$$ but it is up to you.

Re: Materialized View log Purging and Use of sequence Id [message #606179 is a reply to message #606164] Mon, 20 January 2014 22:21 Go to previous messageGo to next message
kgecdeep
Messages: 8
Registered: April 2012
Location: kolkata
Junior Member
Thanks Michel , Its really helpful . Thanks a lot
MV log purging or archiving [message #606487 is a reply to message #606085] Fri, 24 January 2014 10:39 Go to previous messageGo to next message
kgecdeep
Messages: 8
Registered: April 2012
Location: kolkata
Junior Member
I want to purge the MV log weekly basis or monthly basis and I have sequence Id column in the MV log. I am using the MV log as source for one of my ETL job.

I want to archive the MV log using the below query:

delete from Mlog$_EMP where sequence$$ < (max) sequence id that are already processed by ETL job. Is it ok if I use this type of direct delete statement using swquence$$ column in the where clause as I can track the records easily via this sequenceId which are no more required .

I have heard there is a oracle defined process to purge the MV log , procedure , if I ignore the same and use the above delete statement is it ok.
The MV log will be used by my ETL job only.
Re: MV log purging or archiving [message #606489 is a reply to message #606487] Fri, 24 January 2014 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Same answer as in your previous topic; why do you think it should be different? And why do you start a new topic for the same or very closely related question?

MVLog Purge [message #613932 is a reply to message #606085] Tue, 13 May 2014 11:02 Go to previous messageGo to next message
kgecdeep
Messages: 8
Registered: April 2012
Location: kolkata
Junior Member
Hi,

I am using MVLog table for one my ETL job to track the delta changes. The MV Log is having Sequence$$ which is a key field in my ETL job. The MVLog has become very big and I want to delete the old records from the MLOG below certan SEQUENCE$$ only ... but I heard that deleting below certain Sequence$$ will not help performance wise much since the blocks will be still there and the table size will not change much since we are deleting only records.

In that case my select from MVLog table will not improve since it will still access a very large table . Please share your thoughts or any option of purging below certain sequence$$ so that we can make the tabe size less
Re: MVLog Purge [message #614032 is a reply to message #613932] Wed, 14 May 2014 12:53 Go to previous messageGo to next message
kgecdeep
Messages: 8
Registered: April 2012
Location: kolkata
Junior Member
Please help on the above issue
Re: MVLog Purge [message #614033 is a reply to message #614032] Wed, 14 May 2014 13:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post actual SQL & EXPLAIN PLAN for what you report as being slow
Re: MVLog Purge [message #614035 is a reply to message #613932] Wed, 14 May 2014 15:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Continuing your previous topic(s). I merge with them.

Re: MVLog Purge [message #614041 is a reply to message #614035] Wed, 14 May 2014 23:31 Go to previous messageGo to next message
kgecdeep
Messages: 8
Registered: April 2012
Location: kolkata
Junior Member
I can track the sql directly since it is maintained in ETL job dataflow. I saw there is no index on sequence$$ , so everytime the select query is ding a full table scan and even i delete the old records from MV Log table below certain sequence$$ and makes the MVLog smaller in terms of records , still the table size is huge and the select query does a full table scan , which I think does nt improve performance even after deleting records. Is there any way I can use the Purge procedure of MVLog using a sequence$$ parameter so that procedure will do the needful and it may help?
Re: MVLog Purge [message #614042 is a reply to message #614041] Wed, 14 May 2014 23:59 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I saw there is no index on sequence$$


If you need it then create it.

Previous Topic: How to skip comments in logical standby?
Next Topic: Database refresh using different tools
Goto Forum:
  


Current Time: Fri Mar 29 00:33:09 CDT 2024