主页 / dba / 问题 / 84090
Accepted
Daniel Hutmacher
Asked: 2014-12-03 04:52:58 +0800 CST

没有 PARTITION BY 的 ROW_NUMBER() 仍然会生成 Segment 迭代器

  • 772

我正在写一篇关于排名和聚合窗口函数的博客文章，特别是 Segment 和 Sequence Project 迭代器。我理解它的方式是 Segment 标识流中构成组的结束/开始的行，因此以下查询：

SELECT ROW_NUMBER() OVER (PARTITION BY someGroup ORDER BY someOrder)

将使用 Segment 来判断一行何时属于不同于前一行的不同组。然后，Sequence Project 迭代器根据 Segment 迭代器的输出进行实际的行号计算。

但是使用该逻辑的以下查询不必包含 Segment，因为没有分区表达式。

SELECT ROW_NUMBER() OVER (ORDER BY someGroup, someOrder)

但是，当我尝试这个假设时，这两个查询都使用了 Segment 运算符。唯一不同的是，第二个查询不需要GroupBy对 Segment 进行 a。这不是首先消除了对 Segment 的需求吗？

例子

CREATE TABLE dbo.someTable (
    someGroup   int NOT NULL,
    someOrder   int NOT NULL,
    someValue   numeric(8, 2) NOT NULL,
    PRIMARY KEY CLUSTERED (someGroup, someOrder)
);

--- Query 1:
SELECT ROW_NUMBER() OVER (PARTITION BY someGroup ORDER BY someOrder)
FROM dbo.someTable;

--- Query 2:
SELECT ROW_NUMBER() OVER (ORDER BY someGroup, someOrder)
FROM dbo.someTable;
sql-server execution-plan

2 个回答

  1. Best Answer

    我发现这篇 6 年前的博文提到了同样的行为。

    看起来ROW_NUMBER()总是包含一个段运算符，无论是否PARTITION BY使用。如果我不得不猜测，我会说这是因为它使在引擎上创建查询计划变得更容易。

    如果在大多数情况下需要该段，并且在不需要它的情况下，它本质上是一个零成本的非操作，那么在使用窗口函数时总是将它包含在计划中要简单得多。

    • 12

  2. 根据执行计划的showplan.xsdGroupBy ，出现不带minOccursormaxOccurs属性，因此默认为 [1..1] 使得元素是强制性的，不一定是内容。ColumnReference( ) 类型的子元素ColumnReferenceType具有minOccurs0 和maxOccurs无界 [0..*]，使其成为optional，因此允许为空元素。如果您手动尝试删除GroupBy并强制执行计划，则会收到预期的错误：

    Msg 6965, Level 16, State 1, Line 29
XML Validation: Invalid content. Expected element(s): '{http://schemas.microsoft.com/sqlserver/2004/07/showplan}GroupBy','{http://schemas.microsoft.com/sqlserver/2004/07/showplan}DefinedValues','{http://schemas.microsoft.com/sqlserver/2004/07/showplan}InternalInfo'. Found: element '{http://schemas.microsoft.com/sqlserver/2004/07/showplan}SegmentColumn' instead. Location: /*:ShowPlanXML[1]/*:BatchSequence[1]/*:Batch[1]/*:Statements[1]/*:StmtSimple[1]/*:QueryPlan[1]/*:RelOp[1]/*:SequenceProject[1]/*:RelOp[1]/*:Segment[1]/*:SegmentColumn[1].

    有趣的是，我发现您可以手动删除 Segment 运算符以获得有效的强制计划，如下所示：

    在此处输入图像描述

    但是，当您使用该计划（使用OPTION ( USE PLAN ... )）运行时，段运算符会神奇地重新出现。只是表明优化器仅将 XML 计划作为粗略的指导。

    我的试验台：

    USE tempdb
GO
SET NOCOUNT ON
GO
IF OBJECT_ID('dbo.someTable') IS NOT NULL DROP TABLE dbo.someTable
GO
CREATE TABLE dbo.someTable (
    someGroup   int NOT NULL,
    someOrder   int NOT NULL,
    someValue   numeric(8, 2) NOT NULL,
    PRIMARY KEY CLUSTERED (someGroup, someOrder)
);
GO

-- Generate some dummy data
;WITH cte AS (
SELECT TOP 1000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.someTable ( someGroup, someOrder, someValue )
SELECT rn % 333, rn % 444, rn % 55
FROM cte
GO


-- Try and force the plan
SELECT ROW_NUMBER() OVER (ORDER BY someGroup, someOrder)
FROM dbo.someTable
OPTION ( USE PLAN N'<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="12.0.2000.8" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1000" StatementId="1" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="0.00596348" StatementText="SELECT ROW_NUMBER() OVER (ORDER BY someGroup, someOrder)&#xD;&#xA;FROM dbo.someTable" StatementType="SELECT" QueryHash="0x193176312402B8E7" QueryPlanHash="0x77F1D72C455025A4" RetrievedFromCache="true">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="88">
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="131072" EstimatedPagesCached="65536" EstimatedAvailableDegreeOfParallelism="4" />
            <RelOp AvgRowSize="15" EstimateCPU="8E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1000" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Sequence Project" EstimatedTotalSubtreeCost="0.00596348">
              <OutputList>
                <ColumnReference Column="Expr1002" />
              </OutputList>
              <SequenceProject>
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Column="Expr1002" />
                    <ScalarOperator ScalarString="row_number">
                      <Sequence FunctionName="row_number" />
                    </ScalarOperator>
                  </DefinedValue>
                </DefinedValues>

                <!-- Segment operator completely removed from plan -->
                <!--<RelOp AvgRowSize="15" EstimateCPU="2E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1000" LogicalOp="Segment" NodeId="1" Parallel="false" PhysicalOp="Segment" EstimatedTotalSubtreeCost="0.00588348">
                  <OutputList>
                    <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[someTable]" Column="someGroup" />
                    <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[someTable]" Column="someOrder" />
                    <ColumnReference Column="Segment1003" />
                  </OutputList>
                  <Segment>
                    <GroupBy />
                    <SegmentColumn>
                      <ColumnReference Column="Segment1003" />
                    </SegmentColumn>-->


                    <RelOp AvgRowSize="15" EstimateCPU="0.001257" EstimateIO="0.00460648" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1000" LogicalOp="Clustered Index Scan" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00586348" TableCardinality="1000">
                      <OutputList>
                        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[someTable]" Column="someGroup" />
                        <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[someTable]" Column="someOrder" />
                      </OutputList>
                      <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                        <DefinedValues>
                          <DefinedValue>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[someTable]" Column="someGroup" />
                          </DefinedValue>
                          <DefinedValue>
                            <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[someTable]" Column="someOrder" />
                          </DefinedValue>
                        </DefinedValues>
                        <Object Database="[tempdb]" Schema="[dbo]" Table="[someTable]" Index="[PK__someTabl__7CD03C8950FF62C1]" IndexKind="Clustered" Storage="RowStore" />
                      </IndexScan>
                    </RelOp>

                <!--</Segment>
                </RelOp>-->
              </SequenceProject>
            </RelOp>

          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>' )

    从测试台中截取 XML 计划并将其保存为 .sqlplan 以查看减去 Segment 的计划。

    PS 我不会花太多时间手动处理 SQL 计划，就好像您了解我一样，您会知道我将其视为消耗时间的忙碌工作，而我永远不会这样做。哦等一下！？:)

    • 11

相关问题