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, 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)