サンプルデータ準備用の SQL #SQL

よく忘れるのでメモ。

環境

Oracle 11g

サンプルのつくり

この記事で用いる登録先テーブルの構造と、流し込むデータのルール。

テーブル構造

create_table

CREATE TABLE EXAMPLES (
  ID INTEGER
 ,LABEL     VARCHAR2(100)  NOT NULL
 ,SCORE     NUMBER         NOT NULL
 ,RATE      NUMBER         NOT NULL
 ,BIRTHDAY  DATE           NOT NULL
 ,PRIMARY KEY (ID)
);

ルール

登録件数は 50 万件。
ID はシーケンスを使わない連番を登録する。
各列にはランダムな値を登録する。

データ登録 SQL

SQL 1

定番の ALL_CATALOG テーブルを使うやつ。

Oracle データベースに必ず存在し、件数が 1,000 以上あるテーブルということで n 回ループを実現するために ALL_CATALOG を使用している。

insert-with-all_catalog

INSERT INTO EXAMPLES
    SELECT 
      ROWNUM
     ,DBMS_RANDOM.STRING('A', 100)
     ,FLOOR(DBMS_RANDOM.VALUE(1, 100000))
     ,DBMS_RANDOM.VALUE()
     ,TO_DATE('19000101','YYYYMMDD') + FLOOR(DBMS_RANDOM.VALUE(1, 365 * 120))
    FROM
      (select 0 from all_catalog where rownum <= 1000)
     ,(select 0 from all_catalog where rownum <= 500)
;

処理時間

1 回目	2 回目	3 回目
43.190 秒	42.976 秒	42.957 秒

SQL 2

stackoverflow の投稿で初めて知ったやり方。

ダミーテーブルの SELECT に CONNECT BY 句を付け、そこでループ回数を指定する。

insert-with-dual


INSERT INTO EXAMPLES
    SELECT 
      ROWNUM
     ,DBMS_RANDOM.STRING('A', 100)
     ,FLOOR(DBMS_RANDOM.VALUE(1, 100000))
     ,DBMS_RANDOM.VALUE()
     ,TO_DATE('19000101','YYYYMMDD') + FLOOR(DBMS_RANDOM.VALUE(1, 365 * 120))
    FROM
      DUAL
    CONNECT BY
      ROWNUM <= 500000
;

処理時間

1 回目	2 回目	3 回目
43.384 秒	43.406 秒	43.779 秒

注意点

CONNECT BY 句はどうやらメモリの使用量が大きいらしい。件数を上げると以下のエラーが出た。

ORA-30009: CONNECT BY操作のためのメモリーが足りません。

このエラーを避けるにはメモリーを多く積むか、記事中のほかの SQL を使うとよい。

PL/SQL 1

個人的に一番使う方法。

処理速度が遅めであることはわかっているが、使用するテーブルや構文に特殊さがないため一番忘れにくい。

insert-in-plsql

BEGIN
  FOR i IN 1..500000
  LOOP
    INSERT INTO EXAMPLES
        VALUES (
          i
         ,DBMS_RANDOM.STRING('A', 100)
         ,FLOOR(DBMS_RANDOM.VALUE(1, 100000))
         ,DBMS_RANDOM.VALUE()
         ,TO_DATE('19000101','YYYYMMDD') + FLOOR(DBMS_RANDOM.VALUE(1, 365 * 120))
    );
  END LOOP;
END;
/

処理時間

1 回目	2 回目	3 回目
66.249 秒	65.516 秒	68.903 秒

PL/SQL 2

速度向上を求めてバルクインサート化。

速度は上がったが記述が長い。

insert-with-forall-in-plsql

DECLARE
  CURSOR cur
  IS
      SELECT 
        ROWNUM
       ,DBMS_RANDOM.STRING('A', 100)
       ,FLOOR(DBMS_RANDOM.VALUE(1, 100000))
       ,DBMS_RANDOM.VALUE()
       ,TO_DATE('19000101','YYYYMMDD') + FLOOR(DBMS_RANDOM.VALUE(1, 365 * 120))
      FROM
        DUAL
      CONNECT BY
        ROWNUM <= 500000
  ;

  TYPE EXAMPLE_TABLE_TYPE
  IS
      TABLE OF EXAMPLES%ROWTYPE;

  example_table EXAMPLE_TABLE_TYPE;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur BULK COLLECT INTO example_table LIMIT 5000;
    EXIT WHEN example_table.COUNT = 0;

    FORALL i IN 1..example_table.COUNT
        INSERT INTO EXAMPLES VALUES example_table(i);
  END LOOP;
  CLOSE cur;
END;
/

処理時間

1 回目	2 回目	3 回目
43.133 秒	43.688 秒	43.355 秒

どれを使おう

定番の SQL 1 を基本の手段として用い、凝ったデータを登録する際には PL/SQL 2 を使うのがいいか。

最も好みな文は SQL 2 の DUAL テーブルと CONNECT BY 句を用いたものだが、データが巨大だとメモリ不足に陥る可能性があるようだ。 (投稿直後にぶち当たった)