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.

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.

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 😀