The difference between OLTP and OLAP

Online transaction Processing OLTP (on-line transaction processing) primarily performs basic, daily transaction processing , such as the increase, deletion, modification, and checking of database records . For example, in a bank to access a payment, is a transaction transaction. The features of OLTP typically include:

1. High real-time requirements;

2. The amount of data is not very large;

3. Transactions are generally deterministic, so OLTP is access to definitive data (e.g., deposit and withdrawal with a specific amount)

4. Concurrency requirements are high and strict requirements of the integrity of the transaction, security. (such as this: it is possible for you and your family to take the same account at different banks),

Online Analytical Processing OLAP (On-line Analytical Processing) is the main application of the Data Warehouse system, supporting complex analysis operations, focusing on decision support, and providing intuitive and understandable query results . A typical application is a complex, dynamic reporting system. Typical features of OLAP are:

1. Real-time requirements are not very high, many applications are updated daily data;

2. The amount of data is large, because OLAP supports dynamic query, so the user may have to get a lot of data after the statistics to obtain the information they want to know, such as time series analysis, etc., so the amount of data processed is very large;

3. Because the focus is on decision support, the query is generally dynamic, which means allowing users to ask for queries at any time. So in OLAP, an important concept of "dimension" to build a dynamic query platform (or technology) for users to decide what information they need to know.

The following is a general distinction:

OLTP OLAP

User operators, low-level managers, senior managers

function daily operation processing analysis decision

DB Design for application oriented theme

Data current, latest detail, two-dimensional discrete historical, aggregated, multidimensional integrated, unified

Access read/write dozens of records read millions record

Work unit simple Transaction Complex query

The number of users thousands of hundreds of

DB Size 100MB-GB 100GB-TB

These concepts (or techniques) are application-level, and this is irrelevant to what database system to use. Interested friends if you are willing to understand the specific application of OLAP and do not want to spend too much effort, it is advisable to go to the scientific research, education, culture and health system to see, they usually have a statistical analysis software called SPSS, which is a and SQL Server at the same level of the world-class, The most authoritative statistical analysis software, its menu has an "analysis" project, its drop-down of the first project is OLAP, of course, you can also install a try.

To put it simply:

OLTP is online transaction processing, that is, we often say that the relational database, that is, the recording of instant increase, deletion, change, check, is what we often apply, this is the basis of the database;TPCC (Transaction Processing Performance Council ) belongs to this class .

OLAP is the core of data warehouse, the so-called Data Warehouse is a kind of analytic database for a large number of data which has been formed by OLTP, which is used to deal with important decision information such as business intelligence, decision support and so on. The Data warehouse is the processing and analysis of the historical data after the database is applied to certain program. Read more, update less, tpch belongs to this class, for OLAP, Columnstore mode may be more advantageous than the usual row storage mode.

The difference between OLTP and OLAP