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.
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:
-------------------------
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".
query_rewrite_enabled to "TRUE".
The query_rewrite_integrity parameter is an
optional parameter, but can take on the values of: "STALE_TOLERATED",
"TRUSTED", or "ENFORCED".
optional parameter, but can take on the values of: "STALE_TOLERATED",
"TRUSTED", or "ENFORCED".
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
used.
will assume that the data in the mv is correct, and that information will be
used.
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.
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.
use MVs that contain both stale data as well as fresh data.
