Enable or disable the Database In-Memory in Oracle 12c

In-Memory as per name data reside in memory instead of hard disk. This feature enables tables, partitions, materialized views be stored in memory using column format instead of traditional row format which delivers fast SQL processing. Its basically used in Analytics or OLAP.
Column Format A column format database stores each of the attributes of a record in a separate column-structure.
Row Format: A row format where each new record is represented as a new row in a table having multiple columns with each column representing a different attribute about that record.

We can enable Database In-Memory feature at following levels:
• Column
• Table
• Materialized view
• Tablespace
• Partition

Check whether its enabled or disabled for database
Note: 0 means disabled, no memory allocated for in-memory column area

SQL> show parameter inmemory_size

NAME            TYPE            VALUE
--------------- --------------- -------------
inmemory_size   big integer     0

-- If enabled, then check the present IN-Memory column segments
SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION FROM V$IM_SEGMENTS;


Configure or Enable the Database for IM Column Store

1. Set the parameter INMEMORY_SIZE initialization parameter must be set to a non-zero value.

ALTER SYSTEM SET INMEMORY_SIZE=1000M SCOPE=SPFILE;

Note: Minimum space required to parameter is 100M

2. Restart the database

Shutdown immediate;
startup


Enabling and Disabling Tables for the IM Column Store
You have option at create time or alter table if table already exists:

-- Enable at create time:
CREATE TABLE HR.SALARYEMP ( id NUMBER(5) PRIMARY KEY, SALARY Number(10,2)) INMEMORY;
-- Enable with ALTER command:
ALTER TABLE HR.SALARYEMP INMEMORY;
-- Disable with Alter command:
ALTER TABLE HR.SALARYEMP NO INMEMORY;

Note: By default MEMCOMPRESS FOR QUERY is used and PRIORITY NONE is used.

You can change the Compression and Priority methods by following commands:

ALTER TABLE HR.SALARYEMP INMEMORY MEMCOMPRESS FOR CAPACITY LOW;
ALTER TABLE HR.SALARYEMP INMEMORY PRIORITY HIGH;
ALTER TABLE HR.SALARYEMP INMEMORY MEMCOMPRESS FOR CAPACITY HIGH PRIORITY LOW;


Enable Column for a table for IM Column Store

ALTER TABLE HR.SALARYEMP INMEMORY MEMCOMPRESS FOR QUERY (
employee_id, employee_name, manager_id, salary, comm_pct)
INMEMORY MEMCOMPRESS FOR CAPACITY HIGH ( employee_address, employee_period, employee_status, employe_DOJ)
NO INMEMORY ( employee_grad, employee_grad);


Note:
–Starting columns with employee_id to comm_pct are using MEMCOMPRESS FOR QUERY compression method.
–Columns starting with employee_Address to DOJ are using MEMCOMPRESS FOR CAPACITY HIGH compression method.
–Last Columns employee_grad and employee_grad columns are not enabled for the IMcolumn store.
–PRIORITY NONE is used as default. it applied on table or table partition level not on column level.

Check the column compression level

select * from V$IM_COLUMN_LEVEL;


Enabling and Disabling Tablespaces for the IM Column Store

-- Enable at Create time
CREATE TABLESPACE users DATAFILE 'C:\oracle\oradata\users01.dbf' SIZE 100M ONLINE DEFAULT INMEMORY;
--Enable the existing tablespace
ALTER tablespace users INMEMORY;
-- Disable the tablespace for inmemory
alter tablespace users NO INMEMORY;


Enabling and Disabling Materialized Views for the IM Column Store

-- For enabled
ALTER MATERIALIZED VIEW sales.tran_mv INMEMORY PRIORITY HIGH;
--For Disable
ALTER MATERIALIZED VIEW sales.tran_mv NO INMEMORY;