夏椰の東屋

- お遊び記録 -

ホーム 連絡をする 同期する ( RSS 2.0 ) Login
投稿数  108  : 記事  1  : コメント  597  : トラックバック  30

ニュース


落書きしてね♪

IAM
僕がとった写真です。
ご自由にお使いください。

フィードメーター - 夏椰の東屋 track feed
広告


記事カテゴリ

書庫

日記カテゴリ

Other Site From Kaya

こんばんは。あいも変わらず真夜中に記事を書く夏椰です。

年末ですが、普通に記事を書きます。

?

今回は再び再帰クエリです。

再帰クエリが出来るようになって、その実行プランはどうなのさ?って思うので

やってみました。

#今回は実家で画面イメージをいぢるソフト持ち合わせていなく、

#文字ベースでの実行計画を取りました。ごめんなさい。

?

まずはテストデータですね。

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

?


さて、実行計画です!!

#ごめんなさい、テーブルにしたら醜くなったかも・・・・


StmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostPutputListWarningsTypeParallelEstimateExecutions
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 ;110NULLNULL1NULL5NULLNULLNULL0.0066766NULLNULLSELECT0NULL
|--Index Spool(WITH STACK)121Index SpoolLazy SpoolWITH STACKNULL502.5E-0810630.0066766[Expr1019], [Recr1013], [Recr1014], [Recr1015]NULLPLAN_ROW01
|--Concatenation132ConcatenationConcatenationNULL[Expr1019] = ([Expr1016], [Expr1018]), [Recr1013] = ([test].[dbo].[testrc].[id], [b].[id]), [Recr1014] = ([test].[dbo].[testrc].[val], [Expr1011]), [Recr1015] = ([Expr1004], [Expr1012])505E-0910630.006660575[Expr1019], [Recr1013], [Recr1014], [Recr1015]NULLPLAN_ROW01
|--Compute Scalar(DEFINE:([Expr1016]=(0)))143Compute ScalarCompute ScalarDEFINE:([Expr1016]=(0))[Expr1016]=(0)105E-0810635E-08[Expr1016], [test].[dbo].[testrc].[id], [test].[dbo].[testrc].[val], [Expr1004]NULLPLAN_ROW06
| |--Compute Scalar(DEFINE:([Expr1004]=(0)))154Compute ScalarCompute ScalarDEFINE:([Expr1004]=(0))[Expr1004]=(0)303E-0740310.0032981[test].[dbo].[testrc].[id], [test].[dbo].[testrc].[val], [Expr1004]NULLPLAN_ROW01
| |--Table Scan(OBJECT:([test].[dbo].[testrc]), WHERE:([test].[dbo].[testrc].[pid]=(0)))165Table ScanTable ScanOBJECT:([test].[dbo].[testrc]), WHERE:([test].[dbo].[testrc].[pid]=(0))[test].[dbo].[testrc].[id], [test].[dbo].[testrc].[val]30.0031250.00016840310.003293[test].[dbo].[testrc].[id], [test].[dbo].[testrc].[val]NULLPLAN_ROW01
|--Assert(WHERE:(CASE WHEN [Expr1018]>(100) THEN (0) ELSE NULL END))1133AssertAssertWHERE:(CASE WHEN [Expr1018]>(100) THEN (0) ELSE NULL END)NULL1.66666704.2E-0710630.00336247[Expr1018], [b].[id], [Expr1011], [Expr1012]NULLPLAN_ROW06
|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1018], [Recr1008], [Recr1009], [Recr1010]))11413Nested LoopsInner JoinOUTER REFERENCES:([Expr1018], [Recr1008], [Recr1009], [Recr1010])NULL1.66666704.2E-0710630.00336247[Expr1018], [b].[id], [Expr1011], [Expr1012]NULLPLAN_ROW06
|--Compute Scalar(DEFINE:([Expr1018]=[Expr1017]+(1)))11514Compute ScalarCompute ScalarDEFINE:([Expr1018]=[Expr1017]+(1))[Expr1018]=[Expr1017]+(1)105E-0810635E-08[Expr1018], [Recr1008], [Recr1009], [Recr1010]NULLPLAN_ROW06
| |--Table Spool(WITH STACK)11615Table SpoolLazy SpoolWITH STACKNULL105E-0810635E-08[Expr1017], [Recr1008], [Recr1009], [Recr1010]NULLPLAN_ROW06
|--Compute Scalar(DEFINE:([Expr1011]=[Recr1009]+[test].[dbo].[testrc].[val] as [b].[val], [Expr1012]=[Recr1010]+(1)))12014Compute ScalarCompute ScalarDEFINE:([Expr1011]=[Recr1009]+[test].[dbo].[testrc].[val] as [b].[val], [Expr1012]=[Recr1010]+(1))[Expr1011]=[Recr1009]+[test].[dbo].[testrc].[val] as [b].[val], [Expr1012]=[Recr1010]+(1)202E-0710630.003362[b].[id], [Expr1011], [Expr1012]NULLPLAN_ROW05
|--Table Scan(OBJECT:([test].[dbo].[testrc] AS [b]), WHERE:([test].[dbo].[testrc].[pid] as [b].[pid]=[Recr1008]))12120Table ScanTable ScanOBJECT:([test].[dbo].[testrc] AS [b]), WHERE:([test].[dbo].[testrc].[pid] as [b].[pid]=[Recr1008])[b].[id], [b].[val]20.00320358.95E-0540310.003337[b].[id], [b].[val]NULLPLAN_ROW05


これを見て気づくのがASSERTです。

100回超えたらいけないそうですよ。

なんで、ためしに99、100、101回の再帰をしてみたところ、

101回だけ

メッセージ 530、レベル 16、状態 1、行 2
ステートメントが終了しました。ステートメントの完了前に最大再帰数 100 に達しました。

が表示されました。

100回の再帰まではOKだそうです。

メッセージがちょぉっと引っかかるけど・・・・。「100に達した」=「100はいけない?」って感じもするし・・・。

#よって結果は101行(ベース行+再帰で得た100行です)

?

再帰クエリを使用する際は、100回!にご注意ください。

?

投稿日時 : 2006年12月31日 4:21

コメント

# re: 再帰クエリその2 2006/12/31 8:05 ぽぴ王子
> #ごめんなさい、テーブルにしたら醜くなったかも・・・・
つ【CSS】

ExprXXXXってあるから、DBはOracleですよね(どこにも書いてなかった)。
昔のを見たらOracle10gって書いてあった。
いきなり今回のから見始める人もいるかと思うので、書いてあるとうれしいかも。

それはそれとして。
100回って面白いですね。無制限にすると処理として重くなるだろうし、どこかで
制限をつけるとしても255とかじゃなくて100か。
SQL Serverだと制限をつけるにしても255とかにしそうだけど、Oracleだから?100という気がする。いや勝手な妄想。
Oracle最近触ってないけど、ちょっと遊んでみたくなった。うーうー。
どうしようかな。やっぱSQL Serverで我慢しておくかな。
って内容とぜんぜん関係ないコメントでスイマセン。

Post Feedback

タイトル
名前
Url:
コメント