Oracle 12c In-memory and IM scan CUs pruned mechanics

Recently I’ve learnt about one interesting optimization technique used by Oracle in-memory database engine, i.e. CU (compression units) pruning.
(see http://blogs.oracle.com/In-Memory/entry/getting_started_with_oracle_database2).
It looks similar to the trick used by Exadata storage indexes (or zone maps) to skip(prune) big data ranges by storing the min and max values of some columns
( see https://richardfoote.wordpress.com/2014/10/30/12-1-0-2-introduction-to-zone-maps-part-ii-changes/).

And I’ve almost immediate come up with idea that it also can suffer from “bad” data clustering in the same way as described by Richard Foote in his marvelous blog.

So I decided to check it and here is the result.

I’ve create a table as

CREATE TABLE IDXTEST
AS select (1e&pw) id, 1e4 id2, rpad('a',999,'a') filler
from dual where rownum <2;

ALTER TABLE IDXTEST INMEMORY PRIORITY CRITICAL;


and populated it in 2 ways:

1st (good clustering):

insert into IDXTEST select 1e&pw + trunc(rownum/1)+1 id, mod(rownum,20) id2, rpad('a',990,'b') || rownum filler
from dual connect by level <=2e5;
commit;


2nd (bad clustering):

insert into IDXTEST select 1e&pw + trunc(rownum/1)+1 id, mod(rownum,20) id2, rpad('a',990,'b') || rownum filler
from dual connect by level <=2e5 order by dbms_random.value;
commit;

I used the following query to do “in-memory” scans:
VARIABLE id2 number
EXEC :id2 :=(1e&pw.)+ 1.5e5

SELECT /*+ dynamic_sampling(t 0) FULL(t)*/ count(*) from IDXTEST t
WHERE id >:id2;

You need to run it twice:
1st time to populate in memory column store
and 2nd time to do “pure” in-memory scan.
(You need to wait 1-2 minutes before run the query 2nd time to finish population of the in memory column store, you can monitor the population process as
SELECT populate_status, bytes_not_populated,bytes,inmemory_size from v$im_segments)

and here is the corresponding stats

1st(good clustering)	2nd(bad clustering)
2 IM scan CUs pruned	0 IM scan CUs pruned
5 IM scan segments minmax eligible	5 IM scan segments minmax eligible

Moralite’: it’s still very important how your data is physically ordered/clustered even if you read it from memory.

PS
I had a small hope that Oracle in-memory engine could reorder data during populating column store but it seems to expensive operation to be true.