なぜ、主キー制約の追加時間に違いがでるのでしょうか？ （東京都 ITエンジニア 男性）/ FAQ のおまけ
Previously on Mac De Oracle
前回は、なぜ、主キー制約の追加時間に違いがでるのでしょうか？ （東京都 ITエンジニア 男性）/ FAQでした。
今日は、おまけとして、前回意図的に書いていなかったツールを利用した場合にも意図せず発症してしまう一例を紹介したいと思います。
前回のエントリーで以下のようなことを書いていたのですがおぼえているでしょうか？。
回避方法は、主キー列のNOT NULL制約（チェック制約）が落ちてしまうのを避ければよいだけなので大した内容ではないのですが、Data Pumpっていう癖の多いツールが絡む場合の例なので書いておいたほうが良いかなぁと。ww
”それ以外のケースでは、各種ツール（Oracle database純正のもを含む）の使い方次第では意図せず遭遇してしまうこともあります。(ex. impdp利用時のオブジェクトの扱いを制御するパラメータの使い方によるもの、とか)”
では、早速準備から。
impdpするので、expdpしておかないといけません。それより前にData Pump用のディレクトリオブジェクトを作っておきましょう。ホスト側に実際のディレクトリを用意して権限なども忘れずに設定しておきましょうね。
CREATE DIRECTORY文ではそこまで検証してくれないので、impdp/expdp実行時に、”あ”〜っ” みたいなエラーに遭遇しないようにしておきますw（けっこうやらかしがちなんですけどね。私もwwww)
[oracle@localhost work]$ sqlplus scott@orclpdb1
...中略...
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
に接続されました。
SCOTT@orclpdb1> CREATE DIRECTORY mydump_dir AS '/work';
ディレクトリが作成されました。
経過: 00:00:00.57
SCOTT@orclpdb1> ! ls -l /work
合計 0
SCOTT@orclpdb1>
次に、前回も作成したTABLE_2だけを用意します。主キー列にはNOT NULL制約があり、主キー制約も作成しておきます。（主キー制約追加で遅延が発生しなかった状態）
SCOTT@orclpdb1> @additional_example.sql
表が削除されました。
経過: 00:00:00.54
1 CREATE TABLE table_2
2 (
3 id1 NUMBER NOT NULL
4 ,id2 NUMBER NOT NULL
5 ,text VARCHAR2(10)
6* )
表が作成されました。
経過: 00:00:00.11
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ID1 NOT NULL NUMBER
ID2 NOT NULL NUMBER
TEXT VARCHAR2(10)
1 BEGIN
2 FOR i in 1..15000000 LOOP
3 INSERT INTO table_2 VALUES(i,i,i);
4 IF MOD(i,1000) = 0
5 THEN
6 COMMIT;
7 END IF;
8 END LOOP;
9* END;
PL/SQLプロシージャが正常に完了しました。
経過: 00:07:06.40
1* ALTER TABLE table_2 ADD CONSTRAINT pk_table_2 PRIMARY KEY (id1, id2) USING INDEX
表が変更されました。
経過: 00:00:35.23
expdpで表モードでエクスポートしておきます。TABLE_2だけで十分。
正常にエクスポートできたら準備完了！
[oracle@localhost ~]$ expdp scott@orclpdb1 tables=scott.table_2 directory=mydump_dir dumpfile=scott_table_2.dmp
Export: Release 21.0.0.0.0 - Production on 土 8月 31 15:36:56 2024
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
パスワード:
接続先: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
"SCOTT"."SYS_EXPORT_TABLE_01"を起動しています: scott/********@orclpdb1 tables=scott.table_2 directory=mydump_dir dumpfile=scott_table_2.dmp
オブジェクト型TABLE_EXPORT/TABLE/TABLE_DATAの処理中です
オブジェクト型TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/MARKERの処理中です
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
オブジェクト型TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTの処理中です
. . "SCOTT"."TABLE_2" 344.8 MB 15000000行がエクスポートされました
マスター表"SCOTT"."SYS_EXPORT_TABLE_01"は正常にロード/アンロードされました
******************************************************************************
SCOTT.SYS_EXPORT_TABLE_01に設定されたダンプ・ファイルは次のとおりです:
/work/scott_table_2.dmp
ジョブ"SCOTT"."SYS_EXPORT_TABLE_01"が土 8月 31 15:38:00 2024 elapsed 0 00:00:50で正常に完了しました
では、主キー制約の追加で時間を要してしまう例の紹介から。
excludeオプションで、CONSTRAINTを除外してしまっています。後述しますが、これ方法で除外してしまうと全ての制約が除外されてしまいます。 NOT NULLもCHECK制約とい制約の一つなので、副作用を考慮せず除外してしまうと、NULLBALEな状態でインポートされていまうことを意味します。
つまり、今回話題にしている症状が発症してしまう典型的な状況が作り出されてしまうということですよね。そろそろ気づき始めましたかね？
以下のインポートのログには、CONSTRAINTオブジェクトがインポートされたとは記録されません。exclude しちゃってますからね！（実は、制約名で一部の制約だけにしても同様のログになるのでわかりにくいのですけどね！ Data Pumpの癖の一つw）
[oracle@localhost ~]$ impdp scott@orclpdb1 tables=scott.table_2 exclude=constraint table_exists_action=replace directory=mydump_dir dumpfile=scott_table_2.dmp
Import: Release 21.0.0.0.0 - Production on 土 8月 31 16:15:43 2024
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
パスワード:
接続先: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
マスター表"SCOTT"."SYS_IMPORT_TABLE_01"は正常にロード/アンロードされました
"SCOTT"."SYS_IMPORT_TABLE_01"を起動しています: scott/********@orclpdb1 tables=scott.table_2 exclude=constraint table_exists_action=replace directory=mydump_dir
dumpfile=scott_table_2.dmp
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
オブジェクト型TABLE_EXPORT/TABLE/TABLE_DATAの処理中です
. . "SCOTT"."TABLE_2" 344.8 MB 15000000行がインポートされました
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/MARKERの処理中です
ジョブ"SCOTT"."SYS_IMPORT_TABLE_01"が土 8月 31 16:17:02 2024 elapsed 0 00:01:05で正常に完了しました
インポート完了後、desc でみると ID1, ID2ともにNULLABLEになっています。まずい状態ですよね
制約は全く存在しません。（exclude=constraintにしてしまったので当然ではあるわけですがw）
[oracle@localhost ~]$ sqlplus scott@orclpdb1
...中略...
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
に接続されました。
1SCOTT@orclpdb1> set line 80
1SCOTT@orclpdb1> desc table_2
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ID1 NUMBER
ID2 NUMBER
TEXT VARCHAR2(10)
SCOTT@orclpdb1> SELECT table_name,constraint_name,constraint_type,search_condition FROM user_constraints WHERE table_name = 'TABLE_2';
レコードが選択されませんでした。
経過: 00:00:04.90
SCOTT@orclpdb1> SELECT COUNT(1) FROM table_2;
COUNT(1)
----------
15000000
経過: 00:00:01.03
主キー制約追加で例の症状を発症させてみましょう！ 主キー制約追加に、10秒もかかってますね。
SCOTT@orclpdb1> CREATE UNIQUE INDEX pk_table_2 ON table_2(id1, id2);
索引が作成されました。
経過: 00:00:30.11
SCOTT@orclpdb1> ALTER TABLE table_2 ADD CONSTRAINT pk_table_2 PRIMARY KEY (id1, id2) USING INDEX pk_table_2;
表が変更されました。
経過: 00:00:10.60
SCOTT@orclpdb1> desc table_2
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ID1 NOT NULL NUMBER
ID2 NOT NULL NUMBER
TEXT VARCHAR2(10)
SCOTT@orclpdb1> set line 400
SCOTT@orclpdb1> SELECT table_name,constraint_name,constraint_type,search_condition FROM user_constraints WHERE table_name = 'TABLE_2'
TABLE_NAME CONSTRAINT_NAME CON SEARCH_CONDITION
------------------------------ ------------------------------ --- ------------------------------
TABLE_2 PK_TABLE_2 P
経過: 00:00:00.02
再確認のため、ID1, ID2をNOT NULLに変更した上で、主キー制約を追加してましょう。
見事に瞬殺で完了しているのがわかります！
SCOTT@orclpdb1> ALTER TABLE table_2 DROP PRIMARY KEY DROP INDEX;
表が変更されました。
経過: 00:00:00.39
SCOTT@orclpdb1> ALTER TABLE table_2 MODIFY (id1 NUMBER NOT NULL, id2 NUMBER NOT NULL);
表が変更されました。
経過: 00:00:03.02
SCOTT@orclpdb1> CREATE UNIQUE INDEX pk_table_2 ON table_2(id1, id2);
索引が作成されました。
経過: 00:00:27.11
SCOTT@orclpdb1> ALTER TABLE table_2 ADD CONSTRAINT pk_table_2 PRIMARY KEY (id1, id2) USING INDEX pk_table_2;
表が変更されました。
経過: 00:00:00.01
SCOTT@orclpdb1> set line 80
SCOTT@orclpdb1> desc table_2
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ID1 NOT NULL NUMBER
ID2 NOT NULL NUMBER
TEXT VARCHAR2(10)
SCOTT@orclpdb1> set line 400
SCOTT@orclpdb1> SELECT table_name,constraint_name,constraint_type,search_condition FROM user_constraints WHERE table_name = 'TABLE_2';
TABLE_NAME CONSTRAINT_NAME CON SEARCH_CONDITION
------------------------------ ------------------------------ --- ------------------------------
TABLE_2 SYS_C0010546 C "ID1" IS NOT NULL
TABLE_2 SYS_C0010547 C "ID2" IS NOT NULL
TABLE_2 PK_TABLE_2 P
経過: 00:00:00.03
ということで、Data Pumpのマニュアルの excludeオプションの仕様を確認！！！
Oracle Database / Release 23 / ユーティリティ / 2 Oracle Data Pumpエクスポート / 2.4.23 EXCLUDE https://docs.oracle.com/cd/F82042_01/sutil/oracle-data-pump-export-utility.html#SUTIL-GUID-64249296-2AFF-40EA-AA44-BC0A1B5A1E7C
"制約の除外
次の制約は明示的に除外できません。
表の作成とロードを正常に行うために必要な制約。たとえば、索引構成表の主キー制約、REF列を持つ表のREF SCOPEおよびWITH ROWID制約など
たとえば、次のEXCLUDE文は、次のように解釈されます。
EXCLUDE=CONSTRAINTでは、表の正常な作成およびロードに必要な制約を除く、すべての制約を除外します。
EXCLUDE=REF_CONSTRAINTは、参照整合性(外部キー)制約を除外します。"
と記されています。
SCHEMA_EXPORT_OBJECTSで確認しておきましょう. Oracle Data Pumpと仲良くなるために忘れちゃいけないのがこのビューですよ。（覚えておきましょう）
TABLE/CONSTRAINT , TABLE/CONSTRAINT/REF_CONSTRAINT という制約に関連するオブジェクトパスがありますが、ここがポイント。マニュアル通り、exclude/includeで制御できるのはこのレベルまで
参照整合性制約と、すべての制約のいずれかのパスしか選べません。つまりこれより細かく、例えば、主キー制約だけexcludeしたいという場合には、ピンポイントでオブジェクト名でフィルタする必要があるということを意味します。
つまり、 impdpのexcludeオプションで constraint としてインポートしてしまったため、主キー制約だけでなく、今回話題にしている主キー列のNOT NULL制約（CHECK制約で検証されることは前回も記載したとおり）も丸っと削除されてしまったため、今回話題にしている主キー制約の追加で時間を要してしまう症状が発症してしまった！！ ということ。 前回少しだけ触れていた、ツールの使い方によっては影響を受ける可能性があるということの一例です。
SCOTT@orclpdb1> SELECT object_path,comments FROM schema_export_objects WHERE object_path LIKE '%TABLE%' ORDER BY object_path;
OBJECT_PATH COMMENTS
------------------------------------------------------------ ----------------------------------------------------
...中略...
SCHEMA_EXPORT/TABLE/COMMENT Table and column comments on the selected tables
SCHEMA_EXPORT/TABLE/CONSTRAINT Constraints (including referential constraints)
SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Referential constraints
SCHEMA_EXPORT/TABLE/FGA_POLICY Fine-grained auditing policies
...中略...
TABLE/COMMENT Table and column comments on the selected tables
TABLE/CONSTRAINT Constraints (including referential constraints)
TABLE/CONSTRAINT/REF_CONSTRAINT Referential constraints
TABLE/FGA_POLICY Fine-grained auditing policies
TABLE/GRANT Object grants on the selected tables
...中略...
TABLE/RLS_POLICY/RLS_POLICY Fine-grained access control policies
TABLE/TRIGGER Triggers
TABLESPACE_QUOTA Tablespace quotas granted to users associated with the selected schemas
52行が選択されました。
経過: 00:00:00.40
対処方法は細かくフィルタして、NOT NULL制約は残したまま、主キー制約だけexceludeしましょう。簡単ですよね。Data Pumpに慣れている皆さんなら:)
マニュアルには書かれていないですが、コマンドラインの場合、"(ダブルクォート)や、'(シングルクォート)を利用する場合はバックスラッシュ（Windowsだと¥だったかな。使わないのでド忘れしたので間違ってたらツッコミよろしゅう）でエスケープしておく必要があります。ここでも、Data Pumpの癖が炸裂してますねw ()
今回は、PK_TABLE_2という主キー制約だけを除外しています。（インポート後にNOT NULL制約はインポートされていることを確認します）
[oracle@localhost ~]$ impdp scott@orclpdb1 tables=scott.table_2 exclude=constraint:\"=\'PK_TABLE_2\'\" table_exists_action=replace directory=mydump_dir dumpfile=scott_table_2.dmp
Import: Release 21.0.0.0.0 - Production on 土 8月 31 16:43:11 2024
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
パスワード:
接続先: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
マスター表"SCOTT"."SYS_IMPORT_TABLE_01"は正常にロード/アンロードされました
"SCOTT"."SYS_IMPORT_TABLE_01"を起動しています: scott/********@orclpdb1 tables=scott.table_2 exclude=constraint:"='PK_TABLE_2'" table_exists_action=replace directory=mydump_dir dumpfile=scott_table_2.dmp
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
オブジェクト型TABLE_EXPORT/TABLE/TABLE_DATAの処理中です
. . "SCOTT"."TABLE_2" 344.8 MB 15000000行がインポートされました
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/MARKERの処理中です
ジョブ"SCOTT"."SYS_IMPORT_TABLE_01"が土 8月 31 16:44:22 2024 elapsed 0 00:01:02で正常に完了しました
正常にインポートできました！ 制約関連が想定通りにインポートされているか確認！
（excludeオプションでCONSTRAINTを除外してしまうと、オブジェクト名でフィルタリングしていたとしてもCONSTRAINTがインポートされた（一部）とは記録もされません。この癖も改善して惜しい癖なんだけどなかなか改善してくれないですね→オラクルさん）
ID1, ID2列に NOT NULL制約が付いています。また、主キー制約は存在していません。
descコマンドや、dba/user_tab_clumnsでは、主キー制約によるNOT NULLの制約であっても、NOT NULLと表示されるため確認は、必ず、dba/user_constraintsで該当列のconstraint_typeが'C'でsearch_conditionに xxxx IS NOT NULLというチェック制約でNOT NULLが保証されていることを確認する必要あるので見落とさないようにしてくださいね。（ここ重要!!）
[oracle@localhost ~]$ sqlplus scott@orclpdb1
...中略...
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
に接続されました。
SCOTT@orclpdb1> set line 80
SCOTT@orclpdb1> desc table_2
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ID1 NOT NULL NUMBER
ID2 NOT NULL NUMBER
TEXT VARCHAR2(10)
SCOTT@orclpdb1> set line 400
SCOTT@orclpdb1> col constraint_name for a30
SCOTT@orclpdb1> SELECT table_name,constraint_name,constraint_type,search_condition FROM user_constraints WHERE table_name = 'TABLE_2';
TABLE_NAME CONSTRAINT_NAME CON SEARCH_CONDITION
------------------------------ ------------------------------ --- --------------------------------------------------------------------------------
TABLE_2 SYS_C0010597 C "ID1" IS NOT NULL
TABLE_2 SYS_C0010598 C "ID2" IS NOT NULL
経過: 00:00:00.02
SCOTT@orclpdb1> SELECT table_name,index_name FROM user_indexes WHERE table_name = 'TABLE_2';
レコードが選択されませんでした。
経過: 00:00:00.08
では、主キー制約の追加が瞬殺で終わることを確認して、今回のネタはおしまい！ :)
SCOTT@orclpdb1> CREATE UNIQUE INDEX pk_table_2 ON table_2(id1, id2);
索引が作成されました。
経過: 00:00:29.75
SCOTT@orclpdb1> ALTER TABLE table_2 ADD CONSTRAINT pk_table_2 PRIMARY KEY (id1, id2) USING INDEX pk_table_2;
表が変更されました。
経過: 00:00:00.07
SCOTT@orclpdb1> set line 80
SCOTT@orclpdb1> desc table_2
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ID1 NOT NULL NUMBER
ID2 NOT NULL NUMBER
TEXT VARCHAR2(10)
SCOTT@orclpdb1> set line 400
SCOTT@orclpdb1> SELECT table_name,constraint_name,constraint_type,search_condition FROM user_constraints WHERE table_name = 'TABLE_2';
TABLE_NAME CONSTRAINT_NAME CON SEARCH_CONDITION
------------------------------ ------------------------------ --- --------------------------------------------------------------------------------
TABLE_2 SYS_C0010597 C "ID1" IS NOT NULL
TABLE_2 SYS_C0010598 C "ID2" IS NOT NULL
TABLE_2 PK_TABLE_2 P
経過: 00:00:00.02
SCOTT@orclpdb1> SELECT table_name,index_name FROM user_indexes WHERE table_name = 'TABLE_2';
TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
TABLE_2 PK_TABLE_2
経過: 00:00:00.01
ノロノロ台風？みたいなやつ、まじではなく、どこかへいってほしいっすねw （天候がめんどくさいことになってて勘弁してくれという感じの東京より
ではまた。
参考）準備に利用したスクリプト
[oracle@localhost ~]$ cat additional_example.sql
DROP TABLE table_2;
CREATE TABLE table_2
(
id1 NUMBER NOT NULL
,id2 NUMBER NOT NULL
,text VARCHAR2(10)
)
.
l
/
set line 80
desc table_2
set line 400
BEGIN
FOR i in 1..15000000 LOOP
INSERT INTO table_2 VALUES(i,i,i);
IF MOD(i,1000) = 0
THEN
COMMIT;
END IF;
END LOOP;
END;
.
l
/
ALTER TABLE table_2 ADD CONSTRAINT pk_table_2 PRIMARY KEY (id1, id2) USING INDEX
.
l
/
関連エントリー
・Data Pumpも癖モノだよね〜w その１ - queryパラメーターの解析タイミング
・ata Pumpも癖モノだよね〜w その２ - Materialized ViewをTableとして移行する
・
・Data Pumpも癖モノだよね〜w その4 - schemaモードでMviewを他のPDBへ複製
・Data Pumpも癖モノだよね〜w その4と1/2 - schemaモードでMviewを他のPDBへ複製 (紛らわしいステータスw
・Data Pumpも癖モノだよね〜w その5 - schemaモードでMviewを他のPDBへ複製（オプジェクトパス de 絞り込み）
・Data Pumpも癖モノだよね〜w その6 - schemaモードでMviewを他のPDBへ複製（オプジェクトパスが不足すると...）
・RDS Oracle 雑多なメモ#18 / DBMS_DATAPUMPパッケージ de expdp/impdp
・RDS Oracle 雑多なメモ#19 FAQ / DBMS_DATAPUMPパッケージ de ジョブの停止
・なぜ、主キー制約の追加時間に違いがでるのでしょうか？ （東京都 ITエンジニア 男性）/ FAQ
最近のコメント