关于sql optimizer的一个问题
0
悬赏园豆：50
[已关闭问题]
Merge join和hash join都报错，去掉merge或者修改where谓词之后或能得到plan，高手能分析一下么？<br><br>错误代码：<br><font color="#ff0000">Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.</font><br><br>我的理解是sql server在断定join的两个input各只能产生一行记录，用nested loops join当然是最合适的；但在编译plan报错，让我不解。（难道merge和hash join就是不能用么？？）<br><br><div class="cnblogs_code"><!--<br><br>Code highlighting produced by Actipro CodeHighlighter (freeware)<br>http://www.CodeHighlighter.com/<br><br>--><img src="/Images/OutliningIndicators/None.gif" align="top"><span style="color: rgb(0, 0, 255);">if</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 255);">object_id</span><span style="color: rgb(0, 0, 0);">(</span><span style="color: rgb(255, 0, 0);">'</span><span style="color: rgb(255, 0, 0);">[ladfadfad_WeekAggregate]</span><span style="color: rgb(255, 0, 0);">'</span><span style="color: rgb(0, 0, 0);">) </span><span style="color: rgb(0, 0, 255);">is</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 128, 128);">not</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">null</span><span style="color: rgb(0, 0, 0);"><br><img src="/Images/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(0, 0, 255);">drop</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">table</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">ladfadfad_WeekAggregate</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"><br><img src="/Images/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(0, 0, 255);">go</span><span style="color: rgb(0, 0, 0);"><br><img src="/Images/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(0, 0, 255);">CREATE</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">TABLE</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">dbo</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);">.</span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">ladfadfad_WeekAggregate</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);">(<br><img src="/Images/OutliningIndicators/None.gif" align="top"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">PID</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">int</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 128, 128);">NOT</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">NULL</span><span style="color: rgb(0, 0, 0);">,<br><img src="/Images/OutliningIndicators/None.gif" align="top"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">ItemCategoryID</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">tinyint</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 128, 128);">NOT</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">NULL</span><span style="color: rgb(0, 0, 0);">,<br><img src="/Images/OutliningIndicators/None.gif" align="top"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">ParentItemID</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">bigint</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 128, 128);">NOT</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">NULL</span><span style="color: rgb(0, 0, 0);">,<br><img src="/Images/OutliningIndicators/None.gif" align="top"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">ItemID</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">bigint</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 128, 128);">NOT</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">NULL</span><span style="color: rgb(0, 0, 0);"><br><img src="/Images/OutliningIndicators/None.gif" align="top"> </span><span style="color: rgb(0, 0, 255);">CONSTRAINT</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">ladfadfad_WeekAggregate_PK</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">PRIMARY</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">KEY</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">CLUSTERED</span><span style="color: rgb(0, 0, 0);"> <br><img src="/Images/OutliningIndicators/None.gif" align="top">(<br><img src="/Images/OutliningIndicators/None.gif" align="top"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">PID</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">ASC</span><span style="color: rgb(0, 0, 0);">,<br><img src="/Images/OutliningIndicators/None.gif" align="top"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">ItemCategoryID</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">ASC</span><span style="color: rgb(0, 0, 0);">,<br><img src="/Images/OutliningIndicators/None.gif" align="top"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">ParentItemID</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">ASC</span><span style="color: rgb(0, 0, 0);">,<br><img src="/Images/OutliningIndicators/None.gif" align="top"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">ItemID</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">ASC</span><span style="color: rgb(0, 0, 0);"><br><img src="/Images/OutliningIndicators/None.gif" align="top">)<br><img src="/Images/OutliningIndicators/None.gif" align="top">)<br><img src="/Images/OutliningIndicators/None.gif" align="top"><br><img src="/Images/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(0, 0, 255);">go</span><span style="color: rgb(0, 0, 0);"><br><img src="/Images/OutliningIndicators/None.gif" align="top"><br><img src="/Images/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(0, 0, 255);">if</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 255);">object_id</span><span style="color: rgb(0, 0, 0);">(</span><span style="color: rgb(255, 0, 0);">'</span><span style="color: rgb(255, 0, 0);">ladfadfad_item1</span><span style="color: rgb(255, 0, 0);">'</span><span style="color: rgb(0, 0, 0);">) </span><span style="color: rgb(0, 0, 255);">is</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 128, 128);">not</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">null</span><span style="color: rgb(0, 0, 0);"><br><img src="/Images/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(0, 0, 255);">drop</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">table</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">ladfadfad_Item1</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"><br><img src="/Images/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(0, 0, 255);">go</span><span style="color: rgb(0, 0, 0);"><br><img src="/Images/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(0, 0, 255);">CREATE</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">TABLE</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">dbo</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);">.</span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">ladfadfad_Item1</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);">(<br><img src="/Images/OutliningIndicators/None.gif" align="top"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">PID</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">int</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 128, 128);">NOT</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">NULL</span><span style="color: rgb(0, 0, 0);">,<br><img src="/Images/OutliningIndicators/None.gif" align="top"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">ItemID</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">bigint</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 128, 128);">NOT</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">NULL</span><span style="color: rgb(0, 0, 0);">,<br><img src="/Images/OutliningIndicators/None.gif" align="top"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">ParentItemID</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">bigint</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 128, 128);">NOT</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">NULL</span><span style="color: rgb(0, 0, 0);"><br><img src="/Images/OutliningIndicators/None.gif" align="top"> </span><span style="color: rgb(0, 0, 255);">CONSTRAINT</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">PK_ladfadfad_Item1</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">PRIMARY</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">KEY</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">CLUSTERED</span><span style="color: rgb(0, 0, 0);"> <br><img src="/Images/OutliningIndicators/None.gif" align="top">(<br><img src="/Images/OutliningIndicators/None.gif" align="top"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">PID</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">ASC</span><span style="color: rgb(0, 0, 0);">,<br><img src="/Images/OutliningIndicators/None.gif" align="top"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">ParentItemID</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">ASC</span><span style="color: rgb(0, 0, 0);">,<br><img src="/Images/OutliningIndicators/None.gif" align="top"> </span><span style="color: rgb(255, 0, 0);">[</span><span style="color: rgb(255, 0, 0);">ItemID</span><span style="color: rgb(255, 0, 0);">]</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(0, 0, 255);">ASC</span><span style="color: rgb(0, 0, 0);"><br><img src="/Images/OutliningIndicators/None.gif" align="top">)) <br><img src="/Images/OutliningIndicators/None.gif" align="top"><br><img src="/Images/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(0, 0, 255);">go</span><span style="color: rgb(0, 0, 0);"> <br><img src="/Images/OutliningIndicators/None.gif" align="top"><br><img src="/Images/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(0, 0, 255);">SET</span><span style="color: rgb(0, 0, 0);"> SHOWPLAN_TEXT </span><span style="color: rgb(0, 0, 255);">ON</span><span style="color: rgb(0, 0, 0);">;<br><img src="/Images/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(0, 0, 255);">GO</span><span style="color: rgb(0, 0, 0);"><br><img src="/Images/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(0, 0, 255);">SELECT</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(255, 0, 255);">COUNT</span><span style="color: rgb(0, 0, 0);">(</span><span style="color: rgb(128, 128, 128);">*</span><span style="color: rgb(0, 0, 0);">)<br><img src="/Images/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(0, 0, 255);">FROM</span><span style="color: rgb(0, 0, 0);"> dbo.ladfadfad_Item1 </span><span style="color: rgb(0, 0, 255);">as</span><span style="color: rgb(0, 0, 0);"> ii <br><img src="/Images/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(0, 0, 255);">INNER</span><span style="color: rgb(0, 0, 0);"> merge </span><span style="color: rgb(128, 128, 128);">JOIN</span><span style="color: rgb(0, 0, 0);"> dbo.ladfadfad_WeekAggregate </span><span style="color: rgb(0, 0, 255);">As</span><span style="color: rgb(0, 0, 0);"> ws<br><img src="/Images/OutliningIndicators/None.gif" align="top"> </span><span style="color: rgb(0, 0, 255);">ON</span><span style="color: rgb(0, 0, 0);"> ii.PID </span><span style="color: rgb(128, 128, 128);">=</span><span style="color: rgb(0, 0, 0);"> ws.PID <br><img src="/Images/OutliningIndicators/None.gif" align="top"> </span><span style="color: rgb(128, 128, 128);">and</span><span style="color: rgb(0, 0, 0);"> ii.ParentItemID </span><span style="color: rgb(128, 128, 128);">=</span><span style="color: rgb(0, 0, 0);"> ws.ParentItemID <br><img src="/Images/OutliningIndicators/None.gif" align="top"> </span><span style="color: rgb(128, 128, 128);">and</span><span style="color: rgb(0, 0, 0);"> ii.ItemID </span><span style="color: rgb(128, 128, 128);">=</span><span style="color: rgb(0, 0, 0);">ws.ItemID<br><img src="/Images/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(0, 0, 255);">where</span><span style="color: rgb(0, 0, 0);"> <br><img src="/Images/OutliningIndicators/None.gif" align="top">ws.PID </span><span style="color: rgb(128, 128, 128);">=</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 0, 0); font-weight: bold;">11111</span><span style="color: rgb(0, 0, 0);"><br><img src="/Images/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(128, 128, 128);">and</span><span style="color: rgb(0, 0, 0);"> ws.itemcategoryid </span><span style="color: rgb(128, 128, 128);">=</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 0, 0); font-weight: bold;">1</span><span style="color: rgb(0, 0, 0);"><br><img src="/Images/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(128, 128, 128);">AND</span><span style="color: rgb(0, 0, 0);"> ws.ParentItemID </span><span style="color: rgb(128, 128, 128);">=</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 0, 0); font-weight: bold;">2</span><span style="color: rgb(0, 0, 0);"><br><img src="/Images/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(128, 128, 128);">and</span><span style="color: rgb(0, 0, 0);"> ii.PID </span><span style="color: rgb(128, 128, 128);">=</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 0, 0); font-weight: bold;">11111</span><span style="color: rgb(0, 0, 0);"><br><img src="/Images/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(128, 128, 128);">AND</span><span style="color: rgb(0, 0, 0);"> ii.ParentItemID </span><span style="color: rgb(128, 128, 128);">=</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 0, 0); font-weight: bold;">2</span><span style="color: rgb(0, 0, 0);"><br><img src="/Images/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(128, 128, 128);">and</span><span style="color: rgb(0, 0, 0);"> ws.itemid </span><span style="color: rgb(128, 128, 128);">=</span><span style="color: rgb(128, 0, 0); font-weight: bold;">1</span><span style="color: rgb(0, 0, 0);"> </span><span style="color: rgb(128, 128, 128);">and</span><span style="color: rgb(0, 0, 0);"> ii.itemid </span><span style="color: rgb(128, 128, 128);">=</span><span style="color: rgb(128, 0, 0); font-weight: bold;">1</span><span style="color: rgb(0, 0, 0);"> <br><img src="/Images/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(0, 0, 255);">GO</span><span style="color: rgb(0, 0, 0);"><br><img src="/Images/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(0, 0, 255);">SET</span><span style="color: rgb(0, 0, 0);"> SHOWPLAN_TEXT </span><span style="color: rgb(0, 0, 255);">OFF</span><span style="color: rgb(0, 0, 0);">;<br><img src="/Images/OutliningIndicators/None.gif" align="top"></span><span style="color: rgb(0, 0, 255);">GO</span></div><br>
