ÿþ<?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="25" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="14133.8" StatementText="/* TPC_H Query 5 - Local Supplier Volume */&#xD;&#xA;SELECT N_NAME, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS REVENUE&#xD;&#xA;FROM CUSTOMER, ORDERS, LINEITEM, SUPPLIER, NATION, REGION&#xD;&#xA;WHERE C_CUSTKEY = O_CUSTKEY AND L_ORDERKEY = O_ORDERKEY AND L_SUPPKEY = S_SUPPKEY AND&#xD;&#xA; C_NATIONKEY = S_NATIONKEY AND S_NATIONKEY = N_NATIONKEY AND N_REGIONKEY = R_REGIONKEY&#xD;&#xA; AND R_NAME = 'ASIA' AND O_ORDERDATE &gt;= '1994-01-01' AND O_ORDERDATE &lt; CONVERT(DATE,DATEADD(YY, 1, '1994-01-01'))&#xD;&#xA;GROUP BY N_NAME ORDER BY REVENUE DESC&#xD;&#xA;" StatementType="SELECT" QueryHash="0x5E7489BB771B438A" QueryPlanHash="0xEB2214770FA1D1B1"> <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="128" CompileTime="87" CompileCPU="87" CompileMemory="2440"> <RelOp AvgRowSize="40" EstimateCPU="0.00028115" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25" LogicalOp="Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="14133.8"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Column="Expr1020" /> </OutputList> <MemoryFractions Input="1" Output="1" /> <Sort Distinct="false"> <OrderBy> <OrderByColumn Ascending="false"> <ColumnReference Column="Expr1020" /> </OrderByColumn> </OrderBy> <RelOp AvgRowSize="40" EstimateCPU="92.4807" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25" LogicalOp="Aggregate" NodeId="1" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="14133.8"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Column="Expr1020" /> </OutputList> <MemoryFractions Input="0" Output="0" /> <Hash> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1020" /> <ScalarOperator ScalarString="SUM([Expr1021])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1021" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> </DefinedValues> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </HashKeysBuild> <BuildResidual> <ScalarOperator ScalarString="[tpch].[dbo].[NATION].[N_NAME] = [tpch].[dbo].[NATION].[N_NAME]"> <Compare CompareOp="IS"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </BuildResidual> <RelOp AvgRowSize="40" EstimateCPU="124.579" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="13844600" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="14041.3"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Column="Expr1021" /> </OutputList> <MemoryFractions Input="0.0779624" Output="0.0989544" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_SUPPKEY" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SUPPKEY" /> </HashKeysProbe> <ProbeResidual> <ScalarOperator ScalarString="[tpch].[dbo].[SUPPLIER].[S_NATIONKEY]=[tpch].[dbo].[NATION].[N_NATIONKEY] AND [tpch].[dbo].[LINEITEM].[L_SUPPKEY]=[tpch].[dbo].[SUPPLIER].[S_SUPPKEY]"> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SUPPKEY" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_SUPPKEY" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </ProbeResidual> <RelOp AvgRowSize="15" EstimateCPU="1.10016" EstimateIO="1.01201" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1000000" LogicalOp="Index Scan" NodeId="3" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="2.11217" TableCardinality="1000000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_SUPPKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_SUPPKEY" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Index="[S_NATIONKEY_IDX]" IndexKind="NonClustered" /> </IndexScan> </RelOp> <RelOp AvgRowSize="48" EstimateCPU="4059.43" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="13846600" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="13914.6"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SUPPKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Column="Expr1021" /> </OutputList> <MemoryFractions Input="0.7099" Output="0.901046" /> <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="44" EstimateCPU="175.506" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3461420" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="803.717"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </OutputList> <MemoryFractions Input="0.921884" Output="0.212138" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> </HashKeysProbe> <RelOp AvgRowSize="40" EstimateCPU="70.9409" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3000000" LogicalOp="Inner Join" NodeId="6" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="328.182"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </OutputList> <MemoryFractions Input="0.922038" Output="0.000153518" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NATIONKEY" /> </HashKeysProbe> <RelOp AvgRowSize="36" EstimateCPU="0.0001045" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5" LogicalOp="Inner Join" NodeId="7" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0105035"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </OutputList> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_REGIONKEY" /> </OuterReferences> <RelOp AvgRowSize="40" EstimateCPU="0.0001845" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25" LogicalOp="Clustered Index Scan" NodeId="8" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0033095" TableCardinality="25"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_REGIONKEY" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_REGIONKEY" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[NATION]" Index="[PK_N_NATIONKEY]" IndexKind="Clustered" /> </IndexScan> </RelOp> <RelOp AvgRowSize="32" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="20" EstimateRewinds="4" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0070775" TableCardinality="5"> <OutputList /> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues /> <Object Database="[tpch]" Schema="[dbo]" Table="[REGION]" Index="[PK_R_REGIONKEY]" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[REGION]" Column="R_REGIONKEY" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[tpch].[dbo].[NATION].[N_REGIONKEY]"> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_REGIONKEY" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> <Predicate> <ScalarOperator ScalarString="[tpch].[dbo].[REGION].[R_NAME]='ASIA'"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[REGION]" Column="R_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'ASIA'" /> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </NestedLoops> </RelOp> <RelOp AvgRowSize="15" EstimateCPU="16.5002" EstimateIO="240.731" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="15000000" LogicalOp="Clustered Index Scan" NodeId="10" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="257.231" TableCardinality="15000000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NATIONKEY" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="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_NATIONKEY" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Index="[PK_C_CUSTKEY]" IndexKind="Clustered" /> </IndexScan> </RelOp> </Hash> </RelOp> <RelOp AvgRowSize="19" EstimateCPU="25.0314" EstimateIO="274.997" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="22755700" LogicalOp="Clustered Index Seek" NodeId="12" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="300.029" 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="'1994-01-01'"> <Const ConstValue="'1994-01-01'" /> </ScalarOperator> </RangeExpressions> </StartRange> <EndRange ScanType="LT"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERDATE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="'1995-01-01'"> <Const ConstValue="'1995-01-01'" /> </ScalarOperator> </RangeExpressions> </EndRange> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </Hash> </RelOp> <RelOp AvgRowSize="27" EstimateCPU="60.0038" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="600038000" LogicalOp="Compute Scalar" NodeId="14" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="9051.48"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SUPPKEY" /> <ColumnReference Column="Expr1021" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1021" /> <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="35" EstimateCPU="660.042" EstimateIO="8331.43" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="600038000" LogicalOp="Clustered Index Scan" NodeId="15" 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_SUPPKEY" /> <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_SUPPKEY" /> </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" /> </IndexScan> </RelOp> </ComputeScalar> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Sort> </RelOp> </QueryPlan> </StmtSimple> <StmtSimple StatementCompId="1" StatementEstRows="25" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="3960.16" StatementText="/* TPC_H Query 5 - Local Supplier Volume */&#xD;&#xA;SELECT N_NAME, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS REVENUE&#xD;&#xA;FROM CUSTOMER, ORDERS, LINEITEM, SUPPLIER, NATION, REGION&#xD;&#xA;WHERE C_CUSTKEY = O_CUSTKEY AND L_ORDERKEY = O_ORDERKEY AND L_SUPPKEY = S_SUPPKEY AND&#xD;&#xA; C_NATIONKEY = S_NATIONKEY AND S_NATIONKEY = N_NATIONKEY AND N_REGIONKEY = R_REGIONKEY&#xD;&#xA; AND R_NAME = 'ASIA' AND O_ORDERDATE &gt;= '1994-01-01' AND O_ORDERDATE &lt; CONVERT(DATE,DATEADD(YY, 1, '1994-01-01'))&#xD;&#xA;GROUP BY N_NAME ORDER BY REVENUE DESC&#xD;&#xA;" StatementType="SELECT" QueryHash="0x5E7489BB771B438A" QueryPlanHash="0xFF8DA6D5899F8AF9"> <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="128" CompileTime="119" CompileCPU="119" CompileMemory="2632"> <RelOp AvgRowSize="40" EstimateCPU="0.00028115" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25" LogicalOp="Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="3960.16"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Column="Expr1020" /> </OutputList> <MemoryFractions Input="1" Output="1" /> <Sort Distinct="false"> <OrderBy> <OrderByColumn Ascending="false"> <ColumnReference Column="Expr1020" /> </OrderByColumn> </OrderBy> <RelOp AvgRowSize="40" EstimateCPU="54.8726" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25" LogicalOp="Aggregate" NodeId="1" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="3960.14"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Column="Expr1020" /> </OutputList> <MemoryFractions Input="0" Output="0" /> <Hash> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1020" /> <ScalarOperator ScalarString="SUM([Expr1024])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1024" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> </DefinedValues> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </HashKeysBuild> <BuildResidual> <ScalarOperator ScalarString="[tpch].[dbo].[NATION].[N_NAME] = [tpch].[dbo].[NATION].[N_NAME]"> <Compare CompareOp="IS"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </BuildResidual> <RelOp AvgRowSize="40" EstimateCPU="58.3325" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="8213430" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="3905.27"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Column="Expr1024" /> </OutputList> <MemoryFractions Input="0.0769757" Output="0.110088" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_SUPPKEY" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SUPPKEY" /> </HashKeysProbe> <ProbeResidual> <ScalarOperator ScalarString="[tpch].[dbo].[SUPPLIER].[S_NATIONKEY]=[tpch].[dbo].[NATION].[N_NATIONKEY] AND [tpch].[dbo].[LINEITEM].[L_SUPPKEY]=[tpch].[dbo].[SUPPLIER].[S_SUPPKEY]"> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SUPPKEY" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_SUPPKEY" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </ProbeResidual> <RelOp AvgRowSize="15" EstimateCPU="1.10016" EstimateIO="1.01201" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1000000" LogicalOp="Index Scan" NodeId="3" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="2.11217" TableCardinality="1000000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_SUPPKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_SUPPKEY" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Index="[S_NATIONKEY_IDX]" IndexKind="NonClustered" /> </IndexScan> </RelOp> <RelOp AvgRowSize="48" EstimateCPU="1008.29" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4511700" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="3844.83"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SUPPKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Column="Expr1024" /> </OutputList> <MemoryFractions Input="0.622243" Output="0.889912" /> <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="44" EstimateCPU="175.506" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3461420" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="803.717"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </OutputList> <MemoryFractions Input="0.922871" Output="0.300781" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> </HashKeysProbe> <RelOp AvgRowSize="40" EstimateCPU="70.9409" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3000000" LogicalOp="Inner Join" NodeId="6" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="328.182"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </OutputList> <MemoryFractions Input="0.923024" Output="0.000153682" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NATIONKEY" /> </HashKeysProbe> <RelOp AvgRowSize="36" EstimateCPU="0.0001045" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5" LogicalOp="Inner Join" NodeId="7" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0105035"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </OutputList> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_REGIONKEY" /> </OuterReferences> <RelOp AvgRowSize="40" EstimateCPU="0.0001845" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25" LogicalOp="Clustered Index Scan" NodeId="8" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0033095" TableCardinality="25"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_REGIONKEY" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_REGIONKEY" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[NATION]" Index="[PK_N_NATIONKEY]" IndexKind="Clustered" /> </IndexScan> </RelOp> <RelOp AvgRowSize="32" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="20" EstimateRewinds="4" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0070775" TableCardinality="5"> <OutputList /> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues /> <Object Database="[tpch]" Schema="[dbo]" Table="[REGION]" Index="[PK_R_REGIONKEY]" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[REGION]" Column="R_REGIONKEY" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[tpch].[dbo].[NATION].[N_REGIONKEY]"> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_REGIONKEY" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> <Predicate> <ScalarOperator ScalarString="[tpch].[dbo].[REGION].[R_NAME]='ASIA'"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[REGION]" Column="R_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'ASIA'" /> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </NestedLoops> </RelOp> <RelOp AvgRowSize="15" EstimateCPU="16.5002" EstimateIO="240.731" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="15000000" LogicalOp="Clustered Index Scan" NodeId="10" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="257.231" TableCardinality="15000000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NATIONKEY" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="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_NATIONKEY" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Index="[PK_C_CUSTKEY]" IndexKind="Clustered" /> </IndexScan> </RelOp> </Hash> </RelOp> <RelOp AvgRowSize="19" EstimateCPU="25.0314" EstimateIO="274.997" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="22755700" LogicalOp="Clustered Index Seek" NodeId="12" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="300.029" 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="'1994-01-01'"> <Const ConstValue="'1994-01-01'" /> </ScalarOperator> </RangeExpressions> </StartRange> <EndRange ScanType="LT"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERDATE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="'1995-01-01'"> <Const ConstValue="'1995-01-01'" /> </ScalarOperator> </RangeExpressions> </EndRange> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </Hash> </RelOp> <RelOp AvgRowSize="27" EstimateCPU="13.4759" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="134759000" LogicalOp="Compute Scalar" NodeId="14" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="2032.82"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SUPPKEY" /> <ColumnReference Column="Expr1024" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1024" /> <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="35" EstimateCPU="148.235" EstimateIO="1871.11" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="134759000" LogicalOp="Clustered Index Seek" NodeId="15" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="2019.35" TableCardinality="600038000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SUPPKEY" /> <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_SUPPKEY" /> </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-12-19'"> <Const ConstValue="'1993-12-19'" /> </ScalarOperator> </RangeExpressions> </StartRange> <EndRange ScanType="LT"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SHIPDATE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="'1995-06-12'"> <Const ConstValue="'1995-06-12'" /> </ScalarOperator> </RangeExpressions> </EndRange> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </ComputeScalar> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Sort> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> </BatchSequence> </ShowPlanXML>