問題描述
我有以下 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
TradeCorematching the date condition and stratId = 7 are emitted. - Join on
TradeTransferwith thestratIdandfundIdconditions 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
TradeCorematching the date condition are emitted - Join on
TradeTransferwith a residual predicate on3 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.
這篇關于SQL Server 2016 奇怪的行為 - OR 條件給出了 0 行但是 AND 條件給出了一些行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！