CSVファイルを元にUPDATEする

下記のように主キーと値からなるテーブルがあるとする。

ID	VALUE
1	value1
2	value2
3	value3

上記のテーブルを、下記のようなCSVファイルを入力として更新していくプログラムを考える。

1,update_value1
2,update_value2
3,update_value3

環境

• DB
  • CentOS-6.4-x86_64
  • Oracle Database Express Edition 11g Release 2
• Java
  • Java SE Development Kit 7u45
  • Eclipse Kepler(4.3.1) SR1 IDE for Java EE Developers
  • Oracle Database 11g Release 1 JDBCドライバ（ojdbc6.jar）
• ツール
  • Oracle SQL Developer 4.0
  • RLogin Version 2.14.7

準備

更新対象のテーブルを作成し、データを挿入する。計測の基準をあわせるためにバッファキャッシュをクリアしておく。なお、計測対象のプログラム実行前に下記の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のパラメータが何百個とか中々コーディングがめんどい。

参考文献＆URL

• 達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)
• Oracle Databaseリファレンス11gリリース2 (11.2) 統計情報の説明

ソースコード

https://github.com/kagamihoge/oraclestudy/tree/master/src/main/java/oraclestudy/updatefromfile