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

よく忘れるのでメモ。



環境
----

- Oracle 11g



サンプルのつくり
----

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

### テーブル構造

```sql: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 を使用している。

```sql: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 の投稿](https://stackoverflow.com/questions/11738342/how-to-insert-1-million-random-row-into-table-database-oracle) で初めて知ったやり方。

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

```sql: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

個人的に一番使う方法。

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

```sql: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

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

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

```sql: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` 句を用いたものだが、データが巨大だとメモリ不足に陥る可能性があるようだ。 (投稿直後にぶち当たった)