DBMS_CLOUD.COPY_DATAとAPEX_DATA_LOADINGの比較

 最近、DBMS_CLOUD.EXPORT_DATAとパッケージAPEX_DATA_EXPORTを使ったCSVファイルへのエクスポートについて比較したので、ついでにDBMS_CLOUD.COPY_DATAとパッケージAPEX_DATA_LOADINGも比較してみました。また、外部表を使用したインポートも行ってみました。

速度についてはDBMS_CLOUD.COPY_DATA（および外部表を使ったインポート）が高速です。大量のデータをインポートする場合は、こちらの手順を使うことになると思います。

テストに使用するデータを準備します。こちらの記事と同じ環境でテストを行います。

表BRICKSよりオブジェクト・ストレージに、10,000,000行のデータをエクスポートします。オブジェクト名はbricks_10m_で始まるようにします。同様に100,000行のデータをエクスポートします。オブジェクト名はbricks_100k_で始まるようにします。

オブジェクト・ストレージへのエクスポートには、以下のスクリプトを使用します。

$IFのフラグをtrueとfalseに変更し、それぞれ１回ずつスクリプトを実行すると以下のように、オブジェクトが２つ、指定したバケット以下に作成されます。

DBMS_CLOUD.COPY_DATAを使ったデータ・ロード

DBMS_CLOUD.COPY_DATAを実行し、bricks_10m_xxxx.csv.gzを表BRICKS_IMPにロードします。

データをロードする表BRICKS_IMPを作成します。

create table bricks_imp as select * from bricks where 1<>1;

SQL> create table bricks_imp as select * from bricks where 1<>1;

Table BRICKS_IMPは作成されました。

SQL> desc bricks_imp

            名前    Nullかどうか             タイプ 

______________ ___________ _______________ 

BRICK_ID       NOT NULL    NUMBER          

COLOUR         NOT NULL    VARCHAR2(6)     

SHAPE          NOT NULL    VARCHAR2(8)     

WEIGHT         NOT NULL    NUMBER          

INSERT_DATE    NOT NULL    DATE            

JUNK           NOT NULL    VARCHAR2(50)    

SQL> 

DBMS_CLOUD.COPY_DATAを使って、データをロードするスクリプトは以下になります。

10,000,000行のロードにかかっている時間は、大体30秒といったところです。

SQL> set serveroutput on

SQL> set time on timing on

13:18:47 SQL> @copy_data

22-04-04 04:18:50.369347000 +00:00: dbms_cloud.copy_data

22-04-04 04:19:22.944414000 +00:00: end

22-04-04 04:19:22.953348000 +00:00: COMPLETED

PL/SQLプロシージャが正常に完了しました。

経過時間: 00:00:32.951

経過時間: 00:00:33.122

13:19:23 SQL> select count(*) from bricks_imp;

   COUNT(*) 

___________ 

   10000000 

経過時間: 00:00:00.439

13:19:38 SQL> select * from bricks

  2  minus

  3* select * from bricks_imp;

行が選択されていません

経過時間: 00:00:36.553

13:20:27 SQL> 

表BRICKS_IMPからすべての行を削除して、再度データのロードを実行しました。処理時間にそれほどの違いは出ていません。

13:20:27 SQL> delete from bricks_imp;

10,000,000行削除されました。

経過時間: 00:00:26.661

13:23:37 SQL> commit;

コミットが完了しました。

経過時間: 00:00:00.354

13:23:41 SQL> @copy_data

22-04-04 04:23:45.554795000 +00:00: dbms_cloud.copy_data

22-04-04 04:24:17.105518000 +00:00: end

22-04-04 04:24:17.106193000 +00:00: COMPLETED

PL/SQLプロシージャが正常に完了しました。

経過時間: 00:00:31.905

経過時間: 00:00:32.080

13:24:17 SQL> 

セグメントのサイズを確認してみました。

全件削除したのち再インポートしたときのセグメントのサイズは、表BRICKS_IMPをトランケートした後にインポートしたサイズの２倍になっているので、内部的にはダイレクト・パス・インサートになっているようです。

13:24:17 SQL> select bytes from user_segments where segment_name = 'BRICKS_IMP';

      BYTES 

___________ 

   51380224 

経過時間: 00:00:00.447

13:28:11 SQL> truncate table bricks_imp;

Table BRICKS_IMPが切り捨てられました。

経過時間: 00:00:02.720

13:28:25 SQL> select bytes from user_segments where segment_name = 'BRICKS_IMP';

   BYTES 

________ 

   65536 

経過時間: 00:00:00.342

13:28:29 SQL> @copy_data

22-04-04 04:28:34.204638000 +00:00: dbms_cloud.copy_data

22-04-04 04:29:06.123755000 +00:00: end

22-04-04 04:29:06.124399000 +00:00: COMPLETED

PL/SQLプロシージャが正常に完了しました。

経過時間: 00:00:32.442

経過時間: 00:00:32.615

13:29:06 SQL> select bytes from user_segments where segment_name = 'BRICKS_IMP';

      BYTES 

___________ 

   26214400 

経過時間: 00:00:00.344

13:29:12 SQL>

外部表を使ったデータ・ロード

次のスクリプトを実行して、外部表BRICKS_EXTを作成します。

CREATE TABLE AS SELECTを実行して、表BRICKS_EXTより表BRICKS_IMPを作成します。

create table bricks_imp as select * from bricks_ext;

13:44:20 SQL> create table bricks_imp as select * from bricks_ext;

Table BRICKS_IMPは作成されました。

経過時間: 00:00:33.023

13:45:08 SQL> 

処理時間は33秒で、DBMS_CLOUD.COPY_DATAとそれほど変わりません。

データを表BRICKS_IMPから削除します。その後、INSERT SELECTにて表BRICKS_EXTより表BRICKS_IMPにデータをコピーします。

insert into bricks_imp select * from bricks_ext;

13:59:54 SQL> delete from bricks_imp;

10,000,000行削除されました。

経過時間: 00:00:26.859

14:00:31 SQL> commit;

コミットが完了しました。

経過時間: 00:00:00.365

14:00:36 SQL> select bytes from user_segments where segment_name = 'BRICKS_IMP';

      BYTES 

___________ 

   26214400 

経過時間: 00:00:00.352

14:00:38 SQL> insert into bricks_imp select * from bricks_ext;

10,000,000行挿入しました。

経過時間: 00:00:30.709

14:01:35 SQL> commit;

コミットが完了しました。

経過時間: 00:00:00.525

14:02:30 SQL> select bytes from user_segments where segment_name = 'BRICKS_IMP';

      BYTES 

___________ 

   59768832 

経過時間: 00:00:00.350

14:02:34 SQL> 

INSERT SELECTでも処理時間はそれほど変わりません。インサート後のセグメントのサイズが倍増していることより、こちらもダイレクト・パス・インサートになっているようです。

データのロードを繰り返す場合は、DELETE文よりTRUNCATE文を使う方が、パフォーマンス面での問題が発生しにくいと思われます。

APEX_DATA_LOADINGを使ったデータ・ロード

パッケージAPEX_DATA_LOADINGに含まれるプロシージャLOAD_DATAを使うには、APEXのアプリケーションに作成されたデータ・ロード定義が必要です。

出力されているCSVファイルの、先頭から数行をコピーしておきます。データをCSV形式でエクスポートする際にNLS_LANGを指定していなかったので、日付のフォーマットがDD-MON-RR(英語)になっています。

1,blue,cylinder,2,01-JAN-11,xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

2,blue,pyramid,8,01-JAN-11,xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

3,blue,prism,3,01-JAN-11,xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

4,blue,cube,3,01-JAN-11,xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

5,blue,cylinder,4,01-JAN-11,xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

6,blue,pyramid,2,01-JAN-11,xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

アプリケーション・ビルダーに移ります。

アプリケーションの共有コンポーネントに含まれる、データ・ロード定義を開きます。

作成済みのデータ・ロード定義が一覧されます。

作成をクリックします。

データ・ロードの作成として、最初からを選択します。

次へ進みます。

名前をBRICKSとします。ターゲット・タイプは表、表名としてBRICKS_IMPを選択します。

次へ進みます。

ソース・タイプとしてコピー・アンド・ペーストを選択し、サンプルとして、一部のデータを貼り付けます。

次へ進みます。

DBMS_CLOUD.COPY_DATAの出力にヘッダーが含まれていないため、列見出しの最初の行にヘッダーが含まれるのチェックは外します。マップ先はCSVのデータ位置に合わせて、表BRICKS_IMPの列を指定します。INSERT_DATEの書式マスクとしてDD-MON-RRを指定します。列BRICK_IDの主キーにチェックを入れます。

ページの作成は不要です。データ・ロードの作成をクリックします。

以上でデータ・ロード定義が作成されます。作成されたデータ・ロード定義の細部を調整するために、データ・ロード定義BRICKSを開きます。

APEXのデータ・ロードの場合、ロード・メソッドを追加、マージ、置換から選ぶことができます。DBMS_CLOUD.COPY_DATAとの比較という意味では、追加が近い動作になるため、追加を選びます。コミット間隔の数値を大きくすると速度は上がりますが、今回はデフォルトの200のままにします。

静的IDは、コードからデータ・ロードを呼び出す際に使用します。

データ・プロファイルの編集をクリックして、編集画面を開きます。

残念なことに、詳細の最初の行にヘッダーが含まれるがONになっています。また、列のセレクタ・タイプとセレクタの設定も、ウィザードで指定した通りになっていません。

最初の行にヘッダーが含まれるをOFFにします。また、列をそれぞれ開き、セレクタ・タイプを順序、セレクタに列の位置を数値で設定します。

以上の変更を行い、変更の適用をクリックします。

データ・ロード定義の画面も閉じます。

以上で、データ・ロード定義の作成は完了です。

APEX_DATA_LOADINGを使ったインポートは、以下のスクリプトで実行します。

列INSERT_DATEの月の表記は英語なので、スクリプトの実行時にNLS_LANGUAGEをAmericanに変更します。

16:42:05 SQL> alter session set NLS_LANGUAGE = 'American';

Sessionが変更されました。

経過時間: 00:00:00.526

16:42:20 SQL> @import

22-04-04 07:42:22.534203000 +00:00: dbms_cloud.get_object

Length: 8094330

22-04-04 07:42:22.807388000 +00:00: apex_session.create_session

22-04-04 07:42:22.818248000 +00:00: apex_data_loading.load_data

22-04-04 07:50:31.205545000 +00:00: end

PL/SQLプロシージャが正常に完了しました。

経過時間: 00:08:09.042

経過時間: 00:08:09.219

16:50:31 SQL> 

APEX_DATA_LOADING.LOAD_DATAでは100,000行で8分程度時間がかかっています。コミットに時間がかかっているわけではないので、コミット間隔を増やしてもそれほど効果はなさそうです。

10万行のロードで8分かかる、ということは画面からの実行であればタイムアウトが発生するでしょう。タイムアウトに関しては、データのロード処理をバックグラウンドで実行する（こちらの記事の最後の方で紹介）ことにより対応できます。とはいえ、あまり大量の行をAPEX_DATA_LOADINGを使ってインポートするのは現実的ではなく、Autonomous DatabaseであればDBMS_CLOUDパッケージや外部表、それ以外では外部表やSQL*Loaderなどの利用が有効と言えます。

完

表をCSV形式でダウンロードするアプリの作成

以下の手順で、表のデータをダウンロードするアプリを作成します。

1. パーシング・スキーマから、表をひとつ選択する。
2. 選択した表をオブジェクト・ストレージに、CSV形式でエクスポートする。DBMS_CLOUD.EXPORT_DATAを呼び出します。
3. 出力されたオブジェクトのURLを取得する。
4. オブジェクトのURLにリダイレクトし、ブラウザからダウンロードする。

表のエクスポート先となるバケットを作成します。

OCIのコンソールより、ストレージのオブジェクト・ストレージとアーカイブ・ストレージのバケットを開きます。

バケットの作成をクリックします。

バケット名はdownloadとします。それ以外はデフォルトのまま変更せず、作成を実行します。

バケットdownloadが作成されます。アクセス制御の実装を省くため、バケットdownloadの操作メニューを開き、可視性の編集を実行します。

可視性としてパブリックを選択します。ユーザーにこのバケットのオブジェクトのリスト表示を許可については、チェックを外します。

変更の保存をクリックします。

バケットdownloadの準備は以上で完了です。

APEXのアプリケーション・ビルダーに移ります。

アプリケーション作成ウィザードを起動し、空のアプリケーションを作成します。名前は表のエクスポートとします。

アプリケーションの作成を実行します。

アプリケーションが作成されたら、ページ・デザイナでホーム・ページを開きます。

Content Bodyにリージョンを作成します。

識別のタイトルは表のエクスポート、タイプは静的コンテンツとします。

オブジェクト・ストレージの操作に使用するページ・アイテムを４つ、P1_REGION、P1_NAMESPACE、P1_BUCKETおよびP1_CREDENTIALを作成します。すべてタイプはテキスト・フィールドです。

ページ・アイテムP1_REGIONのラベルはリージョンとします。

ページ・アイテムP1_NAMESPACEのラベルはネームスペースとします。

ページ・アイテムP1_BUCKETのラベルはバケットとします。

ページ・アイテムP1_CREDENTIALのラベルはクリデンシャルとします。

すべてに値が設定されていないとオブジェクト・ストレージの操作ができないため、作成した４つのページ・アイテムを選択し、検証の必須の値をONにします。

エクスポートする対象の表の選択に使用する、ページ・アイテムを作成します。

アイテムの作成を実行します。

識別の名前はP1_TABLE_NAME、タイプとしてポップアップLOVを選択します。ラベルはエクスポートする表とします。検証の必須の値はONです。

LOVのタイプとしてSQL問合せを選択し、SQL問合せに以下を記述します。アプリケーションのパーシング・スキーマに含まれる表から、エクスポートする表を選択します。

select table_name from user_tables order by 1

追加値の表示はOFF、NULL値の表示をONとし、NULL表示値として- エクスポートする表を選択する -を記述します。

選択した表のオブジェクト・ストレージへのエクスポートと、ブラウザへのダウンロードを実行するボタンを作成します。

ボタンの作成を実行します。

識別のボタン名はB_DOWNLOAD、ラベルはダウンロードとします。動作のアクションはデフォルトのページの送信のままとします。

ボタンを押したときに実行されるプロセスを作成します。

左ペインでプロセス・ビューを表示し、プロセスの作成を実行します。

作成したプロセスの識別の名前はダウンロードとします。タイプはコードを実行を選択します。ソースの位置はローカル・データベース、PL/SQLコードとして以下を記述します。

サーバー側の条件のボタン押下時に、B_DOWNLOADを指定します。

以上でアプリケーションは完成です。

アプリケーションを実行すると、先頭のGIF動画のように動作します。

出力先として指定したバケットには、以下のように一時ファイルが残ります。

ハウスキーピングのタスクなどは、要件に応じて実装を行う必要があるでしょう。

今回作成したアプリケーションのエクスポートを以下に置きました。

https://github.com/ujnak/apexapps/blob/master/exports/csv-export-and-download.sql

Oracle APEXのアプリケーション作成の参考になれば幸いです。

完

DBMS_CLOUD.EXPORT_DATAを使ったエクスポートとの速度比較

 データベースの表データをCSV形式にして、オブジェクト・ストレージにエクスポートします。以下の２通りの方法で実施し、速度を比較してみました。

1. DBMS_CLOUD.EXPORT_DATAを使って、直接オブジェクト・ストレージにCSV形式でエクスポートする
2. APEX_DATA_EXPORT.EXPORTを使ってBLOBにCSVデータを書き出した後、DBMS_CLOUD.PUT_OBJECTを呼び出してアップロードする

最初の方法を実行するコードは以下です。

２番目の方法を実行するコードは以下です。

作業自体は表BRICKSの作成なども含め、こちらの記事で作成した環境にて実施しています。

1番目の方法 - 行数10,000 - 約1秒

22-04-01 02:29:51.498847000 +00:00: dbms_cloud.export_data

22-04-01 02:29:52.537668000 +00:00: end

2番目の方法 - 行数10,000 - 約2.5秒

22-04-01 02:30:02.391526000 +00:00: start

22-04-01 02:30:02.397568000 +00:00: apex_exec.open_query_context

22-04-01 02:30:02.399717000 +00:00: apex_data_export.export

22-04-01 02:30:04.462289000 +00:00: utl_compress.lz_compress

22-04-01 02:30:04.487900000 +00:00: utl_file

22-04-01 02:30:04.589217000 +00:00: dbms_cloud.put_object

22-04-01 02:30:04.713105000 +00:00: end

1番目の方法 - 行数100,000 - 約1.5秒

22-04-01 02:31:06.601669000 +00:00: dbms_cloud.export_data

22-04-01 02:31:07.966000000 +00:00: end

２番目の方法 - 行数100,000 - 約25秒

22-04-01 02:31:15.171480000 +00:00: start

22-04-01 02:31:15.176659000 +00:00: apex_exec.open_query_context

22-04-01 02:31:15.178690000 +00:00: apex_data_export.export

22-04-01 02:31:39.284485000 +00:00: utl_compress.lz_compress

22-04-01 02:31:39.469500000 +00:00: utl_file

22-04-01 02:31:39.595496000 +00:00: dbms_cloud.put_object

22-04-01 02:31:39.778184000 +00:00: end

1番目の方法 - 行数1,000,000 - 約3秒

22-04-01 02:54:53.030251000 +00:00: dbms_cloud.export_data

22-04-01 02:54:56.010861000 +00:00: end

２番目の方法 - 行数1,000,000 - 約4分

22-04-01 02:32:12.818698000 +00:00: start

22-04-01 02:32:12.829158000 +00:00: apex_exec.open_query_context

22-04-01 02:32:12.831126000 +00:00: apex_data_export.export

22-04-01 02:36:08.515317000 +00:00: utl_compress.lz_compress

22-04-01 02:36:10.202244000 +00:00: utl_file

22-04-01 02:36:10.474738000 +00:00: dbms_cloud.put_object

22-04-01 02:36:11.024944000 +00:00: end

１番目の方法 - 行数10,000,000 - 約21秒

22-04-01 03:14:09.332436000 +00:00: dbms_cloud.export_data

22-04-01 03:14:30.916486000 +00:00: end

パッケージAPEX_DATA_EXPORTはクラシック・レポート、対話モード・レポートや対話グリッドのダウンロードの機能で使用されています。画面から100万行のデータをダウンロードするといったユース・ケースはあまりないので気にしたことはなかったですが、DBMS_CLOUD.EXPORT_DATAと比べるとかなり重い処理になっているようです。

APEX_DATA_EXPORT.EXPORTがサポートしているファイル形式はXLSX、PDF、HTML、CSV、XMLおよびJSONで、人間が利用するを想定していることがわかります。大量のデータを交換する用途には向いていようです。

列数にも依存関係があるかもしれないので一概には言えないですが、100万行で苦しい感じで1千万行は実行できないでしょう。

Autonomous Databaseで大量のデータをCSVやJSON形式で交換するには、DBMS_CLOUD.EXPORT_DATAの使用を優先的に検討する必要があるででしょう。

完