Oracle 26ai Manual In-Memory Configuration: INMEMORY Clause Deep Dive, Compression, and Column-Level Control

Why Manual Configuration Still Matters

Are you relying only on Automatic In-Memory and not getting expected performance?

Automation is useful, but in real production environments, DBAs often need precise control over:

• Which tables go into memory

• Which columns are optimized

• How compression is applied

Oracle 26ai manual In-Memory configuration allows you to fine-tune performance using the INMEMORY clause.

What Is the INMEMORY Clause in Oracle 26ai

The INMEMORY clause is used to mark database objects for In-Memory Column Store.

It can be applied at multiple levels:

• Table

• Partition

• Column

• Tablespace

• Materialized view

Basic Table-Level Configuration

Enable Table for In-Memory

ALTER TABLE sales INMEMORY;

Disable Table

ALTER TABLE sales NO INMEMORY;

Column-Level Control (Advanced Optimization)

You do not need to load all columns into memory.

Enable Selected Columns

ALTER TABLE sales INMEMORY (product_id, amount_sold);

Exclude Specific Columns

ALTER TABLE sales INMEMORY (product_id, amount_sold)
NO INMEMORY (description, comments);

Why Column-Level Control Matters

• Reduces memory usage

• Improves scan speed

• Avoids unnecessary data loading

Partition-Level Configuration

For large tables, partition-level tuning is essential.

Enable Partition

ALTER TABLE sales MODIFY PARTITION p2024 INMEMORY;

Mixed Configuration

ALTER TABLE sales
MODIFY PARTITION p2023 NO INMEMORY,
MODIFY PARTITION p2024 INMEMORY;

Use Case

• Recent data → INMEMORY

• Historical data → NO INMEMORY

Compression Strategies (Critical for Performance)

Compression directly impacts:

• Memory usage

• Query speed

Set Compression

ALTER TABLE sales INMEMORY MEMCOMPRESS FOR QUERY LOW;

Compression Options

Example with Compression

ALTER TABLE sales
INMEMORY MEMCOMPRESS FOR CAPACITY HIGH;

Priority Control (Population Order)

Oracle allows you to control which objects load first.

Set Priority

ALTER TABLE sales INMEMORY PRIORITY CRITICAL;

Priority Levels

• CRITICAL

• HIGH

• MEDIUM

• LOW

• NONE

Behavior

• CRITICAL → loaded immediately

• NONE → loaded on access

INMEMORY with Tablespaces

You can apply In-Memory at tablespace level.

Example

ALTER TABLESPACE users INMEMORY;

Benefit

• Automatically applies to all objects

• Simplifies configuration

INMEMORY for Materialized Views

Example

CREATE MATERIALIZED VIEW sales_mv
INMEMORY
AS SELECT * FROM sales;

Use Case

• Precomputed analytics

• Faster reporting

Manual Population Techniques

If priority is NONE, manual population is required.

Populate Table

EXEC DBMS_INMEMORY.POPULATE('SALES');

Wait for Population

EXEC DBMS_INMEMORY_ADMIN.POPULATE_WAIT('SALES');

Monitoring Configuration

Check In-Memory Objects

SELECT segment_name, inmemory, inmemory_compression
FROM v$im_segments;

Check Column-Level Settings

SELECT table_name, column_name, inmemory_compression
FROM v$im_column_level;

Advanced Use Case: Hybrid Configuration

Example

ALTER TABLE sales
INMEMORY (product_id, amount_sold)
NO INMEMORY (comments)
MEMCOMPRESS FOR QUERY LOW
PRIORITY HIGH;

What This Does

• Loads only required columns

• Excludes large text data

• Uses fast compression

• Prioritizes loading

Common Mistakes

Loading Entire Table

• Wastes memory

• Reduces efficiency

Wrong Compression Choice

• Too high → slower queries

• Too low → memory waste

Ignoring Column Selection

• Loads unnecessary data

Best Practices

• Use column-level configuration

• Combine partition + compression strategies

• Use priority wisely

• Monitor memory usage regularly

• Test configurations before production

Conclusion

Oracle 26ai INMEMORY clause provides full control over how data is stored and accessed in the In-Memory Column Store.

By carefully selecting:

• Tables

• Columns

• Compression levels

• Priorities

You can significantly improve performance while optimizing memory usage.

Manual configuration is essential for achieving maximum efficiency in real-world systems.

Next Article

How In-Memory Scans Work: SIMD, Column Pruning, and Vector Processing