はじめに
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倍でした.