我问这个问题是为了更好地理解优化器的行为并理解索引线轴的限制.假设我将1到10000的整数放入堆中：
CREATE TABLE X_10000 (ID INT NOT NULL); truncate table X_10000; INSERT INTO X_10000 WITH (TABLOCK) SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM master..spt_values t1 CROSS JOIN master..spt_values t2;
并使用MAXDOP 1强制嵌套循环连接：
SELECT * FROM X_10000 a INNER JOIN X_10000 b ON a.ID = b.ID OPTION (LOOP JOIN,MAXDOP 1);
这是对sql Server采取的一种相当不友好的行为.当两个表都没有任何相关索引时,嵌套循环连接通常不是一个好的选择.这是计划：
我的机器上的查询需要13秒,从表盘中获取100000000行.但是,我不明白为什么查询必须很慢.查询优化器能够通过index spools动态创建索引.此查询似乎是索引假脱机的完美候选.
以下查询返回与第一个查询相同的结果,具有索引假脱机,并在不到一秒的时间内完成：
SELECT * FROM X_10000 a CROSS APPLY (SELECT TOP (9223372036854775807) b.ID FROM X_10000 b WHERE a.ID = b.ID) ca OPTION (LOOP JOIN,MAXDOP 1);
此查询还有一个索引假脱机,并在不到一秒的时间内完成：
SELECT * FROM X_10000 a INNER JOIN X_10000 b ON a.ID >= b.ID AND a.ID <= b.ID OPTION (LOOP JOIN,MAXDOP 1);
为什么原始查询没有索引假脱机？是否有任何一组记录或未记录的提示或跟踪标志会给它一个索引假脱机？我确实找到了this related question,但它没有完全回答我的问题,我无法得到神秘的跟踪标志来为这个查询工作.
解决方法
如您所知,优化器的搜索并非详尽无遗.它尝试在上下文中有意义的事情,并且经常在真实查询上分红.强制两个单列无索引堆表之间的循环连接不是这种情况.也就是说,这里有一些细节：
sql Server喜欢转换应用于早期的连接,因为它知道更多关于连接的技巧.稍后,它可能会探索将联接转换回申请.两者之间的差异是相关参数(外部参考).当内侧有合适的索引时,适用有意义.您的示例没有索引,因此不会说服优化程序探索应用程序的转换.
简单(非应用)连接在连接运算符上具有连接谓词而不是外部引用.非应用的假脱机优化通常是一个惰性表假脱机,因为内侧没有谓词,只有在连接时.
优化器不会考虑动态构建索引以启用应用程序;相反,事件的顺序通常是相反的：转换为适用,因为存在一个好的索引.
您有时可以通过在查询中使用APPLY语法来鼓励应用而不是连接.未记录的跟踪标志9114可以通过阻止优化器将逻辑应用转换为前端联接来辅助这一点.例如：
SELECT * FROM dbo.X_1000 AS a CROSS APPLY (SELECT * FROM dbo.X_1000 AS b WHERE b.ID = a.ID) AS b OPTION (QUERYTRACEON 9114);
索引假脱机最适合应用,因为外部引用意味着选择应用于连接的内侧.您经常会通过SelToIndexOnTheFly看到这个,但存在其他路径.
