The In-Memory Column Store feature enables objects (tables, partitions, and other types) to
be stored in memory in a new format known as the columnar format. This format enables
scans, joins, and aggregates to perform much faster than the traditional on-disk format
The In-Memory Column Store (IM column store) was the headline feature of the 12.1.0.2 patchset. The advantage of having data in memory is obvious, but the columnar storage lends itself extremely well to analytic queries found in business intelligence products.
The in-memory columnar format does not replace the on-disk or buffer cache format. It is a
consistent copy of a table or of some columns of a table converted to the new columnar
format that is independent of the disk format and only available in memory. Because of this
independence, applications are able to transparently use this option without any changes. For
the data to be converted into the new columnar format, a new pool is requested in the SGA.
The pool is the IM column store.
If sufficient space is allocated for the IM column store, a query that accesses objects that are
candidates to be populated into the IM column store performs much faster. The improved
performance allows ad hoc analytic queries to be executed directly on the real-time
transaction data without impacting the existing workload. It is not a replacement of buffer cache, but acts as a supplement so that both memory areas store the same data in different formats.
The key point of Oracle In-Memory is not “What to cache” but “How”. So the major difference of Oracle In-Memory Column Store is that it enables individual database segments to be loaded into memory in the compressed columnar format. This technique enables segment scans to perform much faster than the traditional on-disk formats, providing performance boost for analytical and reporting workload.
Assuming the COMPATIBLE parameter is set to 12.1.0 or higher and there is enough room in the SGA to hold the IM column store, the following process will enable the IM column store.
ALTER SYSTEM SET SGA_TARGET=3G SCOPE=SPFILE;
ALTER SYSTEM SET INMEMORY_SIZE=2G SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
ORACLE instance started.
Total System Global Area 3221225472 bytes
Fixed Size 2929552 bytes
Variable Size 419433584 bytes
Database Buffers 637534208 bytes
Redo Buffers 13844480 bytes
In-Memory Area 2147483648 bytes
Database mounted.
Database opened.The CREATE TABLE and ALTER TABLE commands have been amended to allow you to determine if the table should be stored in the IM column store.
CREATE TABLE im_tab (
id NUMBER
) INMEMORY;
ALTER TABLE DEFAULT_TAB INMEMORY PRIORITY HIGH;