SQL Server 2016 奇怪的行为 - OR 条件给出了 0 行但是 AND 条件给出了一些行

2021-09-10 00:00:00 tsql sql-server

我有以下 SQL 查询:

I have the following SQL query:

SELECT T.tnum,
       T.secId,           
FROM   TradeCore T
       INNER JOIN Sec S
               ON S.secId = T.secId
       INNER JOIN TradeTransfer TT
               ON t.tnum = TT.tnum
WHERE  ( T.td >= '2019-01-01' )
       AND ( T.td <= '2019-02-25' )
       AND ( T.fundId = 3 OR TT.fundId = 3 )
       AND ( T.stratId = 7 OR TT.stratId = 7 ) --Line 1
    -- AND ( T.stratId = 7 AND TT.stratId = 7 ) --Line 2

当我保留最后一行注释时，我得到 0 个结果，但是当我取消注释它并注释它之前的行时，我得到一些结果.

When I keep last line commented I get 0 results, But when I un-comment it and comment the line before it, I get some result.

这怎么可能?

推荐答案

Any row meeting (T.stratId = 7 AND TT.stratId = 7) 一定要满足 (T.stratId= 7 OR TT.stratId = 7) 所以限制较少的谓词返回较少的结果在逻辑上是不可能的.

Any row meeting (T.stratId = 7 AND TT.stratId = 7) must certainly meet (T.stratId = 7 OR TT.stratId = 7) so it is not logically possible that the less restrictive predicate returns less results.

问题是非聚集索引损坏.

The issue is a corrupt non clustered index.

和案例

  • TradeCore 中符合日期条件且stratId = 7 的154 行被发出.
  • 加入 TradeTransfer 并应用 stratId 和 fundId 条件，输出 68 行(估计 34 行)
  • 这些都成功地连接到 Sec 中的一行(使用索引 IX_Sec_secId_sectype_Ccy_valpoint)并返回 68 行作为最终结果.
  • 154 rows in TradeCore matching the date condition and stratId = 7 are emitted.
  • Join on TradeTransfer with the stratId and fundId conditions applied ouputs 68 rows (estimated 34 rows)
  • These all successfully join onto a row in Sec (using index IX_Sec_secId_sectype_Ccy_valpoint) and 68 rows are returned as the final result.

或大小写

  • TradeCore 中符合日期条件的 1173 行被发出
  • 在 3 in (T.fundId, TT.fundId) AND 7 in (T.stratId, TT.stratId) 上加入 TradeTransfer 的残差谓词带来了这个减少到 73(估计 297 行)
  • 然后所有行都被 Sec 上的连接消除 - 尽管我们从上面知道至少有 68 行匹配.
  • 1173 rows in TradeCore matching the date condition are emitted
  • Join on TradeTransfer with a residual predicate on 3 in (T.fundId, TT.fundId) AND 7 in (T.stratId, TT.stratId) brings this down to 73 (estimated 297 rows)
  • Then all rows are eliminated by the join on Sec - despite the fact that we know from above that at least 68 of them have a match.

Sec 的表基数为 2399 行.在连接删除所有行的计划中，SQL Server 对 IX_Sec_idu 进行全面扫描，作为散列连接探测端的输入，但对该索引的全面扫描仅返回 589 行.

The table cardinality of Sec is 2399 rows. In the plan where all rows are removed by the join SQL Server does a full scan on IX_Sec_idu as input to the probe side of the hash join but the full scan on that index only returns 589 rows.

出现在另一个执行计划中的行是从包含这 1,810 个缺失行的不同索引中提取的.

The rows that appear in the other execution plan are pulled from a different index that contains these 1,810 missing rows.

您已在评论中确认以下返回不同的结果

You have confirmed in the comments that the following return differing results

select count(*) from Sec with(index = IX_Sec_idul); --589 
select count(*) from Sec with(index = IX_Sec_secId_sectype_Ccy_valpoint); --2399
select count(*) from Sec with(index = PK_Sec)  --2399

同一表上不同索引的行数不匹配的情况绝不应该出现(除非索引被过滤并且在此处不适用).

This should never be the case that rowcounts from different indexes on the same table don't match (except if an index is filtered and that does not apply here).

因为在 AND 情况下进入 Sec 连接的行估计只有 34，所以它选择了一个带有嵌套循环的计划，因此需要一个带有前导列的索引secId 执行搜索.对于 OR 情况，它估计 297 行，而不是估计 297 行，它选择散列连接，因此选择包含 secId 列的最小可用索引.

Because the row estimates going in to the join on Sec in the AND case are only 34 it chooses a plan with nested loops and therfore needs an index with leading column secId to perform a seek. For the OR case it estimates 297 rows and instead of doing an estimated 297 seeks it chooses a hash join instead so selects the smallest index available containing the secId column.

由于聚集索引中存在所有行，您可以删除 IX_Sec_idul 并重新创建它以希望解决此问题(先备份).

As all rows exist in the clustered index you can drop IX_Sec_idul and create it again to hopefully resolve this issue (take a backup first).

您还应该运行 dbcc checkdb 以查看是否存在任何其他问题.

You should also run dbcc checkdb to see if any other issues are lurking.

相关文章