On July 22nd 2014, Oracle corporation, announced Oracle Database 12c’s latest patch release 12.1.0.2. This latest patch release includes the new Oracle Database In-Memory functionality.
The Oracle Database In-Memory enables a single database to efficiently support mixed workloads. It uses a “dual-format” architecture, that retains the Record-Setting OLTP performance of the oracle databases, while simultaneously supporting real-time analytics and reporting. This is achieved by retaining the traditional oracle memory architecture, but adding a new purely in-memory column format (Automatically created and maintained by oracle), optimized for analytical processing.
So now you have the data stored in the Oracle database in your database files, in a row format, and for any of the objects marked as INMEMORY, oracle creates an In-Memory column store, where the data resides in a column format.The IM column store does not replace the buffer cache, but acts as a supplement, so that data can now be stored in memory in both a row and a column format.
Ok why the dual format, one would ask ?
The Row format is retained as is, so that there is no compromise/degradation in the OLTP performance of the database. In a Row format database each row is made up of multiple columns, with each column representing an attribute about that record. A column format database stores each of the attributes about the transaction in a separate column structure. A column format is ideal for Analytics, but is not very efficient in processing DML requests like insert, update and deletes (Which operates on the whole row). Oracle Database In-Memory (Database In-Memory) provides the best of both worlds by allowing data to be simultaneously populated in both an in-memory row format (the buffer cache) and a new in-memory column format (The In-Memory Store).
The picture above shows the In-Memory area in the SGA
The above picture shows an example of the Column Store.
No changes are necessary to your existing applications in order to take advantage of the Oracle Database In-Memory option. Any query that will benefit from the In-Memory column store will be automatically directed there, by the Optimizer. The In-Memory store is kept transactionally consistent with the buffer cache. There are numerous optimizations that have been implemented that speed up this data access in the In-Memory store. All the database functionality that Oracle has built over the last 30 years, continues to work in this new version.
It is normal that folks would go out and compare, Oracle Databaes In-Memory, with other In Memory Database products available in the Market today. So let us explore the differences with some of those products next.
TimesTen
Oracle TimesTen In-Memory Database is a,row oriented, memory optimized relational database, that resides entirely in the physical memory of a server. Oracle TimesTen In-Memory Database operates on databases that fit entirely in physical memory using standard SQL interfaces. In contrast, the Oracle Database In-Memory, only stores selected objects, in memory, in a column format (As opposed to a row format in Oracle TimesTen), the remaining objects continue to exist on the storage subsystem.
SAP HANA
SAP HANA is an in-memory, column-oriented relational database. So the entire database has to reside in physical memory of the server. As mentioned earlier in this article, OLTP transactions have some disadvantages while operating on column stores.
Todays databases can be in the 10’s or 100’s of TeraBytes. Storing this entire data in physical memory can be an expensive proposition, not to mention that, it is quite prevalent that ,only a small subset of this data, ends up having daily usage. This is where Oracle’s approach of storing only selected objects in memory, has significant benefits.
