SQL*Plusの使いにくさは異常

主にOracleネタの検証ブログ。

超簡単にテストデータを増幅する①

とりあえずDBに大量のデータを突っ込みたいことってあるよね、という話。
例えば「負荷試験のために数年後を想定したデータ量に増幅する」とか。
しかもリリース前のシステムだったりすると、それはもはやデータを「増幅する」というよりは「作成する」と言った方が正しい苦行になることも。
さて、どうやって乗り切るか。

やり方1 Excelで頑張る

思考停止して急場をしのぐならExcelのオートフィルと関数に頼って大量のINSERT文を作るのもアリといえばアリだと思う。
1万レコード未満なら多分これで全然OK。
DBも選ばないし汎用的。
ただし100万レコード作るとかになってくるとそうも言ってられない。
多分無限に時間がかかる。

やり方2 CSVをSQL*Loaderでロードする

みんな一番やりそうなのが簡単なスクリプトを書いてCSVを作ってからSQL*Loaderで流し込むっていう方法なんじゃなかろうか。
一回CSVを作っちゃえば出し入れも簡単だし、Oracleじゃなくても大体CSVからロードする機能はあるので汎用的でGood。
ただこれも100GBレベルのデータをいくつも作るとかになってくるとつらい。
一時的に置くにしても場所に困るし、そもそも一回ファイルに吐き出したデータをもう一回DBに入れるのが実は二度手間になってる。
領域と時間の無駄。

やり方3 SQLで既存データを増やす

サンプルデータとか既存データとか元から入ってるデータがあるならこれが一番早いと思う。
直接DBからDBにデータを入れるだけなので極めてシンプル。

insert into emp_copy
select
    , empno + 1000 -- 適当に重複しないように
    , ename -- そのまま増やして良さそうなものはそのままコピー
    , job
    , mgr + 1000 -- 増幅した範囲同士で紐づくようにしてみる
    , hiredate + trunc(dbms_random.value(1,356)) -- 適当にばらけさせてみる
    , sal
    , comm
    , deptno
from emp
;

元データの分布に沿って増幅できるので手軽。
PL/SQLで適当に繰り返しておけばいくらでも増やせる。

declare
begin
    for i in 1 .. 10 loop
        insert into emp_copy
        select
            , empno + 1000 * i
            :
            , hiredate + 365 * i -- これで1年ずらしてコピーできる
            :
        from emp
        ;
    end loop;
end;
/

ただ大体こんな風にうまくいくことはない。

やり方4 SQLでどこからともなくデータを生み出す

DB上で直接データを作りながら投入しちゃう方法。 これだけ覚えていってもらえれば良い気がする。
基本的には既存データを増やすのと同じ考え方。
データを作らないといけない都合上値を一定の範囲に分散させたりする小細工は関数で頑張る。

insert into test
select
    , level -- 連番
    , lpad(level, 8, '0') -- 連番を8桁パディング
    , mod(level - 1, 8) + 1 -- 1～8の範囲を繰り返し
    , trunc((level - 1)/ 8) + 1 -- 1を8回、2を8回、3を8回、と繰り返し
from dual
connect by level <= 100000000
;

これで1億レコードを虚空の彼方から召喚できる。
connect by は階層問い合わせの構文なので本来はツリーを辿るようなSELECT文で使うもの。
levelは辿る階層の深さの指定。
詳しくは検索すればいっぱい出て来るし微妙にわかりにくいので割愛。
今回の書き方で言えばとにかく level で指定した分のレコードがどこからともなくわいてくるみたいな動きになる。

その他のポイント

データをDBに入れるときにネックになるのは当然I/O。
いろいろ無駄な読み書きや処理を省けばその分高速になる。
ぶっちゃけ何も考えずにやると1日かかっても終わらないデータロードが、ちゃんと対策してやれば10分で終わったりするので舐めない方が良い。
マジで。

  • インデックスとか制約とか全部外す
    これがあると無茶苦茶遅いのでとにかく全部外すか無効かする。
    ロードが終わってから create index ～ parallel nologging とオプションを付けて再作成してやれば良い。
    ただし作り終わった後に noparallel & logging にしてやるのを忘れるとつらいので絶対直すこと。

  • NOARCHIVEモードにする
    検証環境やリリース前の環境ならアーカイブログなんかいらないのでOFFにしちゃう。

  • テーブルを nologging にする
    ロードするテーブルの属性を alter table ～ nologging で変更しておく。
    あわせてNOARCHIVEモードになっている必要があるのでオプションだけ変えて満足しないこと。
    これでほとんどREDOログが出なくなる。

  • ダイレクトパスインサートする
    insert /*+ append */ into test select ～ とヒントを入れてやるとバッファを経由しないI/Oになる。
    めちゃめちゃ高速。
    代わりにHWM以降に書き込まれる点には注意。
    テスト用に真っ新なテーブルを作ってやる場合なんかは気にしなくてOK。

まとめ

とにかくSQLだけで完結する方が省エネなのでちょっとSQLで頑張ってみましょう。
テストデータの増幅みたいな割と遊べるところで変なSQL書いてみても良いんじゃないかと。
あとデータロードを高速化する方法は本当に覚えておいて損はない、というか圧倒的に速くなるのでそのうち計測の検証してみる予定。