軽くぐぐっただけなので間違っているかもしれないが、OracleにはCSV出力の機能なりAPIなりは備わっていない。ただし、SQL*PlusのコマンドであるとかPL/SQLを使うとかでCSV出力は実現出来る。今回のエントリでは、自分が調べられた範囲内でCSV出力の方法を色々試してみようと思う。
試す方法の要約は下記の通り。
- PL/SQLでUTL_FILEを使用してCSV出力（ネイティブコンパイル有効）
- Oracleとは別マシンから、JDBD経由でCSV出力
- Oracleとは別マシンから、SQL*PlusでSPOOLを使用してCSV出力
- Oracleとは別マシンから、SQL Developerのエクスポート機能を使用してCSV出力
以下では、計測対象のテーブル構成に続き、それぞれの方法のプログラム等を示す。そのあと、実行時間の比較と感想を述べる。
環境
計測対象のテーブル構成と実行時間の計測方法
サンプルスキーマ（hr）に入っているEMPLOYEESテーブルを使用する。
CREATE TABLE EMPLOYEES EMPLOYEE_ID NUMBER(6,0), FIRST_NAME VARCHAR2(20 BYTE), LAST_NAME VARCHAR2(25 BYTE) NOT NULL, EMAIL VARCHAR2(25 BYTE) NOT NULL, PHONE_NUMBER VARCHAR2(20 BYTE), HIRE_DATE DATE NOT NULL, JOB_ID VARCHAR2(10 BYTE) NOT NULL, SALARY NUMBER(8,2), COMMISSION_PCT NUMBER(2,2), MANAGER_ID NUMBER(6,0), DEPARTMENT_ID NUMBER(4,0) );
レコード数をおおむね100万件（107 * 1000 * 10）に増やす。
CREATE TABLE employees AS SELECT hr.employees.* FROM hr.employees, (SELECT 1 FROM all_catalog WHERE ROWNUM <= 1000), (SELECT 1 FROM all_catalog WHERE ROWNUM <= 10);
速度計測はこのテーブルの全行CSV出力にかかる時間を計測する。実際に実行するSQLとしては、下記となる。また、吐き出されるCSVファイルはおおむね90MBとなる。
SELECT * FROM employees;
また、計測用プログラムを走らせる前に下記コマンドでバッファキャッシュをクリアする。
ALTER SYSTEM FLUSH BUFFER_CACHE;
計測用プログラム
Java
public class OutputCSV { public static void main(String[] args) throws Exception { long start = System.currentTimeMillis(); final String br = System.getProperty("line.separator"); final String comma = ","; final String sqlStr = "SELECT * FROM employees"; try (Connection connection = DriverManager.getConnection( "jdbc:oracle:thin:@192.168.0.20:1521:XE", "kagamihoge", "xxxx"); PreparedStatement sql = connection.prepareStatement(sqlStr);) { sql.setFetchSize(100); try (ResultSet r = sql.executeQuery(); BufferedWriter writer = Files.newBufferedWriter( Paths.get("employees.csv"), Charset.defaultCharset())) { while (r.next()) { StringBuilder b = new StringBuilder(); b.append(r.getString("employee_id")).append(comma) .append(r.getString("first_name")).append(comma) .append(r.getString("last_name")).append(comma) .append(r.getString("email")).append(comma) .append(r.getString("phone_number")).append(comma) .append(r.getString("hire_date")).append(comma) .append(r.getString("job_id")).append(comma) .append(r.getString("salary")).append(comma) .append(r.getString("commission_pct")).append(comma) .append(r.getString("manager_id")).append(comma) .append(r.getString("department_id")) .append(br); writer.write(b.toString()); } } } long end = System.currentTimeMillis(); System.out.println(end - start); } }
setFetchSize(100)をしている以外、特にこれといった工夫はしていない。なお、上記のプログラムはOracleが動いているマシンとは物理的に異なるマシンで実行する。
PL/SQL(UTL_FILE)
Oracle Database PL/SQLパッケージおよびタイプ・リファレンス11g リリース2(11.2) 224 UTL_FILEを使用してCSV出力を行う。
また、Oracle11gのPL/SQLのネイティブコンパイル - kagamihogeの日記の手順でネイティブコンパイルを有効にした。
create or replace PROCEDURE OUTPUT_CSV AS outputCSVFileHandle UTL_FILE.FILE_TYPE; CURSOR employeesCursor IS SELECT * FROM employees; employeesRow employeesCursor%ROWTYPE; BEGIN outputCSVFileHandle := UTL_FILE.FOPEN('CSV_OUTPUT_DIR','employees.csv','w',100); OPEN employeesCursor; LOOP FETCH employeesCursor INTO employeesRow; EXIT WHEN employeesCursor%NOTFOUND; UTL_FILE.PUT_LINE(outputCSVFileHandle, employeesRow.employee_id ||','|| employeesRow.first_name ||','|| employeesRow.last_name ||','|| employeesRow.email ||','|| employeesRow.phone_number ||','|| employeesRow.hire_date ||','|| employeesRow.job_id ||','|| employeesRow.salary ||','|| employeesRow.commission_pct ||','|| employeesRow.manager_id ||','|| employeesRow.department_id); END LOOP; CLOSE employeesCursor; END OUTPUT_CSV;
また、比較のためにバルクフェッチを使用したバージョンもついでに作成した。
create or replace PROCEDURE OUTPUT_CSV_WITH_BULK AS BULK_SIZE CONSTANT PLS_INTEGER := 100; outputCSVFileHandle UTL_FILE.FILE_TYPE; CURSOR employeesCursor IS SELECT * FROM employees; TYPE typeEmployees IS TABLE OF employeesCursor%ROWTYPE INDEX BY BINARY_INTEGER; employeesRows typeEmployees; BEGIN outputCSVFileHandle := UTL_FILE.FOPEN('CSV_OUTPUT_DIR','employees.csv','w',100); OPEN employeesCursor; LOOP FETCH employeesCursor BULK COLLECT INTO employeesRows LIMIT BULK_SIZE; EXIT WHEN employeesRows.COUNT = 0; FOR i IN 1 .. employeesRows.COUNT LOOP UTL_FILE.PUT_LINE(outputCSVFileHandle, employeesRows(i).employee_id ||','|| employeesRows(i).first_name ||','|| employeesRows(i).last_name ||','|| employeesRows(i).email ||','|| employeesRows(i).phone_number ||','|| employeesRows(i).hire_date ||','|| employeesRows(i).job_id ||','|| employeesRows(i).salary ||','|| employeesRows(i).commission_pct ||','|| employeesRows(i).manager_id ||','|| employeesRows(i).department_id); END LOOP; END LOOP; CLOSE employeesCursor; END OUTPUT_CSV_WITH_BULK;
SQL*Plus(SPOOL)
ＯＲＡＣＬＥ／オラクルＳＱＬリファレンス（SQLPLUS）などを参考にし、SQL*Plusユーザーズ・ガイドおよびリファレンスリリース11.2 8 SQL*Plusのチューニング SQL*Plusスクリプトのチューニングを参考にしてチューニングを施したSQL*Plus用のスクリプトでCSV出力を行う。
下記のうち、SET TIMING ONは実行時間計測のために入れているので、実際には不要である。また、実行時間の計測は2種類おこなう。2種類とは、LINESIZE 100（百）とLINESIZE 1000（千）を意味する。
SET ARRAYSIZE 100 SET FLUSH OFF SET LINESIZE 100 SET PAGESIZE 0 SET SERVEROUTPUT OFF SET SQLPROMPT OF SET FEEDBACK OFF SET TIMING ON SET TERMOUT OFF SET TRIMSPOOL ON SPOOL C:\sqlplustest\employees.csv SELECT e.employee_id ||','|| e.first_name ||','|| e.last_name ||','|| e.email ||','|| e.phone_number ||','|| e.hire_date ||','|| e.job_id ||','|| e.salary ||','|| e.commission_pct ||','|| e.manager_id ||','|| e.department_id FROM employees e; SPOOL OFF QUIT
実行時間
それぞれの方法を、一部の例外を除き、三回ずつ行った結果が以下の通り。
sql*plus LINESIZE 1000（千）は一度しか試行していないが、めんどくさくなって省略した。ARRAYSIZEは100と、明記してない場合はデフォルトの15で実行している。SQL Developerのエクスポート機能は、実行時間の表示は行ってくれない。そのため、実行時間の確認は目視で時計の秒針を数える方法で行った。
| 種類 | 1 | 2 | 3 |
|---|---|---|---|
| Java | 19.953 | 19.985 | 19.985 |
| UTL_FILE | 77.219 | 77.157 | 77.39 |
| UTL_FILE（バルクフェッチ） | 48.672 | 48.453 | 48.781 |
| sql*plus LINESIZE 100 ARRAYSIZE 100 | 33.53 | 33.60 | 33.56 |
| sql*plus LINESIZE 100 | 55.98 | 56.04 | 55.20 |
| sql*plus LINESIZE 1000 | 01:27.64 | なし | なし |
| SQL Developerエクスポート | 01:22.00 | 01:20.00 | 01:20.00 |
感想とか
まず、Javaが異様に速いという結果に困惑した。事前の予想としては、ネットワーク通信を介さないPL/SQLが最速だろう、と考えていた。同一HDDでのディスクtoディスクの処理は不利ではあろうが、ネットワーク通信が間に入るよりかはマシだろう、と。しかし現実には、バルクフェッチにした分だけ速くなりはしたものの、Javaの速度には程遠い結果となった。
こうなる要因とは何か。ハードウェアの性能差が大きいのでは？ と考えている。ハードのスペックいちいち書くの面倒なので省略するけど、ウチのOracleが動いてるマシンはまぁぶっちゃけかなりショボいスペックである。対して、Javaを実行するクライアント機は、CPUにしろディスクにしろ遥かに性能が高い。よって、仮説ではあるが、ネットワーク通信してもなお、Oracleのマシンより遥かに性能がマシなクライアントマシンでCSV出力をやらせる方が速くなるのでは？ と。
今回の計測結果からその裏づけが取れないか、を考える。まず、バルクフェッチのコードからCSV出力を外してみる。このSQLおよびフェッチの実行時間は約4秒程度でしかないため、CSV出力には引き算すると48-4=約44秒程度かかっている目算になる。そこで、同様にJavaでCSV出力の部分を外して実行時間を見ると約9秒程度となった。そのため、SQL実行およびフェッチの部分だけを見ると、ネットワーク通信の生じないPL/SQLがやはり有利である。
しかし、JavaでCSV出力にかかる時間は引き算すると19-9=約10秒程度でしかない。Oracle上でCSV出力は約44秒なのを考えると、SQL実行およびフェッチの約5秒差程度の速度優位は埋れてしまうことになる。以上のことから、ネットワーク通信の不利さを覆してしまう程度には、クライアント機とのマシンスペック差が響いている、と言えるのでは、と考えられる。SQL*Plusもクライアント側で実行しているので、事情は同様と考えられる。
ただまぁ……本番環境でOracleサーバよりもクライアント機のほうがスペックが格段上、というのはちょっと考えづらい*1。なので、今回のエントリの実行時間比較は、前提条件の置き方などのやり方がちょっとまずかったかもなぁ、と反省している。
Oracleが稼動しているマシンスペックが充分なものであれば、おそらく違う結果になるハズである。
とはいえこの点から学べることとは何があるだろうか。まず、ある環境である方法が速いかどうかは、計測してみるまで分からない、点が上げられる。今回はPL/SQLが速いだろうと踏んだわけだが、実際には異なる結果となった。環境に依存するからこの計測結果に余り意味は無いとしても、環境というのはそれこそ千差万別である。現実世界の業務では、その千差万別の環境を実際には相手するわけで、まず計測ありき、と昔から言われるのはその通りだと感じた。
また、CSV出力の実行時間は大きく二種類に分けられる。それは、SQL実行およびフェッチとファイル出力である。どっちもディスクI/Oが絡むとはいえ、ビミョーに色合いの違う処理である。前者はOracle固有だが、後者はそうでは無い。たとえば、Oracleサーバが常に高い負荷がかかっていたとき、ファイル出力は別マシンにやらせた方が速くなるかもしれない。まぁ計測してみないと分からないけども。とはいえ別ブロックに分けられると認識出来れば、手の内も拡がる。なお、速度アップは最終的にはCSV 出力を１０倍高速にする方法 - オラクル・Oracle PL/SQL 入門に行き着くのでは、と思われる。
SQL*Plusは、まずLINESIZEに実行時間が左右される。行が途切れるのは論外としても、あまりデカい幅を取るとただそれだけで実行時間が悪化する。ARRAYSIZEは、リファレンスでは「SQL*PlusおよびOracle Databaseの最近のバージョンでは、ARRAYSIZEによる効果はほとんどありません。」と書いてあるものの、この環境では100くらいまでは実行時間の改善が見られた。300くらいまでは僅かずつ改善があったが、1000にするとほぼ変わらない結果となった。
CSV出力をクライアント側でやる、という点ではJavaもSQL*Plusも同様である。ただ、実行時間はJava優位。そのため、SQL*Plusは大量のデータをさばく用途のツールではないのだろう、と考えられる。
実行時間についてアレコレ書いてきたが、現実の業務では手軽さも重要な点である。SQL*Plus経由の方法はカンタンだし伝統もあるのでネットでの情報も多い。LINESIZEにさえ気を付けてやればそこその速度は出る。ただ、さすがに数千数億行とかよほど大規模なデータを扱うには力不足ではあるだろうけれど。
お手軽さで言えば、SQL Developerのエクスポート機能もある。速度的にはこの中で一番ビミョーなのだが、GUIでぽちぽちっとするだけでOKなのは利点と言える。PL/SQLにしろJavaにしろ作り込むには心構えが必要だが、それに比べるとSQL*Plusやツールの機能を使うのはお手軽である。
参考文献＆URL
*1:Javaなどのアプリケーションサーバとなると話はまた別とは思うが
