Improving IN-MEMORY query with Attribute Clustering (IMCU pruning and compression ratio)

Attribute clustering is a new feature of oracle 12c for more info check http://docs.oracle.com/database/121/DWHSG/attcluster.htm#DWHSG9338.

I will focus here only on how attribute clustering can improve in-memory query.For a description of in-memory pruning please check my previous post.

Here is a quick example to demonstate how Attribute clustering can improve in-memory query pruning and compression ratio. Here is the simple steps :

  1. Create a table and load it in-memory.
  2. Check for the min-max column for every IMCU and the total allocated space in-memory.
  3. Use attribute clustering for one column and then reload the table in-memory.
  4. Check for the min-max column for every IMCU and the total allocated space in-memory.
  5. Campare the results.

1-Create a table and load it in-memory.

Page 1

2-Check for the min-max column for every IMCU and the total allocated space in-memory.

Sans titre

3-Use attribute clustering for one column and then reload the table in-memory.

Sans titre

4-Check for the min-max column for every IMCU and the total allocated space in-memory

Sans titre

5-Campare the results.

Like we can see in-memory pruning can be now more effective.If we run our simple query :

 SELECT COUNT(id) FROM  attrib_cluster  WHERE to_cluster = 10;

It will scan only one IMCU when using attribute clustering.

Also we have an Improved data compression ratio : the size consumed in-memory is much lower .(Many simillar column in the same IMCU).So we will also scan less data.

That’s it 😀

2 thoughts on “Improving IN-MEMORY query with Attribute Clustering (IMCU pruning and compression ratio)

  1. That is very interesting Mahmoud Hatem!
    The thing with Oracle In-Memory is that it clumns store is not sorted like in SAP HANA;
    I made some observations on unsorted access compared to a sorted one with Oracle 12c, but your tip with Attribute Clustering – forcing the sort is great!

    Thank you – Karl

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