An Analysis of the Strengths and Weaknesses of the Big Six Database Servers

Comparison Summary

By Martin Rennhackkamp

DBMS Server Comparison Supplement, November 1996

An Analysis Of The Strengths And Weaknesses Of The Big Six Database Servers.

In 1991 I performed a thorough evaluation and comparison of the four major DBMSs at the time: Informix, Ingres, Oracle, and Sybase. This comparison was done for a client building a huge distributed database application, currently in its second phase of d evelopment, with the first phase running successfully country-wide. At that stage, the distinguishing criteria were query optimizers, triggers, views, and support for distributed databases. Some products had these features, but some others' marketing per sonnel were just talking about them. For example, declarative integrity was a "future" that was at that stage only being phased into most of the DBMS products. It was relatively straightforward to draw up a checklist and fill it in with "yes" and "no" in the various columns.

Now, a mere five years later, these aspects - as well as many new features such as Internet support, remote procedure calls, and support for multiprocessor platforms - are standard items on each product's fact sheet and marketing material. On a very high level, DBMS products are becoming such commodity items in IT shops that it is almost possible to take a one-size-fits-all approach. On a detailed level, however, you must do a much more thorough evaluation. The products are evolving at such a fast pace, and their research and development teams are adding new features at such a rate, that simple yes/no entries in the comparison columns are just not enough. All of the comparisons would be similar, with mostly yes entries in each column and the odd no mar ked with an asterisk referring to a footnote saying, "Scheduled for the next release, currently in beta testing." If you want to do a thorough comparison today, you have to study each feature and check the extent to which it is implemented and the qualit y of the implementation. To explain further, I review a few of the features in this comparison supplement.

Relational Data Model

Although the DBMS products reviewed here can rightfully claim to be relational, their support of the relational data model must be scrutinized closely. Obviously they all support the basic relational concepts, such as data stored in tables and accessed b y high-level set operations, mostly through SQL. However, not one of the Big Six supports domains. It is crucial that these products address this fundamental concept of the relational data model. You must be able to define your domains and then specify y our table columns, and preferably also your stored procedure variables and parameters, in terms of the domains. This process is necessary to ensure tight type-checking, as you can do in some programming languages. If a "small" (in terms of market share) product such as Interbase could implement domains five years ago, surely the Big Six should be able to "get it right" today.

All of the Big Six can rightfully claim that they support declarative integrity constraints. Except for domain constraints, they all support declarative key, column, and referential-integrity constraints. But you must investigate these claims closely as well. Only Informix and Oracle support cascading delete as a referential-integrity constraint violation option, and only DB2 supports the full compliment of set null, cascade, and no action as prescribed by the ANSI SQL-92 standard. You should also check how these constraints are implemented. Most of these products use quite crude mechanisms to implement the constraints. For example, most of them create "hidden" indexes to implement primary-key and unique constraints. In addition, DB2, Informix, and Ora cle restrict you from creating an additional unique index on a column that has already been indexed "behind the scenes" for a unique constraint.

Database Objects

All of the products in this comparison supplement support binary large objects (BLOBs), which you can use to store images, text, documents, voice and sound recordings, and any other unstructured data. However, you must consider carefully how the BLOBs ar e processed by the front-end tools and languages you use to develop applications. Not all front ends and languages can easily handle these tricky data types. With some languages, such as C and C++, using a SQL preprocessor, you must process a text BLOB a s a series of text segments. Some fourth-generation development environments may not be able to process the BLOBs returned from your database server at all.

You should also check carefully how triggers are implemented, which is an important factor. Some products use their trigger mechanisms behind the scenes to implement declarative integrity constraints. It is even more important if you want to implement yo ur business rules using triggers. For example, CA-OpenIngres only has row-based triggers (called rules) that fire after the triggering operations, but you can have any number of triggers per table, each with its own name. Therefore, you can implement you r business rules in a very modular fashion. DB2, Informix, and Oracle, on the other hand, have set-based and row-based triggers, which can fire before and after the triggering operation, but you can only have one trigger per firing condition, which means that you must sometimes integrate unrelated business logic into single triggers. However, you can improve the situation by calling separate stored procedures for each business processing function.

Queries

The level of locking provided by the various DBMS products has long been a contentious issue, quite heavily exploited in some products' marketing material to downplay their opponents in the license-hunting game. There is no clear guideline as to whether row-level locking is generally better than page-level locking; the more suitable type of locking depends on the application and its throughput and isolation requirements. It is, however, extremely important to determine the concurrency control requiremen ts of your applications (preferably not in the presence of a particular product's salesperson) and to check whether the proposed product can satisfy your needs.

All of the DBMS products evaluated in this supplement claim to support the Entry Level ANSI SQL-92 standard. It is important to remember that Entry Level really covers only the most basic