読者です 読者をやめる 読者になる 読者になる

kagamihogeの日記

kagamihogeの日記です。

update単独の更新とループでupdateの速度

DB Oracle SQL

テーブルのある範囲を、別のテーブルから抜き出した結果に基づいて更新する、という処理を考える。このとき、単一のupdateのみによる処理と、カーソルで回しながら主キーに基づいてupdateを逐次実行する処理、とでどのような差が発生するかを調べる。

準備

更新対象となる適当なテーブルを作る。customerとordersが一対多を想定。ordersが更新対象で、対象範囲の主キーはcustomerから得る、という想定。

drop table customer purge;
create table customer 
(
  customer_id integer
, customer_name varchar2(10 byte) 
);

drop table orders purge;
create table orders 
(
  order_id integer 
, customer_id integer 
, amount integer 
);

データを作る。customer1件に対し、ordersが5件持たせることにする。下記のクエリ例は、customerに100万、ordersに500万件入れた場合の例。実テーブル内の行順序が、同一のcustomer_idが連続5件ずつ並び続けている。これはやや現実離れしたものだが、細かいことは気にしないことにする。

insert into customer
  select rownum, DBMS_RANDOM.STRING('X', 10)
    from 
      (select rownum from all_catalog where rownum <= 1000),
      (select rownum from all_catalog where rownum <= 1000);
commit;

insert into orders
  select rownum, CEIL(rownum/5), FLOOR(DBMS_RANDOM.VALUE(1, 100000))
    from 
      (select rownum from all_catalog where rownum <= 1000),
      (select rownum from all_catalog where rownum <= 1000),
      (select rownum from all_catalog where rownum <= 5);
commit;

インデックスをつくる。

create unique index ind_cust_id on customer (customer_id);
create index ind_orders_cust_id on orders (customer_id);

検証用プログラム

各検証用プログラムを実行する前に、キャッシュをクリアする。

alter system flush buffer_cache;
updateのみ

customerから抽出した主キーをもとにordersを更新する、という処理の想定。まぁ↓だと全件更新なのでjoinする意味は無いんだけども、検証ってことで。

update (
  select c.customer_id, o.amount
  from   customer c join orders o on c.customer_id = o.customer_id
)
set amount =  FLOOR(DBMS_RANDOM.VALUE(1, 100000));
commit;
ループ update

customerに対するクエリをカーソルで回しつつ、ordersをupdateしていく。本来はcustomerに対するクエリはもっと複雑な条件が付与されるんだろうけど、そこはとりあえず考えないことにする。

なお、commit間隔の考慮はナシ。

create or replace 
PROCEDURE UPDATE_ORDERS AS
BEGIN
  FOR vRec IN (
    SELECT C.CUSTOMER_ID C_ID FROM CUSTOMER C
  ) LOOP
    UPDATE ORDERS
    SET    AMOUNT = FLOOR(DBMS_RANDOM.VALUE(1, 100000))
    WHERE  CUSTOMER_ID = vRec.C_ID;
  END LOOP;
  COMMIT;
END UPDATE_ORDERS;
バルクフェッチ update

本質的には↑のループupdateと変わらないんだけど、多少の小細工を施したもの。customerに対するクエリをカーソルにしてる部分をバルクフェッチにして、IOの低減を図ってみたもの。バルクのサイズ(下記のコードで「←コレ」)は、途中まで5で、customerを10万件のケースから1000に変更している。

こちらも、commit間隔の考慮はナシ。

CREATE OR REPLACE PROCEDURE UPDATE_BULK_ORDERS AS 
  CURSOR CUR_CUSTOMER IS
    SELECT C.CUSTOMER_ID C_ID FROM CUSTOMER C;
  TYPE TYPE_CUSTOMER_IDS IS TABLE OF CUR_CUSTOMER%ROWTYPE INDEX BY BINARY_INTEGER;
  vCUSTOMER_IDS TYPE_CUSTOMER_IDS;
BEGIN
  OPEN CUR_CUSTOMER;
  LOOP
    FETCH CUR_CUSTOMER BULK COLLECT INTO vCUSTOMER_IDS LIMIT 5;--←コレ
    EXIT WHEN vCUSTOMER_IDS.COUNT = 0;
    FOR i in 1..vCUSTOMER_IDS.COUNT
    LOOP
      UPDATE ORDERS
      SET    AMOUNT = FLOOR(DBMS_RANDOM.VALUE(1, 100000))
      WHERE  CUSTOMER_ID = vCUSTOMER_IDS(I).C_ID;
    END LOOP;
  END LOOP;
  COMMIT;
  CLOSE CUR_CUSTOMER;
END UPDATE_BULK_ORDERS;

参考

実行結果

customerの件数を1,000〜1,000,000に変更して速度検証。先に書いたように、更新対象テーブルのordersの件数はcustomerの5倍。

各ケースで三回実行して実行時間のAVGが下記。

(秒) 1,000 10,000 100,000 1,000,000
update 0.464 4.166 42.000 520.015
ループ 0.666 5.515 58.510 640.750
バルク 0.552 5.292 50.314 615.505

AVGを出すのに使用した各回の実行時間の結果はこちら。

customer 1,000

  1 2 3
update 0.516 0.438 0.438
ループ 0.531 0.625 0.843
バルク 0.579 0.531 0.546

customer 10,000

  1 2 3
update 4.078 4.485 3.937
ループ 6.063 5.250 5.234
バルク 5.610 5.313 4.953

customer 100,000 コレ以降バルクのサイズ=1000

  1 2 3
update 42.187 41.922 41.891
ループ 59.359 58.422 57.750
バルク 41.553 54.969 54.422

customer 1,000,000

  1 2 3
update 523.593 523.797 512.657
ループ 658.781 632.688 630.782
バルク 603.672 640.188 602.656

感想とか

やっぱupdate一発でやるのが一番はやかった。updateの発行回数の差であるとか、RDBMS的に考えれば、まぁそんなもんかなと。フツーに考えれば、集合論的というかSQL一発でやるのが速いしエレガントであるように思う。

一方、集合論的な考えでなく手続き的にやるのはどうか。このケースでは、バルクにしてIOを減らせば、単なるループよりは早くなる。pl/sql良く知らないんでもっと良い方法あるとは思う。なので、手続き的でも頑張って作りこめば速度的には近いもんが出せるかな? とも思う。

ただし、簡潔性で言えばSQL一発には遠く及ばない。update単発のケースでは、実行計画が100万まではネステッドループ、100万ではハッシュジョインが選択されている。SQLに対するチューニングは、ここにヒントつけたりインデックス新たに作ったりでやれる。update本体のロジックには手を入れることなく。ブツの動作には手を付けずにチューニングの余地があるのは中々捨てがたい魅力である。もちろんpl/sqlの中のクエリにも同じ事は言える。が、手続きロジックとSQLを一体で作りこんでいる場合は柔軟性が低くなりやすい。

ロールバックセグメントについて。実は、XEのデフォルト設定でコレやって、ロールバックセグメントの表領域のサイズがいきなり急拡大してびびった。もし、本番でコレが突然発生したら肝が冷えるんでないかな、とか感じたわけで。それと、そんだけ大量のデータをrollbarkすると、猛烈な時間がかかる。計測はしなかったけど、updateそのものの実行時間と変わらん、つまりかなりの時間がかかった記憶がある。最も、rollbackする可能性が極小なら無視してもいいんだろうけど、そうでないならあんまデカい範囲を一気に更新するのは考え物だと思われる。この場合、やはりテキトーにコミット間隔設けることになるんでなかろうか。

ただ、色々読んでると、予め広範囲のupdateがわかってるなら、専用のロールバックセグメントの表領域作って、該当プログラム走らせるときにそっちの表領域使うようにする、とかはDBAな人たちはフツーにやるらしい。Oracle的にはやさしそうなやり方ではある。

Oracle的に優しいとはいえ、現実にはビジネス的な要件に従わないといけない場合もある。何らかの順序に基づいて更新する必要があるとか、優先順位に基づいて更新しないといけない、とか。こういう場合は、技術的にはともかく手続き的にやらんといけなくなると思う。

あとは、SQLだけでやるのって、フツーの手続き型プログラミングに慣れてるプログラマからすると理解が困難になることが多く。速度的に優位だとしても、メンテ不能になる恐れがあるなら手続き的にやるのも已む無し、って場合もあるんではなかろうか。