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 :

Sans titre

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/

 

2 thoughts on “Oracle 12C In-memory : Part 3 (IMCU pruning)

  1. The sattka matka provide a very enjoyfull game for a person. The sattka make is the best game and there are many different types of game to be provided. These games are Kalyan Matka, Main Mumbai Matka, Milan Day Matka, Milan Night Matka, Rajdhani Day Matka, Rajdhani Night Matka. It is a wonderful and enjoy full game.

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