Oracle Database 18c の新機能 Memoptimized Table の機能を簡単に試してみました。
Memoptimized Table は Oracle Database 18c の新機能で、主キー検索の性能を向上させることを狙っています。ブロックとハッシュ値をメモリー上に固定して高速検索させる機能のようです。
フォーマンス・チューニング・ガイドには以下のように記載されています。

高速参照が有効になっていると、表のハッシュ索引が格納されるMemoptimizeプールと呼ばれるシステム・グローバル領域(SGA)内のメモリー領域が、Memoptimizeされた行ストアで使用されます。MEMOPTIMIZE_POOL_SIZE初期化パラメータは、Memoptimizeプールのサイズを制御します。

この機能は以下のエンジニアド・システムでのみ提供されています。

Oracle Database Enterprise Edition on Engineered Systems (EE-ES)
Oracle Database Cloud Service Enterprise Edition – Extreme Performance (DBCS EE-EP)
Oracle Database Exadata Cloud Service (ExaCS)

Memoptimizeプールの作成

Memoptimized テーブルで使用するデータは、SGA 内に専用の領域「Memoptimizeプール」を使用します。このメモリー領域はデフォルトでは作成されないため、初期化パラメーターを設定します。

SQL> ALTER SYSTEM SET memoptimize_pool_size = 200M SCOPE=SPFILE;

System altered.

この初期化パラメーターは動的に変更できないためインスタンスを再起動してから確認します。

SQL> SHOW PARAMETER memoptimize_pool_size;

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

エンジニアド・システム以外でもこの初期化パラメーターは変更できますが、以下のエラーが発生してインスタンス起動が失敗します。

SQL> STARTUP
ORA-12755: Feature Memoptimized Rowstore is disabled due to unsupported capability.
SQL> EXIT

$ oerr ora 12755
12755, 00000, "Feature %s is disabled due to unsupported capability."
// *Document: NO
// *Cause: This feature requires specific capabilities that were not supported
//         in the current database instance.
// *Action: Contact Oracle Support Services to enable this feature.
$

Memoptimized テーブルの作成

次に Memoptimized テーブルを作成します。CREATE TABLE 文、ALTER TABLE 文に MEMOPTIMIZED FOR READ 句を指定します。
解除する際には ALTER TABLE 文に NO MEMOPTIMIZED READ 句を指定します。
マニュアルには記述がありませんが、セグメント作成の遅延設定（初期化パラメーター deferred_segment_creation）が有効になっているとテーブル作成が失敗します。

SQL> CREATE TABLE memopt1(key NUMBER, val VARCHAR2(10)) MEMOPTIMIZE FOR READ;
CREATE TABLE memopt1(key NUMBER, val VARCHAR2(10)) MEMOPTIMIZE FOR READ
*
ERROR at line 1:
ORA-62156: MEMOPTIMIZE FOR READ feature not allowed on segment with deferred storage

このため、テーブル作成時に SEGMENT CREATION IMMEDIATE 句を指定するか、初期化パラメーター deferred_segment_creation を FALSE に指定します。
また、主キーをベースに高速化を行うため、主キー設定が無いテーブルには MEMOPTIMIZED FOR READ 句を指定できません。

SQL> CREATE TABLE memopt1(key NUMBER, val VARCHAR2(10)) SEGMENT CREATION IMMEDIATE MEMOPTIMIZE FOR READ;
CREATE TABLE memopt1(key NUMBER, val VARCHAR2(10)) SEGMENT CREATION IMMEDIATE MEMOPTIMIZE FOR READ
*
ERROR at line 1:
ORA-62142: MEMOPTIMIZE FOR READ feature requires NOT DEFERRABLE PRIMARY KEY
constraint on the table

SEGMENT CREATION IMMEDIATE 句と主キー（PRIMARY KEY) 設定を行うことでテーブル定義が完了します。設定の確認は DBA|USER|ALL_TABLES ビューの MEMOPTIMIZE_READ 列を参照します。

SQL> CREATE TABLE memopt1(key NUMBER PRIMARY KEY, val VARCHAR2(10)) SEGMENT CREATION IMMEDIATE MEMOPTIMIZE FOR READ;

Table created.


SQL> SELECT TABLE_NAME, MEMOPTIMIZE_READ FROM DBA_TABLES WHERE TABLE_NAME='MEMOPT1';

TABLE_NAME                     MEMOPTIM
------------------------------ --------
MEMOPT1                        ENABLED

実行計画を確認

Memoptimized テーブルを検索した場合の実行計画を確認します。主キー検索を行った場合には実行計画内に「INDEX UNIQUE SCAN READ OPTIM」が出力され、Memoptimized テーブルの検索が行われたことがわかります。

SQL> SELECT * FROM scott.memopt1 WHERE key=1000;

       KEY VAL
---------- ----------
      1000 1000

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  cad658c0ca1ww, child number 0
-------------------------------------
SELECT * FROM scott.memopt1 WHERE key=1000

Plan hash value: 3375854384

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

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

   2 - access("KEY"=1000)


19 rows selected.

一方で主キー以外の検索を行った場合には従来と実行計画は変わりません。

SQL> SELECT COUNT(*) FROM scott.memopt1;

  COUNT(*)
----------
    100000

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  8cf1546pqx1w5, child number 0
-------------------------------------
SELECT COUNT(*) FROM scott.memopt1

Plan hash value: 1726900829

-----------------------------------------------------------------------------
| Id  | Operation             | Name        | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |       |    68 (100)|          |
|   1 |  SORT AGGREGATE       |             |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| SYS_C007153 | 75729 |    68   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

その他の制約

SQLリファレンス・ガイドには以下の制約が記載されていますが、機械翻訳なのか日本語が変です。

この句を使用して、頻度の高いデータ問合せ操作のパフォーマンスを向上させます。MEMOPTIMIZE_POOL_SIZE初期化パラメータは、memoptimizeプールのサイズを制御します。この機能は、SGAから追加のメモリーを使用することに注意してください。

•この句は、表の最上位の属性として指定する必要があり、パーティション・レベルまたはサブパーティション・レベルで指定することはできません。
•表からデータを読み取る前に、MEMOPTIMIZE FOR READに対して表を明示的に有効にする必要があります。
•表が不要になった場合は、NO MEMOPTIMIZE FOR READに対して表を明示的に無効にする必要があります。

また、CREATE TABLE文のマニュアルに MEMOPTIMIZE FOR READ 句の指定が無い等、マニュアルの改善を期待します。

Memoptimizedテーブルの機能を試す（Oracle Database 18c)

Memoptimizeプールの作成

Memoptimized テーブルの作成

実行計画を確認

その他の制約