Enabling Column‑Level In‑Memory in Oracle 12c: Step‑by‑Step Guide

Oracle 12c (12.1.0.2) introduces an In‑Memory option that stores enabled objects in the inmemory_size portion of the database cache. The feature can be turned on or off for various object levels, including Column, Table, Materialized View, Tablespace, and Partition.

Testing Column‑Level In‑Memory

To explore column‑level control, a test table is created (e.g., CREATE TABLE tab_col_im (c1 NUMBER, c2 NUMBER, c3 NUMBER);). The view V$IM_COLUMN_LEVEL is queried to confirm that no columns are currently using In‑Memory:

SELECT owner, table_name, column_name, inmemory FROM v$im_column_level WHERE table_name = 'TAB_COL_IM';

Attempting to enable In‑Memory directly on a column results in an error, indicating that column‑level activation is not permitted without first enabling the table.

Enabling In‑Memory at Table Level

Use the ALTER TABLE statement to activate In‑Memory for the entire table: ALTER TABLE tab_col_im INMEMORY; After this change, querying V$IM_COLUMN_LEVEL shows that columns C1, C2, and C3 are now marked as In‑Memory.

Controlling Column‑Level In‑Memory

Specific columns can be toggled with ALTER TABLE … MODIFY syntax, for example: ALTER TABLE tab_col_im MODIFY (c1 INMEMORY, c2 INMEMORY); Similarly, columns can be excluded by omitting the INMEMORY clause or using NO INMEMORY in the modification.

Impact on Execution Plans

Inserting data into tab_col_im and running queries demonstrates the performance effect. Queries that access In‑Memory columns generate execution plans containing TABLE ACCESS INMEMORY FULL and show significantly lower cost compared to non‑In‑Memory columns.

When a WHERE clause is present, the plan still leverages In‑Memory predicates, confirming that both full‑table scans and filtered queries benefit from the feature.

Conclusion

Oracle's In‑Memory capability is comprehensive, allowing administrators to enable it at multiple object levels. Enabling or disabling at the column level requires the table to be In‑Memory first, but once set, the feature transparently improves query performance without requiring application changes.