ÿþ<?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.1" Build="10.0.2746.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"> <BatchSequence> <Batch> <Statements> <StmtSimple StatementCompId="1" StatementEstRows="20" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="11098.6" StatementText="/* TPC_H Query 10 - Returned Item Reporting */&#xD;&#xA;SELECT TOP 20 C_CUSTKEY, C_NAME, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS REVENUE, C_ACCTBAL, N_NAME, C_ADDRESS, C_PHONE, C_COMMENT&#xD;&#xA;FROM CUSTOMER, ORDERS, LINEITEM, NATION&#xD;&#xA;WHERE C_CUSTKEY = O_CUSTKEY AND L_ORDERKEY = O_ORDERKEY&#xD;&#xA; AND O_ORDERDATE&gt;= '1993-10-01' AND O_ORDERDATE &lt; CONVERT(DATE,dateadd(mm, 3, '1993-10-01'))&#xD;&#xA; AND L_RETURNFLAG = 'R' AND C_NATIONKEY= N_NATIONKEY&#xD;&#xA;GROUP BY C_CUSTKEY, C_NAME, C_ACCTBAL, C_PHONE, N_NAME, C_ADDRESS, C_COMMENT&#xD;&#xA;ORDER BY REVENUE DESC&#xD;&#xA;" StatementType="SELECT" QueryHash="0x41BC2EB07291A736" QueryPlanHash="0x82AC905A6D4E91A6"> <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /> <QueryPlan CachedPlanSize="64" CompileTime="38" CompileCPU="38" CompileMemory="1496"> <RelOp AvgRowSize="165" EstimateCPU="2E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="20" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="11098.6"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_ADDRESS" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_PHONE" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_ACCTBAL" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_COMMENT" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Column="Expr1014" /> </OutputList> <Top RowCount="false" IsPercent="false" WithTies="false"> <TopExpression> <ScalarOperator ScalarString="(20)"> <Const ConstValue="(20)" /> </ScalarOperator> </TopExpression> <RelOp AvgRowSize="165" EstimateCPU="19.9426" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="20" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="11098.6"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_ADDRESS" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_PHONE" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_ACCTBAL" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_COMMENT" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Column="Expr1014" /> </OutputList> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NATIONKEY" /> </OuterReferences> <RelOp AvgRowSize="144" EstimateCPU="19.9426" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="26.2963" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="11098.6"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_ADDRESS" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_PHONE" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_ACCTBAL" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_COMMENT" /> <ColumnReference Column="Expr1014" /> </OutputList> <NestedLoops Optimized="false" WithOrderedPrefetch="true"> <OuterReferences> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> <ColumnReference Column="Expr1018" /> </OuterReferences> <RelOp AvgRowSize="19" EstimateCPU="485.84" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="26.2963" LogicalOp="Sort" NodeId="6" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="11098.5"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> <ColumnReference Column="Expr1014" /> </OutputList> <MemoryFractions Input="0.417266" Output="1" /> <Sort Distinct="false"> <OrderBy> <OrderByColumn Ascending="false"> <ColumnReference Column="Expr1014" /> </OrderByColumn> </OrderBy> <RelOp AvgRowSize="19" EstimateCPU="110.357" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4770950" LogicalOp="Aggregate" NodeId="7" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="10612.7"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> <ColumnReference Column="Expr1014" /> </OutputList> <MemoryFractions Input="0.161458" Output="0.582734" /> <Hash> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1014" /> <ScalarOperator ScalarString="SUM([Expr1015])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1015" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> </DefinedValues> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> </HashKeysBuild> <RelOp AvgRowSize="19" EstimateCPU="1087.07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9032970" LogicalOp="Inner Join" NodeId="8" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="10502.3"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> <ColumnReference Column="Expr1015" /> </OutputList> <MemoryFractions Input="1" Output="0.838542" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> </HashKeysProbe> <ProbeResidual> <ScalarOperator ScalarString="[tpch].[dbo].[LINEITEM].[L_ORDERKEY]=[tpch].[dbo].[ORDERS].[O_ORDERKEY]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </ProbeResidual> <RelOp AvgRowSize="19" EstimateCPU="6.31889" EstimateIO="69.4209" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5744300" LogicalOp="Clustered Index Seek" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="75.7398" TableCardinality="150000000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Index="[O_ORDERDATE_CLUIDX]" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <StartRange ScanType="GE"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERDATE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="'1993-10-01'"> <Const ConstValue="'1993-10-01'" /> </ScalarOperator> </RangeExpressions> </StartRange> <EndRange ScanType="LT"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERDATE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="'1994-01-01'"> <Const ConstValue="'1994-01-01'" /> </ScalarOperator> </RangeExpressions> </EndRange> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> <RelOp AvgRowSize="24" EstimateCPU="60.0038" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="147849000" LogicalOp="Compute Scalar" NodeId="10" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="9051.48"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> <ColumnReference Column="Expr1015" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1015" /> <ScalarOperator ScalarString="[tpch].[dbo].[LINEITEM].[L_EXTENDEDPRICE]*((1.000000000000000e+000)-[tpch].[dbo].[LINEITEM].[L_DISCOUNT])"> <Arithmetic Operation="MULT"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_EXTENDEDPRICE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Arithmetic Operation="SUB"> <ScalarOperator> <Const ConstValue="(1.000000000000000e+000)" /> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_DISCOUNT" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> </Arithmetic> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="32" EstimateCPU="660.042" EstimateIO="8331.43" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="147849000" LogicalOp="Clustered Index Scan" NodeId="11" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="8991.47" TableCardinality="600038000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_EXTENDEDPRICE" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_DISCOUNT" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_EXTENDEDPRICE" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_DISCOUNT" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Index="[L_SHIPDATE_CLUIDX]" IndexKind="Clustered" /> <Predicate> <ScalarOperator ScalarString="[tpch].[dbo].[LINEITEM].[L_RETURNFLAG]='R'"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_RETURNFLAG" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'R'" /> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </ComputeScalar> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Sort> </RelOp> <RelOp AvgRowSize="136" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="26.2963" EstimateRewinds="2.32705E-22" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="19" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0896129" TableCardinality="15000000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_ADDRESS" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_PHONE" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_ACCTBAL" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_COMMENT" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NAME" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_ADDRESS" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NATIONKEY" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_PHONE" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_ACCTBAL" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_COMMENT" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Index="[PK_C_CUSTKEY]" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[tpch].[dbo].[ORDERS].[O_CUSTKEY]"> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </NestedLoops> </RelOp> <RelOp AvgRowSize="32" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="40" EstimateRewinds="1.05199" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="21" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00977342" TableCardinality="25"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[NATION]" Index="[PK_N_NATIONKEY]" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[tpch].[dbo].[CUSTOMER].[C_NATIONKEY]"> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NATIONKEY" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </NestedLoops> </RelOp> </Top> </RelOp> </QueryPlan> </StmtSimple> <StmtSimple StatementCompId="1" StatementEstRows="20" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="3234.63" StatementText="/* TPC_H Query 10 - Returned Item Reporting */&#xD;&#xA;SELECT TOP 20 C_CUSTKEY, C_NAME, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS REVENUE, C_ACCTBAL, N_NAME, C_ADDRESS, C_PHONE, C_COMMENT&#xD;&#xA;FROM CUSTOMER, ORDERS, LINEITEM, NATION&#xD;&#xA;WHERE C_CUSTKEY = O_CUSTKEY AND L_ORDERKEY = O_ORDERKEY&#xD;&#xA; AND O_ORDERDATE&gt;= '1993-10-01' AND O_ORDERDATE &lt; CONVERT(DATE,dateadd(mm, 3, '1993-10-01'))&#xD;&#xA; AND L_RETURNFLAG = 'R' AND C_NATIONKEY= N_NATIONKEY&#xD;&#xA;GROUP BY C_CUSTKEY, C_NAME, C_ACCTBAL, C_PHONE, N_NAME, C_ADDRESS, C_COMMENT&#xD;&#xA;ORDER BY REVENUE DESC&#xD;&#xA;" StatementType="SELECT" QueryHash="0x41BC2EB07291A736" QueryPlanHash="0x8A26343ED5E48F09"> <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /> <QueryPlan CachedPlanSize="72" CompileTime="54" CompileCPU="54" CompileMemory="1472"> <RelOp AvgRowSize="165" EstimateCPU="2E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="20" LogicalOp="Top" NodeId="1" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="3234.63"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_ADDRESS" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_PHONE" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_ACCTBAL" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_COMMENT" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Column="Expr1014" /> </OutputList> <Top RowCount="false" IsPercent="false" WithTies="false"> <TopExpression> <ScalarOperator ScalarString="(20)"> <Const ConstValue="(20)" /> </ScalarOperator> </TopExpression> <RelOp AvgRowSize="165" EstimateCPU="57.5257" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="20" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="3234.63"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_ADDRESS" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_PHONE" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_ACCTBAL" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_COMMENT" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Column="Expr1014" /> </OutputList> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NATIONKEY" /> </OuterReferences> <RelOp AvgRowSize="144" EstimateCPU="57.5257" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="26.2963" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="3234.62"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_ADDRESS" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_PHONE" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_ACCTBAL" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_COMMENT" /> <ColumnReference Column="Expr1014" /> </OutputList> <NestedLoops Optimized="false" WithOrderedPrefetch="true"> <OuterReferences> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> <ColumnReference Column="Expr1021" /> </OuterReferences> <RelOp AvgRowSize="19" EstimateCPU="533.864" EstimateIO="865.811" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="26.2963" LogicalOp="Sort" NodeId="6" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="3234.53"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> <ColumnReference Column="Expr1014" /> </OutputList> <MemoryFractions Input="0.417015" Output="1" /> <Sort Distinct="false"> <OrderBy> <OrderByColumn Ascending="false"> <ColumnReference Column="Expr1014" /> </OrderByColumn> </OrderBy> <RelOp AvgRowSize="19" EstimateCPU="273.95" EstimateIO="206.221" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="13762100" LogicalOp="Aggregate" NodeId="7" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1834.86"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> <ColumnReference Column="Expr1014" /> </OutputList> <MemoryFractions Input="0.545044" Output="0.582985" /> <Hash> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1014" /> <ScalarOperator ScalarString="SUM([Expr1018])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1018" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> </DefinedValues> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> </HashKeysBuild> <RelOp AvgRowSize="19" EstimateCPU="237.311" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="16569900" LogicalOp="Inner Join" NodeId="8" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1354.69"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> <ColumnReference Column="Expr1018" /> </OutputList> <MemoryFractions Input="1" Output="0.454956" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> </HashKeysProbe> <ProbeResidual> <ScalarOperator ScalarString="[tpch].[dbo].[LINEITEM].[L_ORDERKEY]=[tpch].[dbo].[ORDERS].[O_ORDERKEY]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </ProbeResidual> <RelOp AvgRowSize="19" EstimateCPU="6.31889" EstimateIO="69.4209" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5744300" LogicalOp="Clustered Index Seek" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="75.7398" TableCardinality="150000000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Index="[O_ORDERDATE_CLUIDX]" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <StartRange ScanType="GE"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERDATE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="'1993-10-01'"> <Const ConstValue="'1993-10-01'" /> </ScalarOperator> </RangeExpressions> </StartRange> <EndRange ScanType="LT"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERDATE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="'1994-01-01'"> <Const ConstValue="'1994-01-01'" /> </ScalarOperator> </RangeExpressions> </EndRange> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> <RelOp AvgRowSize="23" EstimateCPU="1.65699" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="16569900" LogicalOp="Compute Scalar" NodeId="10" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1041.64"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> <ColumnReference Column="Expr1018" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1018" /> <ScalarOperator ScalarString="[tpch].[dbo].[LINEITEM].[L_EXTENDEDPRICE]*((1.000000000000000e+000)-[tpch].[dbo].[LINEITEM].[L_DISCOUNT])"> <Arithmetic Operation="MULT"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_EXTENDEDPRICE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Arithmetic Operation="SUB"> <ScalarOperator> <Const ConstValue="(1.000000000000000e+000)" /> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_DISCOUNT" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> </Arithmetic> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="32" EstimateCPU="73.9728" EstimateIO="933.729" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="16569900" LogicalOp="Clustered Index Seek" NodeId="11" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="1007.7" TableCardinality="600038000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_EXTENDEDPRICE" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_DISCOUNT" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_EXTENDEDPRICE" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_DISCOUNT" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Index="[L_SHIPDATE_CLUIDX]" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <StartRange ScanType="GE"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SHIPDATE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="'1993-09-20'"> <Const ConstValue="'1993-09-20'" /> </ScalarOperator> </RangeExpressions> </StartRange> <EndRange ScanType="LT"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SHIPDATE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="'1994-06-17'"> <Const ConstValue="'1994-06-17'" /> </ScalarOperator> </RangeExpressions> </EndRange> </SeekKeys> </SeekPredicateNew> </SeekPredicates> <Predicate> <ScalarOperator ScalarString="[tpch].[dbo].[LINEITEM].[L_RETURNFLAG]='R'"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_RETURNFLAG" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'R'" /> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </ComputeScalar> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Sort> </RelOp> <RelOp AvgRowSize="136" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="26.2963" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="19" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.089613" TableCardinality="15000000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_ADDRESS" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_PHONE" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_ACCTBAL" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_COMMENT" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NAME" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_ADDRESS" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NATIONKEY" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_PHONE" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_ACCTBAL" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_COMMENT" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Index="[PK_C_CUSTKEY]" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[tpch].[dbo].[ORDERS].[O_CUSTKEY]"> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </NestedLoops> </RelOp> <RelOp AvgRowSize="32" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="40" EstimateRewinds="1.05199" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="21" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00977342" TableCardinality="25"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[NATION]" Index="[PK_N_NATIONKEY]" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[tpch].[dbo].[CUSTOMER].[C_NATIONKEY]"> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NATIONKEY" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </NestedLoops> </RelOp> </Top> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> </BatchSequence> </ShowPlanXML>