表扫描如何返回比表中更多的行？
我对数据库进行了复杂的查询，其中包含错误的统计信息和碎片索引。让我感到困惑的是，当我检查实际的查询计划时，我从具有 23 K 行的表上的表扫描中获得了 54 M 行。在查询计划的更深处，该表与其自身连接（23 K 中仅包含 260 K 行）。这怎么可能？
运行一些其他查询或重建索引和统计数据可以使这种情况消失，我只是想了解为什么会发生这种情况。
我在恢复同一数据库时使用 SQL 2005 和 SQL 2008 R2 重现了这一点。
更新：是的，这是一个实际的计划。行数为 20039（不是上面提到的 23 K）。这是最右边的节点之一。
I have a complex query on a database with bad statistics and fragmented indices. What I'm puzzled by is that when I examine an actual query plan I get 54 M rows from a table scan on a table that has 23 K rows. Much further up the query plan this table is joined against itself (only 260 K rows out of 23 K). How is this possible?
Running some other queries or rebuilding indices and statistics makes this go away, I'm just trying to understand why this would happen.
I have reproduced this with SQL 2005 and SQL 2008 R2 on a restore of the same database.
Update: Yes, this is an actual plan. Number of rows is 20039 (not 23 K as mentioned above). This is one the the rightmost nodes.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
看起来执行计划中的这个节点是嵌套循环连接中涉及的“第二个”表，“第一个”表中有 2701 行（感谢 Martin！）。
由于 HistoricalPrice 表上似乎没有合适的索引，因此必须扫描堆以查找循环连接中的每一行，总共有 2701*20039 = 54,125,339 行。来自嵌套循环运算符的行数将是连接/匹配行的总数。
虽然执行计划仅显示作为一个节点访问的表，但循环联接最终会访问该表的次数与行数一样多。如果没有索引，则必须扫描整个表，每次都会向嵌套循环运算符返回 20,039 行。
如果在表上放置适当的索引来支持连接，则可能只会查找一行，从而将较少数量的行发送回嵌套循环。
It looks as though this node in the execution plan is the "second" table involved in a nested loop join, with 2701 rows in the "first" table (thanks Martin!).
As there appears to be no appropriate index on the HistoricalPrice table, the heap must be scanned for every row in the loop join, resulting in a total of 2701*20039 = 54,125,339 rows. The number of rows coming out of the Nested Loop operator will be the total number of joined/matched rows.
While the execution plan only shows the table being accessed as one node, the loop join would end up accessing that table as many times as there are rows. Without an index, the entire table must be scanned, which returns 20,039 rows back to the Nested Loop operator each time.
If an appropriate index was placed on the table to support the join, then perhaps only a single row would be seeked, and thus a smaller number of rows sent back to the Nested Loop.