Databases Reference
In-Depth Information
The financial services industry is a leader in information technology for transaction
processing, so this notion of feeding legacy downstream applications is very common
in banks and insurance companies. For example, users often enter insurance claims into
frontend online systems. Once all the data has been entered, if the claim has been ap‐
proved, it's extracted and fed into legacy systems for further processing and payment.
Oracle features, such as transportable tablespaces (discussed in
Chapter 13
), are aimed
in part at providing the functionality required by distributed OLTP systems in a more
timely fashion than traditional batch jobs.
OLTP Versus Business Intelligence
Mixed workloads—OLTP and reporting—are the source of many performance chal‐
lenges and the topic of intense debate. The data warehousing industry had its genesis
in the realization that OLTP systems could not realistically provide the needed trans‐
action throughput while supporting the enormous amount of historical data and ad hoc
query workload that business analysts needed for things like multiyear trend analysis.
The issue isn't simply one of adequate machine horsepower; rather, it's the way data is
modeled, stored, and accessed, which is typically quite different. In OLTP, the design
centers on analyzing and automating business processes to provide consistent perfor‐
mance for a well-known set of transactions and users. The workload revolves around
large numbers of short and well-defined transactions—with a fairly significant per‐
centage of write transactions.
Business intelligence typically operates on larger data stores that frequently are assem‐
bled from multiple data sources and contain long histories. The schema design for data
warehouses is usually very different from the fully normalized design best suited for
OLTP data stores. And data warehouses can support ad hoc queries that, because of
their complexity and the amount of data accessed, can place significant loads on a system
with only a handful of requests.
Reporting and query functions are part of an OLTP system, but the scope and frequency
are typically more controlled than in a data warehouse environment. For example, a
banking OLTP system will include queries for customer status and account balances,
but potentially not multiyear transaction patterns.
An OLTP system typically provides forms that allow well-targeted queries that are ex‐
ecuted efficiently and don't consume undue resources. However, hard and fast rules—
for example, that OLTP systems don't include extensive query facilities—don't neces‐
sarily hold true. The I/O performed by most OLTP systems tends to be approximately
70-80 percent reads and 20-30 percent writes. Most transactions involve the querying
of data, such as product codes, customer names, account balances, inventory levels, and
so on. Users submitting tuned queries for specific business functions are a key part of
OLTP. Ad hoc queries across broad data sets are not.
