SQL Server で既にデータが格納されているテーブルに外部キー制約 (Foreign Key Constraint) を設定する際に、デフォルトの設定で外部キーを設定した場合、「WITH CHECK」が既定の動作となるため、テーブル間でデータ整合性のチェックが行われています。
このチェックの際には、どのようなクエリが実行されているのかを本投稿でメモとして残しておきます。(以前から書こうと思っていて忘れていた内容)
実行されるデータ整合性チェック処理の内容
テスト用のテーブルの作成
今回は TPC-H の次のようなテーブルに対して外部キー制約を設定します。
CREATE TABLE [dbo].[NATION]( [N_NATIONKEY] [int] NOT NULL, [N_NAME] [char](25) NOT NULL, [N_REGIONKEY] [int] NOT NULL, [N_COMMENT] [varchar](152) NULL, CONSTRAINT [PK_NATION] PRIMARY KEY CLUSTERED ([N_NATIONKEY] ASC) ) ON [PRIMARY] GO CREATE TABLE [dbo].[SUPPLIER]( [S_SUPPKEY] [int] NOT NULL, [S_NAME] [char](25) NOT NULL, [S_ADDRESS] [varchar](40) NOT NULL, [S_NATIONKEY] [int] NOT NULL, [S_PHONE] [char](15) NOT NULL, [S_ACCTBAL] [decimal](15, 2) NOT NULL, [S_COMMENT] [varchar](101) NOT NULL ) ON [PRIMARY] GO
外部キーの作成
外部キーを作成するためのクエリとしては次のクエリを実行します。
ALTER TABLE [dbo].[SUPPLIER] ADD CONSTRAINT FK_SUPPLIER_NATION FOREIGN KEY ([S_NATIONKEY]) REFERENCES [dbo].[NATION]([N_NATIONKEY])
実行プランの取得 (Memo の情報を基にして確認)
上記のクエリを「実行プランの表示」を有効にして、実行しても実行プランは取得されません。
これでは、チェックが実行される際にはどのようなプランが指定されるかを確認することができませんので、More Undocumented Query Optimizer Trace Flags / Secrets of the Query Optimizer Revealed 等で解説されている Memo の情報を使用します。
Memo の解説はとても複雑なものとなりますので、本投稿では Memo を出力するために使用するトレースフラグだけ記載しておきます。
「クエリがコンパイル / リコンパイル」されることで、情報が出力されますので、Memo を確認しようとした場合に、情報が出力されない場合は、「DBCC FREEPROCCACHE」「OPTOIN (RECOMPILE)」等を実行して、クエリのコンパイルを誘発させてください。
トレースフラグを有効化した後の挙動が想定したものとなっていれば、オプティマイザによって実行プランが生成される際の、論理ツリーの生成と物理操作のルールの選定についての情報が出力されます。
DBCC TRACEON(
3604 -- Output Message
, 8606 -- Optimize Tree
, 8612 -- Tree Aditional Info
, 8675 -- Output Task / Time / Cost
, 8619 -- Transform Rule
, 8620 -- Transform Rule Additional Info
, 8608 -- Initial Memo Structure
, 8615 -- Final Memo Structure
)
最終的に使用される Logical Tree は次のような内容となります。
LogOp_Assert(
ForeignKey COLUMN CNST: FK_SUPPLIER_NATION COL: N_NATIONKEY, ) [ Card=1 ]
LogOp_LeftAntiSemiJoin [ Card=1 ]
LogOp_Get TBL: dbo.SUPPLIER dbo.SUPPLIER TableID=1509580416 TableReferenceID=0 IsRow: COL: IsBaseRow1001 [ Card=1000 ]
LogOp_Get TBL: [fk_test].[dbo].[NATION] NATION TableID=1477580302 TableReferenceID=0 IsRow: COL: IsBaseRow1003 Hints( READ-COMMITTED ) [ Card=25 ]
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [fk_test].[dbo].[SUPPLIER].S_NATIONKEY
ScaOp_Identifier QCOL: [fk_test].[dbo].[NATION].N_NATIONKEY
ScaOp_Const TI(bit,ML=1) XVAR(bit,Not Owned,Value=1)
「LogOp_LeftAntiSemiJoin」が使用されて、データが存在するかを確認するための論理操作が生成されていることが確認できます。
上記の Logical Tree ですが、次のクエリを実行した場合に近い内容となるのではないでしょうか。
SELECT 1 FROM [SUPPLIER] WHERE S_NATIONKEY NOT IN( SELECT N_NATIONKEY FROM NATION )
このクエリで作成された論理操作の内容がこちらとなります。
LogOp_Project
LogOp_LeftAntiSemiJoin [ Card=1 ]
LogOp_Get TBL: SUPPLIER SUPPLIER TableID=1509580416 TableReferenceID=0 IsRow: COL: IsBaseRow1001 [ Card=1000 ]
LogOp_Get TBL: NATION NATION TableID=1477580302 TableReferenceID=0 IsRow: COL: IsBaseRow1003 [ Card=25 ]
ScaOp_Comp x_cmpEq
ScaOp_Identifier QCOL: [fk_test].[dbo].[SUPPLIER].S_NATIONKEY
ScaOp_Identifier QCOL: [fk_test].[dbo].[NATION].N_NATIONKEY
AncOp_PrjList
AncOp_PrjEl COL: Expr1005
ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=1)
NOT IN を使用したクエリを実行しているのですが、LeftAntiSemiJoin が使用されてデータの検索が行われていますね。
今回のケースであれば、外部キーを WITH CHECK を有効にするクエリで、使用される実行プランはこの形に近いものとなるのではないでしょうか。
まとめ
外部キーを作成する際には、一般的には、データの整合性チェックが実行されますので、データ量に応じて処理に時間がかかります。
外部キーを作成する際に必要な処理時間については、NOT IN を使用したクエリを事前に実行し、処理時間を計測しておくことで、作成に必要となる処理時間を推定できるのではないでしょうか。