Oracle’s In-Memory Column Store (IMCS) has been in the database since 12c, but 23ai brings meaningful enhancements to this performance layer. If you use the In-Memory option, these changes deserve attention.
Automatic In-Memory (AIM) improvements:
Oracle 23ai refines the Automatic In-Memory feature — where Oracle automatically decides which segments to populate in the column store based on access patterns.
The 23ai version uses a more sophisticated heat map that accounts for:
- Query frequency AND recency
- Join column correlation (prefetching joined tables together)
- Current available In-Memory area utilization
-- Check AIM recommendations
SELECT segment_name, recommendation, reason
FROM v$im_segments_detail
WHERE imeu_size > 0
ORDER BY priority_score DESC;In-Memory Hybrid Scans:
23ai introduces Hybrid Scans — the optimizer can now split a query between the In-Memory column store and on-disk row store within the same scan, when only part of the table fits in memory. This avoids the all-or-nothing limitation of earlier versions.
In-Memory for JSON:
Columnar storage is now available for JSON document columns, allowing analytical queries on JSON fields to benefit from In-Memory performance without projecting out JSON to relational columns first.
ALTER TABLE events INMEMORY;
-- JSON field analytics now use IMCS automatically
SELECT data.event_type, COUNT(*)
FROM events
GROUP BY data.event_type;Monitoring In-Memory effectiveness:
SELECT table_name, inmemory_size, bytes_not_populated,
populate_status, ROUND(inmemory_size/1024/1024) AS size_mb
FROM v$im_segments
ORDER BY inmemory_size DESC;The IMCS continues to be one of Oracle’s most powerful performance tools. The 23ai enhancements make it smarter and more flexible.
