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.
Hi! I am Bruno, a Brazilian born and bred. Former Oracle ACE, Computer Scientist, MSc in Data Science, over ten years of experience in companies such as IBM, Epico Tech, and Playtech based in three different countries (Brazil, Hungary, and Sweden) and joined projects remotely in many others. I am super excited to show you my interest in Databases, Cloud, Data Science, Data Engineering, Bigdata, AI, Programming, Software Engineering, and data in general.
(Continue reading)