Materialized Views And Cache Tables

So, just what is a materialized view? A materialized view is a set of denormalized data designed to replicate data to non-master sites. The concept, originally called snapshots by Oracle, but is now finding its way into database schema's all over. However, this trend is moving from its original purpose to simply storing "cache tables" to avoid complex JOINs, triggers and stored procedures.

Materialized Views work well in replication environments where a company may have a central server, with several, or perhaps hundreds, of regional offices. By providing the data to the regional offices, the load on the central server is greatly reduced, thus, minimizing network load, and load on the central server. The central server works as a master, and the regional servers as a slaves.

By not requiring a network connection all the time, the regional offices can work independently of the central office, and gain accessability by having data locally stored.

Today there is a growing trend to store this kind of denormalized data for no other reason than it is faster to store the data again in a cache table than to do the hard yards of correctly normalizing and indexing the database.

Others find that complex database designs require multiple JOIN statements to gain access from one table to another, and, rather than compute these complex JOINs, the cache table is used instead. In a replication environment with Oracle, this would be the source of an VARRAY, but has no place in non-replication environments that duplicate data for the reasons of easier access. This is why DBA's are paid good money, to normalize the data environment, and to maximize speed and ease of access.

In a replication environment, the caching of complex JOINs and aggregations greatly reduces server load, by retrieving complex data structures in a single disk I/O. This should not be confused with single server environment where caching of data is performed simply because disk space is cheap now. Data structures should be built from its atomic pieces every time a query is run, not from a duplicate copies.

The moral of this story is, do not use materialized views, or "cache tables" to cover up a lack of design skills, Materialized Views have a place in master/slave network environments, but denormalized data has no place in single server environments