Databases Reference
In-Depth Information
you need real-time data, keep in mind that updating a materialized view whenever there
is a commit on the source table(s) can be resource intensive. You may not be able to sup-
port as many materialized views as you would with a scheduled refresh strategy.
Oracle has a QUERY_REWRITE_ENABLED setting that should be set in the
configuration parameter file to make fuller use of the materialized views you create. If
query rewrite is turned on, then Oracle automatically recognizes when a small materi-
alized view can be used to answer a query, instead of reading the larger underlying
source tables.
TIPS AND INSIGHTS FOR DATABASE PROFESSIONALS
•
Tip 1. Utilizing materialized views can bring marked improvement in both total
disk I/O and query response.
Focus on the queries that occur frequently and also
heavily consume disk I/O. These queries are the greatest opportunities for performance
gain through materialized views.
•
Tip 2. Good candidates to consider for materialization are the natural views of fre-
quent queries, and also common ancestors of those views.
•
Tip 3. Star schemas can make a materialized view applicable to a large family of
queries, thereby multiplying the gain for the given resources.
•
Tip 4. Lattice structure diagrams can facilitate the selection of materialized views
and also the planning of data update paths through the lattice.
•
Tip 5. Decide on the update strategy for each materialized view.
Incremental
updates within a fixed update window during off hours are a common strategy. If a
large portion of the rows in a materialized view change with each update, the complete
refresh strategy takes advantage of bulk load operations. If real-time data is needed, the
refresh on commit strategy is the way to go.
•
Tip 6. Set a limit on the number of views you are willing to design and maintain.
There are two reasons why this is so important. First because each materialized view
consumes storage space on disk. Second, each materialized view adds compelxity to the
search for the optimal query execution plan during query compilation. As a result, a
large number of materialized views can have a profoundy negative consequence on the
time required for query compilation. Queries that might otherwise compile in fractions
of a second may expand to take minutes. In some extreme cases where a large number
of materialized view have been defined the compilation time can significantly exceed
the time for the query to execute.
•
Tip 7. Decide on a limit for the amount of disk space available for materialized
views.
Committing larger amounts of disk space will in general bring quicker query
response, but with diminishing returns. Generally, few data warehouses allocate more
than 10 -20% of the total system storage for materialized views.
