The common commercial uses for relational database management systems are online transaction processing (OLTP) and data warehousing (DW). These approaches have different access patterns and make different demands on the underlying Database Engine 20.
The next topics examine the different access patterns of OLTP and DW processing to show how different the two styles of processing are.
OLTP
Consider the following simple example: A customer, Mr. Brown, orders a calendar. The graphic diagrams the actions taken against the database.
The transaction flow is outlined in the following process stages:
- A new order, 7326, is opened on the Order table.
- The remaining columns for the order are filled out, including the Customer number for Mr. Brown, 3, and the date the order was placed, April 5.
- A new shipping order, with order number 7326 and item number 3509, is opened on the Order Item Shipped table.
- The remaining columns for the shipper are filled out, including the quantity shipped, 1, and the date the order was shipped, April 5.
- The transaction is complete.
- A small number of the possible tables were accessed.
- None of the accessed tables, which may have billions of rows, were scanned.
- Little I/O processing was required to complete the transaction.
These three attributes are characteristic of OLTP environments. The requirements involve only a quick-in, quick-out approach to the database. Typically, relational database engines are designed specifically for maximum efficiency with such workloads.
Data Warehousing
Consider the following decision support example using the same database to emphasize the contrast in processing requirements: A business analyst must answer a question about pencil customers to target those customers for a promotion. The natural language query the analyst performs is: which customers placed the majority of their orders for pencils in the first quarter of this year? The graphic diagrams the actions taken against the database.
The processing flow is outlined in the following process stages:
- The Database Engine 20 interrogates the Item table for the item number for pencils, which is 3421.
- The engine next scans the Order Item Shipped table looking for all orders associated with item number 3421.
- The row examined in this example shipped 0 pencils, indicating that the order was back ordered.
- The engine accesses the Order Item Backordered table row having an order and item number matching that examined in stage 3 and finds that 144 pencils were back ordered.
- The engine must now locate the customer who placed this order because the whole point of the query was to identify customers who had ordered pencils in the first quarter of the year.
To do this, the engine must access the Order table to find that customer number 2 placed the order in question.
- The engine accesses the Customer table to identify the name of the customer associated with customer number 2, which is James.
- The large number of tables that had to be accessed to answer it.
This complex access path through multiple tables is a hallmark of decision support analysis. (This is an extremely simple example.)
- The tables were not only touched lightly in response to the query, but required massive searches, and multiple scans may be required.
- The data gathered to answer the query must be processed further using aggregation, joins, sorts, conditional requirements, and so on.
All this processing is heavily resource-intensive. The following table presents a summary contrast of DW and OLTP processing.
| OLTP Transaction | Attribute | DW Query |
|---|---|---|
| No | Multiple tables scanned | Yes |
| No | Large volumes of data examined | Yes |
| No | Processing-intensive | Yes |
| No | Response time is a function of database size | Yes |