Differences between OLTP and Data Warehouse Processing
The following table examines 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 |
This comparison shows the dramatic difference between OLTP transactions and Data Warehouse queries, which may 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, which are 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 do not scan multiple tables simultaneously or 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.