Differences Between OLTP and Data Warehouse Processing
The following table examines some of the principal differences in OLTP and Data Warehouse processing that are critical to database design.
| Attribute | Processing Type | |
|---|---|---|
| OLTP Transaction | Data Warehouse Query | |
| Multiple tables scanned? | No | Yes |
| Large data volumes? | No | Yes |
| Processing intensive? | No | Yes |
| Processing time a function of database size? | No | Yes |
As you can see from this side-by-side comparison, OLTP transactions and Data Warehouse queries are dramatically different entities and, by inference, might be expected to require physically different database support.
Data Warehousing Support
- Ad hoc SQL or natural language-generated SQL queries
- Repeatedly stated queries in the form of macros, embedded SQL applications, or stored procedures
- Data mining and OLAP explorations
The data in the warehouse is stored in the form of multiple normalized tables that model your enterprise. The decision support applications that explore these data often perform full file scans of multiple large tables, making them processing intensive. Because processing time is directly related to data volume, the responsiveness of the system is heavily affected by the size of the underlying database.
OLTP
OLTP transaction processing operates differently. OLTP systems do not scan multiple tables simultaneously, nor do they access large volumes of data. This makes their consumption of processing and I/O resources minuscule in comparison with decision support query processing. Size is not a factor in system responsiveness.