22 July 2015

Data Warehousing – Materialized views (or indexed views)

Sometimes when we are using Cognos for business intelligence and the performance is very slow, that could happen because Cognos generates the SQL dynamically. So there are cases when we can tune it with Materialized Views (using query re-write).

Cognos is similar to other OLAP tools that generates ad-hoc SQL and then builds in-RAM cubes of the result data.

When building cubes, Cognos will often request aggregated data, and the resulting SQL may have repeating full-table scans. A few well-placed Materialized views can re-write the Cognos SQL to access the pre-summarized data.

Materialized views are physical tables that cache the results of a query. Materialized views in many cases can improve the performance of our reports by tuning in the database or the extract of data. We can use Materialized views to pre-compute summaries and complex joins and can be a way to improve query performance for complex queries. This way we can increase the speed of queries on large databases or to replicate data.

Like a standard view, a materialized view represents data stored in other database tables. However, a materialized view contains actual data, but, the data in a materialized view must be explicitly refreshed. A materialized view is also similar to a snapshot and we can specify when the data is to be refreshed.

Materialized views can be accessed directly using a select statement. Depending on the type of refresh required, materialized views can also be accessed directly in insert, update or delete statements.

However, there are costs associated with materialized views, such as performance costs for maintaining it and storage costs, because it's a physical table after all.

Example of a materialized view creation

CREATE MATERIALIZED VIEW <name> <storage_options>

BUILD <build_clause_body>
REFRESH <refresh_clause_body>
[ENABLE QUERY REWRITE]
AS
SELECT <select_clause_body>

The body of the BUILD clause allows to specify when to build the actual data in the table. There are three options: IMMEDIATE, DEFERRED or PREBUILT TABLE.

The body of the REFRESH clause specifies when and how the data is to be refreshed to reflect changes in the database.

Refresh mode

ON COMMIT refresh occurs automatically on the next COMMIT to the master tables ON DEMAND refresh occurs when we execute manually one of the refresh procedures in the DBMS_MVIEW package at specified times refreshes are initiated using JOB_QUEUE_PROCESSES or JOB_QUEUE_INTERVAL parameters.

Refresh options

COMPLETE completely recreates the materialized view, by recalculating the query for the materialized view

FAST performs an incremental refresh

FORCE determines if a FAST refresh is possible, otherwise performs a COMPLETE refresh

NEVER suppresses refreshing of the materialized view

Query Rewrite

Enable

  • Enforced - Only rewrites when data consistency and integrity is guaranteed. Constraints status must be ENABLED and VALIDATED (this is default).

  • Trusted - Only rewrites when constraints status must be ENABLED and RELY.

  • Stale_tolerated - It is not based on constraints, so always rewrite regardless data consistency and integrity.

Disable

As indicated above, materialized views are variations of views which contain actual data. They are stored summaries of queries containing pre-computed results.

Materialized views improve query performance by pre-calculating expensive join and aggregation operations on the database in advance and storing these results in the database.

The query rewrite facility is activated by including ENABLE QUERY REWRITE clause when creating the materialized view.

Other considerations

Materialized views can be partitioned if necessary.

Indexes can be created on materialized views.

Materialized views enhance the benefits of snapshots by being able to perform fast refreshes and improves overall refresh performance.

.

.

.

.

     João Marques
  Associate Manager
Solutions Development