Relational databases typically are optimized for online transaction processing (OLTP). OLTP systems are designed to meet the day-to-day operational needs of the business, and the database performance is tuned for those operational needs. Consequently, the database can retrieve a small number of records quickly, but it can be slow if you need to retrieve a large number of records and summarize data on the fly. Some potential disadvantages of OLTP systems are as follows:
In contrast, a dimensional database is designed and tuned to support the analysis of business trends and projections. This type of informational processing is known as online analytical processing (OLAP) or decision-support processing. OLAP is also the term that database designers use to describe a dimensional approach to informational processing.
A dimensional database is optimized for data retrieval and analysis. Any new data that you load into the database is usually updated in batch, often from multiple sources. Whereas OLTP systems tend to organize data around specific processes (such as order entry), a dimensional database tends to be subject oriented and aims to answer questions such as, "What products are selling well?" "At what time of year do products sell best?" "In what regions are sales weakest?"
The following table summarizes the key differences between OLTP and OLAP databases.
| Relational Database (OLTP) | Dimensional Database (OLAP) |
|---|---|
| Data is atomized | Data is summarized |
| Data is current | Data is historical |
| Processes one record at a time | Processes many records at a time |
| Process oriented | Subject oriented |
| Designed for highly structured repetitive processing | Designed for highly unstructured analytical processing |
Many of the problems that businesses attempt to solve with relational technology are multidimensional in nature. For example, SQL queries that create summaries of product sales by region, region sales by product, and so on, might require hours of processing on a traditional relational database. However, a dimensional database could process the same queries in a fraction of the time.
Besides the characteristic schema design differences between OLTP and OLAP databases that this chapter discusses, the query optimizer typically should be tuned differently for these two types of tasks. For example, in OLTP operations, the OPTCOMPIND setting (as specified by the environment variable or by the configuration parameter of that name) should typically be set to zero, to support nested-loop joins. OLAP operations, in contrast, tend to be more efficient with an OPTCOMPIND setting of 2 to support cost-based query plans. See the IBM Informix: Guide to SQL Reference and the IBM Informix: Administrator's Reference for more information about the OPTCOMPIND environment variable and the OPTCOMPIND configuration parameter respectively. See the IBM Informix: Performance Guide for additional information about OPTCOMPIND, join methods, and the query optimizer.
(Dynamic Server also supports the SET ENVIRONMENT OPTCOMPIND statement to change OPTCOMPIND setting dynamically during sessions in which both OLTP and OLAP operations are required. See the IBM Informix: Guide to SQL Syntax for more information about the SET ENVIRONMENT statement of SQL.)
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]