Effective Tips for Materialized Views!!

Author: Don Burleson

Page: 1 2 3

Materialized views are amongst the most significant SQL tuning tools that allow you to pre-join complex views and pre-compute summaries for super-fast response time. Although they are complex still they play an important role in reducing repetitive I/O.  In this article I will give some effective tips for materialized views.

Materialized views in Oracle:

• Materialized views were first introduced in Oracle8.
• Materialized views were enhanced in Oracle9 i to allow very fast dynamic creation of complex objects.
• Materialized views allow sub-second response times by pre-computing aggregate information and Oracle9 i dynamically rewrites SQL queries to reference existing materialized views.
• Without materialized views many unnecessary repeating large-table full-table scans are seen as summaries are computed, over and over.

Using materialized views:

Special initialization parameters must be set and special authority must be granted to the users of materialized views in order to use them.

Below code shows how to set initialization parameters within Oracle to enable the mechanisms for materialized views and query rewrite.

query_rewrite_integrity:

If the CURSOR_SHARING parameter is set to FORCE, some rewrites that were text-match based in 8.1.5 may no longer be possible with 8.1.6. There are three acceptable values for query_rewrite_integrity

System privileges:

You must grant several system privileges to all users who will be using the materialized views. In many cases, the Oracle DBA will encapsulate these grant statements into a single role and grant the role to the end users:

Page: 1 2 3

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Beware of DISTINCT Clause, Important Concerns!!
   Oracle 11g Enhanced Optimizer Statistics Maintenance!!
   The power of Oracle Diagnostic Tools!
   Avoiding Rollback Generation in Bulk Delete!!