Enabling and Disabling Tables for the In-Memory Column Store

View All Scripts Login to Run Script

Script Name Enabling and Disabling Tables for the In-Memory Column Store
Description The In-Memory Column Store (IM column store) is an optional portion of the system global area (SGA) that stores copies of tables, table partitions, and other database objects. In the IM column store, data is populated by column rather than row as it is in other parts of the SGA, and data is optimized for rapid scans. This script enables the IM column store for a table with various options and then disables IM column store for the table. This example assumes that the IM column store is enabled for the current database.
Area SQL General
Referenced In Database Administrator's Guide
Contributor Oracle
Created Monday October 05, 2015

Statement 1
This statement creates the test_inmem table and enables it for the IM column store.
Creating A Table And Enabling It for The Im Column Store
```
CREATE TABLE test_inmem (  
     id        NUMBER(5) PRIMARY KEY,  
     test_col  VARCHAR2(15))  
  INMEMORY
```
```
Table created.
```
Statement 2
This query verifies the test_inmem table is enabled for the IM column store. The defaults are used for priority and compression. The priority level determines where the table is placed in the queue for population of its data in the IM column store. The compression setting determines the data compression method used for the table's data in the IM column store.
Verifying That the Table Is Enabled for the IM Column Store
```
SELECT TABLE_NAME, INMEMORY, INMEMORY_PRIORITY, INMEMORY_COMPRESSION FROM USER_TABLES
```
```
TABLE_NAME INMEMORY INMEMORY_PRIORITY INMEMORY_COMPRESSION
TEST_INMEM ENABLED NONE FOR QUERY LOW
```
Statement 3
This statement enables the test_inmem table for the IM column store and specifies the compression method FOR CAPACITY LOW. This statement uses the default for the PRIORITY clause. Therefore, PRIORITY NONE is used.
Enabling A Table For the IM Column Store With FOR CAPACITY LOW Compression
```
ALTER TABLE test_inmem INMEMORY MEMCOMPRESS FOR CAPACITY LOW
```
```
Table altered.
```
Statement 4
This query verifies that the test_inmem table is enabled for the IM column store with FOR CAPACITY LOW compression.
Verifying That the Table Is Enabled for the IM Column Store With FOR CAPACITY LOW Compression
```
SELECT TABLE_NAME, INMEMORY, INMEMORY_PRIORITY, INMEMORY_COMPRESSION FROM USER_TABLES
```
```
TABLE_NAME INMEMORY INMEMORY_PRIORITY INMEMORY_COMPRESSION
TEST_INMEM ENABLED NONE FOR CAPACITY LOW
```
Statement 5
This statement enables the test_inmem table for the IM column store and specifies PRIORITY HIGH for populating the table data in memory. This statement uses the default for the MEMCOMPRESS clause. Therefore, MEMCOMPRESS FOR QUERY is used.
Enabling A Table For the IM Column Store With HIGH Data Population Priority
```
ALTER TABLE test_inmem INMEMORY PRIORITY HIGH
```
```
Table altered.
```
Statement 6
This query verifies that the test_inmem table is enabled for the IM column store with PRIORITY HIGH for populating the table data in memory and FOR QUERY HIGH LOW compression.
Verifying That the Table Is Enabled for the IM Column Store With HIGH Data Population Priority
```
SELECT TABLE_NAME, INMEMORY, INMEMORY_PRIORITY, INMEMORY_COMPRESSION FROM USER_TABLES
```
```
TABLE_NAME INMEMORY INMEMORY_PRIORITY INMEMORY_COMPRESSION
TEST_INMEM ENABLED HIGH FOR CAPACITY LOW
```
Statement 7
This statement enables the test_inmem table for the IM column store and specifies FOR CAPACITY HIGH table compression and PRIORITY LOW for populating the table data in memory.
Enabling a Table for the IM Column Store With FOR CAPACITY HIGH Compression and LOW Data Population Priority
```
ALTER TABLE test_inmem INMEMORY  
  MEMCOMPRESS FOR CAPACITY HIGH  
  PRIORITY LOW
```
```
Table altered.
```
Statement 8
This query verifies that the test_inmem table is enabled for the IM column store with FOR CAPACITY HIGH table compression and PRIORITY LOW for populating the table data in memory.
Verifying That the Table Is Enabled for the IM Column Store With FOR CAPACITY HIGH Compression and LOW Data Population Priority
```
SELECT TABLE_NAME, INMEMORY, INMEMORY_PRIORITY, INMEMORY_COMPRESSION FROM USER_TABLES
```
```
TABLE_NAME INMEMORY INMEMORY_PRIORITY INMEMORY_COMPRESSION
TEST_INMEM ENABLED LOW FOR CAPACITY HIGH
```
Statement 9
This statement disables the test_inmem table for the IM column store.
Disabling a Table for the IM Column Store
```
ALTER TABLE test_inmem NO INMEMORY
```
```
Table altered.
```

TABLE_NAME	INMEMORY	INMEMORY_PRIORITY	INMEMORY_COMPRESSION
TEST_INMEM	ENABLED	NONE	FOR QUERY LOW

TABLE_NAME	INMEMORY	INMEMORY_PRIORITY	INMEMORY_COMPRESSION
TEST_INMEM	ENABLED	NONE	FOR CAPACITY LOW

TABLE_NAME	INMEMORY	INMEMORY_PRIORITY	INMEMORY_COMPRESSION
TEST_INMEM	ENABLED	HIGH	FOR CAPACITY LOW

TABLE_NAME	INMEMORY	INMEMORY_PRIORITY	INMEMORY_COMPRESSION
TEST_INMEM	ENABLED	LOW	FOR CAPACITY HIGH

Statement 10

This query verifies the test_inmem table is disabled for the IM column store.

Verifying That the Table Is Disabled for the IM Column Store

SELECT TABLE_NAME, INMEMORY, INMEMORY_PRIORITY, INMEMORY_COMPRESSION FROM USER_TABLES


TABLE_NAME INMEMORY INMEMORY_PRIORITY INMEMORY_COMPRESSION
TEST_INMEM DISABLED  -  -

TABLE_NAME	INMEMORY	INMEMORY_PRIORITY	INMEMORY_COMPRESSION
TEST_INMEM	DISABLED	-	-

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database