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.
Original publish date: July 14, 2014