In this blog’s Oracle Business Intelligence category I made introduction before to both Parallel Execution and Partitioning two epical and maybe most used two options of Oracle on BIS environments.
Using Materialized Views is a critical performance argument for data warehousing – decision support – reporting needs, they are also widely used to replicate and distribute data. By calculating the answers to the really hard questions and also query rewrite feature, we greatly reduce the load on the system and these pre-computed summaries and would typically be very small compared to the original source data. Query rewrite is a mechanism where Oracle or applications from the end user or database transparently improve query response time, by automatically rewriting the SQL query to use the materialized view instead of accessing the original tables –
Code Listing 121 – Materialized View Basics Demo
A materialized view can be partitioned, indexed, large materialized views in can be created in parallel and you may use Materialized View Logs for incremental refreshes. Also they are very similar to indexes in several ways –
* They consume storage space,
* They must be refreshed when the data in their master tables changes,
* They improve the performance of SQL execution when they are used for query rewrites,
* Their existence is transparent to SQL applications and users,
* With write operations they have performance impact.
Unlike indexes, materialized views can be accessed directly using a SELECT statement. Depending on the types of refresh that are required, they can also be accessed directly in an INSERT, UPDATE, or DELETE statement.
Managing Materialized Views after 10g is much more easier, if you are interested with dbms_advisor.tune_mview – dbms_advisor.create_file – select /*+ REWRITE_OR_ERROR */ .. stuff please check this Arup Nanda‘s article; Oracle Database 10g: The Top 20 Features for DBAs by Arup Nanda – Week 12 – Materialized Views
Also this week in the group oracle-l, which I mentioned earlier in one of my posts, “Materialized View Refresh method has been changed from 9i to 10g” was a hot topic. As a summary; prior to 9i for complete refreshes Oracle did truncate mv and insert /*+ append */ where as after 10g it does delete and normal insert causing performance overhead. This was also discussed on this AskTom thread. As Jared Still mentioned on one of his follow ups, by changing the refresh method to set atomic_refresh = false, the truncate/append behavior can be restored;
begin
— dbms_mview.refresh(‘MVTEST_MV’,method => ‘C’);
— use this with 10g to return to truncate/append behavior
dbms_mview.refresh(‘MVTEST_MV’,method => ‘C’, atomic_refresh=>false);
end;
/
ps: to whom may be interested, as I promised earlier I published my two days Optimizing for Performance seminar by Joze Senegacnik seminar notes.
Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Refences Used :
http://www.akadia.com/services/ora_materialized_views.html
Oracle® Database Performance Tuning Guide 10g Release 2 (10.2) – DBMS_ADVISOR.TUNE_MVIEW Procedure
http://psoug.org/reference/dbms_advisor.html
Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog 