IMCS Configuration

This chapter describes how to enable or disable IMCS, and set individual object for population into IMCS.

Enabling and Disabling In-Memory Column Store

IMCS is enabled and disabled by specifying the value of the INMEMORY_SIZE parameter.

INMEMORY_SIZE Parameter

The default value of the INMEMORY_SIZE parameter is 0, which means IMCS is disabled. To enable IMCS, you need to set the INMEMORY_SIZE parameter to at least 100 MB before starting the database. If the INMEMORY_SIZE parameter is set to a value that is smaller than 100 MB, it defaults to 0.

The INMEMORY_SIZE parameter cannot be dynamically modified. To modify the parameter, you need to restart the database.

1. Modify the INMEMORY_SIZE parameter value. Change the value of INMEMORY_SIZE in the configuration file (tip file).

INMEMORY_SIZE = 500M

1. Shut down the database.

2. Restart the database.

Enabling In-Memory Column Store

To enable IMCS, you need to restart the database.

1. Set the INMEMORY_SIZE parameter. Before starting the database, set INMEMORY_SIZE to at least 100M in the configuration file (tip file).

INMEMORY_SIZE = 100M

1. If the database is running, shut it down.

2. Restart the database.

Disabling In-Memory Column Store

To disable IMCS, you need to restart the database.

1. Set the INMEMORY_SIZE parameter. Before starting the database, set INMEMORY_SIZE to 0 in the configuration file (tip file) or delete the INMEMORY_SIZE parameter.

INMEMORY_SIZE = 0

#INMEMORY_SIZE = 500M

1. Shut down the database.

2. Restart the database.

---

Enabling Objects for Population into In-Memory Column Store

This section describes how to enable or disable individual objects for population into IMCS with priority and compression options.

In-Memory Population

In-Memory Population (Population) is a separate step that occurs when the database reads existing row format data from disk and converts it into columnar format, and then loads it into IMCS. Only tables, partitions and subpartitions can be populated in IMCS.

In-Memory Population Behaviour

Based on the priority option, population is enabled when starting up the database or when accessing an In-Memory object.

In-Memory Population Priority

Priority of population can be controlled by DDL statements that include an INMEMORY PRIORITY clause.

Population priority can be set for tables, partitions and subpartitions. Columns are not eligible for priority setting. Note that setting the In-Memory option for objects is just a pre-population task, and that it does not enable population by itself.

Any segment that is smaller than 64 KB cannot be populated. This means that there may exist some objects set as In-Memory that are still not eligible for population.

Behaviours of In-Memory population depending on the priority

Behaviour	Description
On-demand population	The default value of the INMEMORY PRIORITY option is NONE. In this case, population is enabled only when accessing the object through an In-Memory scan. Population is not possible if the object is not accessed, or accessed through an index scan or full table scan.
Priority-based population	If the INMEMORY PRIORITY option is set to a value other than NONE, population is enabled without access to the object. Population starts with the highest priority level. With the same priority level, the order is not assured. When there is no more space for IMCS, population stops.

PRIORITY Options

Options	Description
PRIORITY NONE	Population occurs only when accessing objects.
PRIORITY LOW	Population occurs whether or not objects are accessed. Objects with this level are populated after others with the following priority levels are completed. MEDIUM HIGH CRITICAL
PRIORITY MEDIUM	Population occurs whether or not objects are accessed. Objects with this level are populated after others with the following priority levels are completed. HIGH CRITICAL
PRIORITY HIGH	Population occurs whether or not objects are accessed. Objects with this level are populated after others with the following priority levels are completed. CRITICAL
PRIORITY CRITICAL	Population occurs whether or not objects are accessed. Objects with this level are populated first among all.

Example of PRIORITY Option setting

• CREATE TABLE statement

CREATE TABLE inmemory_test_priority 
        ( id	NUMBER(5) PRIMARY KEY,
            test_column VARCHAR2(15)) 
        INMEMORY PRIORITY HIGH;

• ALTER TABLE statement

ALTER TABLE inmemory_test_priority INMEMORY PRIORITY HIGH;

In-Memory Population Control

Using an INMEMORY clause in DDL statements, you can set the INMEMORY option for tablespaces, tables, partitions and subpartitions.

INMEMORY Clause

Basically, the INMEMORY clause can be specified only at the segment level. If it is specified at the column level, the range of available options will be limited. The column-level INMEMORY clause will be discussed later.

To set the INMEMORY option, specify an INMEMORY clause in the following statements.

• CREATE TABLESPACE or ALTER TABLESPACE If the INMEMORY option is specified on a tablespace, the option will apply to all tables created in the tablespace. If the INMEMORY option is specified on a table, it will be overridden by the tablespace. The INMEMORY option specified for the tablespace has control over its new tables only. Therefore, even if you use the INMEMORY option through the ALTER TABLESPACE statement, the option does not apply to already existing tables. Likewise, If you change the option to NO INMEMORY through ALTER TABLESPACE, existing tables that are already set to INMEMORY cannot switch to NO INMEMORY.

• CREATE TABLE or ALTER TABLE If you use the INMEMORY option for a table, non-virtual columns become eligible for population by default. You can selectively make specific columns eligible for population by using the column-level INMEMORY clause. For partitioned tables, you can specify the INMEMORY option for individual partitions. If the INMEMORY option is not specified, the option is inherited from the table level. If the INMEMORY option is specified, it will be overridden at the table level.

Setting a Table for IMCS

• CREATE TABLE statement

CREATE TABLE inmemory_test (
        id NUMBER(5) PRIMARY KEY,
        test_column VARCHAR2(15)) 
    INMEMORY;

• ALTER TABLE statement

ALTER TABLE inmemory_test INMEMORY;

Setting a Column for IMCS

You can make a specific column ineligible for In-Memory population by using the column-level INMEMORY clause.

• CREATE TABLE statement

CREATE TABLE inmemory_test (
                id NUMBER(5) PRIMARY KEY,
                test_column VARCHAR2(15), 
                no_inmemory_column VARCHAR2(20))
        INMEMORY
        NO INMEMORY (no_inmemory_column);

• ALTER TABLE statement

ALTER TABLE inmemory_test NO INMEMORY (no_inmemory_column);

Setting a Tablespace for IMCS

The INMEMORY clause at the tablespace level must be preceded by the DEFAULT clause.

• CREATE TABLESPACE statement

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

• ALTER TABLESPACE statement

LTER TABLESPACE inmemory_ts DEFAULT INMEMORY;