久久久久久久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)題和答案的組合))
主站蜘蛛池模板: 91视频91| 97国产成人 | 97人澡人人添人人爽欧美 | 欧美久久天堂 | 成人在线黄色 | 天天色天天 | 国内精品免费久久久久软件老师 | 亚洲精选一区二区 | 久久久久久久久久久久91 | 国产露脸对白88av | 成人在线精品视频 | 欧美激情久久久 | 日韩欧美三级电影 | 中文字幕1区 | 久久久www成人免费无遮挡大片 | 夜夜艹 | 日本亚洲精品 | 欧美精品久久 | 一级a爱片久久毛片 | 亚洲人成人一区二区在线观看 | re久久| avhd101在线成人播放 | 国产电影一区二区在线观看 | 日韩一级免费观看 | 中文字幕一区二区三区在线观看 | 国产精品成人品 | 免费看片国产 | 欧美一级淫片免费视频黄 | 日韩欧美二区 | 亚洲免费婷婷 | 久久福利网站 | 欧美群妇大交群中文字幕 | h视频在线观看免费 | 国产精品久久久久久久久免费软件 | 特一级毛片 | 国产欧美一区二区三区在线看 | 婷婷五月色综合香五月 | 91超碰在线观看 | 国产精品久久久久久模特 | 成人午夜在线 | 国产精品视频免费播放 |