18c New Features - Memoptimized rowstore fast lookup

Fast lookup enables fast data retrieval from database tables for applications, such as Internet of Things (IoT) applications.

Fast lookup uses a hash index that is stored in the SGA buffer area called memoptimize pool to provide fast access to blocks of tables permanently pinned in the buffer cache, thus avoiding disk I/O and improving query performance.

Reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/tuning-system-global-area.html#GUID-E46EF11C-E999-4277-950F-E78EEC895ABB

Execution Plan with memoptimized read fast lookup

SQL> show parameter memoptimize_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memoptimize_pool_size                big integer 400M


SQL> alter table test_fast_ingest memoptimize for read;

Table altered.

SQL> select * from test_fast_ingest where id=1;

        ID TEST_COL
---------- ---------------
         1 test

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1177632651

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                  |     1 |    22 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID READ OPTIM| TEST_FAST_INGEST |     1 |    22 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN READ OPTIM         | SYS_C008161      |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        487  bytes sent via SQL*Net to client
        392  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Execution Plan without memoptimize

SQL> alter table test_fast_ingest no memoptimize for read;

Table altered.

Elapsed: 00:00:00.01
SQL> /* take 2nd execution output, avoid overhead with SQL parsing */
SQL> select * from test_fast_ingest where id=1;

        ID TEST_COL
---------- ---------------
         1 test

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1177632651

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    22 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_FAST_INGEST |     1 |    22 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C008161      |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        487  bytes sent via SQL*Net to client
        392  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processe