Growing with Linux intelligence

1. Intelligent query optimization

Query optimization improves the performance of a relational database, especially for executing complex SQL statements. A query optimizer determines the best strategy for performing each query.

It chooses, for example, whether or not to use indexes for a given query and which join techniques to use when joining multiple tables. These decisions have a tremendous effect on SQL performance. The query optimizer is entirely transparent to the application and end users.

2. Materialized views

Materialized views were first introduced in Oracle8i. The materialized view can be thought of as a special kind of view that physically exists inside the database.

It can contain joins and aggregates, improving query execution time by precalculating expensive joins and aggregation operations prior to execution.

3. Bitmapped indexing

Bitmapped indexes provide significant savings in index creation time and the space required to store indexes. Oracle's patented bitmap indexes are widely used, particularly in data warehouse applications.

Whereas other database vendors provide "dynamic" bitmap indexes, Oracle supports real bitmap indexes (in addition to dynamic bitmap indexes).
Real bitmap indexes are index structures in which the compressed bitmap representation of the index is stored in the database, whereas dynamic bitmap indexes convert B-tree index structures in the database into bitmap structures during query processing.

Real bitmap indexes provide larger space savings than regular B-tree indexes. These space savings also translate to performance benefits in the form of fewer disk I/Os.

4. Data partitioning

Partitioning is an important feature for improving complex query performance and easing the management of large volumes of data. Oracle Partitioning offers a choice of range, hash, and list partitioning mechanisms to meet different requirements.

For example, range partitioning is a very useful partitioning method where "rolling windows" of data are common. Range partitioning on date ranges can have enormous benefits for the load/drop cycle in a data warehouse, in terms of both efficiency and manageability.

MLT Vacations' data warehouse has played a key role in helping the company determine how to cut costs during slow times and is also essential for forecasting and tracking trends in inventory, pricing, and profitability.

"We had a couple of years where we were focused on controlling expenses and rightsizing our business," MLT's Kress notes. "Now we're definitely looking ahead to how we can grow the business. Our data warehouse has become a key part of our success."

"Our daily flash report gives managers a snapshot of what yesterday, last week, and last month looked like compared to our budget or compared to last year," Kress continues.

"We have inventory reports that show how things are booking this period, compared to last period. I just don't see how we could deliver that information in a timely manner if we didn't have the data warehouse."

To improve query performance and support larger systems, Oracle works closely with OS vendors to recommend changes to the Linux kernel, such as the ability to process database requests asynchronously in the I/O subsystem, support for very large memory capabilities, and the ability to exploit 64-bit architectures to run Linux applications.

These capabilities will become progressively more important as Linux customers develop larger data warehouses for greater numbers of users running queries.

"A data warehouse occupying a terabyte of storage—a rarity a few years ago—is now quite common," says Richard Winter, president of the Waltham, Massachusetts-based Winter Corporation, a consulting firm that specializes in the database industry.

Many Oracle customers now have multiterabyte warehouses deployed on clustered Linux platforms. In one instance, a retail customer runs a 16-node data warehouse built with Oracle Real Application Clusters on affordable Intel Xeon machines, low-cost storage arrays, and the Red Hat Enterprise Linux operating system.

The data warehouse is more than 23 terabytes in size and continues to double each year in both size and query volume.