12c: In Memory Column Store

In 11g relational table row store in memory, rows are stored with row id in rom format 

In 12c relational table stored in column store

column representation :

On space management level, on disk logical column representation ( segment blocks on disks ). When we implemented in memory feature, IM store are created in SGA.  In memory compression unit : IMCU: chunks are created automatically. for Each IMCU,  transaction Journal TX is created automatically. Each column is stored separately in IMCU, chunks have maximum speed for cpu. Values for predicate columns in IMCU are scanned for cycle of the cpu and these chunks are eligible for fast processing. IMCU can store up to 1 million rows at run time based on table same and structure. IMCU: stores min and max values for range of column in memory. Range is nothing but predicate value in where clause

Table loaded in memory in imcu and transactional journal is updated automatically. when ever value in buffer cache is updated TX journal in IM store is updated automatically and a record of IMCU are updated to stale state. It will  update the value in IMCU on when the transaction is committed in the buffer cache and transaction journal is updated automatically. when Transaction jOURNAL cache received thresholds or by in memory co-ordinator refreshes the transactional journal depend on internal threshold or journal run low on memory or or every 2 minutes. In memory co-ordiantor is back ground process, refreshes  address values from transactional journal to IMCU store

Implementation: 

In memory column store  can be implemented when application is scanning large number of rows or  query subset of columns in a table or joining  small and big tables, querying dimensions tables or joining fact tables

set following parameter on database 
sql> alter system set inmemory_size=200mb;

(minimum size is 100MB as per oracle documentation)

we have new view associated with this in memory “v$inmemory_area"

using in memory option, tablespace and tables will be loaded into IM Store automatically by issuing following command

--Note: if you enable at tablespace level, all tables and materialized view's on that tablespace will be loaded into IM store by default  

syntax:

sql> alter table <table_name> default inmemory;

we can priority the tables to load the data into the IM store by setting different priority levels 

sql> alter table <table_name> priority critical;

critical - when database is opened, table is loaded into the IM store automatically 
High - If the space are available after all the priority tables are loaded, the high priority tables are loaded into the IM store 
Medium - objects or tables are loaded after all critical and high Priority tables are loaded in IM store 
Low - objects or tables are loaded after all critical, high Priority and medium tables are loaded in IM store 

None - Objects are loaded only when they are queried for first time and the space is available in IM store 

Note - by default it is None

Exceptions: 

-- Objects owned by sys user and objects stored in system and sysaux tablespace can be stored in IM store
-- We cannot load IOT tables ( Index organized tables ) 

-- longs & lobs are not supported in IM store 

---Nikhil Tatineni---
---Oracle in memory---