| Materialized View fast refresh without MV logs [message #196593] |
Fri, 06 October 2006 01:59  |
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 #197846 is a reply to message #197807] |
Thu, 12 October 2006 20:44  |
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 |
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
