inner merge join 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.

select -- t20400.name as [$<性别>],会员人数 as [$<会员人数>],销售额 as [$<销售额>]
会员人数
from
(
    select Gender ,sum(Members) as 会员人数
    from
    (
        select c.Gender,count(distinct c.customerid ) as Members
        from DimCustomer c       
        where 1=1
            and c.Gender='2'
        group by Gender
    ) b
    group by Gender
) a inner merge join (select -1 id,0 pid,'All' name,'9' code union all select 0 id, -1 pid, 'Female' name, '2' code union all select 1 id, -1 pid, 'Male' name, '1' code union all select 2 id, -1 pid, 'Unknown' name, '0' code) t20400 on a.gender=t20400.code

 

原因：

首先是一个人发现他的脚本使用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上不会提示错误

  1. USE TEMPDB
  2. GO
  3. IF OBJECT_ID('TA') IS NOT NULL DROP TABLE TA
  4. IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
  5. IF OBJECT_ID('P1') IS NOT NULL DROP PROCEDURE P1
  6. GO
  7. CREATE TABLE TA(ID INT,VAL VARCHAR(50))
  8. CREATE TABLE TB(ID INT,AID INT,VAL VARCHAR(50))
  9. GO
  10. SET NOCOUNT ON
  11. GO
  12. INSERT INTO TA
  13. SELECT 1,'A' UNION ALL
  14. SELECT 2,'B' UNION ALL
  15. SELECT 3,'C' UNION ALL
  16. SELECT 4,'D' UNION ALL
  17. SELECT 5,'E'
  18. GO
  19. INSERT INTO TB
  20. SELECT 1,1,'AA' UNION ALL
  21. SELECT 2,1,'AB' UNION ALL
  22. SELECT 1,2,'BA' UNION ALL
  23. SELECT 2,2,'BB' UNION ALL
  24. SELECT 2,3,'BC'
  25. GO
  26. GO
  27. PRINT '-----------------------'
  28. PRINT 'TA.ID>=1 AND TA.ID<2可以'
  29. GO
  30. SELECT *
  31. FROM TA
  32. INNER HASH JOIN TB ON TA.ID=TB.AID
  33. WHERE TA.ID>=1 AND TA.ID<2
  34. GO
  35. PRINT '-----------------------'
  36. PRINT '加=1就不行'
  37. GO
  38. SELECT *
  39. FROM TA
  40. INNER HASH JOIN TB ON TA.ID=TB.AID
  41. WHERE TA.ID>=1 AND TA.ID<=1
  42. GO
  43. PRINT '-----------------------'
  44. PRINT '同样报错的语句,用存储过程就可以'
  45. GO
  46. CREATE PROCEDURE P1(@MIN INT,@MAX INT)
  47. AS
  48. SELECT *
  49. FROM TA T1
  50. INNER HASH JOIN TB T2 ON T1.ID=T2.AID
  51. WHERE T1.ID >=@MIN AND T2.ID<=@MAX
  52. GO
  53. EXEC P1 1,1
  54. /*
  55. -----------------------
  56. TA.ID>=1 AND TA.ID<2可以
  57. 警告: 由于使用了本地联接提示，联接次序得以强制实施。
  58. -----------------------
  59. 加=1就不行
  60. 消息 8622，级别 16，状态 1，第 1 行
  61. 由于此查询中定义了提示，查询处理器未能生成查询计划。请重新提交查询，并且不要在查询中指定任何提示，也不要使用 SET FORCEPLAN。
  62. -----------------------
  63. 同样报错的语句,用存储过程就可以
  64. 警告: 由于使用了本地联接提示，联接次序得以强制实施。
  65. */


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

  1. 首先根据MSDN ,Hash Join必须要满足一个特性
  2. “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”
  3. http://msdn.microsoft.com/en-us/library/aa178403(v=sql.80).aspx
  4.  
  5. 下面来逐个解释
  6. 1 ，'加=1就不行'
  7. SELECT *FROM TA INNER HASH JOIN TB ON TA.ID=TB.AIDWHERE TA.ID =1
  8. 我们把先 HASH JOIN 改为nested Loop,
  9. SET statistics profile on
  10. go
  11. SELECT *FROM TA INNER HASH JOIN TB ON TA.ID=TB.AIDWHERE TA.ID =1
  12.  
  13. 生成如下的计划
  14. |--Nested Loops(Inner Join)
  15. |--Table Scan(OBJECT:([tempdb].[dbo].[TA]), WHERE:([tempdb].[dbo].[TA].[ID]=(1)))
  16. |--Table Scan(OBJECT:([tempdb].[dbo].[TB]), WHERE:([tempdb].[dbo].[TB].[AID]=(1)))
  17.  
  18. 上面会发现什么？
  19. Table Scan(OBJECT:([tempdb].[dbo].[TB]) 多了一个[TB].[AID]=1 ，而在原T-SQL中是没有的，这是优化器优化的结果，排除更多的行嘛，提高性能，
  20. 而Nested Loops(Inner Join)却没有任何谓词，这是容易理解的，既然Table Scan的时候 [TA].[ID] ，[TB].[AID] 都等于1，
  21. 那就没有必要在Nested Loops(Inner Join)再加上谓词了，不幸的是hash join是至少需要一个谓词的。
  22. 所以你会发现无论是 TA.ID =1 还是2，3，4，5.或者 把TA.ID =1 改为TA.ID =100 and [TB].[AID]=100 ,都会发生这样的错误。
  23.  
  24. 2， SELECT *FROM TA INNER HASH JOIN TB ON TA.ID=TB.AID WHERE TA.ID >0 AND TA.ID<2
  25.  
  26. |--Hash Match(Inner Join, HASH:([tempdb].[dbo].[TA].[ID])=([tempdb].[dbo].[TB].[AID]), RESIDUAL:([tempdb].[dbo].[TB].[AID]=[tempdb].[dbo].[TA].[ID]))
  27. |--Table Scan(OBJECT:([tempdb].[dbo].[TA]), WHERE:([tempdb].[dbo].[TA].[ID]>(0) AND [tempdb].[dbo].[TA].[ID]<(2)))
  28. |--Table Scan(OBJECT:([tempdb].[dbo].[TB]), WHERE:([tempdb].[dbo].[TB].[AID]>(0) AND [tempdb].[dbo].[TB].[AID]<(2)))
  29.  
  30. 很明显，这个时候跟上面的情况不同，Hash Match(Inner Join的谓词是绝对不能省略的，这就给hash join创造了条件。
  31.  
  32.  
  33. 3，SQL SERVER 2012 为什么没有错，看一下执行计划
  34. SELECT *FROM TA INNER HASH JOIN TB ON TA.ID=TB.AIDWHERE TA.ID =1
  35.  
  36. |--Hash Match(Inner Join, HASH:([tempdb].[dbo].[TA].[ID])=([tempdb].[dbo].[TB].[AID]), RESIDUAL:([tempdb].[dbo].[TA].[ID] = [tempdb].[dbo].[TB].[AID]))
  37. |--Table Scan(OBJECT:([tempdb].[dbo].[TA]), WHERE:([tempdb].[dbo].[TA].[ID]=(1)))
  38. |--Table Scan(OBJECT:([tempdb].[dbo].[TB]), WHERE:([tempdb].[dbo].[TB].[AID]=(1)))
  39. 虽然在Table Scan的时候 [TA].[ID] ，[TB].[AID] 都等于1的情况下，没有必要再在Hash Match(Inner Join操作符中添加谓词，
  40. 但是SQL SERVER为了FIX这个BUG，还是多此一举的加上去了，这就为HASH JOIN创造了条件，这也就是为什么SQL SERVER 2012没有报错的原因。

 

以上最重要的 部分就是

  1. 首先根据MSDN ,Hash Join必须要满足一个特性
  2. “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”
  3. http://msdn.microsoft.com/en-us/library/aa178403(v=sql.80).aspx

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

VAL =‘A’

大家会觉得不是已有ID=1了吗？这个是因为使用inner join时，SQL引擎优化了执行计划。因为join的连接TA.id=tb.aid已存在，这时就会省略在tb.aid=1部分，

  |--Nested Loops(Inner Join)

就造成了不满足hash join 所必须的equality  clause了。

而若是使用WHERE TA.ID=1 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]))

简单来说这个问题就是因为SQL引擎优化，导致的强制使用的hint,hash match条件不满足造成的异常。

所以这个错误又一次告诉我们，hint并不应该使用在生产环境中。今天的高效明天可能就是性能隐患或者像这样严重的错误，我们使用hint应该只是判断最优的执行计划是怎么样的。应该通过调整SQL 引导SQL 引擎走向最佳执行计划，而不是使用hint.

posted @ 2020-04-26 13:39  KJXY  阅读(548)  评论(0)    收藏  举报