ÿþ<?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="2.1249" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="10988.6" StatementText="/* TPC_H Query 7 - Volume Shipping */&#xD;&#xA;SELECT SUPP_NATION, CUST_NATION, L_YEAR, SUM(VOLUME) AS REVENUE&#xD;&#xA;FROM ( SELECT N1.N_NAME AS SUPP_NATION, N2.N_NAME AS CUST_NATION, datepart(yy, L_SHIPDATE) AS L_YEAR, L_EXTENDEDPRICE*(1-L_DISCOUNT) AS VOLUME&#xD;&#xA; FROM SUPPLIER, LINEITEM, ORDERS, CUSTOMER, NATION N1, NATION N2&#xD;&#xA; WHERE S_SUPPKEY = L_SUPPKEY AND O_ORDERKEY = L_ORDERKEY AND C_CUSTKEY = O_CUSTKEY&#xD;&#xA; AND S_NATIONKEY = N1.N_NATIONKEY AND C_NATIONKEY = N2.N_NATIONKEY AND&#xD;&#xA; ((N1.N_NAME = 'FRANCE' AND N2.N_NAME = 'GERMANY') OR (N1.N_NAME = 'GERMANY' AND N2.N_NAME = 'FRANCE')) AND &#xD;&#xA; L_SHIPDATE BETWEEN '1995-01-01' AND '1996-12-31' ) AS SHIPPING&#xD;&#xA;GROUP BY SUPP_NATION, CUST_NATION, L_YEAR&#xD;&#xA;ORDER BY SUPP_NATION, CUST_NATION, L_YEAR&#xD;&#xA;" StatementType="SELECT" QueryHash="0x262670FEB10CD49D" QueryPlanHash="0xE235091266B41F56"> <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="136" CompileTime="75" CompileCPU="75" CompileMemory="2248"> <RelOp AvgRowSize="69" EstimateCPU="6.16217E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2.1249" LogicalOp="Aggregate" NodeId="0" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="10988.6"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> <ColumnReference Column="Expr1018" /> <ColumnReference Column="Expr1020" /> </OutputList> <StreamAggregate> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1020" /> <ScalarOperator ScalarString="SUM([partialagg1023])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="partialagg1023" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> </DefinedValues> <GroupBy> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> <ColumnReference Column="Expr1018" /> </GroupBy> <RelOp AvgRowSize="69" EstimateCPU="0.000141005" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="8.49952" LogicalOp="Sort" NodeId="1" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="10988.6"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> <ColumnReference Column="Expr1018" /> <ColumnReference Column="partialagg1023" /> </OutputList> <MemoryFractions Input="1" Output="1" /> <Sort Distinct="false"> <OrderBy> <OrderByColumn Ascending="true"> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NAME" /> </OrderByColumn> <OrderByColumn Ascending="true"> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> </OrderByColumn> <OrderByColumn Ascending="true"> <ColumnReference Column="Expr1018" /> </OrderByColumn> </OrderBy> <RelOp AvgRowSize="69" EstimateCPU="0.000443945" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="8.49952" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="10988.6"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> <ColumnReference Column="Expr1018" /> <ColumnReference Column="partialagg1023" /> </OutputList> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> </OuterReferences> <RelOp AvgRowSize="48" EstimateCPU="97.4829" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="106.207" LogicalOp="Aggregate" NodeId="3" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="10988.5"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> <ColumnReference Column="Expr1018" /> <ColumnReference Column="partialagg1023" /> </OutputList> <MemoryFractions Input="0" Output="0" /> <Hash> <DefinedValues> <DefinedValue> <ColumnReference Column="partialagg1023" /> <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="[SUPPLIER]" Column="S_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> <ColumnReference Column="Expr1018" /> </HashKeysBuild> <BuildResidual> <ScalarOperator ScalarString="[tpch].[dbo].[SUPPLIER].[S_NATIONKEY] = [tpch].[dbo].[SUPPLIER].[S_NATIONKEY] AND [tpch].[dbo].[NATION].[N_NAME] as [N2].[N_NAME] = [tpch].[dbo].[NATION].[N_NAME] as [N2].[N_NAME] AND [Expr1018] = [Expr1018]"> <Logical Operation="AND"> <ScalarOperator> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="IS"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="IS"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> <ScalarOperator> <Compare CompareOp="IS"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1018" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1018" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </BuildResidual> <RelOp AvgRowSize="48" EstimateCPU="89.5891" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="14593000" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="10891"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> <ColumnReference Column="Expr1018" /> <ColumnReference Column="Expr1021" /> </OutputList> <MemoryFractions Input="0.0570084" Output="0.0668024" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_SUPPKEY" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SUPPKEY" /> </HashKeysProbe> <RelOp AvgRowSize="15" EstimateCPU="1.10016" EstimateIO="1.01201" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1000000" LogicalOp="Index Scan" NodeId="5" 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="1599.76" EstimateIO="3426.73" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="14593000" LogicalOp="Inner Join" NodeId="6" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="10799.3"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SUPPKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> <ColumnReference Column="Expr1018" /> <ColumnReference Column="Expr1021" /> </OutputList> <MemoryFractions Input="0.79638" Output="0.933198" /> <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].[ORDERS].[O_ORDERKEY]=[tpch].[dbo].[LINEITEM].[L_ORDERKEY]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </ProbeResidual> <RelOp AvgRowSize="40" EstimateCPU="720.709" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="12017600" LogicalOp="Inner Join" NodeId="7" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="3025.21"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> </OutputList> <MemoryFractions Input="0.942828" Output="0.146612" /> <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="36" EstimateCPU="69.562" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1200000" LogicalOp="Inner Join" NodeId="8" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="326.796"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> </OutputList> <MemoryFractions Input="0.942992" Output="0.000163328" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NATIONKEY" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NATIONKEY" /> </HashKeysProbe> <RelOp AvgRowSize="36" EstimateCPU="0.0001845" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2" LogicalOp="Clustered Index Scan" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0033095" TableCardinality="25"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NATIONKEY" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[NATION]" Index="[PK_N_NATIONKEY]" Alias="[N2]" IndexKind="Clustered" /> <Predicate> <ScalarOperator ScalarString="[tpch].[dbo].[NATION].[N_NAME] as [N2].[N_NAME]='FRANCE' OR [tpch].[dbo].[NATION].[N_NAME] as [N2].[N_NAME]='GERMANY'"> <Logical Operation="OR"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'FRANCE'" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'GERMANY'" /> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </Predicate> </IndexScan> </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="165" EstimateIO="1812.7" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="150000000" LogicalOp="Clustered Index Scan" NodeId="12" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="1977.7" 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="false" ForcedIndex="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" /> </IndexScan> </RelOp> </Hash> </RelOp> <RelOp AvgRowSize="31" EstimateCPU="18.2146" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="182146000" LogicalOp="Compute Scalar" NodeId="14" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="2747.65"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SUPPKEY" /> <ColumnReference Column="Expr1018" /> <ColumnReference Column="Expr1021" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1018" /> <ScalarOperator ScalarString="datepart(year,[tpch].[dbo].[LINEITEM].[L_SHIPDATE])"> <Intrinsic FunctionName="datepart"> <ScalarOperator> <Const ConstValue="(0)" /> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SHIPDATE" /> </Identifier> </ScalarOperator> </Intrinsic> </ScalarOperator> </DefinedValue> <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="38" EstimateCPU="200.361" EstimateIO="2529.07" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="182146000" LogicalOp="Clustered Index Seek" NodeId="15" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="2729.43" 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" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SHIPDATE" /> </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> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SHIPDATE" /> </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="'1995-01-01'"> <Const ConstValue="'1995-01-01'" /> </ScalarOperator> </RangeExpressions> </StartRange> <EndRange ScanType="LE"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SHIPDATE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="'1996-12-31'"> <Const ConstValue="'1996-12-31'" /> </ScalarOperator> </RangeExpressions> </EndRange> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </ComputeScalar> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Hash> </RelOp> <RelOp AvgRowSize="32" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="103.964" EstimateRewinds="1.24279" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="26" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0199163" TableCardinality="25"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NAME" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NAME" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[NATION]" Index="[PK_N_NATIONKEY]" Alias="[N1]" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NATIONKEY" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[tpch].[dbo].[SUPPLIER].[S_NATIONKEY]"> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> <Predicate> <ScalarOperator ScalarString="([tpch].[dbo].[NATION].[N_NAME] as [N1].[N_NAME]='FRANCE' OR [tpch].[dbo].[NATION].[N_NAME] as [N1].[N_NAME]='GERMANY') AND ([tpch].[dbo].[NATION].[N_NAME] as [N1].[N_NAME]='FRANCE' AND [tpch].[dbo].[NATION].[N_NAME] as [N2].[N_NAME]='GERMANY' OR [tpch].[dbo].[NATION].[N_NAME] as [N1].[N_NAME]='GERMANY' AND [tpch].[dbo].[NATION].[N_NAME] as [N2].[N_NAME]='FRANCE')"> <Logical Operation="AND"> <ScalarOperator> <Logical Operation="OR"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'FRANCE'" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'GERMANY'" /> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> <ScalarOperator> <Logical Operation="OR"> <ScalarOperator> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'FRANCE'" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'GERMANY'" /> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> <ScalarOperator> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'GERMANY'" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'FRANCE'" /> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </Logical> </ScalarOperator> </Logical> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </NestedLoops> </RelOp> </Sort> </RelOp> </StreamAggregate> </RelOp> </QueryPlan> </StmtSimple> <StmtSimple StatementCompId="1" StatementEstRows="8.49962" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="5516.7" StatementText="/* TPC_H Query 7 - Volume Shipping */&#xD;&#xA;SELECT SUPP_NATION, CUST_NATION, L_YEAR, SUM(VOLUME) AS REVENUE&#xD;&#xA;FROM ( SELECT N1.N_NAME AS SUPP_NATION, N2.N_NAME AS CUST_NATION, datepart(yy, L_SHIPDATE) AS L_YEAR, L_EXTENDEDPRICE*(1-L_DISCOUNT) AS VOLUME&#xD;&#xA; FROM SUPPLIER, LINEITEM, ORDERS, CUSTOMER, NATION N1, NATION N2&#xD;&#xA; WHERE S_SUPPKEY = L_SUPPKEY AND O_ORDERKEY = L_ORDERKEY AND C_CUSTKEY = O_CUSTKEY&#xD;&#xA; AND S_NATIONKEY = N1.N_NATIONKEY AND C_NATIONKEY = N2.N_NATIONKEY AND&#xD;&#xA; ((N1.N_NAME = 'FRANCE' AND N2.N_NAME = 'GERMANY') OR (N1.N_NAME = 'GERMANY' AND N2.N_NAME = 'FRANCE')) AND &#xD;&#xA; L_SHIPDATE BETWEEN '1995-01-01' AND '1996-12-31' ) AS SHIPPING&#xD;&#xA;GROUP BY SUPP_NATION, CUST_NATION, L_YEAR&#xD;&#xA;ORDER BY SUPP_NATION, CUST_NATION, L_YEAR&#xD;&#xA;" StatementType="SELECT" QueryHash="0x262670FEB10CD49D" QueryPlanHash="0x76C992FCA0AA06CD"> <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="160" CompileTime="75" CompileCPU="75" CompileMemory="2104"> <RelOp AvgRowSize="69" EstimateCPU="0.000141006" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="8.49962" LogicalOp="Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="5516.7"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> <ColumnReference Column="Expr1018" /> <ColumnReference Column="Expr1020" /> </OutputList> <MemoryFractions Input="1" Output="1" /> <Sort Distinct="false"> <OrderBy> <OrderByColumn Ascending="true"> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NAME" /> </OrderByColumn> <OrderByColumn Ascending="true"> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> </OrderByColumn> <OrderByColumn Ascending="true"> <ColumnReference Column="Expr1018" /> </OrderByColumn> </OrderBy> <RelOp AvgRowSize="69" EstimateCPU="0.719172" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="8.49962" LogicalOp="Aggregate" NodeId="1" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="5516.68"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> <ColumnReference Column="Expr1018" /> <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]" Alias="[N1]" Column="N_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> <ColumnReference Column="Expr1018" /> </HashKeysBuild> <BuildResidual> <ScalarOperator ScalarString="[tpch].[dbo].[NATION].[N_NAME] as [N1].[N_NAME] = [tpch].[dbo].[NATION].[N_NAME] as [N1].[N_NAME] AND [tpch].[dbo].[NATION].[N_NAME] as [N2].[N_NAME] = [tpch].[dbo].[NATION].[N_NAME] as [N2].[N_NAME] AND [Expr1018] = [Expr1018]"> <Logical Operation="AND"> <ScalarOperator> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="IS"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NAME" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="IS"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> <ScalarOperator> <Compare CompareOp="IS"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1018" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1018" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </BuildResidual> <RelOp AvgRowSize="69" EstimateCPU="25.6696" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="104953" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="5515.96"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> <ColumnReference Column="Expr1018" /> <ColumnReference Column="Expr1024" /> </OutputList> <MemoryFractions Input="0" Output="0" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NATIONKEY" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> </HashKeysProbe> <ProbeResidual> <ScalarOperator ScalarString="[tpch].[dbo].[SUPPLIER].[S_NATIONKEY]=[tpch].[dbo].[NATION].[N_NATIONKEY] as [N1].[N_NATIONKEY] AND ([tpch].[dbo].[NATION].[N_NAME] as [N1].[N_NAME]='FRANCE' AND [tpch].[dbo].[NATION].[N_NAME] as [N2].[N_NAME]='GERMANY' OR [tpch].[dbo].[NATION].[N_NAME] as [N1].[N_NAME]='GERMANY' AND [tpch].[dbo].[NATION].[N_NAME] as [N2].[N_NAME]='FRANCE')"> <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]" Alias="[N1]" Column="N_NATIONKEY" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Logical Operation="OR"> <ScalarOperator> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'FRANCE'" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'GERMANY'" /> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> <ScalarOperator> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'GERMANY'" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'FRANCE'" /> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </Logical> </ScalarOperator> </Logical> </ScalarOperator> </ProbeResidual> <RelOp AvgRowSize="36" EstimateCPU="0.0001845" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2" LogicalOp="Clustered Index Scan" NodeId="3" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0033095" TableCardinality="25"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NAME" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NATIONKEY" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NAME" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[NATION]" Index="[PK_N_NATIONKEY]" Alias="[N1]" IndexKind="Clustered" /> <Predicate> <ScalarOperator ScalarString="[tpch].[dbo].[NATION].[N_NAME] as [N1].[N_NAME]='FRANCE' OR [tpch].[dbo].[NATION].[N_NAME] as [N1].[N_NAME]='GERMANY'"> <Logical Operation="OR"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'FRANCE'" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'GERMANY'" /> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </Predicate> </IndexScan> </RelOp> <RelOp AvgRowSize="48" EstimateCPU="41.5005" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5589380" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="5490.29"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> <ColumnReference Column="Expr1018" /> <ColumnReference Column="Expr1024" /> </OutputList> <MemoryFractions Input="0.0741602" Output="0.0914812" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_SUPPKEY" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SUPPKEY" /> </HashKeysProbe> <RelOp AvgRowSize="15" EstimateCPU="1.10016" EstimateIO="1.01201" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1000000" LogicalOp="Index Scan" NodeId="5" 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="1347.24" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5589350" LogicalOp="Inner Join" NodeId="6" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="5446.68"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SUPPKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> <ColumnReference Column="Expr1018" /> <ColumnReference Column="Expr1024" /> </OutputList> <MemoryFractions Input="0.7365" Output="0.908519" /> <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].[ORDERS].[O_ORDERKEY]=[tpch].[dbo].[LINEITEM].[L_ORDERKEY]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </ProbeResidual> <RelOp AvgRowSize="40" EstimateCPU="285.411" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4602910" LogicalOp="Inner Join" NodeId="7" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1351.79"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> </OutputList> <MemoryFractions Input="0.92562" Output="0.18934" /> <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="36" EstimateCPU="69.562" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1200000" LogicalOp="Inner Join" NodeId="8" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="326.796"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> </OutputList> <MemoryFractions Input="0.92584" Output="0.000220144" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NATIONKEY" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NATIONKEY" /> </HashKeysProbe> <RelOp AvgRowSize="36" EstimateCPU="0.0001845" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2" LogicalOp="Clustered Index Scan" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0033095" TableCardinality="25"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NATIONKEY" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[NATION]" Index="[PK_N_NATIONKEY]" Alias="[N2]" IndexKind="Clustered" /> <Predicate> <ScalarOperator ScalarString="[tpch].[dbo].[NATION].[N_NAME] as [N2].[N_NAME]='FRANCE' OR [tpch].[dbo].[NATION].[N_NAME] as [N2].[N_NAME]='GERMANY'"> <Logical Operation="OR"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'FRANCE'" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'GERMANY'" /> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </Predicate> </IndexScan> </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="61.7039" EstimateIO="677.883" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="56094300" LogicalOp="Clustered Index Seek" NodeId="12" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="739.587" 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-07-17'"> <Const ConstValue="'1994-07-17'" /> </ScalarOperator> </RangeExpressions> </StartRange> <EndRange ScanType="LT"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERDATE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="'1997-01-02'"> <Const ConstValue="'1997-01-02'" /> </ScalarOperator> </RangeExpressions> </EndRange> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </Hash> </RelOp> <RelOp AvgRowSize="31" EstimateCPU="18.2146" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="182146000" LogicalOp="Compute Scalar" NodeId="14" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="2747.65"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SUPPKEY" /> <ColumnReference Column="Expr1018" /> <ColumnReference Column="Expr1024" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1018" /> <ScalarOperator ScalarString="datepart(year,[tpch].[dbo].[LINEITEM].[L_SHIPDATE])"> <Intrinsic FunctionName="datepart"> <ScalarOperator> <Const ConstValue="(0)" /> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SHIPDATE" /> </Identifier> </ScalarOperator> </Intrinsic> </ScalarOperator> </DefinedValue> <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="38" EstimateCPU="200.361" EstimateIO="2529.07" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="182146000" LogicalOp="Clustered Index Seek" NodeId="15" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="2729.43" 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" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SHIPDATE" /> </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> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SHIPDATE" /> </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="'1995-01-01'"> <Const ConstValue="'1995-01-01'" /> </ScalarOperator> </RangeExpressions> </StartRange> <EndRange ScanType="LE"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SHIPDATE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="'1996-12-31'"> <Const ConstValue="'1996-12-31'" /> </ScalarOperator> </RangeExpressions> </EndRange> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </ComputeScalar> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Sort> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> </BatchSequence> </ShowPlanXML>