12c: Using In-Memory Column Store

The In-Memory Column Store (IM column store) is an optional portion of the system global area (SGA) that stores copies of tables, table partitions, and other database objects. In the IM column store, data is populated by column rather than row as it is in other parts of the SGA, and data is optimized for rapid scans, it does not replace the buffer cache, but acts as a supplement. The IM column store is included with the Oracle Database In-Memory option. To enable the IM column store, the INMEMORY_SIZE initialization parameter must be set to a non-zero value.

If it is enabled at the tablespace level, then all tables and materialized views in the tablespace are enabled for the IM column store by default. You can populate all of a database object's columns in the IM column store or a subset of the database object's columns. Similarly, for a partitioned table or materialized view, you can populate all of the partitions in the IM column store or a subset of the partitions.

When to use?

• A query that scans a large number of rows and applies filters that use operators such as the following: =, <, >, and IN
• A query that selects a small number of columns from a table or materialized view with a large number of columns, such as a query that selects five columns from a table with 100 columns
• A query that joins a small table to a large table
• A query that aggregates data

When not to use?

• Queries with complex predicates
• Queries that select a large number of columns
• Queries that return a large number of rows
• Queries with multiple large table joins

Where to enable?

You can enable it using INMEMORY clause in the following SQL statements

• CREATE TABLE
• ALTER TABLE
• CREATE TABLESPACE
• ALTER TABLESPACE
• CREATE MATERIALIZED VIEW
• ALTER MATERIALIZED VIEW

Additionally you have to set the initialization parameters related to the IM column store for which The default value is 0, means that the IM column store is not used. The minimum setting is 100M.

SQL> show parameter INMEMORY_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ----------
inmemory_size big integer 0

SQL> alter system set INMEMORY_SIZE=100M scope=spfile;
System altered.

Examples:

CREATE TABLE t( id NUMBER(5) PRIMARY KEY, test_col VARCHAR2(15)) INMEMORY;
ALTER TABLE scott.emp INMEMORY;

-- Disable a table for the IM column store
ALTER TABLE scott.emp NO INMEMORY;
--Tablespace with InMemory 

CREATE TABLESPACE tbs1 DATAFILE 'tbs1.dbf' SIZE 40M ONLINE DEFAULT INMEMORY;

IM and Datapump

You can import database objects that are enabled for the IM column store using the TRANSFORM=INMEMORY:y option of the impdp command. With this option, Data Pump keeps the IM column store clause for all objects that have one. When the TRANSFORM=INMEMORY:n option is specified, Data Pump drops the IM column store clause from all objects that have one.

How to monitor?

To determine which database objects are populated in the IM column store currently, run the following query on theV$IM_SEGMENTS view:

SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION FROM V$IM_SEGMENTS;

You can query the V$IM_COLUMN_LEVEL view to determine the selective column compression levels that are defined for a database object.