Total Pageviews

November 23, 2016

11/23/2016 04:34:00 PM
Oracle apps Materialized views
Oracle SQL : Materialized views

What is Materialized views? Generally  will call  them as "MV"s

       What is the difference between view and materialized view?
  • Materialized views are disk based and are updated periodically based upon the query definition.
  • Views are virtual only and run the query definition each time they are accessed.

Oracle MVs are a good option for data warehousing and replication.
MVs based on inner/outer equi-joins can be refreshed on demand or periodically,
if desired.  

MVs that are based on sub-queries on remote tables support bi-directional replication. 

An MV includes a query that is transparent to the user, includes a rewrite based on cost optimization.  

MVs improve performance because expensive join and aggregation operations are
pre-calculated before execution time. 

The optimizer  automatically recognize when a MV can be used to satisfy a request to the database.  
The rewrite, as stated above, is transparent to the end user.  

Parameters affecting  MVs

The db parameters that pertain to MVs are:
  •    optimizer_mode
  •    query_rewrite_enabled
  •    query_rewrite_integrity
  •    compatible

In order to use query rewrite, the optimizer_mode init.ora parameter should be
set to "ALL_ROWS" or "FIRST_ROWS" or by analyzing the tables and setting this
parameter to "CHOOSE".  

You must also enable rewrite at startup by setting
query_rewrite_enabled to "TRUE".  

The query_rewrite_integrity parameter is an
optional parameter, but can take on the values of: "STALE_TOLERATED",

The parameter controls the accuracy of the query rewrite. 

Setting query_rewrite_integrity to "TRUSTED" means that the optimizer
will assume that the data in the mv is correct, and that information will be

If this parameter is set to "ENFORCED", the default, the optimizer will
use MVs that it knows contains fresh data.  If this parameter is set to
"ENFORCED", the user must ensure to validate the constraints in the data
warehouse schema.  

If the mode is set to "STALE_TOLERATED", the optimizer will
use MVs that contain both stale data as well as fresh data.
Related Posts Plugin for WordPress, Blogger...