Skip to content

New Memoptimized Rowstore

Feature Summary

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.

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 is 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;

 

To populate a Table in the Memoptimize Pool

SQL> execute DBMS_MEMOPTIMIZE.POPULATE(‘SH’,’PRODUCTS’);

 

To disable an Existing Table for Fast Lookup

SQL> ALTER TABLE sh.products NO MEMOPTIMIZE FOR READ;

 

NOTE

This feature is allowed on Engineered systems and Oracle Cloud, and disabled for non-Engineered systems and non-Oracle Cloud.
The error ORA-12754 error is expected for  non-Engineered systems and non-Oracle Cloud.

SQL> alter table test memoptimize for read;
*
ERROR at line 1:
ORA-12754: Feature ‘Memoptimized Rowstore’ is disabled due to missing capability ‘Runtime Environment’.

Workaround : Set the init.ora parameter _exadata_feature_on=true, restart the database with memoptimize_pool_size > 0.

 

Restrictions

Tables enabled for fast lookup cannot be compressed.
Tables enabled for fast lookup must have a primary key constraint enabled.