Oracle Scratchpad

August 3, 2014

Analogy – 2

Filed under: 12c,in-memory,Oracle — Jonathan Lewis @ 1:41 pm GMT Aug 3,2014

I suggested a little while ago that thinking about the new in-memory columnar store as a variation on the principle of bitmap indexes was quite a good idea. I’ve had a couple of emails since then asking me to expand on the idea because “it’s wrong” – I will follow that one up as soon as I can, but in the meantime here’s another angle for connecting old technology with new technology:

It is a feature of in-memory column storage that the default strategy is to store all columns in memory. But it’s quite likely that you’ve got some tables where a subset of the columns are frequently accessed and other columns are rarely accessed and it might seem a waste of resources to keep all the columns in memory just for the few occasional queries. So the feature allows you to de-select columns with the “no inmemory({list of columns})” option – it’s also possible to use different degrees of compression for different columns, of course, which adds another dimension to design and planning – but that’s a thought for another day.

So where else do you see an example of being selective about where you put columns ?  Index Organized Tables (IOTs) – where you can choose to put popular columns in the index (IOT_TOP) segment, and the rest in the overflow segment, knowing that this can give you good performance for critical queries, but less desirable performance for the less important or less frequent queries. IOTs allow you to specify the (typically short) list of columns you want “in” – it might be quite nice if the same were true for the in-memory option, I can imagine cases where I would want to include a small set of columns and exclude a very large number of them (for reasons that bring me back to the bitmap index analogy).

 

5 Comments »

  1. Jonathan, how does Oracle reconstruct read consistent image of in-memory data?

    Comment by Yuri — August 11, 2014 @ 3:33 pm GMT Aug 11,2014 | Reply

    • Yuri,

      That’s a very good question, that’s worth some careful investigation. It’s possible that the mechanism is linked in with the enhancements to HCC (hybrid columnar compression) which allow for row-locking in 12.1.0.2.

      The in-memory feature uses a journalling mechanism to ensure that only committed data is present in the in-memory image of the columns – but that doesn’t help if you have a long-running query that starts before some other session updates and commits the data you want to see.

      The need for read-consistency suggests that there could be some sort of ITL associated with each compression unit so that the reader can see that a transaction has changed the data in the compression unit – with an ITL in place it would be possible to re-direct the reader back to the data blocks for just that compression unit. It should be fairly easy to set up a couple of experiments that would give some weight (or disprove) the hypothesis. An interesting question – if the hypothesis is roughly correct – is how large the limit on the size of ITL would be.

      Comment by Jonathan Lewis — August 15, 2014 @ 10:29 am GMT Aug 15,2014 | Reply

  2. […] comment on one of my early blogs about the 12c in-memory database option asked how Oracle would deal with read-consistency. I came up with a couple of […]

    Pingback by In-memory Consistency | Oracle Scratchpad — August 27, 2014 @ 7:00 pm GMT Aug 27,2014 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,308 other followers