Enabling Real-Time Analytics With Database In-Memory

In-Memory Column Store versus the Buffer Cache

Maria Colgan
Master Product Manager

In this week's blog post I'm going to address just one question. It's actually the question I have gotten the most since I started blogging on Oracle Database In-Memory.

Why does In-Memory populate complete tables or partitions into memory instead of caching frequently accessed blocks like the buffer cache?

Standard caches such as a buffer cache rely on non-uniform access patterns that are common in OLTP applications. Some rows within a table are accessed much more frequently than other rows. That is why you can have a buffer cache that is 10% the size of a table but that captures 95% of the random(OLTP) accesses to the table. In this example, lets assume you get a 20x speedup with a cache that is 10% the size of the table.

The main use case for the In-Memory column store is fast analytics, which has very different access patterns to OLTP. Typically analytic queries access a few columns from a table but they examine all of the entries in those columns. You need to scan the whole table or segment, not just a small part of the table to answer this queries. Let's say a scan of a table is 100x faster using the In-Memory column store. But if only 10% of the table is in the column store, then you will get a 1.1X speedup instead of a 100X speedup. This is because 90% of the table will have to be read using the conventional row store, and this is no faster than before.

That is a huge difference with the buffer cache example above where having 10% of the table in cache produced a 20X speedup for OLTP workloads.
Therefore, for the In-Memory column store to work effectively, all of the table or partition should be populated in memory.

Join the discussion

Comments ( 4 )
  • guest Wednesday, July 30, 2014


    could you compare performance on oltp query (not analytic) with table s in KEEP cache(all blocks are cached), with same table in IM area? Is there also huge difference in perf.?



  • guest Wednesday, July 30, 2014

    So how is this different than setting the buffer_pool to keep or "alter table <table> cache?"

  • Maria Colgan Thursday, July 31, 2014

    The difference is how the data is accessed and scanned. I will explain more details on this in the upcoming blog posts.



  • Leopoldo Gonzalez Wednesday, October 22, 2014

    Thanks for your posts Maria.

    In Memory benefits go beyond of just having data blocks in RAM.

    IM storage indexes are automatically created for each column, that means that you can put any column in the WHERE section of your SELECT statements, even those without a "traditional" index.

    Vector operations that process multiple values in a single instruction and perform faster aggregations

    Data compression (less data blocks to handle with)

    Optimizer's new features.

Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.