.
\nIn the ‘traditional’ row store, the indexes are maintained at the same time as rows are changed. It’s different with the In-memory Column Store. Changes are maintained by background processes. When rows are changed, the Snapshot Metadata Units (SMU) logs the changes and In-Memory Compression Units (IMCU) are re-populated asynchronously. Let’s see an example.
\n<\/p>\n
I create a one million rows table inmemory and I ensure that it is populated in memory:<\/p>\n
\nSQL> create table DEMO inmemory tablespace IOPS as select rownum num,mod(rownum,10) ten from xmltable('1 to 1000000');\nTable created.\n \nSQL> alter session set \"_inmemory_populate_wait\"=true;\nSession altered.\n \nSQL> set timing on\nSQL> select count(*) from DEMO;\n \n COUNT(*)\n----------\n 1000000\n \nElapsed: 00:00:04.32\n<\/code><\/pre>\nI’ve used the “_inmemory_populate_wait” to wait for the first population and see how long it takes. It is not set for the following sessions.<\/p>\n
Invalidation and repopulation statistics<\/h3>\n
For the moment, all rows are populated in memory<\/p>\n
\nSQL> select to_char(sysdate,'hh24:mi:ss') \"now\",load_scn,total_rows,invalid_rows,invalid_blocks from V$IM_SMU_HEAD;\n \nnow LOAD_SCN TOTAL_ROWS INVALID_ROWS INVALID_BLOCKS\n-------- ---------------- ---------- ------------ --------------\n16:03:19 940920160 491079 0 0\n16:03:19 940920160 508921 0 0\n \nSQL> select prepopulated,repopulated,trickle_repopulated,num_rows,num_blocks,time_to_populate,to_char(timestamp,'hh24:mi:ss') timestamp from V$IM_HEADER;\n \nPREPOPULATED REPOPULATED TRICKLE_REPOPULATED NUM_ROWS NUM_BLOCKS TIME_TO_POPULATE TIMESTAM\n------------ ----------- ------------------- ---------- ---------- ---------------- --------\n 0 0 0 491079 874 534 16:03:16\n 0 0 0 508921 908 506 16:03:16\n<\/code><\/pre>\nAs far as i know the TIME_TO_POPULATE is in milliseconds. It’s the minimum I ever seen with In-memory.<\/p>\n
Consistent gets<\/h3>\n
Everything being in memory, a select should not read any block from the buffer cache:<\/p>\n
\nExecution Plan\n----------------------------------------------------------\nPlan hash value: 4000794843\n-----------------------------------------------------------------------------------\n| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |\n-----------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 100K| 781K| 32 (4)| 00:00:01 |\n|* 1 | TABLE ACCESS INMEMORY FULL| DEMO | 100K| 781K| 32 (4)| 00:00:01 |\n-----------------------------------------------------------------------------------\nPredicate Information (identified by operation id):\n---------------------------------------------------\n 1 - inmemory(\"TEN\"<0)\n filter("TEN"<0)\nStatistics\n----------------------------------------------------------\n 0 recursive calls\n 0 db block gets\n 3 consistent gets\n 0 physical reads\n 0 redo size\n 307 bytes sent via SQL*Net to client\n 489 bytes received via SQL*Net from client\n 1 SQL*Net roundtrips to\/from client\n 0 sorts (memory)\n 0 sorts (disk)\n 0 rows processed\n<\/code><\/pre>\n3 consistent gets are (probably) from the dictionary.
\nThe IM statistics show that all rows have been read from the IM columns store:<\/p>\n
\nNAME VALUE\n---------------------------------------------------------------- ----------\nIM scan rows 1000000\nIM scan rows optimized 1000000\n<\/code><\/pre>\nUpdate<\/h3>\n
Let’s update 20000 rows (that is 2% of total rows)<\/p>\n
\nSQL> UPDATE DEMO set ten=ten+1 where rownum<=20000\n20000 rows updated.\n \nSQL> commit;\nCommit complete.\n<\/code><\/pre>\nInvalid rows<\/h3>\n
The updated rows are now stale in the IMCS, they are marked as ‘invalid’ in the IMCS metadata:<\/p>\n
\nSQL> select to_char(sysdate,'hh24:mi:ss') \"now\",load_scn,total_rows,invalid_rows,invalid_blocks from V$IM_SMU_HEAD;\n \nnow LOAD_SCN TOTAL_ROWS INVALID_ROWS INVALID_BLOCKS\n-------- ---------------- ---------- ------------ --------------\n16:03:30 940920160 491079 20000 35\n16:03:30 940920160 508921 0 0\n \nSQL> select prepopulated,repopulated,trickle_repopulated,num_rows,num_blocks,time_to_populate,to_char(timestamp,'hh24:mi:ss') timestamp from V$IM_HEADER;\n \nPREPOPULATED REPOPULATED TRICKLE_REPOPULATED NUM_ROWS NUM_BLOCKS TIME_TO_POPULATE TIMESTAM\n------------ ----------- ------------------- ---------- ---------- ---------------- --------\n 0 0 0 491079 874 534 16:03:16\n 0 0 0 508921 908 506 16:03:16\n<\/code><\/pre>\nNo repopulation yet, the updates are only marked as invalid.
\nThere are 20000 invalid rows and they are stored in 35 blocks in the row store.<\/p>\n
Hybrid select<\/h3>\n
In that state, a select will have to read those 20000 rows from the buffer cache. Here are the statistics:<\/p>\n
\nStatistics\n----------------------------------------------------------\n 0 recursive calls\n 0 db block gets\n 38 consistent gets\n 0 physical reads\n 0 redo size\n 307 bytes sent via SQL*Net to client\n 489 bytes received via SQL*Net from client\n 1 SQL*Net roundtrips to\/from client\n 0 sorts (memory)\n 0 sorts (disk)\n 0 rows processed\n \nNAME VALUE\n---------------------------------------------------------------- ----------\nIM scan rows 1000000\nIM scan rows optimized 1000000\nIM scan rows cache 20000\nIM scan blocks cache 35\n<\/code><\/pre>\n35 additional consistent gets, which is the number of invalid blocks for gotten from buffer cache.<\/p>\n
This is where In-Memory may not be very efficient for tables that have concurrent updates. The FULL TABLE SCAN plan is used, but may have to get some blocks from the row store. Of course, if the changes are not committed or were commited after the start of your query, some undo blocks have to be read as well.<\/p>\n
Repopulation<\/h3>\n
Repopulation is asynchronous. Even after my select which had to read the 20000 updated rows nothing has changed in the IMCS:<\/p>\n
\nSQL> select to_char(sysdate,'hh24:mi:ss') \"now\",load_scn,total_rows,invalid_rows,invalid_blocks from V$IM_SMU_HEAD;\n \nnow LOAD_SCN TOTAL_ROWS INVALID_ROWS INVALID_BLOCKS\n-------- ---------------- ---------- ------------ --------------\n16:03:35 940920160 491079 20000 35\n16:03:35 940920160 508921 0 0\n \nSQL> select prepopulated,repopulated,trickle_repopulated,num_rows,num_blocks,time_to_populate,to_char(timestamp,'hh24:mi:ss') timestamp from V$IM_HEADER;\n \nPREPOPULATED REPOPULATED TRICKLE_REPOPULATED NUM_ROWS NUM_BLOCKS TIME_TO_POPULATE TIMESTAM\n------------ ----------- ------------------- ---------- ---------- ---------------- --------\n 0 0 0 491079 874 534 16:03:16\n 0 0 0 508921 908 506 16:03:16\n<\/code><\/pre>\n