なぜ、主キー制約の追加時間に違いがでるのでしょうか？ （東京都 ITエンジニア 男性）/ FAQ
え～〜っ、
十年ひと昔てぇなぁこと申しますけれど、十年経つと元へ戻るんですってねぇ。
あの問題でハマった〜ってぇ話題をおもいだしやして、ググってみたんでさぁ、ありましたよ、ありましたよ！
十年数年前。
落語調で入ってみましたww
さて、今日の患者さん、
何にハマってたのかぁってぇと、主キー制約を追加するのに〜、偉い時間がかかって、頭を抱えて相談に。
（いい加減、落語っぽい言葉遣いヤメぇ！w
この話題、10年以上前のOracle Forumでも話題になった有名な、FAQだよねー。
元々そういうリスクのあるテーブル定義だったり、各種ツールの使い方によっては遭遇することもあるので、この症状を覚えておくと対処もしやすいですよ。というお話をしたいと思います。
まずは、当時話題になったスレ。
Oracle Forum - SQL & PL/SQL "ALTER TABLE xxx add constraint primary key" takes long long time!
この症状、ご存知の方も多いと思うのですが、Forum外で言及しているエントリーって見たこともあまりないので、実はあまり知られてなかったりして。。w という心配もあり、メモがわりにブログに書いておきますね:)
ただし、この症状が顕著に現れるのは大規模な表の場合だけ（数十TBとか）なので、少量のデータだと気づかずというか影響していないことも多いので注意が必要なんです。（データが増加してから初めて気づいたりしてザワザワしたりするので）
巨大なデータを扱う場合、物凄い遅延に繋がってしまったりするので、舐めちゃいけません。
症状と原因をサクッとまとめると、
きっかけとなる主キー制約には以下の特徴があります。
・主キー制約では、キー値（複合キーの場合はキー全体で）が一意であり、NULL が含まれないでないことが保証されます。
NULLでないことが保証されるという点が今回のポイント。NULLと聞いてガタッとしたあなた！。いいリアクションです！w
主キー制約追加時には、主キーに含まれる列の値は NULL ではないことが検証されます。
主キー制約追加時には、主キーに含まれる列の値は NULL ではないことが検証されます。
主キー制約追加時には、主キーに含まれる列の値は NULL ではないことが検証されます。
（ここが大切なので3度書いておきましたw 太字でw
この NULL ではないことを保証するために、追加のIOと NULL でないことのチェックが塵も積もで、処理時間が伸びてしまう症状につながります。これは対象のデータ量に比例して伸びるので、データ量が少ないとほぼ気づきませんw
ただでさえ処理時間を要する大量データの場合のみ顕著な影響が現れます！！！！！
ただ、一般的には、主キー列として利用される列は NOT NULL となっている多いはずですが、
稀に、どうせ主キー制約でNOT NULLが保証されるから、それぞれの列には NOT NULL制約 (Oracle DatabaseではCHECK制約として実装されます) を付加しない！ という方も見かけますが、それ手を抜かない方がいいですよ。
そんな横着していると、今回お見せするような症状を発症してしまうことがあります。
それ以外のケースでは、各種ツール（Oracle database純正のもを含む）の使い方次第では意図せず遭遇してしまうこともあります。(ex. impdp利用時のオブジェクトの扱いを制御するパラメータの使い方によるもの、とか)
症状と原因が理解できれば対処方法は簡単。そのような状態を避ければ良いだけですね！ w
そんな大した話ではなくて、
・主キー制約に含める列は列定義レベルで、NOT NULL制約を付加しておきましょう！
・各種ツールの利用方法による副作用で、NULLABLEになってしまうようなケースでは、その状況を回避する利用手順なり、オプションを選ぶようにすることです。
これ覚えておくと、妙なところで時間溶かさなくて済むので、頭の片隅に置いておくと良いと思いますよ:)
では、簡単な例で、遅延の発生と、裏で何が起こっているのか10046トレースで、サクッと軽めに確認しておきましょう！
スクリプトの内容は本記事の後半に載せますので参考にしてみてください。
table_1/tabl_2を作成して、それぞれ、400MBほどになるようにデータを登録。（違いは、id2の列がNULLABLEかNOT NULLかだけです。この列は複合主キーに含まれます）
SCOTT@orclpdb1> @why_do_you_think_it_is_slow_adding_the_primary_constraint.sql
表が削除されました。
経過: 00:00:01.25
表が削除されました。
経過: 00:00:00.07
1 CREATE TABLE table_1
2 (
3 id1 NUMBER NOT NULL
4 ,id2 NUMBER
5 ,text VARCHAR2(10)
6* )
表が作成されました。
経過: 00:00:00.18
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ID1 NOT NULL NUMBER
ID2 NUMBER
TEXT VARCHAR2(10)
1 CREATE TABLE table_2
2 (
3 id1 NUMBER NOT NULL
4 ,id2 NUMBER NOT NULL
5 ,text VARCHAR2(10)
6* )
表が作成されました。
経過: 00:00:00.02
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ID1 NOT NULL NUMBER
ID2 NOT NULL NUMBER
TEXT VARCHAR2(10)
1 BEGIN
2 FOR i in 1..15000000 LOOP
3 INSERT ALL
4 INTO table_1 VALUES(i,i,i)
5 INTO table_2 VALUES(i,i,i)
6 SELECT null FROM dual
7 ;
8 IF MOD(i,1000) = 0
9 THEN
10 COMMIT;
11 END IF;
12 END LOOP;
13* END;
PL/SQLプロシージャが正常に完了しました。
経過: 00:11:42.62
ALTER TABLE ADD CONSTRAINT PRIMARY KEYで id1とid2の複合キーで主キー制約を追加。同時に索引も作成させる
今回の程度データ量だとびっくりするほどの差は見えずらいですが、id2がnullableであるtable_1の方が、約11秒ほど処理時間が長くなっています！
ちょっとわかりにくくなる原因は、制約追加と同時に索引も作成しているため索引作成の時間に埋もれやすく、索引作成の時間だと勘違いする方が多い影響もありますが、データ量が多くなればなるほどこの差は大きくなります。
1* ALTER TABLE table_1 ADD CONSTRAINT pk_table_1 PRIMARY KEY (id1, id2) USING INDEX
表が変更されました。
経過: 00:00:48.31
1* ALTER TABLE table_2 ADD CONSTRAINT pk_table_2 PRIMARY KEY (id1, id2) USING INDEX
表が変更されました。
経過: 00:00:36.92
最初の例は、索引の作成時間も含まれいるため制約の追加のオーバーヘッドがわかりにくいので他の手順で確認してみると。。。。
ALTER TABLE ADD CONSTRAINT PRIMARY KEY USING [事前に作成しておいた一意索引名]部分（赤字）に注目。
制約の追加で、id2がNULLABLEかNOT NULLという部分だけの違いで、272倍の差になっています。table_2の制約追加時間はデータ量が増加してもほぼ一定なので、データ量が増加した場合の影響はどれぐらいかは想像してみてくださいね。
今回は、2列からなる複合主キーの1列だけですが、異なる条件では差は広がる可能性はあります。（環境の差も含め）
1* ALTER TABLE table_1 DROP PRIMARY KEY DROP INDEX
表が変更されました。
経過: 00:00:00.93
1* ALTER TABLE table_2 DROP PRIMARY KEY DROP INDEX
表が変更されました。
経過: 00:00:00.10
1* CREATE UNIQUE INDEX pk_table_1 ON table_1(id1, id2)
索引が作成されました。
経過: 00:00:33.84
1* CREATE UNIQUE INDEX pk_table_2 ON table_2(id1, id2)
索引が作成されました。
経過: 00:00:36.13
1* ALTER TABLE table_1 ADD CONSTRAINT pk_table_1 PRIMARY KEY (id1, id2) USING INDEX pk_table_1
表が変更されました。
経過: 00:00:05.44
1* ALTER TABLE table_2 ADD CONSTRAINT pk_table_2 PRIMARY KEY (id1, id2) USING INDEX pk_table_2
表が変更されました。
経過: 00:00:00.02
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:18.53
PL/SQLプロシージャが正常に完了しました。
経過: 00:00:15.50
SEGMENT_NAME MB
------------------------------ ----------
PK_TABLE_1 368
PK_TABLE_2 368
TABLE_1 416
TABLE_2 416
経過: 00:00:00.26
最後にNOT NULLにしておけば回避できることも確認
table_1のid2列をNOT NULLに変更して。。。
1* ALTER TABLE table_1 DROP PRIMARY KEY DROP INDEX
表が変更されました。
経過: 00:00:00.41
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ID1 NOT NULL NUMBER
ID2 NUMBER
TEXT VARCHAR2(10)
1* ALTER TABLE table_1 MODIFY id2 NUMBER NOT NULL
表が変更されました。
経過: 00:00:03.14
変更後のtabl_1 (table_2と同一定義になっています！)表の id2はNULLABLE から NOT NULL へ変更
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ID1 NOT NULL NUMBER
ID2 NOT NULL NUMBER
TEXT VARCHAR2(10)
table_2同様の処理時間となり大きく改善！ 11秒のオーバーヘッドが消えてますね！
1* ALTER TABLE table_1 ADD CONSTRAINT pk_table_1 PRIMARY KEY (id1, id2) USING INDEX
表が変更されました。
経過: 00:00:34.65
主キー制約の追加部分に絞って改善を確認してみましょう！
table_2同様の処理時間となり大きく改善！ 0.02秒で完了
1* ALTER TABLE table_1 DROP PRIMARY KEY DROP INDEX
表が変更されました。
経過: 00:00:00.13
1* CREATE UNIQUE INDEX pk_table_1 ON table_1(id1, id2)
索引が作成されました。
経過: 00:00:32.22
1* ALTER TABLE table_1 ADD CONSTRAINT pk_table_1 PRIMARY KEY (id1, id2) USING INDEX pk_table_1
表が変更されました。
経過: 00:00:00.02
最後に、10046トレースで違い見る！
一目瞭然ですよね。 10046トレースでALTER文を拾ってみると、ID2列がNULLABLEdであるケースではexecuteで大量のqueryが発生していて、db file scattered readやdb file sequential readでオブジェクトを読みにに行っていることがわかります。これようするに該当列値に NULLが存在しないことを保証しなければならない主キー制約の宿命なわけですが、この作業を軽くしてくれているので、table_2のid2列にあるようなNOT NULL制約です。これがあればメタ情報をみるだけで NULL は存在しないことを確認できちゃいますからね。処理時間の差はそこだけです！
SCOTT@orclpdb1> @dive_in
DROP TABLE table_1 PURGE
*
行1でエラーが発生しました。:
ORA-00942: 表またはビューが存在しません。
経過: 00:00:00.01
DROP TABLE table_2 PURGE
*
行1でエラーが発生しました。:
ORA-00942: 表またはビューが存在しません。
経過: 00:00:00.01
1 CREATE TABLE table_1
2 (
3 id1 NUMBER NOT NULL
4 ,id2 NUMBER
5 ,text VARCHAR2(10)
6* )
表が作成されました。
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ID1 NOT NULL NUMBER
ID2 NUMBER
TEXT VARCHAR2(10)
1 CREATE TABLE table_2
2 (
3 id1 NUMBER NOT NULL
4 ,id2 NUMBER NOT NULL
5 ,text VARCHAR2(10)v 6* )
表が作成されました。
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ID1 NOT NULL NUMBER
ID2 NOT NULL NUMBER
TEXT VARCHAR2(10)
1 BEGIN
2 FOR i in 1..15000000 LOOP
3 INSERT ALL
4 INTO table_1 VALUES(i,i,i)
5 INTO table_2 VALUES(i,i,i)
6 SELECT null FROM dual
7 ;
8 IF MOD(i,1000) = 0
9 THEN
10 COMMIT;
11 END IF;
12 END LOOP;
13* END;
PL/SQLプロシージャが正常に完了しました。
1* CREATE UNIQUE INDEX pk_table_1 ON table_1(id1, id2)
索引が作成されました。
1* CREATE UNIQUE INDEX pk_table_2 ON table_2(id1, id2)
索引が作成されました。
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0との接続が切断されました。
接続されました。
セッションが変更されました。
...略...
経過: 00:00:00.02
1* ALTER TABLE table_1 ADD CONSTRAINT pk_table_1 PRIMARY KEY (id1, id2) USING INDEX pk_table_1
表が変更されました。
経過: 00:00:07.12
セッションが変更されました。
経過: 00:00:00.01
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0との接続が切断されました。
接続されました。
...略...
セッションが変更されました。
経過: 00:00:00.01
1* ALTER TABLE table_2 ADD CONSTRAINT pk_table_2 PRIMARY KEY (id1, id2) USING INDEX pk_table_2
表が変更されました。
経過: 00:00:00.05
セッションが変更されました。
経過: 00:00:00.00
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0との接続が切断されました。
接続されました。
表が削除されました。
表が削除されました。
[oracle@localhost ~]$ ls -lrt $ORACLE_BASE/diag/rdbms/orclcdb/ORCLCDB/trace/*table_1*
-rw-r-----. 1 oracle oinstall 48367 8月 28 20:11 /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace/ORCLCDB_ora_7254_10046_table_1_slow.trm
-rw-r-----. 1 oracle oinstall 348949 8月 28 20:11 /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace/ORCLCDB_ora_7254_10046_table_1_slow.trc
[oracle@localhost ~]$ tkprof /opt/oracle/diag20dbms/orclcdb/ORCLCDB/trace/ORCLCDB_ora_7254_10046_table_1_slow.trc ORCLCDB_ora_7254_10046_table_1_slow.txt sys=yes waits=yes
TKPROF: Release 21.0.0.0.0 - Development on 水 8月 28 20:14:16 2024
[oracle@localhost ~]$ view ORCLCDB_ora_7254_10046_table_1_slow.txt
...略...
SQL ID: 48v4hxnmpykdy Plan Hash: 0
ALTER TABLE table_1 ADD CONSTRAINT pk_table_1 PRIMARY KEY (id1, id2) USING
INDEX pk_table_1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 2 4 0 0
Execute 1 4.15 6.85 48874 52229 2 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 4.16 6.88 48876 52233 2 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 109
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 1 0.00 0.00
db file scattered read 1425 0.01 2.03
db file sequential read 54 0.00 0.04
Compression analysis 9 0.00 0.00
log file sync 1 0.00 0.00
PGA memory operation 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
...略...
[oracle@localhost ~]$ ls -lrt $ORACLE_BASE/diag/rdbms/orclcdb/ORCLCDB/trace/*table_2*
-rw-r-----. 1 oracle oinstall 15431 8月 28 20:11 /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace/ORCLCDB_ora_7256_10046_table_2_fast.trm
-rw-r-----. 1 oracle oinstall 67970 8月 28 20:11 /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace/ORCLCDB_ora_7256_10046_table_2_fast.trc
[oracle@localhost ~]$
[oracle@localhost ~]$ tkprof /opt/oracle/diag/rdbms/orclcdb/ORCLCDB/trace/ORCLCDB_ora_7256_10046_table_2_fast.trc ORCLCDB_ora_7256_10046_table_2_fast.txt sys=yes waits=yes
TKPROF: Release 21.0.0.0.0 - Development on 水 8月 28 20:17:33 2024
[oracle@localhost ~]$ view ORCLCDB_ora_7256_10046_table_2_fast.txt
...略...
SQL ID: 7x7hqgxcpkyky Plan Hash: 0
ALTER TABLE table_2 ADD CONSTRAINT pk_table_2 PRIMARY KEY (id1, id2) USING
INDEX pk_table_2
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 2 4 0 0
Execute 1 0.00 0.00 3 41 2 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.01 5 45 2 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 109
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Compression analysis 9 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
（参考）利用したスクリプト
[oracle@localhost ~]$ cat why_do_you_think_it_is_slow_adding_the_primary_constraint.sql
set line 80
DROP TABLE table_1 PURGE;
DROP TABLE table_2 PURGE;
CREATE TABLE table_1
(
id1 NUMBER NOT NULL
,id2 NUMBER
,text VARCHAR2(10)
)
.
l
/
desc table_1
CREATE TABLE table_2
(
id1 NUMBER NOT NULL
,id2 NUMBER NOT NULL
,text VARCHAR2(10)
)
.
l
/
desc table_2
set line 400
BEGIN
FOR i in 1..15000000 LOOP
INSERT ALL
INTO table_1 VALUES(i,i,i)
INTO table_2 VALUES(i,i,i)
SELECT null FROM dual
;
IF MOD(i,1000) = 0
THEN
COMMIT;
END IF;
END LOOP;
END;
.
l
/
ALTER TABLE table_1 ADD CONSTRAINT pk_table_1 PRIMARY KEY (id1, id2) USING INDEX
.
l
/
ALTER TABLE table_2 ADD CONSTRAINT pk_table_2 PRIMARY KEY (id1, id2) USING INDEX
.
l
/
ALTER TABLE table_1 DROP PRIMARY KEY DROP INDEX
.
l
/
ALTER TABLE table_2 DROP PRIMARY KEY DROP INDEX
.
l
/
CREATE UNIQUE INDEX pk_table_1 ON table_1(id1, id2)
.
l
/
CREATE UNIQUE INDEX pk_table_2 ON table_2(id1, id2)
.
l
/
ALTER TABLE table_1 ADD CONSTRAINT pk_table_1 PRIMARY KEY (id1, id2) USING INDEX pk_table_1
.
l
/
ALTER TABLE table_2 ADD CONSTRAINT pk_table_2 PRIMARY KEY (id1, id2) USING INDEX pk_table_2
.
l
/
@gather_tab_stats table_1
@gather_tab_stats table_2
col segment_name for a30
SELECT
SEGMENT_NAME
,BLOCKS * 8 / 1024 AS "MB"
FROM
USER_SEGMENTS
WHERE
SEGMENT_NAME IN (
'TABLE_1'
,'TABLE_2'
,'PK_TABLE_1'
,'PK_TABLE_2'
)
ORDER BY
SEGMENT_NAME
;
PROMPT **************** table_1のid2列をNOT NULLに変更 ******************
ALTER TABLE table_1 DROP PRIMARY KEY DROP INDEX
.
l
/
set line 80
PROMPT 変更前のtable_1
desc table_1
ALTER TABLE table_1 MODIFY id2 NUMBER NOT NULL
.
l
/
PROMPT 変更後のtabl_1 (table_2と同一定義になっています！) id2はNULLABLE から NOT NULL へ変更されました
desc table_1
set line 400
PROMPT ****** table_2同様の処理時間となり大きく改善！ *******
ALTER TABLE table_1 ADD CONSTRAINT pk_table_1 PRIMARY KEY (id1, id2) USING INDEX
.
l
/
PROMPT ***** 主キー制約の追加部分に絞って改善を確認してみましょう！
ALTER TABLE table_1 DROP PRIMARY KEY DROP INDEX
.
l
/
CREATE UNIQUE INDEX pk_table_1 ON table_1(id1, id2)
.
l
/
PROMPT ****** table_2同様の処理時間となり大きく改善！ *****
ALTER TABLE table_1 ADD CONSTRAINT pk_table_1 PRIMARY KEY (id1, id2) USING INDEX pk_table_1
.
l
/
（参考）内部で利用している統計情報取得スクリプト
[oracle@localhost ~]$ cat gather_tab_stats.sql
set verify on
eXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT',tabname=>upper('&1'),cascade=>true,no_invalidate=>false);
set verify off
undefine 1
（参考）10046トレース状況を覗くためのスクリプト
[oracle@localhost ~]$ cat dive_in.sql
set line 80
DROP TABLE table_1 PURGE;
DROP TABLE table_2 PURGE;
CREATE TABLE table_1
(
id1 NUMBER NOT NULL
,id2 NUMBER
,text VARCHAR2(10)
)
.
l
/
desc table_1
CREATE TABLE table_2
(
id1 NUMBER NOT NULL
,id2 NUMBER NOT NULL
,text VARCHAR2(10)
)
.
l
/
desc table_2
set line 400
BEGIN
FOR i in 1..15000000 LOOP
INSERT ALL
INTO table_1 VALUES(i,i,i)
INTO table_2 VALUES(i,i,i)
SELECT null FROM dual
;
IF MOD(i,1000) = 0
THEN
COMMIT;
END IF;
END LOOP;
END;
.
l
/
CREATE UNIQUE INDEX pk_table_1 ON table_1(id1, id2)
.
l
/
CREATE UNIQUE INDEX pk_table_2 ON table_2(id1, id2)
.
l
/
disconnect
connect scott/[パスワード]@orclpdb1
ALTER SESSION SET tracefile_identifier='10046_table_1_slow';
ALTER SESSION SET timed_statistics = true;
ALTER SESSION SET statistics_level=all;
ALTER SESSION SET max_dump_file_size = unlimited;
ALTER SESSION SET events '10046 trace name context forever,level 12';
ALTER TABLE table_1 ADD CONSTRAINT pk_table_1 PRIMARY KEY (id1, id2) USING INDEX pk_table_1
.
l
/
ALTER SESSION SET events '10046 trace name context off';
disconnect
connect scott/[パスワード]@orclpdb1
ALTER SESSION SET tracefile_identifier='10046_table_2_fast';
ALTER SESSION SET timed_statistics = true;
ALTER SESSION SET statistics_level=all;
ALTER SESSION SET max_dump_file_size = unlimited;
ALTER SESSION SET events '10046 trace name context forever,level 12';
ALTER TABLE table_2 ADD CONSTRAINT pk_table_2 PRIMARY KEY (id1, id2) USING INDEX pk_table_2
.
l
/
ALTER SESSION SET events '10046 trace name context off';
disconnect
connect scott/[パスワード]@orclpdb1
DROP TABLE table_1 PURGE;
DROP TABLE table_2 PURGE;
酷暑じゃなければ、台風起因の大雨の東京より。
みなさん、安全最優先で！
ではまた。
最近のコメント