Comparing In-Memory Technology in Oracle, IBM and SQL Server – Part II

comparison of In-Memory technology in a variety of databases examined the technology in Oracle’s databases and also compared it with SQL Server. Here are some other features we’ll look at.

In-Memory Columnstore feature in Oracle 12c

Oracle Corporation offers In-Memory Columnstore technology as an optional add-on with Oracle Database 12c for users. If this option is enabled for tables or table spaces, Oracle generates a copy of the subset of the data in the caching format as columnar storage. Thus, database administrators can only select the In-Memory data, which is very important in operational terms, and accelerate the analysis process. The process of changing the in-memory data does not always continue and should be restarted when the Oracle DB is restarted. The Oracle In-Memory design is like a Transparent for applications and tools.

Dual Oracle Dual Architecture

Compare Oracle 12c and SQL Server In-Memory Columnstore

In the table below, we will compare the in-memory features of Oracle12c and SQL Server.

Property Oracle 12c SQL Server 2014 SQL Server 2016
Continuity of Columnstore No Yes Yes
Aggregate Pushdown Yes No Yes
Search for future versions No No Yes
Practical Approaches toColumnstore Yes No No
Integrate with In-Memory OLTP No No Yes
Batch processing No Yes Yes
Integration with R Yes No Yes
Configuration and operations Provides a large number of Knobsthat make the configuration and operation more complicated. Full automation after the Index ofColumnstore Full automation after the Index of Columnstore , including deleted records management

In-Memory Table  : Compare Oracle 12c and SQL Server

Continuity of Column store

Data Warehouses are usually large and one can not expect all data to be in-memory under different conditions. If the Oracle database is restarted, there are no data in the Columnstore Index and data is created in Background. Therefore, Query analysis cannot use Columnar Storage. SQL Server can maintain the Column store Index, and therefore there is no need to rebuild it or prepare Memory to maintain in-memory. The data in SQL Server is entered and logged on the basis of queries being executed.

Aggregate Pushdown

This feature is referred to as pre-computed aggregation operations (such as aggregation, averaging, etc.) for database values ​​and entered into a storage layer. It should be noted that Oracle 12c supports Aggregate Pushdown functionality. In SQL Server 2014, compilation operations are performed outside the storage engine and in the implementation layer. In the next version of SQL Server, this feature is supposed to be transferred to the storage layer, improving performance by up to four or more.

Query operation in later versions

In later versions of SQL Server, users can query the data warehouse in secondary versions. Oracle has a similar configuration to AlwaysOn , but it does not allow users to run Query packages on secondary versions. This is a major advantage for SQL Server, while data warehousing workloads are operationally important and configured for high availability.

Practical Approaches to Columnstore

This view has specific uses. If the user is able to create a practical view of the OLTP table and store this view in the Columnstore format, it ultimately leads to a cube-like function. There are also pre-aggregated data, such as OLTP, that are kept up-to-date. In any case, the maintenance cost is very high, and although it is worth it, it will hinder the data load and OLTP performance.

Integrate with In-Memory OLTP

Currently, SQL Server 2014 and Oracle 12c do not support Integration with In-Memory OLTP. Although the Columnstore Index and OLTP workloads are integrated into Transparent, SQL Server 2016. To do this, a Columnstore Index is created in one or more tables in OLTP. Since Columnstore is updated, it allows users to run OLTP workloads in the table and execute the Query process in the same table.

Hand Processing or Batch Mode Processing

Hands-on processing is one of the other benefits of SQL Server 2014, which generally improves Query performance by about 2-4 times. It should be noted that Oracle has not yet had such technology.

Related posts