|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
Materialized views in Oracle
A materialized view is a stored summary containing precomputes results (originating from an SQL select statement).
As the data is precomputed, materialized views allow for (seemingly) faster dataware query answers
Types of materialized views
There are three types of materialized views:
Read only materialized views
Updateable materialized views
Writeable materialized views
They are created with the for update clause during creation without then adding the materialized view to a materialized view group. In such a case, the materialized view is updatable, but the changes are lost when the materialized view refreshes.
Writeable materialized views require the advnced replication option to be installed.
... yet to be finished ..
The query rewrite facility is totally transparent to an application which needs not be aware of the existance of the underlying materialized view.
Refreshing a materialized view
Refreshing a materialized view synchronizes is with its master table.
Oracle performs the following operations when refreshing a materialized view. In the case of a complete refresh (using dbms_mview.refresh)
In the case of a fast refresh, the steps are:
If a materialized view is being refreshed can be checked by querying the type of v$lock: if the type is JI a refresh is being performed.
The following query checks for this:
select o.owner "Owner", o.object_name "Mat View", username "Username", s.sid "Sid" from v$lock l, dba_objects o, v$session s where o.object_id = l.id1 and l.type ='JI' and l.lmode = 6 and s.sid = l.sid and o.object_type = 'TABLE'
Errors during the automatic refresh of materialized views
If an error occurs during the automatic refresh of a materialized view, an error message is written into the alert.log.
Thanks to Wm. Scott Lewis, Bhavani Shankar and Jim Kotan who each pointed out an error on this page.