LoginSignup
3
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@ora_gonsuke777in日本オラクル株式会社

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

Posted at

表題の通り 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> 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部分のコメント・アウトを制御しながら確認していきます。

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> 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> 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> 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ヒント でデータが圧縮されることを確認しました。どんどん活用して下さいね。
彡(^)(^)

3
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?