Article Preview

FEATURE

Using Materialized Views

A New/Old Tool For the Toolbelt

Issue: 10.4 (May/June 2012)
Author: Craig Boyd
Author Bio: Craig Boyd is currently a Sr. Data Modeler for a large well known financial institution. But during his twelve years in the IT industry he has been everything from a PC Technician to iSeries System Administrator to iSeries Programmer and Sr. Technical Lead.
Article Description: No description available.
Article Length (in bytes): 13,977
Starting Page Number: 35
Article Number: 10412
Related Web Link(s):

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0000927.htm
http://docs.oracle.com/cd/E11882
http://docs.oracle.com/cd/E11882_01/server.112/e10706/repmview.htm#i34980
http://msdn.microsoft.com/en-us/library/dd171921
http://wiki.postgresql.org/wiki/Materialized_Views
http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views
http://www.pgcon.org/2008/schedule/events/69.en.html
http://www.fromdual.com/mysql-materialized-views
http://code.google.com/p/flexviews/

Excerpt of article text...

One of the great things about being a software developer is all the cool tools we get to use and learn about. There are always new ones being created and then there are also some older ones that we wonder how it is that we have never heard of them.

In this article I am going to introduce a tool that has been around for a while and exists in some form on most DBMS (database management system) platforms. It is called a materialized view.

Setting the Stage

I need to set some boundaries before we get too far into this discussion. As we come across database terms that may be unclear to the reader I will define them in a general sort of way. The reason for this is because, while conceptually the terms can be used in the context of almost any DBMS, they are usually implemented in very different ways and can have profound impacts on how you design the physical structure of your database.

So let's step back for moment. When you run a SELECT statement, essentially what happens is that you get back a virtual table or result set. This result set is very short lived and takes up no space in the database. Additionally, it may or may not perform well depending on several factors such as available indexes, current workload of the server, the DBMS's query optimizer, etc. The need for the output is immediate and it is expected that the results will be current as of the point in time when the statement executed.

Sometimes there is a need for a particular SELECT statement to persist past a few brief moments. When this need arises, a developer, data modeler, or database administrator (DBA) may decide to instantiate the SELECT statement as a view. A view is basically just a SELECT statement that is predefined. It does not contain any data so it takes up very little space in a database. It does not produce a result set until it is queried. It is subject to all the same performance constraints that an ad hoc SELECT statement is subject to and the general expectation of the data is that it is current.

...End of Excerpt. Please purchase the magazine to read the full article.