hash join提示错误

最新推荐文章于 2023-10-18 12:14:48 发布

转载最新推荐文章于 2023-10-18 12:14:48 发布 · 3.3k 阅读

SQL技术专栏收录该内容

37 篇文章

订阅专栏

今天在论坛看到大家讨论的一个问题，觉得比较有用，就记下来免得忘记了

首先是一个人发现他的脚本使用inner hash join的时候提示错误

Msg 8622, Level 16, State 1, Line 1
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.

然后一个大牛重现了这个错误，并且发现2012上不会提示错误

USE TEMPDB
GO
IF OBJECT_ID('TA') IS NOT NULL DROP TABLE TA
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
IF OBJECT_ID('P1') IS NOT NULL DROP PROCEDURE P1
GO
CREATE TABLE TA(ID INT,VAL VARCHAR(50))
CREATE TABLE TB(ID INT,AID INT,VAL VARCHAR(50))
GO
SET NOCOUNT ON
GO
INSERT INTO TA
SELECT 1,'A' UNION ALL
SELECT 2,'B' UNION ALL
SELECT 3,'C' UNION ALL
SELECT 4,'D' UNION ALL
SELECT 5,'E' 
GO
INSERT INTO TB
SELECT 1,1,'AA' UNION ALL
SELECT 2,1,'AB' UNION ALL
SELECT 1,2,'BA' UNION ALL
SELECT 2,2,'BB' UNION ALL
SELECT 2,3,'BC'
GO
GO
PRINT '-----------------------'
PRINT 'TA.ID>=1 AND TA.ID<2可以'
GO
SELECT *
FROM TA
INNER HASH JOIN TB ON TA.ID=TB.AID
WHERE TA.ID>=1 AND TA.ID<2
GO
PRINT '-----------------------'
PRINT '加=1就不行'
GO
SELECT *
FROM TA
INNER HASH JOIN TB ON TA.ID=TB.AID
WHERE TA.ID>=1 AND TA.ID<=1
GO
PRINT '-----------------------'
PRINT '同样报错的语句,用存储过程就可以'
GO
CREATE PROCEDURE P1(@MIN INT,@MAX INT)
AS
SELECT *
FROM TA T1
INNER HASH JOIN TB T2 ON T1.ID=T2.AID
WHERE T1.ID >=@MIN AND T2.ID<=@MAX
GO
EXEC P1 1,1
/*
-----------------------
TA.ID>=1 AND TA.ID<2可以
警告: 由于使用了本地联接提示，联接次序得以强制实施。
-----------------------
加=1就不行
消息 8622，级别 16，状态 1，第 1 行
由于此查询中定义了提示，查询处理器未能生成查询计划。请重新提交查询，并且不要在查询中指定任何提示，也不要使用 SET FORCEPLAN。
-----------------------
同样报错的语句,用存储过程就可以
警告: 由于使用了本地联接提示，联接次序得以强制实施。
*/

而下面是另外一个大牛的原因分析。

首先根据MSDN ,Hash Join必须要满足一个特性
“Similar to a merge join, a hash join can be used only if there is at least one equality (WHERE) clause in the join predicate”
http://msdn.microsoft.com/en-us/library/aa178403(v=sql.80).aspx

下面来逐个解释
1 ，'加=1就不行'
SELECT *FROM TA INNER HASH JOIN TB ON TA.ID=TB.AIDWHERE TA.ID =1 
我们把先   HASH JOIN 改为nested Loop,
SET statistics profile on
go
SELECT *FROM TA INNER HASH JOIN TB ON TA.ID=TB.AIDWHERE TA.ID =1 

生成如下的计划
  |--Nested Loops(Inner Join)
       |--Table Scan(OBJECT:([tempdb].[dbo].[TA]), WHERE:([tempdb].[dbo].[TA].[ID]=(1)))
       |--Table Scan(OBJECT:([tempdb].[dbo].[TB]), WHERE:([tempdb].[dbo].[TB].[AID]=(1)))

上面会发现什么？ 
Table Scan(OBJECT:([tempdb].[dbo].[TB]) 多了一个[TB].[AID]=1 ，而在原T-SQL中是没有的，这是优化器优化的结果，排除更多的行嘛，提高性能，
而Nested Loops(Inner Join)却没有任何谓词，这是容易理解的，既然Table Scan的时候 [TA].[ID] ，[TB].[AID] 都等于1，
那就没有必要在Nested Loops(Inner Join)再加上谓词了，不幸的是hash join是至少需要一个谓词的。
所以你会发现无论是 TA.ID =1  还是2，3，4，5.或者 把TA.ID =1 改为TA.ID =100 and [TB].[AID]=100 ,都会发生这样的错误。

2， SELECT *FROM TA INNER HASH JOIN TB ON TA.ID=TB.AID WHERE TA.ID >0 AND TA.ID<2

  |--Hash Match(Inner Join, HASH:([tempdb].[dbo].[TA].[ID])=([tempdb].[dbo].[TB].[AID]), RESIDUAL:([tempdb].[dbo].[TB].[AID]=[tempdb].[dbo].[TA].[ID]))
       |--Table Scan(OBJECT:([tempdb].[dbo].[TA]), WHERE:([tempdb].[dbo].[TA].[ID]>(0) AND [tempdb].[dbo].[TA].[ID]<(2)))
       |--Table Scan(OBJECT:([tempdb].[dbo].[TB]), WHERE:([tempdb].[dbo].[TB].[AID]>(0) AND [tempdb].[dbo].[TB].[AID]<(2)))

很明显，这个时候跟上面的情况不同，Hash Match(Inner Join的谓词是绝对不能省略的，这就给hash join创造了条件。


3，SQL SERVER 2012 为什么没有错，看一下执行计划
SELECT *FROM TA INNER HASH JOIN TB ON TA.ID=TB.AIDWHERE TA.ID =1 

  |--Hash Match(Inner Join, HASH:([tempdb].[dbo].[TA].[ID])=([tempdb].[dbo].[TB].[AID]), RESIDUAL:([tempdb].[dbo].[TA].[ID] = [tempdb].[dbo].[TB].[AID]))
       |--Table Scan(OBJECT:([tempdb].[dbo].[TA]), WHERE:([tempdb].[dbo].[TA].[ID]=(1)))
       |--Table Scan(OBJECT:([tempdb].[dbo].[TB]), WHERE:([tempdb].[dbo].[TB].[AID]=(1)))
虽然在Table Scan的时候 [TA].[ID] ，[TB].[AID] 都等于1的情况下，没有必要再在Hash Match(Inner Join操作符中添加谓词，
但是SQL SERVER为了FIX这个BUG，还是多此一举的加上去了，这就为HASH JOIN创造了条件，这也就是为什么SQL SERVER 2012没有报错的原因。

以上最重要的部分就是

首先根据MSDN ,Hash Join必须要满足一个特性
“Similar to a merge join, a hash join can be used only if there is at least one equality (WHERE) clause in the join predicate”
http://msdn.microsoft.com/en-us/library/aa178403(v=sql.80).aspx

这个意思是什么呢？是指 where中或on中hash join需要至少 equality clause比如