Enabling and disabling table for the IM column store in Oracle 12c

brunors

In order to Enable or Disable the IM Column Store for Tables, your database must be with the Oracle Database In-Memory (Database In-Memory) option enabled.
If not, see this post : https://www.techdatabasket.com/enabling-oracle-database-in-memory-database-in-memory-option-in-oracle-database-12c-release-1/

According to Oracle, “You enable a table for the IM column store by including an INMEMORY clause in a CREATE TABLE or ALTER TABLE statement. You disable a table for the IM column store by including a NO INMEMORY clause in a CREATE TABLE or ALTER TABLE statement.”.

Then, in this post I am going to show you how to setup the table INMEMORY and the option levels:

— Example about how to create a Table and enable it for the IM column store:

?
1
2
3
4
CREATE TABLE techdatabasket (
id        NUMBER(5) PRIMARY KEY,
blog_type  VARCHAR2(15))
INMEMORY;

— There are several priorities and each one belongs to a determined SQL-Key as below:

?
1
2
3
4
5
ALTER TABLE techdatabasket INMEMORY PRIORITY NONE;
ALTER TABLE techdatabasket INMEMORY PRIORITY HIGH;
ALTER TABLE techdatabasket INMEMORY PRIORITY LOW;
ALTER TABLE techdatabasket INMEMORY PRIORITY MEDIUM;
ALTER TABLE techdatabasket INMEMORY PRIORITY CRITICAL;

Each priority determines how Oracle will manage and populate the datas of the object in the IM Column Store.

You can easily check whether your table are IN-MEMORY using the following select:

?
1
2
3
SQL> SELECT table_name, inmemory_compression, inmemory_priority , inmemory_distribute FROM user_tables;
 
TECHDATABASKET     FOR QUERY LOW     NONE     AUTO

or

?
1
2
SELECT OWNER, SEGMENT_NAME, INMEMORY_PRIORITY, INMEMORY_COMPRESSION
FROM V$IM_SEGMENTS;

— Disable the TECHDATABASKET for the IM Column Store:

?
1
2
3
SQL> ALTER TABLE TECHDATABASKET  NO INMEMORY;
 
Table altered.

Related posts

Leave a Comment