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

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:

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:

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:

SQL> SELECT table_name, inmemory_compression, inmemory_priority , inmemory_distribute FROM user_tables;

TECHDATABASKET     FOR QUERY LOW     NONE     AUTO

or

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

— Disable the TECHDATABASKET for the IM Column Store:

SQL> ALTER TABLE TECHDATABASKET  NO INMEMORY;

Table altered.

brunors

Hi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely in many others. I am super excited to share my interests in Databases, Cybersecurity, Cloud, Data Science, Data Engineering, Big Data, AI, Programming, Software Engineering, and data in general.
(Continue reading)