This post gives a summary of 18c new Memoptimized Rowstore feature.
The memoptimized rowstore provides the capability of fast lookup of data for the tables that are mainly queried based on primary key columns. The memoptimized rowstore uses a memory area in the system global area (SGA) called the memoptimize pool that stores the hash indexes of the tables when enabled for fast lookup.
Fast lookup is enabled by a hash index structure in the memoptimize pool that provides fast access to the blocks of a given table permanently pinned in the buffer cache to avoid disk I/O. This hash index is created when the Memoptimized Rowstore is configured and is maintained automatically by Oracle Database. When a table is enabled for fast lookup, the table’s blocks are pinned in the buffer cache, and queries on the table use the hash index in the memoptimize pool to improve performance.
Enabling memoptimize pool
To enable the memoptimize pool, set the MEMOPTIMIZE_POOL_SIZE initialization parameter to a non-zero value. The minimum setting is 100M.
SQL> ALTER SYSTEM SET MEMOPTIMIZE_POOL_SIZE = 2G SCOPE=SPFILE;
To enable a Table for Fast Lookup
Specify MEMOPTIMIZE FOR READ in CREATE TABLE or ALTER TABLE statements. The memoptimize pool should first be enabled.
SQL> CREATE TABLE fast_lookup (id NUMBER(5) PRIMARY KEY, test_col VARCHAR2(15)) MEMOPTIMIZE FOR READ; SQL> ALTER TABLE sh.products MEMOPTIMIZE FOR READ;
Populating a Table in memoptimize Pool
To populate a Table in the Memoptimize Pool:
SQL> execute DBMS_MEMOPTIMIZE.POPULATE('SH','PRODUCTS');
Disabling an Exisiting table for fast lookup
To disable an Existing Table for Fast Lookup:
SQL> ALTER TABLE sh.products NO MEMOPTIMIZE FOR READ;
– Tables enabled for fast lookup cannot be compressed.
– Tables enabled for fast lookup must have a primary key constraint enabled.