テーブルのある範囲を、別のテーブルから抜き出した結果に基づいて更新する、という処理を考える。このとき、単一の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だけでやるのって、フツーの手続き型プログラミングに慣れてるプログラマからすると理解が困難になることが多く。速度的に優位だとしても、メンテ不能になる恐れがあるなら手続き的にやるのも已む無し、って場合もあるんではなかろうか。
