Materialized View Review

I decided to brush up on materialized views this week. It is easy to gloss over all the different options available during creation. I found that the real learning happens when you attempt to actually create some of these views. Let me share some insights.


You can specify FOR UPDATE when creating the materialized view. That means you can issue an UPDATE statement on the materialized view. Not sure why you would want to do that. Those changes only get made to the materialized view. They do not propagate back to the source database tables. The next refresh will wipe out your updates.


You also can specify a REFRESH FAST option during creation. This means that only the source records that changed will cause an update in your materialized view. This might provide a great performance improvement. However you must first create a materialized view log for your source table before creating this type of performance view.


Finally there are the START WITH and NEXT options. These tell Oracle when to do the first and subsequent automatic refreshes of your materialized view. I offer this hint: The changes to the source tables need to be committed before they will propagate down into your materialized view.


Hopefully these hints can clear up some misconceptions and save you some time when dealing with your own materialized views.