Materialized Views Bring Queries to New Heights
Innovations in technology often occur in increments, advancing so slowly that barely anyone notices. That had been the case with Transaction Processing Performance Council (TPC, www.tpc.org) benchmark results for databases. IBM Corp. and Oracle Corp. methodically leapfrogged each other in an attempt to define the standard in transaction processing speed. Once in a while, however, a technology takes hold of a market so violently that records are not leaped, but shattered.
One such technology is materialized views, or as IBM blandly calls it, automated summary tables. The introduction of materialized views to the market occurred just under a year ago when IBM shipped version 5.2 of its relational database management system, DB2 Universal Database. Materialized views was part of the database, but it took expert programming to use it. Then IBM shipped 6.1 in July and the performance brain of DB2 became more aware of the capability it contained.
"Something had to be done to get a leap in performance," says Jeff Jones, senior program manager in data management marketing at IBM. "This came about because in heavy query environments, there's this notion that customers are really analyzing the tons of data they're getting."
In the interest of speeding things along, IBM developed materialized views. This technology allows the database administrator to program specific tables that are queried on a regular basis to aggregate the data before the query is made. When the query is made, the data is already on a hard disk, ready to be accessed. This made for an extremely quick query, and ruined all hope of maintaining TPC’s decision-support benchmark, the TPC-D, as a meaningful measure. There are now two benchmarks, the TPC-H for ad hoc queries and the TPC-R for queries using materialized views.
Since its introduction, the technology has been implemented into Oracle Corp.'s latest version of Oracle 8i and Microsoft Corp. plans to integrate the technology into Shiloh, the next version of SQL Server due out next year. "What used to take hours now takes seconds," says Barry Goffe, SQL Server product manager at Microsoft. "There is a disadvantage, because the data being processed is shifting the processing of the data from the report to the other transactions that are occurring in the database."
Another trade off, says Mike Schiff, analyst with Current Analysis Inc. (www.currentanalysis.com), is the work that goes in to setting up these materialized views. "If you have very structured analysis, know the queries, and know the summary levels you'll need, they're a great thing to have," Schiff says. "On the other hand, if you've guessed wrong or people are always doing ad hoc queries all over the place that you don't have summaries for, it's not going to help you."
IBM's Jones downplays the negatives of using materialized views. He says from the position of a hit on database processing, that loss is invisible because of the improvements in time the queries are getting. He acknowledges some application engineering is required to set up the tables, but that too is offset by the improvements in query processing speed.
As for the future, Jones says do not expect any dramatic leaps in database technology like materialized views in the near future, but there will be incremental steps to automate the process. For instance, if the database is intelligent enough to know what queries are made on a consistent basis, the next step may be for the database to create a materialized view automatically.