Optimize What You Know About Databases

Computerworld - Some pundits -- and some technology vendors -- assert that database management systems have become commodities and that DBMS innovation is either dead or irrelevant. They're wrong. DBMSs haven't stopped evolving, and they aren't close to being all alike.
In particular, the major relational DBMS vendors are pursuing technical differentiation in three important areas:
Lower-cost analytical processing. DBMS vendors are enhancing their query optimizers to relieve much of the burden on database administrators, as well as to directly improve query performance. They're also improving processing speed though specialized indices and materialized views.
Real-time analytical processing. In some cases, these enhancements make it practical to run analytics straight off a production database, greatly facilitating real-time analysis. This is especially important in CRM, where it allows customer-specific pricing and confirmed product availability. Real-time analytics have also become cost-effective in some supply chain, logistics and portfolio/risk analysis applications.
Nonrelational data types. Specialized data types are crucial to many applications - geographic data for marketing, mineral exploration and homeland security, or genomic data for drug research, for example. Text data is used for a broad range of search and document applications. And among the horde of new XML-based applications, a small but significant fraction depends on actual XML data storage.
Much of this differentiation is closely connected to the query optimizer, the brain of a relational DBMS. Any improvement in how a DBMS processes queries -- such as better parallelism, a new kind of index or a new kind of data type -- must be understood by the optimizer, or the DBMS can't take advantage of it.
All optimizers are not created equal, and understanding what a particular one does and doesn't do gives a lot of insight into the capabilities of the overall DBMS. So to understand differences among DBMSs, it's useful to know a bit about how optimizers work.
For each query, the optimizer determines which indices and table columns should be read and joined, what kinds of joins should be used and in what order the joins should be performed. Modern query optimizers are all cost-based, i.e., the optimizer estimates the cost of each operation contained in each reasonable query path, adds them up and chooses the cheapest path. Costs are computed for I/O, in-memory processing and interprocessor communication, based on summary statistics about the distribution of specific values in the underlying data.
Unfortunately, these estimates aren't perfect, so optimizers often fail to find the best query plan. Database administrators then need to laboriously hand-optimize SQL code or optimizer parameters. In response,