materialized view

For many years and versions, the MS SQL Server database platform has supported the ability to create virtual tables known as views. Views in SQL Server can be defined as a stored select statement on a base table. Data in a view is not stored in the database and the result set is determined while the view is executed. Traditionally, views provide a security mechanism that restricts users to a certain subset of data in one or more base tables or provide a mechanism that allow developers to customize how users can logically view the data stored in base tables. Prior to the addition of indexed views in SQL Server, views never contained indexes of their own.

Starting with Microsoft SQL Server 2000, indexed views are views whose results are persisted in the database (similar to tables) and contain database indexes for fast retrieval of data. A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space. Indexed views work best when the underlying data is infrequently updated. The maintenance of an indexed view can be higher than the cost of maintaining a table index. If the underlying data is updated frequently, then the cost of maintaining the indexed view data may outweigh the performance benefits of using the indexed view.

The main benefit of an indexed view is query optimization and faster query performance. Because the indexed view physically exists on disk, there can be substantial savings in response time to queries that involve the indexed view. Additionally data within the indexed view can store joins between tables, can store a subset of fields and/or records that exist within a table, can store calculations of table fields, and can store aggregations or groupings of data. Thus less data is retrieved in a query of the the indexed table than the query of the base tables. And finally, indexed views utilize performance-enhancing indexes corresponding to its own data where ordinary views only utilize indexes related to the indexes in its base table.

The main costs of an indexed view are additional maintenance of the indexes, additional disk space taken up by both the indexed view and related indexes, additional overhead of maintaining the view on disk as the base tables are modified. One must weigh the cost to maintain the index against the benefit of query optimization provided by the index. When the underlying tables are subject to significant inserts, updates, and deletes, be very careful in selecting the indexes (both table and view) that will provide the greatest coverage across your queries for the lowest cost.

Typically, environments that are best suited for indexed views are analytical data repositories such as data warehouses and data marts. Analytical data repositories require rapid query speeds and the underlying data changes infrequently.

The syntax to create a Indexed View in SQL Server is:

CREATE VIEW IndexView

WITH SCHEMABINDING

AS SELECT Au_Fname + ‘ ‘ + Au_Lname, Au_id FROM Authors WHERE Contract = 1 GO

CREATE UNIQUE CLUSTERED Index MyINDEX ON IndexView (au_id) Go

A materialized view within an Oracle database is an object that extends the functionality of an ordinary view and contains the results of a query (SQL select statement) in a persistent or physical format. Materialized views can use either tables, views, and other materialized views as its data source and can be both indexed and/or refreshed.

Within an Oracle database, a view is a virtual table representing the result of a database query or select command with a table or another view as its source. Whenever a select query or an update addresses a view, the database converts these statements into select queries or updates against the source tables of the view’s select query. Thus a view is just a stored query or virtual table with no storage of physical data, and the physical data is stored in a table. However, a materialized view takes a different approach in which the query result is cached as a concrete table that can be refreshed from the source tables of the select query. This enables better query performance, at the cost of some data being potentially out-of-date.

Because a materialized view is manifested as a physical table, anything that can be performed to a real table can be done to the materialized view. With the most important thing that can be performed is the ability to build indexes on any field, enabling drastic speedups in query performance time. In a view, it’s typically only possible to utilize indexes on columns that come directly from (or have a mapping to) indexed columns in the source table. In addition, a materialized view can contain denormalized data that physically exists in multiple tables, a subset of fields and records that exist in physical tables, pre-calculation of table fields, and data pre-aggregated at summary levels.

Materialized views are most useful in analytical data repositories such data warehouses or data marts, where query performance is important and data refreshes and updates occur infrequently. Materialized views are one of the single most important database tuning tools that exist allowing you to pre-join complex table and views and pre-compute summaries for fast response time of queries.

Create Materialized View Syntax

Create Materialized View DeptCheckMV

Start with Sysdate

Select * From EmployeeTbl

Where Salary > (Select avg (salary) from EmployeeTbl

Posts

MS SQL Server Indexed View Defined

Oracle’s Materialized View Defined