Special

Introducing the “Welcome to Xojo” Bundle!

New to Xojo and looking for guidance? We've put together a terrific bundle to welcome you! Xojo Bundle

This bundle includes six back issues of the magazine -- all of year 15 in printed book and digital formats -- plus a one-year subscription so you'll be learning all about Xojo for the next year. It's the perfect way to get started programming with Xojo. And you save as much as $35 over the non-bundle price!

This offer is only available for a limited time as supplies are limited, so hurry today and order this special bundle before the offer goes away!

Article Preview


Buy Now

Issue 10.4 ('Extreme Programming')
Instant purchase and download via GumRoad!

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.