How in-memory CU pruning works with a new attribute clustering clause

In the previous post I’ve shown how data ordering can potentially affect your  IM memory performance.

But actually you don’t need a perfect data ordering to provide effective CU pruning, it seems you just need a good data clustering to start gaining benefits from in-memory storage indexes.

Another new Oracle feature can help with that: attribute clustering clause 

Let’s do the similar experiment as in my previous post:

CREATE TABLE IDXTEST CLUSTERING BY LINEAR ORDER (id) YES ON DATA MOVEMENT
AS select (1e&pw) + rownum id, 1e4 id2, rpad('a',999,'a') filler 
from dual where rownum <2;
insert /*+ */ into IDXTEST select 1e&pw + trunc(rownum/50)*50+1 id, mod(rownum,20) id2, rpad('a',990,'b') || rownum filler 
from dual connect by level <=2e5 order by dbms_random.value; 
commit;
ALTER TABLE IDXTEST MOVE;

We populated our table with 200k data and then MOVE it to provide better clustering.
let’s put our table in the memory column store:

ALTER TABLE IDXTEST INMEMORY PRIORITY CRITICAL;
SELECT  /*+ dynamic_sampling(t 0) FULL(t)*/ count(*) from IDXTEST t WHERE id >:id2

to populate to populate in-memory store, let’s wait a couple of minutes.
and execute the same query again

PAUSE wait for inmemory segment to be populated  (select populate_status, bytes_not_populated,bytes,inmemory_size from v$im_segments))

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

You would see a similar result:

SQL_ID  d1s2yfn2js4yh, child number 0                                                                                                                                                                   
-------------------------------------                                                                                                                                                                   
SELECT  /*+ dynamic_sampling(t 0) FULL(t)*/ count(*) from IDXTEST t                                                                                                                                     
WHERE id >:id2                                                                                                                                                                                          
                                                                                                                                                                                                        
Plan hash value: 4867743                                                                                                                                                                                
                                                                                                                                                                                                        
---------------------------------------------------------------------------------------                                                                                                                 
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                 
---------------------------------------------------------------------------------------                                                                                                                 
|   0 | SELECT STATEMENT            |         |       |       |     1 (100)|          |                                                                                                                 
|   1 |  SORT AGGREGATE             |         |     1 |     3 |            |          |                                                                                                                 
|*  2 |   TABLE ACCESS INMEMORY FULL| IDXTEST |     1 |     3 |     1   (0)| 00:00:01 |                                                                                                                 
---------------------------------------------------------------------------------------                                                                                                                 
                                                                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                                                                     
---------------------------------------------------                                                                                                                                                     
                                                                                                                                                                                                        
   2 - inmemory("ID">:ID2)                                                                                                                                                                              
       filter("ID">:ID2)                                                                                                                                                                                
                                                                                                                                                                                                        

21 rows selected.

         0 IM scan CUs no memcompress                                                                                                                                                                   
         0 IM scan CUs memcompress for dml                                                                                                                                                              
         4 IM scan CUs memcompress for query low                                                                                                                                                        
         0 IM scan CUs memcompress for query high                                                                                                                                                       
         0 IM scan CUs memcompress for capacity low                                                                                                                                                     
         0 IM scan CUs memcompress for capacity high                                                                                                                                                    
         0 IM scan segments disk                                                                                                                                                                        
         0 IM scan CUs columns decompressed                                                                                                                                                             
         4 IM scan CUs predicates received                                                                                                                                                              
         4 IM scan CUs predicates applied                                                                                                                                                               
         4 IM scan CUs predicates optimized                                                                                                                                                             
         4 IM scan CUs pruned                                                                                                                                                                           
         4 IM scan segments minmax eligible                                                                                                                                                             

So result is even better comparing the previous post.

Advertisements

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