問(wèn)題描述

我有以下 SQL 查詢(xún):

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

當(dāng)我保留最后一行注釋時(shí)，我得到 0 個(gè)結(jié)果，但是當(dāng)我取消注釋它并注釋它之前的行時(shí)，我得到一些結(jié)果.

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) 一定要滿(mǎn)足 (T.stratId= 7 OR TT.stratId = 7) 所以限制較少的謂詞返回較少的結(jié)果在邏輯上是不可能的.

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.

問(wèn)題是非聚集索引損壞.

The issue is a corrupt non clustered index.

和案例

• TradeCore 中符合日期條件且stratId = 7 的154 行被發(fā)出.
• 加入 TradeTransfer 并應(yīng)用 stratId 和 fundId 條件，輸出 68 行(估計(jì) 34 行)
• 這些都成功地連接到 Sec 中的一行(使用索引 IX_Sec_secId_sectype_Ccy_valpoint)并返回 68 行作為最終結(jié)果.

• 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.

或大小寫(xiě)

• TradeCore 中符合日期條件的 1173 行被發(fā)出
• 在 3 in (T.fundId, TT.fundId) AND 7 in (T.stratId, TT.stratId) 上加入 TradeTransfer 的殘差謂詞帶來(lái)了這個(gè)減少到 73(估計(jì) 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 的表基數(shù)為 2399 行.在連接刪除所有行的計(jì)劃中，SQL Server 對(duì) IX_Sec_idu 進(jìn)行全面掃描，作為散列連接探測(cè)端的輸入，但對(duì)該索引的全面掃描僅返回 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.

出現(xiàn)在另一個(gè)執(zhí)行計(jì)劃中的行是從包含這 1,810 個(gè)缺失行的不同索引中提取的.

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

您已在評(píng)論中確認(rèn)以下返回不同的結(jié)果

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

同一表上不同索引的行數(shù)不匹配的情況絕不應(yīng)該出現(xiàn)(除非索引被過(guò)濾并且在此處不適用).

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).

因?yàn)樵?AND 情況下進(jìn)入 Sec 連接的行估計(jì)只有 34，所以它選擇了一個(gè)帶有嵌套循環(huán)的計(jì)劃，因此需要一個(gè)帶有前導(dǎo)列的索引secId 執(zhí)行搜索.對(duì)于 OR 情況，它估計(jì) 297 行，而不是估計(jì) 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 并重新創(chuàng)建它以希望解決此問(wèn)題(先備份).

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).

您還應(yīng)該運(yùn)行 dbcc checkdb 以查看是否存在任何其他問(wèn)題.

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

這篇關(guān)于SQL Server 2016 奇怪的行為 - OR 條件給出了 0 行但是 AND 條件給出了一些行的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！