Materialized Views in Database Systems

Introduction

This article is intended to illustrate the concepts of materialized views in database systems and their realization through examples written for SQL Server and Oracle database systems. This kind of view enables enhancing the performance of your application, especially in business intelligence solutions where we deal with very complex queries (a lot of join operations) accessing databases commonly ranging in size from several gigabytes to a few terabytes. We'll walk through some common scenarios in an enterprise where materialized view solutions can be applied in order to improve the response time on expensive operations such as report queries that join two very large tables.

Find more about SQL Server here: SQL Server

Materialized Views

Views are virtual tables composed of the result set of a SQL query and the contents are usually not stored physically. They allow for hiding the complexity of SQL queries thus creating a level of abstraction. You can think of views as a stored query in the server. The main advantage of views is that they can subset the data contained in a table exposing only the interesting columns and rows in order to abstract the presentation of the table; they can join and simplify multiple tables into a single virtual table; they can act as aggregated tables; they can restrict the direct access to tables by end users, and finally, they can provide a standard interface of the data.

Find more about Views here: Views in SQL Server

Views cannot solve all the problems explained above. For example, if a view performs an aggregation of millions of rows, the query performance of the view decreases dramatically and this operation is performed many times every day, the database system must access millions of rows repeatedly. A new data structure must be defined to deal with this kind of scenario. A materialized view is defined just as a regular view but the result set of the query is stored as persistent data object such as a table which is frequently updated from the underlying base tables when changes occur. They are useful to aggregate data in business intelligence applications with complex queries.

Materialized views in Oracle Database

Oracle has added the capability of creating materialized views since Oracle 8i stores the underlying result set as a physical object like a table.

Let's suppose you want to create a Decision-Support System (DSS) and use Business Intelligence (BI) technologies for achieving your goals. Typically, BI solutions use pre-calculated formulas for speeding up long-time queries. One strategy to create pre-calculated data is to use materialized views which store the calculated data and refreshed any change from the base tables. I will illustrate this solution through an example.

First of all, we need to analyze the SQL statement syntax for materialized views. You can create a materialized view in Oracle with the SQL statement create materialized view. There are some options associated with this statement such as.

Build method. It's the time when the view contents are first computed. There are two options: the first one is immediate to specify that the view is to be populated when created, this is the default build method and the second one is deferred to specify that the view is to be populated by the next refresh operation.
Refresh mode. It's the time when the view contents are recomputed and how often it occurs. When the view is refreshed each time the underlying base tables are changed and commit the change, the mode is called on commit. When the user calls one of the three DBMS_MVIEW refresh procedures, the mode is called on demand. The last mode is to refresh the view periodically and the user can tell when to refresh and how often to do it.
Refresh method. It tells how to refresh the view. The complete method is to recompute the view from the base tables and the queries. The fast method tries to update the view incrementally without recomputing. The last method is force and Oracle will decide if a fast method is possible, if not, will do a complete refresh.
Query rewriting. The enable query rewrite tells the query optimizer that it should try to rewrite the associated queries.

Let's test materialized view in Oracle Database. First of all, let's create and execute one SQL SELECT statement to report the number of employees by department. The base tables are emp and dep tables from Scott's schema in the sample Oracle database as shown in Listing 1. In my case, it took 35 seconds to execute but for thousands of millions of rows like any normal data warehouse used in business intelligence solutions, it might take minutes.

set timing on; 
select d.dname, count(e.*)
from scott.emp e,scott.dept d
where e.deptno=d.deptno;
group by d.dname
set timing off;

Listing 1. SQL Select statement

Suppose that you access this query information very frequently in the DSS application, and you want to improve the response time, so your strategy is to create a materialized view for this query as shown in Listing 2.

create materialized view mv_TotalEmpByDept

  build immediate
  refresh fast on commit
  enable query rewrite
as

  select d.dname, count(e.*)
  from scott.emp e,scott.dept d
  where e.deptno=d.deptno;
  group by d.dname

Listing 2. Creation of the materialized view

The view is filled out immediately and is refreshed incrementally each time the changes to the underlying tables are committed. Finally, the query optimizer will attempt to rewrite the query whenever possible.

Now let's run a SQL SELECT statement against the materialized view as shown in Listing 3. In my case, it took just less than a second to execute.

set timing on;
select *
from mv_TotalEmpByDept; 
set timing off;

Listing 3. SQL Select statement against the materialized view

Materialized views in Microsoft SQL Server

Now let's see how to implement materialized view in SQL Server. Index views are the SQL Server realization of materialized views.

Our SQL Server solution is a DSS for tracking information about the transactions that occurred in our enterprise and the associated products. We're going to use the sample AdventureWorks database shipped with SQL Server 2005 and the tables Production. Transaction history and Production.A product that stores information about the entity's transaction history and the underlying products. We're going to write a SQL SELECT to report the total cost and quantity by-product (see Listing 4). When the query is executed, in my case, it took 42334 microseconds to execute.

SET STATISTICS TIME ON

select p.ProductID, sum(t.ActualCost), sum(t.Quantity)

from Production.TransactionHistory t inner join Production.Product p on t.ProductID=p.ProductID group by p.ProductID;

Listing 4. SQL Select statement

To improve the response time, our strategy is to implement a materialized view in SQL Server. In order to approach our solution, first of all, we have to create a new view as shown in Listing 5. It is remarkable to say that we need to include the WITH SCHEMABINDING option to bind the view to the schema of the base tables.

create view v_TotalCostQuantityByProduct with schemabinding

as
  select p.ProductID, sum(t.ActualCost), sum(t.Quantity)

from Production.TransactionHistory t inner join Production.Product p on t.ProductID=p.ProductID group by p.ProductID;

Listing 5. Creating the view

And then a clustered index will be created on this regular view. This turns the regular view into an indexed view as shown in Listing 6. It is remarkable to say that this operation took 20998 microseconds to execute.

create unique clustered index TotalCostQuantityByProduct on v_TotalCostQuantityByProduct (ProductID)

Listing 6. Creating an index on the view v_TotalCostQuantityByProduct

Now let's run a SQL SELECT statement against the created materialized view as shown in Listing 7. In my case, it took just 32 microseconds to execute. Good improvement.

select *
from v_TotalCostQuantityByProduct

Listing 7. SQL Select statement against the indexed view v_TotalCostQuantityByProduct

Conclusion

Now you have the insights to create materialized views in Oracle Database and Microsoft SQL Server for achieving better response times in your business intelligence solutions. You can apply the example solutions to your own business scenario.

1 Comment

Join the conversation! Your thoughts help the community grow.

Indrani Roy8y ago
Hello sir, I want help in creating materialized view dynamically. Suppose I create a web form in vb that accepts a sql query in a text box, there's a ADD button to add this query to the database, a HIT/MISS button to calculate how many times the attributes are used by a particular query and store it in a matrix, from this matrix the frequently used attributes are fetched and forms a materialized view. Please guide me how to do this