# turn on the inmemory option (it’s part of the SGA, so get sure that the SGA is big enought)<\/p>\n
alter system set inmemory_size=1G scope=spfile;<\/p>\n
One gigabyte seems to be less, but inmemory tables are stored column oriented. so, Oracle uses also compression .. for example i loaded a table with 1.2 Mio Rows (but only 5 columns) and i just needed ~30MB (in)memory.<\/p>\n
# create a materialized view which is refreshd every 5 minutes<\/p>\n
create materialized view sma_ext_5min_snp
\nrefresh complete start with (sysdate) next (sysdate+5\/24\/60) with rowid
\nas select * from sma_ext_5min;<\/p>\n
# put the materialized view table into memory
\nalter table sma_ext_5min_snp inmemory;<\/p>\n
# check the inmemory state of the materialized view (table)<\/p>\n
SELECT table_name, inmemory, inmemory_priority, inmemory_distribute, inmemory_compression, inmemory_duplicate FROM user_tables WHERE table_name = ‘SMA_EXT_5MIN_SNP’<\/p>\n
# access the view so that it will be loaded into memory (default is load on use)<\/p>\n
select count(*) from sma_ext_5min_snp;<\/p>\n
# check if it is loaded into memory:<\/p>\n
select * from v$im_segments<\/p>\n","protected":false},"excerpt":{"rendered":"
# turn on the inmemory option (it’s part of the SGA, so get sure that the SGA is big enought) alter system set inmemory_size=1G scope=spfile; One gigabyte seems to be less, but inmemory tables are stored column oriented. so, Oracle … Continue reading