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 to databases commonly range in size from several gigabytes to a few terabytes. We'l walk through some common scenarios on an enterprise where materialized view solutions can be applied in order to improve the response time on expensive operations such report queries which join two very large tables.
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 hiding the complexity of SQL queries thus creating a level of abstraction. You can think over 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 interested 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 direct access to tables by end users, and finally they can provide a standard interface of the data.
Views cannot solve all the problems explained above. For example, if a view performs an aggregation of millions of rows, 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 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 storing the underlying result set is a physical object like a table.
Let's suppose you want to create a Decision-Support Systems (DSS) and use Business Intelligence (BI) technologies for achieving your goals. Typically, BI solutions use pre-calculated formula for speeding up the long time queries. One strategy to create pre-calculated data is to use materialized views which stores 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 to 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 the 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.TransactionHistory and Production.Product which stores information about the entities 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.