こんばんは。あいも変わらず真夜中に記事を書く夏椰です。
年末ですが、普通に記事を書きます。
?
今回は再び再帰クエリです。
再帰クエリが出来るようになって、その実行プランはどうなのさ?って思うので
やってみました。
#今回は実家で画面イメージをいぢるソフト持ち合わせていなく、
#文字ベースでの実行計画を取りました。ごめんなさい。
?
まずはテストデータですね。
id |
val |
pid |
1 |
親1 |
0 |
2 |
親2 |
0 |
3 |
親3 |
0 |
4 |
子1 |
1 |
5 |
子2 |
1 |
6 |
子3 |
1 |
7 |
子1 |
2 |
8 |
子1 |
3 |
9 |
子2 |
3 |
10 |
孫1 |
6 |
前と同じ用にPID列に親のIDを持つようにしてみました。
で再帰SQL文は以下のように書きました。
with query(id,val,level) as
(
select id, val, 0 from testrc where pid = 0
union all
select b.id, q.val + b.val as val, level + 1 from testrc b join query q on b.pid = q.id
)
select * from query
;
?
結果は以下のとおりに取れます。
id |
val |
level |
1 |
親1 |
0 |
2 |
親2 |
0 |
3 |
親3 |
0 |
8 |
親3子1 |
1 |
9 |
親3子2 |
1 |
7 |
親2子1 |
1 |
4 |
親1子1 |
1 |
5 |
親1子2 |
1 |
6 |
親1子3 |
1 |
10 |
親1子3孫1 |
2 |
?
さて、実行計画です!!
#ごめんなさい、テーブルにしたら醜くなったかも・・・・
StmtText | StmtId | NodeId | Parent | PhysicalOp | LogicalOp | Argument | DefinedValues | EstimateRows | EstimateIO | EstimateCPU | AvgRowSize | TotalSubtreeCost | PutputList | Warnings | Type | Parallel | EstimateExecutions |
---|
with query(id,val,level) as ( select id, val, 0 from testrc where pid = 0 union all select b.id, q.val + b.val as val, level + 1 from testrc b join query q on b.pid = q.id ) select * from query ; | 1 | 1 | 0 | NULL | NULL | 1 | NULL | 5 | NULL | NULL | NULL | 0.0066766 | NULL | NULL | SELECT | 0 | NULL |
|--Index Spool(WITH STACK) | 1 | 2 | 1 | Index Spool | Lazy Spool | WITH STACK | NULL | 5 | 0 | 2.5E-08 | 1063 | 0.0066766 | [Expr1019], [Recr1013], [Recr1014], [Recr1015] | NULL | PLAN_ROW | 0 | 1 |
|--Concatenation | 1 | 3 | 2 | Concatenation | Concatenation | NULL | [Expr1019] = ([Expr1016], [Expr1018]), [Recr1013] = ([test].[dbo].[testrc].[id], [b].[id]), [Recr1014] = ([test].[dbo].[testrc].[val], [Expr1011]), [Recr1015] = ([Expr1004], [Expr1012]) | 5 | 0 | 5E-09 | 1063 | 0.006660575 | [Expr1019], [Recr1013], [Recr1014], [Recr1015] | NULL | PLAN_ROW | 0 | 1 |
|--Compute Scalar(DEFINE:([Expr1016]=(0))) | 1 | 4 | 3 | Compute Scalar | Compute Scalar | DEFINE:([Expr1016]=(0)) | [Expr1016]=(0) | 1 | 0 | 5E-08 | 1063 | 5E-08 | [Expr1016], [test].[dbo].[testrc].[id], [test].[dbo].[testrc].[val], [Expr1004] | NULL | PLAN_ROW | 0 | 6 |
| |--Compute Scalar(DEFINE:([Expr1004]=(0))) | 1 | 5 | 4 | Compute Scalar | Compute Scalar | DEFINE:([Expr1004]=(0)) | [Expr1004]=(0) | 3 | 0 | 3E-07 | 4031 | 0.0032981 | [test].[dbo].[testrc].[id], [test].[dbo].[testrc].[val], [Expr1004] | NULL | PLAN_ROW | 0 | 1 |
| |--Table Scan(OBJECT:([test].[dbo].[testrc]), WHERE:([test].[dbo].[testrc].[pid]=(0))) | 1 | 6 | 5 | Table Scan | Table Scan | OBJECT:([test].[dbo].[testrc]), WHERE:([test].[dbo].[testrc].[pid]=(0)) | [test].[dbo].[testrc].[id], [test].[dbo].[testrc].[val] | 3 | 0.003125 | 0.000168 | 4031 | 0.003293 | [test].[dbo].[testrc].[id], [test].[dbo].[testrc].[val] | NULL | PLAN_ROW | 0 | 1 |
|--Assert(WHERE:(CASE WHEN [Expr1018]>(100) THEN (0) ELSE NULL END)) | 1 | 13 | 3 | Assert | Assert | WHERE:(CASE WHEN [Expr1018]>(100) THEN (0) ELSE NULL END) | NULL | 1.666667 | 0 | 4.2E-07 | 1063 | 0.00336247 | [Expr1018], [b].[id], [Expr1011], [Expr1012] | NULL | PLAN_ROW | 0 | 6 |
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1018], [Recr1008], [Recr1009], [Recr1010])) | 1 | 14 | 13 | Nested Loops | Inner Join | OUTER REFERENCES:([Expr1018], [Recr1008], [Recr1009], [Recr1010]) | NULL | 1.666667 | 0 | 4.2E-07 | 1063 | 0.00336247 | [Expr1018], [b].[id], [Expr1011], [Expr1012] | NULL | PLAN_ROW | 0 | 6 |
|--Compute Scalar(DEFINE:([Expr1018]=[Expr1017]+(1))) | 1 | 15 | 14 | Compute Scalar | Compute Scalar | DEFINE:([Expr1018]=[Expr1017]+(1)) | [Expr1018]=[Expr1017]+(1) | 1 | 0 | 5E-08 | 1063 | 5E-08 | [Expr1018], [Recr1008], [Recr1009], [Recr1010] | NULL | PLAN_ROW | 0 | 6 |
| |--Table Spool(WITH STACK) | 1 | 16 | 15 | Table Spool | Lazy Spool | WITH STACK | NULL | 1 | 0 | 5E-08 | 1063 | 5E-08 | [Expr1017], [Recr1008], [Recr1009], [Recr1010] | NULL | PLAN_ROW | 0 | 6 |
|--Compute Scalar(DEFINE:([Expr1011]=[Recr1009]+[test].[dbo].[testrc].[val] as [b].[val], [Expr1012]=[Recr1010]+(1))) | 1 | 20 | 14 | Compute Scalar | Compute Scalar | DEFINE:([Expr1011]=[Recr1009]+[test].[dbo].[testrc].[val] as [b].[val], [Expr1012]=[Recr1010]+(1)) | [Expr1011]=[Recr1009]+[test].[dbo].[testrc].[val] as [b].[val], [Expr1012]=[Recr1010]+(1) | 2 | 0 | 2E-07 | 1063 | 0.003362 | [b].[id], [Expr1011], [Expr1012] | NULL | PLAN_ROW | 0 | 5 |
|--Table Scan(OBJECT:([test].[dbo].[testrc] AS [b]), WHERE:([test].[dbo].[testrc].[pid] as [b].[pid]=[Recr1008])) | 1 | 21 | 20 | Table Scan | Table Scan | OBJECT:([test].[dbo].[testrc] AS [b]), WHERE:([test].[dbo].[testrc].[pid] as [b].[pid]=[Recr1008]) | [b].[id], [b].[val] | 2 | 0.0032035 | 8.95E-05 | 4031 | 0.003337 | [b].[id], [b].[val] | NULL | PLAN_ROW | 0 | 5 |
これを見て気づくのがASSERTです。
100回超えたらいけないそうですよ。
なんで、ためしに99、100、101回の再帰をしてみたところ、
101回だけ
メッセージ 530、レベル 16、状態 1、行 2
ステートメントが終了しました。ステートメントの完了前に最大再帰数 100 に達しました。
が表示されました。
100回の再帰まではOKだそうです。
メッセージがちょぉっと引っかかるけど・・・・。「100に達した」=「100はいけない?」って感じもするし・・・。
#よって結果は101行(ベース行+再帰で得た100行です)
?
再帰クエリを使用する際は、100回!にご注意ください。
?