xxiv Separation of OLTP and Data Warehouse Databases
On the contrary, when the store manager runs a report once every month to
do a stock take and find out what and how much must be reordered, his
report reads all the stock records for the entire month. So what is the dis-
parity? Each sold item updates a single row. The report reads all the rows.
Let’s say the table is extremely large and the store is large and belongs to a
chain of stores all over the country, you have a very large database. Where
the single row update of each sale requires functionality to read individual
rows, the report wants to read everything. In terms of database performance
these two disparate requirements can cause serious conflicts. Data ware-
houses were invented to separate these two requirements, in effect separat-
ing active and historical data, attempting to remove some batch and
reporting activity from OLTP databases.
Note:
There are numerous names associated with data warehouses, such as
Inmon and Kimball. It is perhaps best not to throw names around or at
least to stop at associating them with any specific activity or invention.
Separation of OLTP and Data
Warehouse Databases
So why is there separation between these two types of databases? The
answer is actually very simple. An OLTP database requires fast turnaround
of exact row hits. A data warehouse database requires high throughput per-
formance for large amounts of data. In the old days of client server envi-
ronments, where applications were in-house within a single company only,
everyone went home at night and data warehouse batch updates and
reporting could be performed overnight. In the modern global economy of
the Internet and OLTP databases, end user operational applications are
required to be active 24/7, 365 days a year. Thats permanently! What it
means is that there is no window for any type of batch activity, because
when we are asleep in North America everyone is awake in the Far East,
and the global economy requires that those who are awake when we are
snoozing are serviced in the same manner. Thus, data warehouse activity
using historical data, be it updates to the data warehouse or reporting,
must be separated from the processing of OLTP, quick reaction concur-
rency requirements. A user will lose interest in a Web site after seven sec-
onds of inactivity.

Get Oracle Data Warehouse Tuning for 10g now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.