下記のように主キーと値からなるテーブルがあるとする。
| ID | VALUE |
|---|---|
| 1 | value1 |
| 2 | value2 |
| 3 | value3 |
上記のテーブルを、下記のようなCSVファイルを入力として更新していくプログラムを考える。
1,update_value1 2,update_value2 3,update_value3
環境
準備
更新対象のテーブルを作成し、データを挿入する。計測の基準をあわせるためにバッファキャッシュをクリアしておく。なお、計測対象のプログラム実行前に下記のSQLを実行して、サラの状態を作ること、とする。
DROP TABLE UPDATE_TABLE PURGE; CREATE TABLE UPDATE_TABLE ( ID INTEGER NOT NULL , VALUE VARCHAR2(20) NOT NULL , CONSTRAINT UPDATE_TABLE_PK PRIMARY KEY ( ID ) ENABLE ); INSERT INTO UPDATE_TABLE(ID, VALUE) SELECT ROWNUM, dbms_random.string('X', 20) FROM (SELECT ROWNUM FROM all_catalog WHERE ROWNUM <= 1000), (SELECT ROWNUM FROM all_catalog WHERE ROWNUM <= 100); COMMIT; ALTER SYSTEM FLUSH BUFFER_CACHE;
更新内容が書かれたCSVファイル（input.csv）はこんな感じ。CSVファイルの各行がテーブルの一行に相当し、1列目が主キー列のIDに、2列目が値列のVALUEに、それぞれ対応する。
1,AAA5X2444FCJ05D1RFZJ 2,AAAS3X74XE35JB0XZ5PS 3,AAAG7MX9T8UCVJ7724VI
計測対象のプログラム
一行ずつUPDATE
JavaでCSVファイルを読み込み、一行ずつUPDATEを発行する。
public class UpdateFromFile { public static void main(String[] args) throws IOException, SQLException { final int IND_ID = 0; final int IND_VALUE = 1; final int PARAM_IND_ID= 2; final int PARAM_IND_VALUE = 1; long start = System.currentTimeMillis(); List<String> lines = Files.readAllLines(Paths.get("input.csv"), Charset.defaultCharset()); final String updateStr = "UPDATE update_table SET VALUE = ? WHERE ID = ?"; try (Connection connection = DriverManager.getConnection( "jdbc:oracle:thin:@192.168.0.20:1521:XE", "kagamihoge", "xxxx"); PreparedStatement sql = connection.prepareStatement(updateStr);) { connection.setAutoCommit(false); for (String idAndValue : lines) { String[] split = idAndValue.split(","); sql.setString(PARAM_IND_VALUE, split[IND_VALUE]); sql.setInt(PARAM_IND_ID, Integer.parseInt(split[IND_ID])); sql.executeUpdate(); } connection.commit(); } long end = System.currentTimeMillis(); System.out.println(end - start); } }
CASE使用UPDATE
CASE式を使用して複数のUPDATEをひとつにまとめる。参考：達人に学ぶ SQL徹底指南書 (CodeZine BOOKS) p.19 第一部 魔法のSQL 1-1 CASE式のススメ 条件を分岐させたUPDATE
JavaにSQLが埋め込まれていて分かりにくいが、下記のようなSQLをCSVファイル50行ごとに1回発行している。コード中ではSQLの文字列変数にWHEN 〜 THENが50回繰り返し現れるのでちょっと見づらいが勘弁していただきたい。
UPDATE UPDATE_TABLE SET VALUE = CASE WHEN ID = 1 THEN 'update_value_1' WHEN ID = 2 THEN 'update_value_2' WHEN ID = 3 THEN 'update_value_3' ELSE VALUE END WHERE ID IN (1, 2, 3);
public class UpdateWithCaseFromFile { public static void main(String[] args) throws Exception { final int IND_ID = 0; final int IND_VALUE = 1; long start = System.currentTimeMillis(); List<String> lines = Files.readAllLines(Paths.get("input.csv"), Charset.defaultCharset()); final String updateStr = "UPDATE UPDATE_TABLE SET VALUE = CASE WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? WHEN ID = ? THEN ? ELSE VALUE END WHERE ID IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; try (Connection connection = DriverManager.getConnection( "jdbc:oracle:thin:@192.168.0.20:1521:XE", "kagamihoge", "xxxx"); PreparedStatement sql = connection.prepareStatement(updateStr);) { connection.setAutoCommit(false); int count = 0; for (String idAndValue : lines) { String[] split = idAndValue.split(","); count++; sql.setInt((count*2)-1, Integer.parseInt(split[IND_ID])); sql.setString(count*2, split[IND_VALUE]); sql.setInt(count + 100, Integer.parseInt(split[IND_ID])); if (count == 50) { sql.executeUpdate(); count = 0; } } connection.commit(); } long end = System.currentTimeMillis(); System.out.println(end - start); } }
一時表経由UPDATE
一時表にすべてのデータをINSERTしたあと、その一時表からターゲットテーブルへ一回のSQLでUPDATEする。
まず、下記のSQLで一時表を作成する。
CREATE GLOBAL TEMPORARY TABLE TEMP_UPDATE_TABLE ( ID INTEGER NOT NULL , VALUE VARCHAR2(20) , CONSTRAINT TEMP_UPDATE_TABLE_PK PRIMARY KEY ( ID ) ENABLE ) ON COMMIT DELETE ROWS;
上記の一時表にデータをINSERTしたあと、下記のSQLでUPDATEをかける。
MERGE INTO UPDATE_TABLE u USING ( SELECT ID temp_id, VALUE temp_value FROM temp_update_table ) t ON (u.ID = t.temp_id) WHEN MATCHED THEN UPDATE SET u.VALUE = t.temp_value;
public class UpdateThroughTemporaryFromFile { public static void main(String[] args) throws Exception { final int IND_ID = 0; final int IND_VALUE = 1; final int PARAM_IND_ID= 1; final int PARAM_IND_VALUE = 2; long start = System.currentTimeMillis(); List<String> lines = Files.readAllLines(Paths.get("input.csv"), Charset.defaultCharset()); final String insertIntoTempStr = "INSERT INTO temp_update_table(ID, VALUE) VALUES (?, ?)"; final String updateFromTempStr = "MERGE INTO UPDATE_TABLE u USING (SELECT ID temp_id, VALUE temp_value FROM temp_update_table) t ON (u.ID = t.temp_id) WHEN MATCHED THEN UPDATE SET u.VALUE = t.temp_value"; try (Connection connection = DriverManager.getConnection( "jdbc:oracle:thin:@192.168.0.20:1521:XE", "kagamihoge", "xxxx"); PreparedStatement sql = connection.prepareStatement(insertIntoTempStr); PreparedStatement updateSql = connection.prepareStatement(updateFromTempStr);) { connection.setAutoCommit(false); for (String idAndValue : lines) { String[] split = idAndValue.split(","); sql.setInt(PARAM_IND_ID, Integer.parseInt(split[IND_ID])); sql.setString(PARAM_IND_VALUE, split[IND_VALUE]); sql.executeUpdate(); } updateSql.executeUpdate(); connection.commit(); } long end = System.currentTimeMillis(); System.out.println(end - start); } }
外部表経由UPDATE
外部表でCSVファイルを参照できるようにしたあと、その外部表からターゲットテーブルへ一回のUPDATEで更新をかける。
まず、CSVファイルに対応する外部表を作成する。なお、ディレクトリオブジェクトとかは作成済みとする。
DROP TABLE EXT_UPDATE_TABLE; CREATE TABLE EXT_UPDATE_TABLE ( ID INTEGER, VALUE VARCHAR(20) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY "EXTERNAL_DATA" ACCESS PARAMETERS ( RECORDS DELIMITED BY '\r\n' CHARACTERSET JA16SJISTILDE BADFILE "EXTERNAL_DATA":'BADFILE_LOG.bad' DISCARDFILE "EXTERNAL_LOGS":'DISCARDFILE_LOG.log' LOGFILE "EXTERNAL_LOGS":'LOGFILE_LOG.log' FIELDS TERMINATED BY ',' ( ID DECIMAL EXTERNAL, VALUE CHAR(20) ) ) LOCATION ('input.csv') );
外部表とターゲットテーブルをJOINして更新をかける。
MERGE INTO UPDATE_TABLE u USING ( SELECT ID e_id, VALUE e_value FROM EXT_UPDATE_TABLE ) e ON (u.ID = e.e_ID) WHEN MATCHED THEN UPDATE SET U.VALUE = E.E_VALUE; commit;
なお、上記のクエリはこう書ける気もするのだがORA-01779になる。そして11gではBYPASS_UJVCヒントが使えないので、世間的にはmergeで代用するのが一般的らしい。
UPDATE ( SELECT u.ID u_id, u.VALUE u_value, e.VALUE e_value FROM UPDATE_TABLE u JOIN EXT_UPDATE_TABLE e ON u.ID = e.ID ) set u_value = e_value;
実行結果
| 手法 | 1 | 2 | 3 |
|---|---|---|---|
| 一行ずつ | 35906 | 35609 | 35360 |
| CASE使用 | 4125 | 4032 | 3922 |
| 一時表経由 | 44031 | 41468 | 41031 |
| 外部表経由 | 2875 | 2109 | 2094 |
外部表が圧倒的に早く、次にCASE使用、そのあとかなり差があり、一行ずつ、一時表経由という結果になった。感覚的には、SQLの発行回数が少なければ少ないほど早くなる筈なので、ほぼその通りの結果になっているのは納得がいく。
感想とか
なぜ実行時間に差が出るのか、statspackレポートを使用して理由を考えてみる。下記は、それぞれの方法を実行した前後でのレポートから、Statisticセクションから主なものを抜粋してきたもの。
| Statistic | 外部表 | CASE使用 | 一行ずつ |
|---|---|---|---|
| SQL*Net roundtrips to/from client | 17 | 2,017 | 100,017 |
| consistent gets | 1,959 | 19,804 | 200,635 |
| consistent gets from cache | 1,959 | 19,804 | 200,635 |
| db block changes | 202,948 | 15,903 | 202,789 |
| db block gets | 104,425 | 6,539 | 104,246 |
| db block gets from cache | 104,425 | 6,539 | 104,246 |
| physical read total bytes | 11,183,104 | 11,632,640 | 12,198,912 |
| physical write total bytes | 31,160,832 | 14,868,992 | 31,272,960 |
| redo size | 29,857,800 | 14,303,736 | 29,808,544 |
| undo change vector size | 11,158,016 | 6,749,292 | 11,139,192 |
OracleにおけるUPDATEの基本的な動きだが、更新をするためにデータブロックからバッファキャッシュに読み込みをする。もしバッファキャッシュにあればそこは省略し、バッファキャッシュに変更する値を書き込む。UNDOの動きは省略。一行ずつUPDATEする場合、主キーで更新対象行を一行に絞り込む。ので、まずインデックス経由で1ブロック、データブロックで1ブロックの計2回ブロックアクセスが発生する。レポートの表を見ると、一行ずつのconsistent getsは約20万で、（1 + 1）× 10万行で約20万と考えられる。
次にcase使用のconsistent getsだが、約20万 / 50で4000くらいになりそうだがなっていない。理由は知識不足で分からないが、INLIST ITERATORでデータブロックにアクセスするときは1ブロック1回ずつ読みに行くだろうからその辺かな？ とかそのぐらい。
そして外部表経由は圧倒的にconsistent getsが少ない。この検証用プログラムでは結局全行更新なので、全ブロックにアクセスすることは人間には分かっている。DB側も、一発のUPDATEで全行更新だと判別できると、1つのデータブロックに対する更新はまとめて行って良いと判断できるだろうと推測できる。その結果、consistent getsは少なくなる。しかし、一行ずつ更新する場合はどう足掻いてもブロックを2回アクセスしなければならないので、両者の差は大きく開くことになる。この検証用プログラムの場合、データブロックの頭から順に更新していくからキャッシュヒット率は高いとはいえ、UPDATEは他にもいろんな処理をしなければならず、そのオーバーヘッドが積もり積もって実行時間の差になっている、と考えられる。
また、redo sizeとundo change vector sizeの差も目に付く。case使用は他に比べて約半分になっており、これも実行時間を抑制する主因になっていると考えられる。外部表経由の一発UPDATEももっと減ってよさそうなものであるが、何故CASE使用だけこんなにも減るかの理由はちょっと思い至らない。とはいえ、REDOが減る感覚的な理由は、一回一回チマチマ更新するよりも、どーんとまとめて更新したほうがそりゃ後者の方が小さかろう、といった想像はつく。
ということは、CASE使用で更新する行をもっと増やすと速くなりそうだがIN句に大量の値を指定すると他の要因で遅くなりそうな気がしている。実際に試してないんでなんともいえないが。あと、Java経由であればバッチ更新もあるのでそれを併用すれば外部表経由との速度差はもう少し詰まるかもしれない。ただ、一発のSQLで済ませられるシンプルさではマッタク適わないのだけど。PreparedStatementのパラメータが何百個とか中々コーディングがめんどい。
