How to use In-Memory Columnar Caching in Oracle Exadata

Leave a reply

Oracle Exadata has a special feature called In-Memory Columnar Caching, which boosts query performance by storing data in a columnar format inside the Exadata Smart Flash Cache. This makes analytical queries faster because the database can read only the needed columns instead of scanning entire rows.

What is Columnar Cache?

  • A section of Exadata Smart Flash Cache reserved for storing data in columnar format.
  • Managed automatically by Oracle Database—no manual setup required.
  • Available only if you have the Oracle Database In-Memory license.

How to Enable It

You can enable In-Memory Columnar Caching with database parameters:

Set INMEMORY_SIZE > 0

    ALTER SYSTEM SET INMEMORY_SIZE = 2G;

    Use INMEMORY_FORCE (from Oracle 19.8 onwards)

    ALTER SYSTEM SET INMEMORY_FORCE = cellmemory_level;

    Controlling Behavior with CELLMEMORY

    You can override default caching behavior for specific tables or segments using the CELLMEMORY option.

    Options:

    • NO CELLMEMORY
      • Excludes a table from columnar caching.
    ALTER TABLE employees NO CELLMEMORY;

    CELLMEMORY MEMCOMPRESS FOR CAPACITY

    • Stores table in compressed In-Memory format (saves space).
    ALTER TABLE sales CELLMEMORY MEMCOMPRESS FOR CAPACITY;

    CELLMEMORY MEMCOMPRESS FOR QUERY

    • Less compression, faster queries (uses more flash space).
    ALTER TABLE orders CELLMEMORY MEMCOMPRESS FOR QUERY;
    • LOW / HIGH: Not implemented yet.

    📌 Using INMEMORY and CELLMEMORY Together

    You can combine both options for flexibility:

    CREATE TABLE t (c1 NUMBER) 
    INMEMORY CELLMEMORY MEMCOMPRESS FOR QUERY;

    This is useful for low-priority tables that may not load into memory but can still benefit from columnar caching in flash.

    CELLMEMORY Cheat Sheet (Oracle Exadata)

    OptionPurposeSpace UsagePerformance ImpactExample Command
    NO CELLMEMORYExcludes table/segment from columnar caching.No flash space used.No columnar performance benefits.ALTER TABLE employees NO CELLMEMORY;
    CELLMEMORY MEMCOMPRESS FOR CAPACITYStores data in compressed In-Memory format (saves space).Less flash space required.Good performance, optimized for storage.ALTER TABLE sales CELLMEMORY MEMCOMPRESS FOR CAPACITY;
    CELLMEMORY MEMCOMPRESS FOR QUERYStores data with less compression for faster queries.Almost double flash space used.Higher query speed, better analytics.ALTER TABLE orders CELLMEMORY MEMCOMPRESS FOR QUERY;
    LOW / HIGHPlaceholder options (not implemented yet).N/AN/AN/A

    ✅ Best Practices

    • Use MEMCOMPRESS FOR CAPACITY for large tables where space savings matter.
    • Use MEMCOMPRESS FOR QUERY for performance-critical tables.
    • Apply NO CELLMEMORY to rarely accessed data to save cache space.
    • Combine INMEMORY + CELLMEMORY for tables that may not fit in memory but still need columnar performance.

    Conclusion

    In-Memory Columnar Caching in Exadata is a powerful way to speed up queries by storing data in columnar format inside flash cache. With simple SQL commands, you can control which tables are cached, how they are compressed, and balance between space efficiency and query speed.

    Leave a Reply