The quickest and simplest way of ensuring what has been populated in In-Memory column store (IMCS) is to query the V$IM_SEGMENTS view. Whenever an object is loaded into IMCS, the database will add a record about it into this view. In general, I guess the appearance of the IMCS as such is an attempt of Oracle to transition to NewSQL stack.
According to Oracle documentation the IMCS can be enabled for a tablespace, table (including external and excluding IOT), table partition and materialized view (MV). What is more interesting is that it also mentions when, for example, a table gets populated in IMCS – only when it is fully scanned (and not parallelly). Index access methods that cover table tuples and table tuples populated by rowids never load the tuples in IMCS.
Documentation does not say a word about when a MV gets populated in IMCS. It would be reasonable to assume that since a MV is very much like a heap table, then the table IMCS population rules apply to the former as well i.e. for a MV to get populated in IMCS an explicit nonparallel full scan of it is necessary. This is indeed so. Additionally, implicit population of MV is also possible and this with a little trick only. The table access which causes query rewrite (fully accessing the MV) can also trigger it. All this said, this post is not about something advanced but rather an interesting take-away about IMCS.
The test case is on Oracle 12C. First, let’s create a non-empty table and define a MV on it:
SQL> create table testtable1 as select object_id, object_name from dba_objects;
Table created.
SQL> create materialized view testview1 enable query rewrite as select * from testtable1;
Materialized view created.
SQL>
Let’s now enable IMCS for the view and confirm that the it really gets populated in there when scanned fully:
SQL> alter materialized view testview1 inmemory;
Materialized view altered.
SQL> select count(*) from testview1;
COUNT(*)
----------
73306
SQL> select segment_name, populate_status from v$im_segments;
SEGMENT_NAME POPULATE_STATUS
----------------------------------- -------------
TESTVIEW1 COMPLETED
Now, let’s flush the MV from IMCS but make it eligible for population again. Also, let’s query the table so that the query rewrite kicks in:
SQL> alter materialized view testview1 no inmemory;
Materialized view altered.
SQL> alter materialized view testview1 inmemory;
SQL> select segment_name, populate_status from v$im_segments;
no rows selected.
SQL>
As the result from v$im_segments shows, the MV is not there…yet. Let’s cause a full scan of the MV again:
SQL> select count(*) from testview1;
COUNT(*)
----------
73306
SQL> select segment_name, populate_status from v$im_segments;
no rows selected.
SQL>
Subsequent executions of the same query still doesn’t make the MV appear in IMCS, although the little trick will make it to get populated:
SQL> alter table testtable1 inmemory;
Table altered.
SQL> select count(*) from testtable1;
COUNT(*)
----------
73305
SQL> select segment_name, populate_status from v$im_segments;
SEGMENT_NAME POPULATE_STATUS
------------------------------ -------------
TESTVIEW1 COMPLETED
SQL>
The query plan now is as follows:
Plan hash value: 3466806115
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 73306 | 3 (0)| 00:00:01 | Q1,00 | PCWC | |
| 6 | MAT_VIEW REWRITE ACCESS INMEMORY FULL| TESTVIEW1 | 73306 | 3 (0)| 00:00:01 | Q1,00 | PCWP | |
-----------------------------------------------------------------------------------------------------------------------------
The next time the query will execute the table data will be retrieved from the MV which is now in IMCS (the reason for going parallel is described in the note 2373556.1 which is a bug and has a workaround). The query rewrite can cause MV population in IMCS and for that to happen the base table must have the INMEMORY option enabled.
That’s all for this time. More advanced stuff is coming soon…