UJP - 技術情報2 バックアップ : Oracle/MassDataGenerate のバックアップ差分(No.1)

Life is fun and easy!
不正IP報告数

Okan Sensor
Home Information Service Tech(Free) Tech(Member) Blog FAQ
 
メイン
ログイン

ユーザー名:


パスワード:





パスワード再発行手続き  |無料会員入会手続へ...
ブログ　カテゴリ一覧
1: 2016-06-02 (木) 00:19:22 nobuaki ソース
Line 1: Line 1:
 +*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倍でした．
トップ   差分 バックアップ 複製 名前変更 リロード   ページ新規作成 全ページ一覧 単語検索 最新ページの一覧   ヘルプ   最新ページのRSS 1.0 最新ページのRSS 2.0 最新ページのRSS Atom
Counter: 7987, today: 2, yesterday: 1
ページ別名: 未設定
ページ作成: shinnai
サイト管理: administrator
広告スペース
Google