UJP - 技術情報2 : Oracleに大量データをINSERTしてシーケンスを使ってダミーデータを作成する Oracle/MassDataGenerate

Life is fun and easy!

不正IP報告数

Okan Sensor
 
メイン
ログイン
ブログ カテゴリ一覧

Page Top

はじめに anchor.png

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

Page Top

desc empで環境を確認する anchor.png

 まず,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>
Page Top

alter tableで桁数を増やす anchor.png

 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桁もいる会社なんて嫌だな...

Page Top

empテーブルの初期データを消す anchor.png

 最初から作りたので,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>

 消えた事が確認できた.

Page Top

SEQUENCEを作成する anchor.png

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

Page Top

整合性制約でエラーになった anchor.png

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

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

Page Top

シーケンスをリセットする. anchor.png

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

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

Page Top

大量データを入れる準備 anchor.png

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

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$
Page Top

大量データを入れるスクリプトを確認する anchor.png

 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処理が終わってから実行.

Page Top

大量データを入れるスクリプトを実行する(最後にcommit) anchor.png

 まずは実行してみる.

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秒.

Page Top

大量データを入れるスクリプトを実行する(都度commit) anchor.png

 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倍でした.


トップ   差分 バックアップ 複製 名前変更 リロード   ページ新規作成 全ページ一覧 単語検索 最新ページの一覧   ヘルプ   最新ページのRSS 1.0 最新ページのRSS 2.0 最新ページのRSS Atom
Counter: 7894, today: 1, yesterday: 9
最終更新: 2016-06-02 (木) 00:19:22 (JST) (2857d) by shinnai(shinnai)

広告スペース
Google