SQL Server 2008：执行计划包含错误数据？
考虑以下情况：
- 有一个包含数据 @XmlData 的 xml
- 将 @XmlData 提取到关系表 (@ItemList)
- 定义表变量 (@Table)
- 从 2 个 db 表（tblCdbA0 和 tblCdbG2）提取数据到 @XmlData使用exists 子句的表
下面是一个代码示例：
--1. There is an xml that contains data @XmlData
declare @XmlData xml = '
<Root>
<Item rid="522822E251CA11D18F1400A02427D15E" />
</Root>'
--2. The @XmlData is extracted into a relational table (@ItemList)
declare @ItemList table (ItemRid char(32) primary key);
insert into @ItemList(ItemRid)
select Tab.rid
from ( select Item.Rid.value('@rid','char(32)') rid
from @XmlData.nodes('(/Root)[1]/Item') Item(Rid)) Tab
group by Tab.rid
--3. A table variable is defined (@table)
declare @Table TABLE
(
Rid char(32) primary key
,Rid1 char(32)
,Rid2 char(32)
)
--4. The data extracted from 2 db tables (tblCdbA0 and tblCdbG2) into @Table using exists clause
insert into @Table(Rid,Rid1,Rid2)
select A0.A0RID, A0.T4RID, A0.T6RID
from tblCdbA0 A0 with (nolock)
where
exists (select null
from tblCdbG2 G2 with(nolock)
inner join @ItemList Items
on Items.ItemRid = G2.G0RID
where A0.A0RID=G2.A0RID)
表 tblCdbG2 包含 63582 行。 让我们看看最后一条语句的实际执行计划向我们展示了什么：
执行计划显示了该语句的数据行数从 tblCdbG2 表中提取的行数等于 807 行，而不是 63582。
在我看来，从 tblCdbG2 中提取的行数必须是 63582。应用 @ItemList 内部联接后，行数必须为 807。
在内连接之前显示已过滤的行数的原因是什么？
更新：
我稍微修改了现有查询，计划显示相同的值807。
查询：
select G2.A0RID
from tblCdbG2 G2 with(nolock)
inner join @ItemList Items
on Items.ItemRid = G2.G0RID
计划：
Consider the following situation:
- There is an xml that contains data @XmlData
- The @XmlData is extracted into a relational table (@ItemList)
- A table variable is defined (@Table)
- The data extracted from 2 db tables (tblCdbA0 and tblCdbG2) into @Table using exists clause
Here is a code sample:
--1. There is an xml that contains data @XmlData
declare @XmlData xml = '
<Root>
<Item rid="522822E251CA11D18F1400A02427D15E" />
</Root>'
--2. The @XmlData is extracted into a relational table (@ItemList)
declare @ItemList table (ItemRid char(32) primary key);
insert into @ItemList(ItemRid)
select Tab.rid
from ( select Item.Rid.value('@rid','char(32)') rid
from @XmlData.nodes('(/Root)[1]/Item') Item(Rid)) Tab
group by Tab.rid
--3. A table variable is defined (@table)
declare @Table TABLE
(
Rid char(32) primary key
,Rid1 char(32)
,Rid2 char(32)
)
--4. The data extracted from 2 db tables (tblCdbA0 and tblCdbG2) into @Table using exists clause
insert into @Table(Rid,Rid1,Rid2)
select A0.A0RID, A0.T4RID, A0.T6RID
from tblCdbA0 A0 with (nolock)
where
exists (select null
from tblCdbG2 G2 with(nolock)
inner join @ItemList Items
on Items.ItemRid = G2.G0RID
where A0.A0RID=G2.A0RID)
The table tblCdbG2 contains 63582 rows.
Let's look what actual execution plan of the last statement shows us:
The execution plan shows that number of data rows that are extracted from tblCdbG2 table equal to 807 rows instead of 63582.
In my view, the number of rows that are extracted from tblCdbG2 must be 63582. After inner join with @ItemList is applied the number of rows must be 807.
What is the reason to show already filtered rows number before inner join?
Update:
I have slightly modified the existing query and the plan shows the same value 807.
The query:
select G2.A0RID
from tblCdbG2 G2 with(nolock)
inner join @ItemList Items
on Items.ItemRid = G2.G0RID
The plan:
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
左侧的嵌套循环正在从@ItemList 中提取每一行。对于 @Item 列表中的每一行，它使用索引（聚集索引查找）来查找 tblCdbG2 中的匹配行。它不会首先提取所有 60+K 行。
The nested loop to left is pulling every row from @ItemList. For each row from @Item list it is using an index (Clustered Index Seek) to find just the matching row(s) in tblCdbG2. It does not first extract all 60+K rows.