UJP - 技術情報2 バックアップ : Oracle/MassDataGenerateのソース

差分を表示
Oracle/MassDataGenerate へ行く。
*Oracleに大量データをINSERTしてシーケンスを使ってダミーデータを作成する [#s368d81b]

**はじめに [#qbc7a723]

　Oracleでインデックス作成時間とかデータバックアップ時間とかを計測するときに，ダミーデータが必要なときがある．それも大量に．ここでは，scott/tigerのempテーブルをサンプルテーブルとして，シンプルなやり方でデータを大量に作成してみる．また，シーケンスのリセットも行っている．
　なお，empテーブルはutlsampl.sqlを実行してscottユーザと共に作成されている事を前提とする．
　また，シリアル番号を生成するためにOracle特有のシーケンスを使っている．
　今回は，Oracle 11g Express Edition(XE)を使っている．

**desc empで環境を確認する [#u740d9ef]

　まず，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で桁数を増やす [#h75dffb8]

　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テーブルの初期データを消す [#l14fcea0]

　最初から作りたので，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を作成する [#f7652d59]

　社員ナンバ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毎としている．

**整合性制約でエラーになった [#o39e5a3b]

　適当にデータを入れてみる．

 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番が追加されている事が確認できる．シーケンスはリセットできない．

**シーケンスをリセットする． [#mb595e36]

　ここで再度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>

　連番が発番されている事が確認できた．


**大量データを入れる準備 [#q903c7bc]

　最初からリセットするために，データを決してシーケンスも再作成する．

 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$

**大量データを入れるスクリプトを確認する [#ee4f9152]

　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） [#v7e9c021]

　まずは実行してみる．

 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） [#dc2eb855]

　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倍でした．
« Prev
Oracle/MassDataGenerate のバックアップ一覧
Oracle/MassDataGenerate のバックアップソース(No. All)
- 1: 2016-06-02 (木) 00:19:22 nobuaki
- 現: 2016-06-02 (木) 00:19:22 shinnai(shinnai)
  - Rewound to 2 ages ago. at 2017-12-04 (月) 23:29:17
Counter: 8479, today: 1, yesterday: 3
Oracleに大量データをINSERTしてシーケンスを使ってダミーデータを作成する のソース

Oracleに大量データをINSERTしてシーケンスを使ってダミーデータを作成するのソース
