Manage the In-Memory Column Store in Oracle 12c release 1(12.1.0.2)

Manage the In-Memory Column Store in Oracle 12c release 1(12.1.0.2)

In-Memory Column Store is optional part of SGA Memory area which stores tables and tables partition.
In-Memory column stores data in formed of column rather than row as traditional used and data is optimized for better performance.
The IM column store is a pool in the SGA.

Enable it at following levels:
• Column
• Table
• Materialized view
• Tablespace
• Partition

Check the present IN-Memory column

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

Configure 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
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;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.