Since its first issue more than 10 years ago, Database Programming & Design has provided a forum for the discussion of major changes in the database landscape. So it seems fitting to use this last installment of VLDB Vision to sum up recent changes in the VLDB scene.
In my first column in Intelligent Enterprise, I’ll take a look forward at what is coming in the technology, architecture, and implementation of scalable systems.
So my own list of the most interesting VLDB developments and advances of the last two or so years—since this column began to appear—is as follows.
Disk prices plummeted. Disk manufacturers continue to pack more bits per track and more tracks per inch on the magnetic disk drives we use to store data. In the last two and a half years we have seen the capacity of standard disk drives increase from two to four to nine gigabytes. We have 23GB drives on the market now, and still the advances in density have not slowed down.
The result is that the financial and physical resources required to store a terabyte of data dropped by at least a factor of five over the two-and-a-half-year period.
The continual, rapid drop in disk prices most profoundly enabled the rapid growth in database size. When you can store a lot of data cheaply, you find business strategies to take advantage of that. Thus we have seen the ongoing boom in data warehousing, driven by business strategies that exploit detailed data.
Parallel database architecture evolved. Two and a half years ago, we had parallel database architecture, and a few hardy souls had deployed it on a large scale. There were plenty of copies of parallel database products installed on Unix SMPs and Unix MPPs, but I can promise you that production Unix systems actually exploiting parallelism on large volumes of data were few and far between.
In fact, from 1995 through perhaps mid-1997, parallel architecture was mainly in its “religious” phase. Which is better: SMP or MPP? Shared nothing or shared disk? The truth is, users don’t really care. In regard to architecture, all that users care about is performance, scalability, availability, and price/performance.
Parallel processing has begun to grow up, and as a consequence, attention is shifting from architecture to value.
The same thing is happening in the database software arena. There has been a tremendous amount of noise about shared-disk vs. shared-nothing architecture. Oracle and DB2/390 are shared disk. All the other leading products are shared nothing. But in my view, no clear advantage to one or the other has arisen. There are theoretical arguments for one approach or the other. There are some cases where one approach ought to be better than another, but what really matters is product engineering and database design. Both Oracle and DB2/390 are alive and well and progressing nicely in the VLDB space, as are the shared-nothing architectures in use in Teradata, DB2 UDB, Informix, and Nonstop SQL. These products differ in their approaches to SMP, cluster, and MPP implementation, but we don’t have a dominant parallel database architecture or any solid, quantitative proof that one architecture is generally superior to the others.
On the other hand, DBMS product engineering and design does matter greatly. On VLDBs, there are huge differences in performance from one engine to the next and one application to the next. But these differences are principally in the techniques for parallel execution, optimization and data access. I have seen no evidence that they are due to the much ballyhooed “shared nothing” vs. “shared disk” distinction.
The bottom line is that parallel database architecture is now in widespread use on a large scale. Parallel query, update, and load are real capabilities that work. Backup and recovery generally function in parallel. There are important differences between products and from application to application. But there is no longer a question about the technology; it works in production, and it is here to stay.
Synchronized scanning. Teradata now does synchronized scanning, which means that if two or more users run queries that scan the same table and those scans would overlap in time if done separately, the system will combine them into a single scan. It is all still done in parallel, but combining the scans means that running the queries at the same time will require less work than running them one after another. This capability is extremely important in successful data warehouse operations, which feature an ever-growing workload as more users take advantage of the capability.
Synchronized scanning was first introduced by Red Brick Systems, as were several other features that I highlight in this article.
Bitmap indexes. Bitmap indexes have been implemented in Sybase IQ, Oracle, and Informix during this period, and they have a profound effect on predicate evaluation on VLDBs. Once again, this feature was first introduced to the Unix relational database scene by Red Brick, but Oracle and Informix both deserve credit for integrating bitmaps into a fully general relational database engine with a robust parallel architecture. And Sybase IQ has integrated bitmap indexing with other innovative techniques, such as column storage, to produce startling gains in yet another set of queries.
We are just beginning to see the effects of this move as users begin to deploy bitmap indexes on a large scale. The significance of bitmap indexes is twofold:
*They reduce the time and effort it takes to evaluate complex predicates by enormous factors; in real, everyday examples they will reduce query times by factors of a hundred or more.
*They reduce the time and effort it takes to build and maintain indexes, making it worthwhile to index low-cardinality fields such as gender, marital status, profession, and so on.
The net effect is to change the approach to database design and make data far more accessible in decision-support applications. Often, queries that would otherwise require large table scans can instead be evaluated entirely on the index, with either no disk I/O or with a relatively small number of disk accesses.
A powerful, related technique is combining indexes dynamically as part of query evaluation before going to the data. DB2/390 has done this for years, and DB2 UDB does it. Combining indexes before going to the data can also result in predicate evaluation without access to the data and is sometimes nearly as powerful as the use of bitmaps.
Bitmap indexes were introduced to the market in the 1970s by Computer Corporation of America (www.cca-int.com) in Model 204, a DBMS for the mainframe. Still around and still delivering astonishingly good performance in predicate evaluation and other areas, Model 204 undoubtedly features the most mature, and probably the best integrated, implementation of bitmap indexes. Of all the database engines on the market, it alone was designed to use bitmap indexes from the beginning. Even in the fast moving world of database technology, you can sometimes find cases where “newest” is not the same as “best.” Model 204 is one of the few products of its era still finding new applications—and still powering important application products such as MarketPulse, a leading direct marketing package—as we approach the millennium.
Join indexes. Go back two to three years and you would find that only Red Brick was using join indexes in VLDBs. Its Star index, used in clever algorithms in combination with bitmap indexes, produced startling performance gains when the database design used the star schema. This technique is very effective and continues to let Red Brick outperform everyone else in an important class of queries.
However, Informix, Oracle, and Teradata have all joined the join index party in some form, and Informix and Oracle use these facilities in combination with stored bitmap indexes to produce dramatic results.
With both join indexes and bitmap indexes, the big payoff on VLDBs requires that these facilities be integrated effectively with the optimizer and the parallel query execution techniques. It also matters whether or not a full array of join techniques is available to the optimizer (for example, hash joins as well as nested loop and sort merge joins) because the join technique needs to be well suited to the particular query at hand. Thus join indexes are important, but they will not pay off in every type of query.
Rolling window technique. A database provides a “rolling window” on a stream of data when the usage requires that periodically, new data be added and old data be dropped in some repeating pattern. An example would be a requirement to store 48 months of sales data online in a data warehouse. Each month, you need to add a month and drop a month of data. In most engines, dropping a month of data in a VLDB is time consuming; it sometimes results in a dirty database that you must reorganize more frequently.
Oracle8’s partitioning feature works beautifully for the rolling window, and its partition elimination in query evaluation contributes to good performance in these situations. It also executes queries on just the most recent month, or the last few months, exceptionally well.
As far as I know, only Oracle8 and Model 204 have this feature.
Aggregate engines. An aggregate engine computes frequently needed aggregates in advance, caches them, and then automatically uses them in queries that require them. Once again, Red Brick has been first to market with this innovative and promising feature.
An aggregate engine lets a relational database engine efficiently handle dimensional queries, a la OLAP, when stated in standard SQL.
DB2 UDB has implemented CUBE and ROLLUP, which provide an alternate technology for efficient handling of some dimensional queries. (When I first attended an IBM presentation on this, I thought the speaker was saying “Cuban Rollup,” which I imagined would have to be some sort of cigar. I mused about this point—was IBM going to give me a cigar, and an illegal Cuban import at that—almost until the end of the session, when I finally saw the terms appear in writing on the screen. I experienced the exhilaration of an “aha!” mixed with the disappointment about not getting a Cuban cigar, which is a rare treat, I assure you.)
It is a significant advance to see these features in VLDB products. While the OLAP products address the same problem, they do not generally perform well on really large databases or in situations where there are a large number of dimensions.
Query rewrite. Gone are the days when most data warehouse queries were handwritten by professionals. More and more queries today come from end users using desktop tools. The tools generate the queries, which come barreling down on the database engine untouched by human hands. And these generated queries frequently look like nothing a human would ever write. Most tools generate the queries with little or no knowledge of what would be best for a given DBMS.
The result is often a query that a standard optimizer won’t handle well. Teradata and DB2 have demonstrated strong capabilities in this regard, and Oracle has invested in this area as well. This is an area to watch. Users who field successful data warehouses that get used throughout the organization need an engine that can really handle end-user queries of all types.
Ten terabytes of data go into production use as a single database. The Winter VLDB Survey reported a database in production for the first time this year with 16.8TB of data, a database at United Parcel Service running on DB2. For several years before, the largest known database on a relational, commercial database product was in the range of one to four terabytes. We have now leaped ahead into the double digits. One hundred terabytes can’t be too far away.
Data warehouse performance measurement advances. The TPC-D benchmark has established itself over the last two years as the standard measure of data warehouse performance. Like any real measure, TPC-D has limitations, but it is useful and has created the greatest base of measurement data that has ever been available for this purpose. If you are unfamiliar with this measure, visit the Transaction Processing Council’s Web site (www.tpc.org), and see the three articles previously published in this column (“Introducing the TPC-D Benchmark,” May 1997; “TPC-D: Pros and Cons,” June 1997; and Stephen Brobst and Mark Monoghan’s “TPC-D Benchmarking: The Good, The Bad, and The Ugly,” November 1997). Just remember to dig below the surface in analyzing the results and look at the full disclosure reports to get the full picture. Three years ago we had nothing but advertising claims to go by, and you could rarely, if ever, tell what was really being run, how the database was set up, or how hardware and software were configured. You didn’t know what was indexed. You didn’t know what the data really looked like. Smart users scoffed at benchmark results—and they were right to do so.
As with any benchmark, TPC-D can be abused, and you have to pay attention to what is going on. But the TPC-D results and related publications convey substantial and useful information that has never been available elsewhere—and you ignore the results at your own risk.
During the past year, I have participated in an interesting new effort to provide a supplement to TPC-D called the Data Warehouse Challenge. In this benchmark, vendors must perform a standard TPC-D and publish it. Then, using the same database and benchmark environment, they run a set of queries that they have not seen in advance. Results and analysis are published at www.datachallenge.com. IBM and NCR participated in the first Data Warehouse Challenge, demonstrating that DB2 UDB and Teradata could handle a tough assortment of surprise queries, on 100GB and 300GB of data, respectively.
Nobody knows for sure what is coming. Certainly, I could not have predicted all the events described above. But see my Scalable Systems column in the first issue of Intelligent Enterprise, and I’ll do my best to offer a glimpse of what’s ahead.
Meanwhile, it has been a privilege to share my view of VLDBs with you in these pages over the last 30 months, and I hope you agree that it has been a fascinating journey. I have especially appreciated the many emails I have received from readers of the column.
I’ll leave you with my wishes for all those who brave the dangerous, high altitudes of the VLDB scene: May your database updates loads run smoothly; may your queries run quickly; may all your database crashes be recoverable; and may your users find treasure in all the databases you build.
Richard Winter is a specialist in large database technology and implementation and president of Boston-based Winter Corp. You can reach him via email at richard.winter@wintercorp.com or by fax at (617) 338-4499.