Creating a Fast Refreshable, ON COMMIT Materialized View

Although most people normally associate materialized views with OLAP environments, it has been my experience there are many uses for them in OLTP systems as well. Oracle 10g has brought with it many enhancements that aim to assist users in creating fast refreshable, query rewrite compatible materialized views; however, a basic understanding of the fast refreshable materialized view and query rewrite concepts should be prologue.

I have constructed a simple example that exhibits how to create a basic materialized view that is fast refreshable ON COMMIT. Because of this, queries against the materialized view's source tables are rewritten effectively. Note in the example how the materialized view stays up to date as DML is performed on its source tables. Additionally, note how queries are rewritten to take advantage of the preaggregated data. At the end of the script, I alter the materialized view to be fast refreshable ON DEMAND; therefore, subsequent DMLs disable the query rewrite due to the ENFORCED query_rewrite_integrity setting. Be aware that although this example works in both 9i and 10g versions, that only the 10g version will show the rewrite operation in the output of the EXPLAIN PLAN - this capability is new in 10g. (Note: Right-click 'Save Target As'):
Sample Script | Script Log

Labels: Data Warehousing