Memoptimized rowstore

• Oracle introduced a new feature called "Memoptimized rowstore", this feature is designed for fast query performance . Most of the times while accessing key value based data, we will be accessing the value based on the primary key value of table. These type of queries will benefit much from this feature .
• “MEMOPTIMIZE FOR READ” enabled tables are pinned in new memory pool called “MEMOPTIMIZE_POOL” with its new hash index. Queries accessing the table via primary keys will bybass the SQL execution layer, and access the data directly using the in-memory hash index. 
• Oracle introduced a new memory pool to manage the "Memoptimized rowstore" storage called MEMOPTIMIZE_POOL, and a new sizing parameter called "MEMOPTIMIZE_POOL_SIZE" is introduces.

Scripts used in demo:

CREATE TABLE key_val_pair (
    id         NUMBER   CONSTRAINT id_pk PRIMARY KEY,
    key_val    VARCHAR2(100),
    key_name   VARCHAR2(100)
)
segment creation IMMEDIATE memoptimize FOR READ;

---

INSERT INTO key_val_pair
SELECT ROWNUM ,'KEY'||ROWNUM, 'VALUE'||ROWNUM
FROM dual
CONNECT BY LEVEL <= 100000;

---

EXPLAIN PLAN FOR
SELECT *
FROM key_val_pair
WHERE id =120;

---

SELECT *
FROM TABLE(dbms_xplan.display);

---

PLAN_TABLE_OUTPUT
PLAN hash value: 2264066049
 
-------------------------------------------------------------------------------------------------------
| id  | operation                              | name         | ROWS  | bytes | cost (%cpu)| TIME     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |              |     1 |   117 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID READ optim| key_val_pair |     1 |   117 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE scan READ optim         | id_pk        |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
 
predicate information (IDENTIFIED BY operation id):
---------------------------------------------------
 
   2 - ACCESS("ID"=120)

---

The new acess paths "INDEX UNIQUE scan READ optim" and "INDEX UNIQUE scan READ optim"  are from oracle 18c accessing using in-memory hash index value.

Rules for new access path:

1. The tables accessed using primary key alone in "where" condition (no other condition should exists), will use two new access paths "TABLE ACCESS BY INDEX ROWID READ OPTIM" and "INDEX UNIQUE SCAN READ OPTIM". This will confirm that the data are accessed via "READ OPTIM" path(ie. using the hash index value from in-memory cache).
2. Only the primary key condition with equality option will use this new access path.
3. Even if multiple primary key values are used in "IN" clause also wont use the access path