FORALL の Bulk Insert と APPEND_VALUESヒントによるダイレクト・パス・インサートでデータが圧縮されるのを確認する。(Oracle Database)

表題の通り Oracle Database の FORALL Bulk Insert と APPEND_VALUESヒントによるダイレクト・パス・インサートでデータが圧縮されるのを確認しますやで。
彡(ﾟ)(ﾟ)

# 1. ダイレクト・パス・インサートとは

ダイレクト・パス・インサートはデータの INSERT時に Oracle Database のバッファ・キャッシュをバイパスして、データファイルにデータを直接書き込むことで高速なINSERT処理を実現する仕組みです。

> 20.4.2.1 ダイレクト・パスINSERTについて
> https://docs.oracle.com/cd/F19136_01/admin/managing-tables.html#GUID-BC07CE7E-92E3-4C2D-BA31-88659D10BD30
> ダイレクト・パスINSERT処理では、表内の既存データの後ろに挿入データが追加されます。データは、バッファ・キャッシュを回避してデータファイルに直接書き込まれます。表の空き領域は再利用されず、参照整合性制約は無視されます。ダイレクト・パスINSERTは従来型のインサートよりもパフォーマンスが大幅に優れています。

ダイレクト・パス・インサートを有効化するには幾つか条件があり、よく使われるのは INSERT /*+ APPEND */ ... SELECT ...文 や CTAS(CREATE TABLE .. AS SELECT ...文) などですが、PL/SQL の FORALL文による Bulk Insert と APPEND_VALUES句を組み合わせることで VALUES句付きの INSERT文でも有効に動作します。

> 20.4.2.3.1 SQL文を使用したシリアル・モード・インサート
> https://docs.oracle.com/cd/F19136_01/admin/managing-tables.html#GUID-C0590D13-D8AD-4B59-9BF0-B698A48A414A
> VALUES句を使用してINSERTを実行している場合は、INSERTキーワードの直後にある各INSERT文にAPPEND_VALUESヒントを指定します。VALUES句を使用するダイレクト・パスINSERTは、ロードする行が数百、数千またはさらに膨大な数になる場合の使用が最適です。一般的な使用例として、OCIを使用した配列の挿入があります。また、PL/SQLのFORALL文での挿入に使用する例もあります。　

> 12.4.1 FORALL文
> https://docs.oracle.com/cd/F19136_01/lnpls/plsql-optimization-and-tuning.html#GUID-6D4A1425-64DD-4723-8AAE-87B0A51A2854
> FORALL文は、バルクSQLの機能であり、DML文をPL/SQLからSQLに1文ずつではなくバッチで送信します。

# 2. 実行環境
環境は Autonomous Database Transaction Processing(ATP) の Always Free環境、バージョンは 19c(RU は 19.21) で検証しました。

```sql
SQL> SET ECHO ON;
SQL> SELECT * FROM V$VERSION;

BANNER                                                                           BANNER_FULL                                                                                                                                                      BANNER_LEGACY                                                                        CON_ID
-------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ----------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production           Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production                                                                                           Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production                    0
                                                                                 Version 19.21.0.1.0                                                                                                                                                                                                                                         
```

# 3. サンプルのSQL(PL/SQL)

下記にサンプルのSQL(PL/SQL)を提示します。(NO)COMPRESS部分のコメント・アウトを制御しながら確認していきます。

```sql
SET ECHO ON;
SET TIME ON;
SET TIMING ON;
SET LINESIZE 170;
SET PAGESIZE 100;
COLUMN SEGMENT_NAME FORMAT A30;
COLUMN BYTES FORMAT 999,999,999,999,999;

DROP TABLE TEST_TABLE;
CREATE TABLE TEST_TABLE (
   P_NO   NUMBER
  ,P_DATE DATE
  ,ELE1   VARCHAR2(100)
  ,ELE2   VARCHAR2(200)
)
NOCOMPRESS
-- COMPRESS
-- COMPRESS FOR QUERY HIGH
;

ALTER TABLE TEST_TABLE ADD CONSTRAINT TEST_TABLE_PK PRIMARY KEY (P_NO) USING INDEX;

SELECT COUNT(*) FROM TEST_TABLE;

-- Bulk insert sample
DECLARE
  -- ROWTYPE Collection TYPE
  TYPE t_array_TEST_TABLE IS TABLE OF TEST_TABLE%ROWTYPE INDEX BY PLS_INTEGER;
  -- ROWTYPE Collection
  array_TEST_TABLE t_array_TEST_TABLE;
  i NUMBER;
  j PLS_INTEGER;
  k NUMBER;
  cnt NUMBER;
BEGIN
  -- Bulk insert is divided into several times. It means a saving of PGA.
  FOR i IN 1..10
  LOOP
    -- Collection initialize
    array_TEST_TABLE.DELETE;
    
    -- Column data create
    FOR j IN 1..100000
    LOOP
      k := ((i-1) * 100000) + j;
      array_TEST_TABLE(j).P_NO   := k;
      array_TEST_TABLE(j).P_DATE := TRUNC(SYSDATE, 'DD') + (k/24/60);
      array_TEST_TABLE(j).ELE1   := 'ELE1_VALUES';
      array_TEST_TABLE(j).ELE2   := 'ELE2_VALUES';
    END LOOP;
    
    -- Bulk insert
    FORALL j IN array_TEST_TABLE.FIRST..array_TEST_TABLE.LAST
      INSERT /*+ APPEND_VALUES */ INTO TEST_TABLE VALUES array_TEST_TABLE(j);
    
    COMMIT;
  END LOOP;
END;
/

SELECT COUNT(*) FROM TEST_TABLE;

SELECT SEGMENT_NAME, BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE 'TEST_TABLE%';
```

# 4. 実行結果(無圧縮の場合)

無圧縮の場合の実行結果は以下の通りです。おおよそ 50MB ほどでしょうか。

```sql
SQL> SET ECHO ON;
SQL> SET TIME ON;
SQL> SET TIMING ON;
SQL> SET LINESIZE 170;
SQL> SET PAGESIZE 100;
SQL> COLUMN SEGMENT_NAME FORMAT A30;
SQL> COLUMN BYTES FORMAT 999,999,999,999,999;
SQL> 
SQL> DROP TABLE TEST_TABLE;

Table TEST_TABLEが削除されました。

経過時間: 00:00:00.158
SQL> CREATE TABLE TEST_TABLE (
  2     P_NO   NUMBER
  3    ,P_DATE DATE
  4    ,ELE1   VARCHAR2(100)
  5    ,ELE2   VARCHAR2(200)
  6  )
  7  NOCOMPRESS
  8  -- COMPRESS
  9  -- COMPRESS FOR QUERY HIGH
 10  ;

Table TEST_TABLEは作成されました。
：
：
PL/SQLプロシージャが正常に完了しました。

経過時間: 00:00:06.771
SQL> 
SQL> SELECT COUNT(*) FROM TEST_TABLE;

  COUNT(*)
----------
   1000000

経過時間: 00:00:00.163
SQL> 
SQL> SELECT SEGMENT_NAME, BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE 'TEST_TABLE%';

SEGMENT_NAME                                  BYTES
------------------------------ --------------------
TEST_TABLE                               50,331,648
TEST_TABLE_PK                            16,777,216

経過時間: 00:00:00.128
```

# 5. 実行結果(基本表圧縮の場合)

基本表圧縮の場合の実行結果は以下の通りです。23MB程度で半分弱くらいまで圧縮されました。

```sql
SQL> SET ECHO ON;
SQL> SET TIME ON;
SQL> SET TIMING ON;
SQL> SET LINESIZE 170;
SQL> SET PAGESIZE 100;
SQL> COLUMN SEGMENT_NAME FORMAT A30;
SQL> COLUMN BYTES FORMAT 999,999,999,999,999;
SQL> 
SQL> DROP TABLE TEST_TABLE;

Table TEST_TABLEが削除されました。

経過時間: 00:00:00.174
SQL> CREATE TABLE TEST_TABLE (
  2     P_NO   NUMBER
  3    ,P_DATE DATE
  4    ,ELE1   VARCHAR2(100)
  5    ,ELE2   VARCHAR2(200)
  6  )
  7  -- NOCOMPRESS
  8  COMPRESS
  9  -- COMPRESS FOR QUERY HIGH
 10  ;

Table TEST_TABLEは作成されました。
：
：
PL/SQLプロシージャが正常に完了しました。

経過時間: 00:00:07.053
SQL> 
SQL> SELECT COUNT(*) FROM TEST_TABLE;

  COUNT(*)
----------
   1000000

経過時間: 00:00:00.152
SQL> 
SQL> SELECT SEGMENT_NAME, BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE 'TEST_TABLE%';

SEGMENT_NAME                                  BYTES
------------------------------ --------------------
TEST_TABLE                               23,068,672
TEST_TABLE_PK                            16,777,216

経過時間: 00:00:00.128
```

# 6. 実行結果(EHCCによる圧縮, QUERY HIGHの場合)

EHCC(Exadata Hybrid Columnar compression)で QUERY HIGH を選択した場合の結果は以下の通りです。重複が多いデータにしたせいか 400KB弱程度まで圧縮されています。

```sql
SQL> SET ECHO ON;
SQL> SET TIME ON;
SQL> SET TIMING ON;
SQL> SET LINESIZE 170;
SQL> SET PAGESIZE 100;
SQL> COLUMN SEGMENT_NAME FORMAT A30;
SQL> COLUMN BYTES FORMAT 999,999,999,999,999;
SQL> 
SQL> DROP TABLE TEST_TABLE;

Table TEST_TABLEが削除されました。

経過時間: 00:00:00.178
SQL> CREATE TABLE TEST_TABLE (
  2     P_NO   NUMBER
  3    ,P_DATE DATE
  4    ,ELE1   VARCHAR2(100)
  5    ,ELE2   VARCHAR2(200)
  6  )
  7  -- NOCOMPRESS
  8  -- COMPRESS
  9  COMPRESS FOR QUERY HIGH
 10  ;

Table TEST_TABLEは作成されました。
：
：
PL/SQLプロシージャが正常に完了しました。

経過時間: 00:00:06.158
SQL> 
SQL> SELECT COUNT(*) FROM TEST_TABLE;

  COUNT(*)
----------
   1000000

経過時間: 00:00:00.149
SQL> 
SQL> SELECT SEGMENT_NAME, BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME LIKE 'TEST_TABLE%';

SEGMENT_NAME                                  BYTES
------------------------------ --------------------
TEST_TABLE                                  393,216
TEST_TABLE_PK                            16,777,216

経過時間: 00:00:00.127
```

# 7. まとめ
FORALL による Bulk Insert と APPEND_VALUESヒント でデータが圧縮されることを確認しました。どんどん活用して下さいね。
彡(^)(^)