小さい頃はエラ呼吸

いつのまにやら肺で呼吸をしています。


【Oracle】all_catalogテーブルを用いた高速なテストデータの作成

f:id:replication:20140711113813p:plain

はじめに

Oracleを使ったアプリケーションの開発で大量のテストデータが必要になることがあります。
テストデータを作成するために、その都度PL/SQLを書いてInsert文を大量に発行していたんだけど、数百万件とかのオーダになると時間がかかってしまう。

時間がかかるものと思っていた大量のテストデータ作成時間を劇的に短縮するための手法が以下の記事にわかりやすくまとまっています。この記事を参考にして、自分でもテストデータを作成するSQLを書いてみました。

Oracleで適当な件数のテストデータつくる - kagamihogeの日記Oracleで適当な件数のテストデータつくる - kagamihogeの日記

Oracle勉強していて何気に面倒なのが、テキトーな件数のレコードを持つテーブルを作ること。ので、いままでに自分の知ることが出来た範囲でやり方をメモしていく。環...

テストデータを投入するテーブル

f:id:replication:20140711113303p:plain

テストデータ作成SQL

以下のクエリは、次のような処理をしています。
1.テーブルを空にします。
2.ID列にゼロ埋めの連番を8桁設定
3.NAME列に「テスト 太郎+連番」を設定
4.FIRIGANA列に「てすと たろう+連番」を設定
5.誕生日列に2013年1月1日から12月31日までの間からランダムに日付を設定
6.タイムスタンプに現在時刻を設定
最後に、1000*1000の100万行をインサートしています。

truncate table table01;
set timing on
insert /*+ APPEND */ into TABLE01 nologging (
id, name, furigana,birthday, timestamp) 
  (select 
  lpad(rownum, 8, '0'),
  concat('テスト 太郎', TO_MULTI_BYTE(rownum)),
  concat('てすと たろう', TO_MULTI_BYTE(rownum)),
  TO_CHAR(TO_DATE('20130101','YYYYMMDD')
    + FLOOR(DBMS_RANDOM.VALUE(0, 365)), 'YYYYMMDD'),
  sysdate
  from (select 0 from all_catalog where rownum <= 1000),
       (select 0 from all_catalog where rownum <= 1000)
  );
commit;

このスクリプトの実行時間は、43秒でした。1分弱で100万レコード投入できるなら、テストデータの作成も苦ではなくなります。

挿入する行数を調整する

インサートする行数を変えたい場合は、rownum <= 1000の値を調整すれば良いです。
ただし、上限があってall_catalogに格納されている行数までしか増やすことはできません。たとえば、all_catalogに2000件入っている場合に、300万件のデータが必要なら以下のようにかけ算した値が合計300万行になるようにすることで調整できます。

  from (select 0 from all_catalog where rownum <= 2000),
       (select 0 from all_catalog where rownum <= 1500)
  );