Home » Other » General » Materialized View fast refresh without MV logs
Materialized View fast refresh without MV logs [message #196593] Fri, 06 October 2006 01:59 Go to next message
jurij_lopotun
Messages: 2
Registered: October 2006
Location: Ukraine, Lviv
Junior Member
Hi

I have materialized view with fast refresh enabled based on partition change tracking functionality (without materialized view logs). Such "fast refresh" must cause "full refresh" of only those table partitions, which were changed since last mv refresh:
http://www.oracle.com/technology/pub/articles/10gdba/nanda_10gr2dba_part4.html

Although if I execute fast refresh procedure 1-2 days after the last refresh I sometimes receive error that "table … does not have a materialized view log", so I need to execute full mv refresh procedure.
I tried to find some information about the reasons which may cause impossibility of such fast refresh, but unfortunatly havn't found anything.
So, could you please tell me in what cases such fast refresh can not be processed or guide me to some documentation.

Thanks in advance.
Re: Materialized View fast refresh without MV logs [message #197807 is a reply to message #196593] Thu, 12 October 2006 15:34 Go to previous messageGo to next message
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
Senior Member
The Datawarehousing Guide has a very detailed description of refreshing MV's:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96520/mv.htm#33766

Maybe that will help?

Regards,
Sabine
Re: Materialized View fast refresh without MV logs [message #197846 is a reply to message #197807] Thu, 12 October 2006 20:44 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I know one thing for sure: one of us is wrong about Partition Change Tracking.

I thought (I'm not saying I'm right, this is just what I think) that PCT refresh only worked on partitions of the source table that have been dropped, truncated, created, or exchanged, ie DDL statements.

If you use INSERT, UPDATE, or DELETE, then these can only be captured by MV Logs. If you use one of these statements to modify a table and then hope to full-refresh just the affected partition, then I don't think that works.

Like I said, I could easily be wrong.

Easy way to check. Try full-refreshing the MV, INSERT a row into the source table, then DBMS_MVIEW.REFRESH('MV_NAME', 'P') and see if it works.

Ross Leishman
Re: Materialized View fast refresh without MV logs [message #198331 is a reply to message #196593] Mon, 16 October 2006 11:23 Go to previous messageGo to next message
jurij_lopotun
Messages: 2
Registered: October 2006
Location: Ukraine, Lviv
Junior Member
Thank you for your help.
Finally I've found the reason: the MV I've created was based on two tables, only one of which was partitioned (second table was a dimension of the first). So as soon as changes were made on non-partitioned table MV PCT refresh became impossible.
Re: Materialized View fast refresh without MV logs [message #198373 is a reply to message #198331] Mon, 16 October 2006 21:16 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Cool. It looks like I was wrong about PCT. I couldn't find any explicit statement in the doco, but the examples shown seem to indicate that DML operations can be PCT refreshed.

Ross Leishman
Previous Topic: Trial Oracle software?
Next Topic: Entity Relationship Models
Goto Forum:
  


Current Time: Sat Dec 14 17:15:38 CST 2024