Oracleに大量データをINSERTしてシーケンスを使ってダミーデータを作成する
技術情報2Oracle
/ MassDataGenerate
Oracleに大量データをINSERTしてシーケンスを使ってダミーデータを作成する 
はじめに
Oracleでインデックス作成時間とかデータバックアップ時間とかを計測するときに，ダミーデータが必要なときがある．それも大量に．ここでは，scott/tigerのempテーブルをサンプルテーブルとして，シンプルなやり方でデータを大量に作成してみる．また，シーケンスのリセットも行っている． なお，empテーブルはutlsampl.sqlを実行してscottユーザと共に作成されている事を前提とする． また，シリアル番号を生成するためにOracle特有のシーケンスを使っている． 今回は，Oracle 11g Express Edition(XE)を使っている．
desc empで環境を確認する
まず，SQL*Plusで，scottユーザで接続する．
SQL> conn scott/tiger@xe 接続されました。 SQL>
empテーブルのテーブル定義を確認する．
SQL> desc emp; 名前 NULL? 型 ----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)
SQL>
alter tableで桁数を増やす
empno(社員番号)が4桁だと999件しか作れないないので，桁数を変更する．
SQL> alter table emp modify ( empno number(38) ) ; 表が変更されました。
SQL>
NUMBER型の最大の38桁．変更された事を確認．
SQL> desc emp; 名前 NULL? 型 ----------------------------------------- -------- ---------------------------- EMPNO NOT NULL NUMBER(38) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)
SQL>
社員数が38桁もいる会社なんて嫌だな．．．
empテーブルの初期データを消す
最初から作りたので，empテーブルのデータを確認する．
SQL> select count(*) from emp;
COUNT(*) ---------- 12
SQL>
この12件のデータは使わないので，消す．
SQL> delete from emp;
12行が削除されました。
SQL> commit;
コミットが完了しました。
SQL> select count(*) from emp;
COUNT(*) ---------- 0
SQL>
消えた事が確認できた．
SEQUENCEを作成する
社員ナンバempnoを自動連番にしたいので，シーケンスを作成する．
SQL> CREATE SEQUENCE seq_empno 2 INCREMENT BY 1 START WITH 1 MAXVALUE 10000000000000000000000000000000000 3 NOMINVALUE CYCLE NOORDER CACHE 100 4 /
順序が作成されました。
SQL>
先ほどempテーブルのデータを消したので，発番は1から1毎としている．
整合性制約でエラーになった
適当にデータを入れてみる．
SQL> insert into emp values (seq_empno.nextval,'a','b',1,sysdate,3,4,5) 2 / insert into emp values (seq_empno.nextval,'a','b',1,sysdate,3,4,5) * 行1でエラーが発生しました。: ORA-02291: 整合性制約(SCOTT.FK_DEPTNO)に違反しました - 親キーがありません
SQL>
DEPTNOがdeptテーブルの外部キーになっていて，つまりdeptテーブルにdeptno=5というのが存在しないのでエラーになっている． deptテーブルのdeptnoを確認する．
SQL> select deptno from dept;
DEPTNO ---------- 10 20 30 40
SQL>
deptnoに設定さされている値，ここでは10を設定する．
SQL> insert into emp values (seq_empno.nextval,'a','b',1,sysdate,3,4,10);
1行が作成されました。
SQL>
もう1回データを登録．
SQL> insert into emp values (seq_empno.nextval,'a','b',1,sysdate,3,4,10);
1行が作成されました。
SQL>
件数を確認．
SQL> select count(*) from emp;
COUNT(*) ---------- 2
SQL>
2件あり．シーケンスで自動発番されるemopno列を確認する．
SQL> select empno,ename,deptno from emp;
EMPNO ENAME DEPTNO ---------- ------------------------------ ---------- 2 a 10 3 a 10
SQL>
シーケンスが2から始まっている．1回目に制約エラーで失敗したので，1番は永久欠番の模様． 面倒なので，データを全部消して，再度追加してみる．
SQL> delete from emp;
2行が削除されました。
SQL> commit;
コミットが完了しました。
SQL> insert into emp values (seq_empno.nextval,'a','b',1,sysdate,3,4,10) 2 /
1行が作成されました。
SQL> select empno from emp;
EMPNO ---------- 4
SQL>
2番3番のレコードが消え，4番が追加されている事が確認できる．シーケンスはリセットできない．
シーケンスをリセットする．
ここで再度1〜3番のレコードを入れようとすると，シーケンスを消して追加する事が一番早い． よって，まずはシーケンスを削除する．
SQL> drop sequence seq_empno;
順序が削除されました。
SQL>
これでシーケンスを削除したので自動的に入らない．手動で欠落したデータを投入する．
SQL> insert into emp values (1,'a','b',1,sysdate,3,4,10);
1行が作成されました。
SQL> insert into emp values (2,'a','b',1,sysdate,3,4,10);
1行が作成されました。
SQL> insert into emp values (3,'a','b',1,sysdate,3,4,10);
1行が作成されました。
SQL> select empno from emp;
EMPNO ---------- 1 2 3 4
SQL>
再度シーケンスを作成．4まで発番されているので，5から発番する事とする．
SQL> CREATE SEQUENCE seq_empno 2 INCREMENT BY 1 3 START WITH 5 4 MAXVALUE 10000000000000000000000000000000000 5 NOMINVALUE CYCLE NOORDER CACHE 100 6 /
順序が作成されました。
SQL>
データを追加して，シーケンスが想定通り連番で発番されているか確認する．
SQL> insert into emp values (seq_empno.nextval,'a','b',1,sysdate,3,4,20);
1行が作成されました。
SQL> insert into emp values (seq_empno.nextval,'a','b',1,sysdate,3,4,20);
1行が作成されました。
SQL>
データを確認する．
SQL> select empno,deptno from emp;
EMPNO DEPTNO ---------- ---------- 1 10 2 10 3 10 5 20 6 20 4 10
6行が選択されました。
SQL>
ソートして表示する．
SQL> select empno,deptno from emp order by empno;
EMPNO DEPTNO ---------- ---------- 1 10 2 10 3 10 4 10 5 20 6 20
6行が選択されました。
SQL>
連番が発番されている事が確認できた．
大量データを入れる準備
最初からリセットするために，データを決してシーケンスも再作成する．
SQL> delete from emp;
6行が削除されました。
SQL> drop sequence seq_empno;
順序が削除されました。
SQL>
再度シーケンスを作成する．データを消したので，STARTを1からとする．
SQL> CREATE SEQUENCE seq_empno 2 INCREMENT BY 1 3 START WITH 1 4 MAXVALUE 10000000000000000000000000000000000 5 NOMINVALUE CYCLE NOORDER CACHE 100 6 /
順序が作成されました。
SQL> quit Oracle Database 11g Express Edition Release 11.2.0.2.0 - Productionとの接続が切断されました。 MBA13:~ ujpadmin$
大量データを入れるスクリプトを確認する
empテーブルに10万件データを挿入するスクリプト．
MBA13:~ ujpadmin$ cat loop_insert.sql
SET SERVEROUTPUT ON
begin
DBMS_OUTPUT.PUT_LINE(SYSTIMESTAMP);
for i in 1..100000 loop
insert into emp values (seq_empno.nextval,'a','b',1,sysdate,3,4,20);
end loop;
commit;
DBMS_OUTPUT.PUT_LINE(SYSTIMESTAMP);
end;
MBA13:~ ujpadmin$処理前と処理後にDBMS_OUTPUT.PUT_LINEで時間を表示．SET SERVEROUTPUT ONをつけておかないと表示されない．commitは100万件のINSERT処理が終わってから実行．
大量データを入れるスクリプトを実行する（最後にcommit）
まずは実行してみる．
SQL> @loop_insert.sql 9 / 16-06-01 01:19:14.649000000 +09:00 16-06-01 01:21:56.259000000 +09:00
PL/SQLプロシージャが正常に完了しました。
SQL> select count(*) from emp;
COUNT(*) ---------- 1000000
SQL>
100万件のデータ挿入が約2分42秒ほど．162秒．
大量データを入れるスクリプトを実行する（都度commit）
commitの位置を変更してみる．
MBA13:~ ujpadmin$ cat loop_insert_commit.sql
SET SERVEROUTPUT ON
begin
DBMS_OUTPUT.PUT_LINE(SYSTIMESTAMP);
for i in 1..1000000 loop
insert into emp values (seq_empno.nextval,'a','b',1,sysdate,3,4,20);
commit;
end loop;
DBMS_OUTPUT.PUT_LINE(SYSTIMESTAMP);
end;
MBA13:~ ujpadmin$1回insert文を実行する都度，commitしている．これは遅いはず．
SQL> @loop_insert_commit.sql 9 / 16-06-01 01:23:09.900000000 +09:00 16-06-01 01:30:09.509000000 +09:00
PL/SQLプロシージャが正常に完了しました。
SQL>
7分．420秒．約2.6倍でした．
