Oracle 12C In-memory : Part 3 (IMCU pruning)

For other parts : Part1 , Part2

Let’s see a quick example of in-memory pruning using storage index :

In-memory white paper : “A further reduction in the amount of data accessed is possible due to the In-Memory Storage Indexes that are automatically created and maintained on each of the columns in the IM column store. Storage Indexes allow data pruning to occur based on the filter predicates supplied in a SQL statement.An In -Memory Storage Index keeps track of minimum and maximum values for each column in an IMCU.When a query specifies a WHERE clause predicate, the In-Memory Storage Index on the referenced column is examined to determine if any entries with the specified column value exist in each IMCU by comparing the specified value(s) to the minimum and maximum values maintained in the Storage Index. If the column value is outside the minimum and maximum range for an IMCU, the scan of that IMCU is avoided”

Quick example :

Create a table with one column (number) and populate it with random data :

hatem@ORCL>  create table inm_prune inmemory  as select 1 a from dual connect by level < 1000000; Table crÚÚe. hatem@ORCL> insert into inm_prune select 10 a from dual connect by level < 1000000; 999999 ligne(s) crÚÚe(s). hatem@ORCL> insert into inm_prune select 15  a from dual connect by level < 1000000; 999999 ligne(s) crÚÚe(s). hatem@ORCL> insert into inm_prune select 20  a from dual connect by level < 1000000; 999999 ligne(s) crÚÚe(s). hatem@ORCL> insert into inm_prune select 30  a from dual connect by level < 1000000; 999999 ligne(s) crÚÚe(s). hatem@ORCL> commit;

 

Popluate the table in-memory and then check the NUMBER of IMCU and the IMCU storage Index

hatem@ORCL> select IMCU_ADDR,ALLOCATED_LEN,USED_LEN from V$IM_HEADER; IMCU_ADDR ALLOCATED_LEN USED_LEN —————- ————- ———- 0000000062D00000 1048576 1004232 0000000062B00000 2097152 1365910 0000000062900000 2097152 1333054 0000000062700000 2097152 1338910 0000000062500000 2097152 1338968 0000000062300000 2097152 1338968 0000000062100000 2097152 1338910 0000000062000000 1048576 1004290 8 ligne(s) sÚlectionnÚe(s). hatem@ORCL> select HEAD_PIECE_ADDRESS IMCU_ADDR,UTL_RAW.CAST_TO_NUMBER(MINIMUM_VALUE) MIN,UTL_RAW.CAST_TO_NUMBER(MAXIMUM_VALUE) MAX from V$IM_COL_CU; IMCU_ADDR MIN MAX —————- ————- ————- 0000000062D00000 30 30 0000000062B00000 1 30 0000000062900000 1 10 0000000062700000 10 10 0000000062500000 10 15 0000000062300000 15 20 0000000062100000 20 20 0000000062000000 20 30

So we have 8 IMCU.We can check the min and max values on ‘V$IM_COL_CU’.

So if i execute ‘select count(*) from inm_prune where a=5;’ i will on only need to scan two IMCU lest see the stats :

Like indicated by “IM scan CUs pruned” we have scaned only two IMCU.We have also avoided the read of 3659040 rows (IM scan rows optimized)

REF : http://blog.itpub.net/27243841/viewspace-1271299/