possible to use a materialized view log on the snapshot database?

[CanNeverThinkOfaName]

hi

I have a problem with my materialized view on my replicated database- its taking about 20 hours to update every day!

I've noticed it does a COMPLETE refresh every day and want it to do a fast refresh but I need to implement a log for this I've read....the DBAs for the live master database say no to the log- is there any way a fast refresh would work if the log lived in my replicated database?

If not, then what other options could I explore please? -besides appending yesterday's data from the live database into a table on my replicated database (but id have to check for any updates/deletes on old data on the live database too)?

Thanks in advance for your help

 

[Dagon]

I don't see how. The MV log is needed to record changes on the source table. There is no way to know what's changed on the source table other than by creating a log on the source table. I can entirely understand why the owners of the master wouldn't want that. MV logs can slow down DML a lot and have a tendency to become very fragmented.

The only other approach I could think of would be to change the target to a regular table and update it from the source using something like a MERGE statement. Or, if the number of updates on the source is very small, you could write a query to compare the two tables and read the differences back using a cursor, which you then apply as updates/inserts.

If you know there are sections of the source table which never change (e.g. data older than a certain date never changes), then you might be able to put restrictions on the queries which reduce the amount of data which needs to be processed. You might even be able to partition the target table into active and non-active parts, so that only the active partition needs to be compared to the source.

 

[CanNeverThinkOfaName]

Thanks Dagon...thought that might be the case alright!

I'll have to go with a nightly dump of data in my database from live it seems!

 

[Turkbear]

Hi,
look more closely at Dagon's idea for a MERGE process each night to a 'real' table in your database instead of the materialized view...Combined with a well-crafted query, it certainly would not take as long to run and would result in a fully-consistant replicant ( at least as up to date as your MV - Run the MERGE more often and keep even more current).





To Paraphrase:"The Help you get is proportional to the Help you give.."