SQL*Plus で CSV ファイルを作成する
ここでは Oracle SQL*Plus 上で CSV ファイルを作成してみます。そもそも SQL*Plus に CSV 出力機能が備わっていればいいのですが、その機能がないので SQL*Plus コマンドの設定で CSV ファイルにデータを出力してみます。また出来る限り高速で出力する方法を試してみます。通常の SQL*Plus コマンドでの CSV ファイル出力は「まぁこんなものかな」という程度の速度だと思いますが、実際にはちょっと SQL*Plus のパラメータを調整してあげるだけでレコード数が多い場合には特に体感的にもとても早くなります。
CSV 出力コマンドの例
以下では SQL*Plus コマンドを使用して CSV ファイルを出力していますが、ARRAYSIZE という設定をしているところが重要です。これにより CSV ファイルの作成パフォーマンスを向上させています。SET ARRAYSIZE 100 SET FLUSH OFF SET LINESIZE 100 SET PAGESIZE 0 SET SQLPROMPT OF SET FEEDBACK OFF SET TIMING ON SET TERMOUT OFF SET TRIMSPOOL ON SET SERVEROUTPUT OFF SPOOL /tmp/test.csv SELECT id ||','|| name FROM test; SPOOL OFF QUIT
ARRAYSIZE パラメータの有無でパフォーマンスを比較
では ARRAYSIZE パラメータの設定有無でどの程度出力パフォーマンスが異なるのか実際に試してみます。ここでは以下のようなテーブルとテストデータを１０万件作成します。
-- テーブル作成
CREATE TABLE test (
id number(10),
name varchar2(20)
);
-- テストデータ作成用プロシージャ
CREATE OR REPLACE PROCEDURE generate_test_data AS
BEGIN
FOR var_i IN 1..100000 LOOP
INSERT INTO test (id, name) VALUES (var_i, 'name');
END LOOP;
COMMIT;
END generate_test_data;
-- テストデータ作成用プロシージャを呼び出し (１０万件作成)
SQL> EXEC generate_test_data;
PL/SQL procedure successfully completed.
-- 件数確認
SQL> SELECT COUNT(*) FROM test;
COUNT(*)
----------
100000
１０万件のデータが作成できましたので、上述の SQL を ARRAYSIZE を設定しない場合とした場合とで速度を比較します。
[ARRAYSIZE を設定していない場合]
$ time sqlplus hoge/hoge @test.sql SQL*Plus: Release 10.2.0.4.0 - Production real 0m2.407s user 0m0.724s sys 0m0.060s
[SET ARRAYSIZE 100 を設定した場合]
$ time sqlplus hoge/hoge @test.sql SQL*Plus: Release 10.2.0.4.0 - Production real 0m1.601s user 0m0.804s sys 0m0.032s
上記結果からは、ARRAYSIZE を設定することによって、私の Oracle データベース環境で１０万件のデータを CSV ファイルに出力する場合、およそ１．５倍程度 ARRAYSIZE を設定したほうが早いことがわかります。
もしデータ数が１００万件であれば速度は更に違ってきますし、ネットワーク越しに実行すれば更に速度に開きが出てきます。
ARRAYSIZE の値によって何故パフォーマンスが異なるのか
SQL*Plus での ARRAYSIZE のデフォルトサイズは 15 なのですが、これだと結果を蓄積するバッファサイズが小さすぎて、イチイチ細かい単位で SQL の実行結果が SQL の発行元に返されてしまい、特にネットワーク越しに実行するとネットワーク経由でのやりとりが非常に多くなります。これにより体感的にも非常に遅く感じます。そのため ARRAYSIZE を 100 に設定することで少しでも早くするようにしています。このことにより ARRAYSIZE のデフォルト値である 15 よりも多くの結果を蓄積して纏めてネットワークを介して SQL 発行元に結果を返すことが出来るようになり、結果としてパフォーマンスが向上します。
なお ARRAYSIZE の値を 1000 等にしてもあまり効果はありませんでしたので 50 から 100 程度が適度なサイズなのかもしれません。
