久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

SQL Server 2016 奇怪的行為 - OR 條件給出了 0 行但是

SQL Server 2016 weird behavior - OR condition gives 0 rows But AND condition gives some rows(SQL Server 2016 奇怪的行為 - OR 條件給出了 0 行但是 AND 條件給出了一些行)
本文介紹了SQL Server 2016 奇怪的行為 - OR 條件給出了 0 行但是 AND 條件給出了一些行的處理方法,對(duì)大家解決問(wèn)題具有一定的參考價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)吧!

問(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)用 stratIdfundId 條件,輸出 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)!

【網(wǎng)站聲明】本站部分內(nèi)容來(lái)源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問(wèn)題,如果有圖片或者內(nèi)容侵犯了您的權(quán)益,請(qǐng)聯(lián)系我們刪除處理,感謝您的支持!

相關(guān)文檔推薦

Converting Every Child Tags in to a Single Column with multiple Delimiters -SQL Server (3)(將每個(gè)子標(biāo)記轉(zhuǎn)換為具有多個(gè)分隔符的單列-SQL Server (3))
How can I create a view from more than one table?(如何從多個(gè)表創(chuàng)建視圖?)
Create calculated value based on calculated value inside previous row(根據(jù)前一行內(nèi)的計(jì)算值創(chuàng)建計(jì)算值)
How do I stack the first two columns of a table into a single column, but also pair third column with the first column only?(如何將表格的前兩列堆疊成一列,但也僅將第三列與第一列配對(duì)?) - IT屋-程序員軟件開(kāi)發(fā)技
Recursive t-sql query(遞歸 t-sql 查詢(xún))
Convert Month Name to Date / Month Number (Combinations of Questions amp; Answers)(將月份名稱(chēng)轉(zhuǎn)換為日期/月份編號(hào)(問(wèn)題和答案的組合))
主站蜘蛛池模板: 五月天婷婷综合 | 一区二区免费 | 一区二区中文字幕 | 欧美99 | 国产精品毛片无码 | 成人福利片 | 国产精品美女久久久久久免费 | 欧美一级片 | 91av视频| 噜噜噜噜狠狠狠7777视频 | 精品国产黄a∨片高清在线 www.一级片 国产欧美日韩综合精品一区二区 | 国产免费国产 | 国产精品3区 | 成人精品网 | 国产精品免费一区二区三区 | 视频第一区 | 午夜精品久久久久久久久久久久 | 久久综合久色欧美综合狠狠 | 免费一区 | 欧美精品在线一区二区三区 | 国产一区二区久久 | 亚洲午夜视频 | 日本二区在线观看 | 日韩欧美福利视频 | 天天操夜夜拍 | 91精品久久久久久久久 | 亚洲欧美日韩精品久久亚洲区 | 欧美精品一区二区在线观看 | 无码日韩精品一区二区免费 | 亚洲一区二区在线 | 欧美一区二区三区在线观看 | 色频| 久久免费观看视频 | 欧美一级一 | 国内精品视频在线观看 | 日韩欧美视频 | 色眯眯视频在线观看 | 青青草视频网 | 91大神xh98xh系列全部 | 欧美精品三区 | 91大神在线资源观看无广告 |