Using In-Memory with Analytic Views

https://docs.oracle.com/en/database/oracle/oracle-database/19/inmem/populating-objects-in-memory.html

In-Memory activation at CDB level

alter system set heat_map=ON scope=spfile;
alter system set db_keep_cache_size=400M scope=spfile;
alter system set inmemory_size=400M scope=spfile;
alter system set inmemory_max_populate_servers=4 scope=spfile;
alter system set inmemory_virtual_columns=enable scope=spfile;
alter system set "_inmemory_64k_percent"=5 scope=spfile;
alter system set "_inmemory_small_segment_threshold"=0 scope=spfile;
alter system set "_optimizer_use_feedback"=FALSE scope=spfile;
alter system set "_imado_enable_coloptim"=FALSE scope=spfile;

This will give 400MB to the keep pool cache and 400MB to the in-memory pool. The first one will cache the same four tables in SGA, so that both the regular and the In-Memory tables are in RAM to make the comparison fair. This changes require a database reboot to apply.

alter table PRODUCT_DIM inmemory;
-- Run a full-scan query to activate loading of PRODUCT_DIM into RAM.
select /*+ full(PRODUCT_DIM) noparallel(PRODUCT_DIM) */ count(*) from PRODUCT_DIM;

— Check that the table is in in-memory.
select table_name, buffer_pool, compression, inmemory,
inmemory_priority, inmemory_distribute, inmemory_compression
from user_tables
where table_name in ('PRODUCT_DIM');

— let’s check the 400MB pool is enough to hold them and they have been successfully populated to the In-Memory region
select owner, segment_name, partition_name, populate_status, bytes,
inmemory_size, bytes_not_populated
from v$im_segments
order by owner, segment_name, partition_name;

The database optimizer will determine that this is the most effective way to handle this query. In-memory doesn’t require to change anything: it’s transparent for the applications. It will take advantage of the columnar data format and of many other optimizations (predicate push-down, vector transformation, vector group by…) to get the job done.

And of course it keeps data consistency at all time: as soon as a new row is inserted or an exiting one is updated or deleted, In-Memory will also reflect those transactional changes.

Disabling In-Memory

alter session set INMEMORY_QUERY = DISABLE;

To remove a table from in-memory
alter table PRODUCT_DIM no inmemory;