ÿþ<?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="5.82801" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="11685.5" StatementText="/* TPC_H Query 1 - Pricing Summary Report */&#xD;&#xA;SELECT L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY) AS SUM_QTY, SUM(L_EXTENDEDPRICE)&#xD;&#xA;AS SUM_BASE_PRICE, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS SUM_DISC_PRICE,&#xD;&#xA;SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)) AS SUM_CHARGE, AVG(L_QUANTITY) AS AVG_QTY,&#xD;&#xA;AVG(L_EXTENDEDPRICE) AS AVG_PRICE, AVG(L_DISCOUNT) AS AVG_DISC, COUNT(*) AS COUNT_ORDER&#xD;&#xA;FROM LINEITEM WHERE L_SHIPDATE &lt;= CONVERT(DATE,dateadd(dd, -90, '1998-12-01')) &#xD;&#xA;GROUP BY L_RETURNFLAG, L_LINESTATUS ORDER BY L_RETURNFLAG, L_LINESTATUS&#xD;&#xA;" StatementType="SELECT" QueryHash="0xA7AFB1511145EFC4" QueryPlanHash="0x66A0E7E2E468C64A"> <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="10" CompileCPU="10" CompileMemory="664"> <RelOp AvgRowSize="70" EstimateCPU="0.00012319" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5.82801" LogicalOp="Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="11685.5"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_RETURNFLAG" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_LINESTATUS" /> <ColumnReference Column="Expr1004" /> <ColumnReference Column="Expr1005" /> <ColumnReference Column="Expr1006" /> <ColumnReference Column="Expr1007" /> <ColumnReference Column="Expr1008" /> <ColumnReference Column="Expr1009" /> <ColumnReference Column="Expr1010" /> <ColumnReference Column="Expr1011" /> </OutputList> <MemoryFractions Input="1" Output="1" /> <Sort Distinct="false"> <OrderBy> <OrderByColumn Ascending="true"> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_RETURNFLAG" /> </OrderByColumn> <OrderByColumn Ascending="true"> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_LINESTATUS" /> </OrderByColumn> </OrderBy> <RelOp AvgRowSize="70" EstimateCPU="2764.7" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5.82801" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="11685.5"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_RETURNFLAG" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_LINESTATUS" /> <ColumnReference Column="Expr1004" /> <ColumnReference Column="Expr1005" /> <ColumnReference Column="Expr1006" /> <ColumnReference Column="Expr1007" /> <ColumnReference Column="Expr1008" /> <ColumnReference Column="Expr1009" /> <ColumnReference Column="Expr1010" /> <ColumnReference Column="Expr1011" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1008" /> <ScalarOperator ScalarString="CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1004]/CONVERT_IMPLICIT(float(53),[Expr1035],0) END"> <IF> <Condition> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1035" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(0)" /> </ScalarOperator> </Compare> </ScalarOperator> </Condition> <Then> <ScalarOperator> <Const ConstValue="NULL" /> </ScalarOperator> </Then> <Else> <ScalarOperator> <Arithmetic Operation="DIV"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1004" /> </Identifier> </ScalarOperator> <ScalarOperator> <Convert DataType="float" Scale="0" Style="0" Implicit="true"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1035" /> </Identifier> </ScalarOperator> </Convert> </ScalarOperator> </Arithmetic> </ScalarOperator> </Else> </IF> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1009" /> <ScalarOperator ScalarString="CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1005]/CONVERT_IMPLICIT(float(53),[Expr1035],0) END"> <IF> <Condition> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1035" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(0)" /> </ScalarOperator> </Compare> </ScalarOperator> </Condition> <Then> <ScalarOperator> <Const ConstValue="NULL" /> </ScalarOperator> </Then> <Else> <ScalarOperator> <Arithmetic Operation="DIV"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1005" /> </Identifier> </ScalarOperator> <ScalarOperator> <Convert DataType="float" Scale="0" Style="0" Implicit="true"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1035" /> </Identifier> </ScalarOperator> </Convert> </ScalarOperator> </Arithmetic> </ScalarOperator> </Else> </IF> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1010" /> <ScalarOperator ScalarString="CASE WHEN [Expr1035]=(0) THEN NULL ELSE [Expr1036]/CONVERT_IMPLICIT(float(53),[Expr1035],0) END"> <IF> <Condition> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1035" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(0)" /> </ScalarOperator> </Compare> </ScalarOperator> </Condition> <Then> <ScalarOperator> <Const ConstValue="NULL" /> </ScalarOperator> </Then> <Else> <ScalarOperator> <Arithmetic Operation="DIV"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1036" /> </Identifier> </ScalarOperator> <ScalarOperator> <Convert DataType="float" Scale="0" Style="0" Implicit="true"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1035" /> </Identifier> </ScalarOperator> </Convert> </ScalarOperator> </Arithmetic> </ScalarOperator> </Else> </IF> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1011" /> <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1035],0)"> <Convert DataType="int" Style="0" Implicit="true"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1035" /> </Identifier> </ScalarOperator> </Convert> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="70" EstimateCPU="2764.7" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5.82801" LogicalOp="Aggregate" NodeId="2" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="11685.5"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_RETURNFLAG" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_LINESTATUS" /> <ColumnReference Column="Expr1004" /> <ColumnReference Column="Expr1005" /> <ColumnReference Column="Expr1006" /> <ColumnReference Column="Expr1007" /> <ColumnReference Column="Expr1035" /> <ColumnReference Column="Expr1036" /> </OutputList> <MemoryFractions Input="0" Output="0" /> <Hash> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1004" /> <ScalarOperator ScalarString="SUM([tpch].[dbo].[LINEITEM].[L_QUANTITY])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_QUANTITY" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1005" /> <ScalarOperator ScalarString="SUM([tpch].[dbo].[LINEITEM].[L_EXTENDEDPRICE])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_EXTENDEDPRICE" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1006" /> <ScalarOperator ScalarString="SUM([Expr1012])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1012" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1007" /> <ScalarOperator ScalarString="SUM([Expr1013])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1013" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1035" /> <ScalarOperator ScalarString="COUNT(*)"> <Aggregate AggType="COUNT*" Distinct="false" /> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1036" /> <ScalarOperator ScalarString="SUM([tpch].[dbo].[LINEITEM].[L_DISCOUNT])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_DISCOUNT" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> </DefinedValues> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_RETURNFLAG" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_LINESTATUS" /> </HashKeysBuild> <BuildResidual> <ScalarOperator ScalarString="[tpch].[dbo].[LINEITEM].[L_RETURNFLAG] = [tpch].[dbo].[LINEITEM].[L_RETURNFLAG] AND [tpch].[dbo].[LINEITEM].[L_LINESTATUS] = [tpch].[dbo].[LINEITEM].[L_LINESTATUS]"> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="IS"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_RETURNFLAG" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_RETURNFLAG" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="IS"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_LINESTATUS" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_LINESTATUS" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </BuildResidual> <RelOp AvgRowSize="49" EstimateCPU="59.1375" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="591375000" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="8920.8"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_QUANTITY" /> <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_RETURNFLAG" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_LINESTATUS" /> <ColumnReference Column="Expr1012" /> <ColumnReference Column="Expr1013" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1012" /> <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> <DefinedValue> <ColumnReference Column="Expr1013" /> <ScalarOperator ScalarString="([tpch].[dbo].[LINEITEM].[L_EXTENDEDPRICE]*((1.000000000000000e+000)-[tpch].[dbo].[LINEITEM].[L_DISCOUNT]))*((1.000000000000000e+000)+[tpch].[dbo].[LINEITEM].[L_TAX])"> <Arithmetic Operation="MULT"> <ScalarOperator> <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> <ScalarOperator> <Arithmetic Operation="ADD"> <ScalarOperator> <Const ConstValue="(1.000000000000000e+000)" /> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_TAX" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> </Arithmetic> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="41" EstimateCPU="650.513" EstimateIO="8211.15" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="591375000" LogicalOp="Clustered Index Seek" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="8861.67" TableCardinality="600038000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_QUANTITY" /> <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_TAX" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_RETURNFLAG" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_LINESTATUS" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_QUANTITY" /> </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_TAX" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_RETURNFLAG" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_LINESTATUS" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Index="[L_SHIPDATE_CLUIDX]" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <EndRange ScanType="LE"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SHIPDATE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="'1998-09-02'"> <Const ConstValue="'1998-09-02'" /> </ScalarOperator> </RangeExpressions> </EndRange> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </ComputeScalar> </RelOp> </Hash> </RelOp> </ComputeScalar> </RelOp> </Sort> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> <Batch> <Statements> <StmtSimple StatementCompId="1" StatementEstRows="100" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="621.13" StatementText="/* TPC_H Query 2 - Minimum Cost Supplier */&#xD;&#xA;SELECT TOP 100 S_ACCTBAL, S_NAME, N_NAME, P_PARTKEY, P_MFGR, S_ADDRESS, S_PHONE, S_COMMENT&#xD;&#xA;FROM PART, SUPPLIER, PARTSUPP,NATION, REGION&#xD;&#xA;WHERE P_PARTKEY = PS_PARTKEY AND S_SUPPKEY = PS_SUPPKEY AND P_SIZE = 15 AND&#xD;&#xA; P_TYPE LIKE '%BRASS' AND S_NATIONKEY = N_NATIONKEY AND N_REGIONKEY = R_REGIONKEY AND R_NAME = 'EUROPE' AND&#xD;&#xA; PS_SUPPLYCOST = (SELECT MIN(PS_SUPPLYCOST) FROM PARTSUPP, SUPPLIER, NATION, REGION&#xD;&#xA; WHERE P_PARTKEY = PS_PARTKEY AND S_SUPPKEY = PS_SUPPKEY AND S_NATIONKEY= N_NATIONKEY AND N_REGIONKEY = R_REGIONKEY AND R_NAME = 'EUROPE')&#xD;&#xA;ORDER BY S_ACCTBAL DESC, N_NAME, S_NAME, P_PARTKEY&#xD;&#xA;" StatementType="SELECT" QueryHash="0xF41CBD58B002A99C" QueryPlanHash="0x131183ABC8493998"> <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="144" CompileTime="144" CompileCPU="144" CompileMemory="3944"> <RelOp AvgRowSize="185" EstimateCPU="1E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="100" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="621.13"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_MFGR" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_ADDRESS" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_PHONE" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_ACCTBAL" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_COMMENT" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </OutputList> <Top RowCount="false" IsPercent="false" WithTies="false"> <TopExpression> <ScalarOperator ScalarString="(100)"> <Const ConstValue="(100)" /> </ScalarOperator> </TopExpression> <RelOp AvgRowSize="185" EstimateCPU="0.209261" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="100" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="621.13"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_MFGR" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_ADDRESS" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_PHONE" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_ACCTBAL" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_COMMENT" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </OutputList> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPLYCOST" /> </OuterReferences> <RelOp AvgRowSize="198" EstimateCPU="3.5874" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="100" LogicalOp="Sort" NodeId="3" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="619.359"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_MFGR" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_ADDRESS" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_PHONE" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_ACCTBAL" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_COMMENT" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPLYCOST" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </OutputList> <MemoryFractions Input="0.109838" Output="1" /> <Sort Distinct="false"> <OrderBy> <OrderByColumn Ascending="false"> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_ACCTBAL" /> </OrderByColumn> <OrderByColumn Ascending="true"> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </OrderByColumn> <OrderByColumn Ascending="true"> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NAME" /> </OrderByColumn> <OrderByColumn Ascending="true"> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> </OrderByColumn> </OrderBy> <RelOp AvgRowSize="198" EstimateCPU="1.20129" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="50062.4" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="615.761"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_MFGR" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_ADDRESS" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_PHONE" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_ACCTBAL" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_COMMENT" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_PARTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPLYCOST" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </OutputList> <MemoryFractions Input="0" Output="0" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[REGION]" Column="R_REGIONKEY" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_REGIONKEY" /> </HashKeysProbe> <RelOp AvgRowSize="36" EstimateCPU="0.0001625" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032875" TableCardinality="5"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[REGION]" Column="R_REGIONKEY" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[REGION]" Column="R_REGIONKEY" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[REGION]" Index="[PK_R_REGIONKEY]" TableReferenceId="1" IndexKind="Clustered" /> <Predicate> <ScalarOperator ScalarString="[tpch].[dbo].[REGION].[R_NAME]='EUROPE'"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[REGION]" Column="R_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'EUROPE'" /> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </IndexScan> </RelOp> <RelOp AvgRowSize="202" EstimateCPU="1.71709" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="250312" LogicalOp="Inner Join" NodeId="6" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="614.556"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_MFGR" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_ADDRESS" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_PHONE" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_ACCTBAL" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_COMMENT" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_PARTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPLYCOST" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_REGIONKEY" /> </OutputList> <MemoryFractions Input="0" Output="0" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> </HashKeysProbe> <RelOp AvgRowSize="40" EstimateCPU="0.0001845" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25" LogicalOp="Clustered Index Scan" NodeId="7" 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="false" ForcedIndex="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]" TableReferenceId="1" IndexKind="Clustered" /> </IndexScan> </RelOp> <RelOp AvgRowSize="177" EstimateCPU="14.2496" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="329406" LogicalOp="Inner Join" NodeId="8" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="612.836"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_MFGR" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_ADDRESS" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_PHONE" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_ACCTBAL" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_COMMENT" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_PARTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPLYCOST" /> </OutputList> <MemoryFractions Input="0.0443689" Output="0.890162" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPKEY" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_SUPPKEY" /> </HashKeysProbe> <RelOp AvgRowSize="52" EstimateCPU="1.37692" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="329406" LogicalOp="Inner Join" NodeId="9" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="582.835"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_MFGR" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_PARTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPLYCOST" /> </OutputList> <NestedLoops Optimized="true" WithUnorderedPrefetch="true"> <OuterReferences> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> <ColumnReference Column="Expr1034" /> </OuterReferences> <RelOp AvgRowSize="64" EstimateCPU="22.0001" EstimateIO="269.885" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="82351.4" LogicalOp="Clustered Index Scan" NodeId="12" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="291.885" TableCardinality="20000000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_MFGR" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_MFGR" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[PART]" Index="[PK_P_PARTKEY]" IndexKind="Clustered" /> <Predicate> <ScalarOperator ScalarString="[tpch].[dbo].[PART].[P_SIZE]=(15) AND [tpch].[dbo].[PART].[P_TYPE] like '%BRASS'"> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_SIZE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(15)" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Intrinsic FunctionName="like"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_TYPE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'%BRASS'" /> </ScalarOperator> </Intrinsic> </ScalarOperator> </Logical> </ScalarOperator> </Predicate> </IndexScan> </RelOp> <RelOp AvgRowSize="23" EstimateCPU="0.0001614" EstimateIO="0.003125" EstimateRebinds="82350.4" EstimateRewinds="0" EstimateRows="4" LogicalOp="Clustered Index Seek" NodeId="13" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="263.973" TableCardinality="80000000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_PARTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPLYCOST" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_PARTKEY" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPKEY" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPLYCOST" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Index="[PK_PS_PARTKEY_PS_SUPPKEY]" TableReferenceId="1" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_PARTKEY" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[tpch].[dbo].[PART].[P_PARTKEY]"> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </NestedLoops> </RelOp> <RelOp AvgRowSize="139" EstimateCPU="1.10016" EstimateIO="14.6513" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1000000" LogicalOp="Clustered Index Scan" NodeId="14" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="15.7514" TableCardinality="1000000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_SUPPKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NAME" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_ADDRESS" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_PHONE" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_ACCTBAL" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_COMMENT" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="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_NAME" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_ADDRESS" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_PHONE" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_ACCTBAL" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_COMMENT" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Index="[PK_S_SUPPKEY]" TableReferenceId="1" IndexKind="Clustered" /> </IndexScan> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Sort> </RelOp> <RelOp AvgRowSize="9" EstimateCPU="4.8E-07" EstimateIO="0" EstimateRebinds="99.9992" EstimateRewinds="0" EstimateRows="1" LogicalOp="Filter" NodeId="21" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="1.77057"> <OutputList /> <Filter StartupExpression="false"> <RelOp AvgRowSize="15" EstimateCPU="1.1E-06" EstimateIO="0" EstimateRebinds="99.9992" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="22" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="1.77052"> <OutputList> <ColumnReference Column="Expr1027" /> </OutputList> <StreamAggregate> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1027" /> <ScalarOperator ScalarString="MIN([tpch].[dbo].[PARTSUPP].[PS_SUPPLYCOST])"> <Aggregate AggType="MIN" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPLYCOST" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="15" EstimateCPU="1.672E-05" EstimateIO="0" EstimateRebinds="99.9992" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner Join" NodeId="23" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.77041"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPLYCOST" /> </OutputList> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_REGIONKEY" /> </OuterReferences> <RelOp AvgRowSize="19" EstimateCPU="1.672E-05" EstimateIO="0" EstimateRebinds="99.9992" EstimateRewinds="0" EstimateRows="4" LogicalOp="Inner Join" NodeId="24" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.70153"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPLYCOST" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_REGIONKEY" /> </OutputList> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> </OuterReferences> <RelOp AvgRowSize="19" EstimateCPU="1.672E-05" EstimateIO="0" EstimateRebinds="99.9992" EstimateRewinds="0" EstimateRows="4" LogicalOp="Inner Join" NodeId="25" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.63284"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPLYCOST" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> </OutputList> <NestedLoops Optimized="false"> <OuterReferences> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPKEY" /> </OuterReferences> <RelOp AvgRowSize="19" EstimateCPU="0.0001614" EstimateIO="0.003125" EstimateRebinds="99.9992" EstimateRewinds="0" EstimateRows="4" LogicalOp="Clustered Index Seek" NodeId="26" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.331523" TableCardinality="80000000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPLYCOST" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPKEY" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPLYCOST" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Index="[PK_PS_PARTKEY_PS_SUPPKEY]" TableReferenceId="2" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_PARTKEY" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[tpch].[dbo].[PART].[P_PARTKEY]"> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> <RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="402.997" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="27" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="1.29963" TableCardinality="1000000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Index="[PK_S_SUPPKEY]" TableReferenceId="2" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <Prefix ScanType="EQ"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_SUPPKEY" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[tpch].[dbo].[PARTSUPP].[PS_SUPPKEY]"> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPKEY" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </NestedLoops> </RelOp> <RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="402.997" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="28" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0669969" TableCardinality="25"> <OutputList> <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_REGIONKEY" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[NATION]" Index="[PK_N_NATIONKEY]" TableReferenceId="2" 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].[SUPPLIER].[S_NATIONKEY]"> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> </Identifier> </ScalarOperator> </RangeExpressions> </Prefix> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </NestedLoops> </RelOp> <RelOp AvgRowSize="32" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="402.997" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="29" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0669969" 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]" TableReferenceId="2" 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]='EUROPE'"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[REGION]" Column="R_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'EUROPE'" /> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </NestedLoops> </RelOp> </StreamAggregate> </RelOp> <Predicate> <ScalarOperator ScalarString="[tpch].[dbo].[PARTSUPP].[PS_SUPPLYCOST]=[Expr1027]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPLYCOST" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1027" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </Filter> </RelOp> </NestedLoops> </RelOp> </Top> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> <Batch> <Statements> <StmtSimple StatementCompId="1" StatementEstRows="10" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="15595.3" StatementText="/* TPC_H Query 3 - Shipping Priority */&#xD;&#xA;SELECT TOP 10 L_ORDERKEY, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT))AS REVENUE, O_ORDERDATE, O_SHIPPRIORITY&#xD;&#xA;FROM CUSTOMER, ORDERS, LINEITEM&#xD;&#xA;WHERE C_MKTSEGMENT = 'BUILDING' AND C_CUSTKEY = O_CUSTKEY AND L_ORDERKEY = O_ORDERKEY&#xD;&#xA; AND O_ORDERDATE &lt; '1995-03-15' AND L_SHIPDATE &gt; '1995-03-15'&#xD;&#xA;GROUP BY L_ORDERKEY, O_ORDERDATE, O_SHIPPRIORITY&#xD;&#xA;ORDER BY REVENUE DESC, O_ORDERDATE&#xD;&#xA;" StatementType="SELECT" QueryHash="0x3E64DEBFAF72428B" QueryPlanHash="0x6E2A764FE0E132D4"> <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="80" CompileTime="23" CompileCPU="23" CompileMemory="1024"> <RelOp AvgRowSize="30" EstimateCPU="580.773" EstimateIO="1164.68" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10" LogicalOp="TopN Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="15595.3"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERDATE" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_SHIPPRIORITY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> <ColumnReference Column="Expr1011" /> </OutputList> <MemoryFractions Input="4.76224E-06" Output="1" /> <TopSort Distinct="false" Rows="10"> <OrderBy> <OrderByColumn Ascending="false"> <ColumnReference Column="Expr1011" /> </OrderByColumn> <OrderByColumn Ascending="true"> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERDATE" /> </OrderByColumn> </OrderBy> <RelOp AvgRowSize="30" EstimateCPU="633.863" EstimateIO="999.26" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="14789600" LogicalOp="Aggregate" NodeId="1" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="13849.8"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERDATE" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_SHIPPRIORITY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> <ColumnReference Column="Expr1011" /> </OutputList> <MemoryFractions Input="0.212795" Output="0.999995" /> <Hash> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1011" /> <ScalarOperator ScalarString="SUM([Expr1012])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1012" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERDATE" /> <ScalarOperator ScalarString="ANY([tpch].[dbo].[ORDERS].[O_ORDERDATE])"> <Aggregate AggType="ANY" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERDATE" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_SHIPPRIORITY" /> <ScalarOperator ScalarString="ANY([tpch].[dbo].[ORDERS].[O_SHIPPRIORITY])"> <Aggregate AggType="ANY" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_SHIPPRIORITY" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> </DefinedValues> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> </HashKeysBuild> <BuildResidual> <ScalarOperator ScalarString="[tpch].[dbo].[LINEITEM].[L_ORDERKEY] = [tpch].[dbo].[LINEITEM].[L_ORDERKEY]"> <Compare CompareOp="IS"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </BuildResidual> <RelOp AvgRowSize="30" EstimateCPU="2474.78" EstimateIO="3260.67" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="31902100" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="12216.7"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERDATE" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_SHIPPRIORITY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> <ColumnReference Column="Expr1012" /> </OutputList> <MemoryFractions Input="0.825683" Output="0.787205" /> <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="22" EstimateCPU="374.389" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="14789600" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1600.38"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERDATE" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_SHIPPRIORITY" /> </OutputList> <MemoryFractions Input="1" Output="0.174317" /> <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="21" EstimateCPU="16.5002" EstimateIO="240.731" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3014090" LogicalOp="Clustered Index Scan" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="257.231" TableCardinality="15000000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Index="[PK_C_CUSTKEY]" IndexKind="Clustered" /> <Predicate> <ScalarOperator ScalarString="[tpch].[dbo].[CUSTOMER].[C_MKTSEGMENT]='BUILDING'"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_MKTSEGMENT" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'BUILDING'" /> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </IndexScan> </RelOp> <RelOp AvgRowSize="26" EstimateCPU="80.2232" EstimateIO="881.337" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="72930000" LogicalOp="Clustered Index Seek" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="961.56" TableCardinality="150000000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERDATE" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_SHIPPRIORITY" /> </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> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERDATE" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_SHIPPRIORITY" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Index="[O_ORDERDATE_CLUIDX]" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <EndRange ScanType="LT"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERDATE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="'1995-03-15'"> <Const ConstValue="'1995-03-15'" /> </ScalarOperator> </RangeExpressions> </EndRange> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </Hash> </RelOp> <RelOp AvgRowSize="23" EstimateCPU="32.356" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="323560000" LogicalOp="Compute Scalar" NodeId="7" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="4880.85"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> <ColumnReference Column="Expr1012" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1012" /> <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="31" EstimateCPU="355.916" EstimateIO="4492.58" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="323560000" LogicalOp="Clustered Index Seek" NodeId="8" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="4848.5" 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="GT"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SHIPDATE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="'1995-03-15'"> <Const ConstValue="'1995-03-15'" /> </ScalarOperator> </RangeExpressions> </StartRange> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </ComputeScalar> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </TopSort> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> <Batch> <Statements> <StmtSimple StatementCompId="1" StatementEstRows="5" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="10731.1" StatementText="/* TPC_H Query 4 - Order Priority Checking */&#xD;&#xA;SELECT O_ORDERPRIORITY, COUNT(*) AS ORDER_COUNT FROM ORDERS&#xD;&#xA;WHERE O_ORDERDATE &gt;= '1993-07-01' AND O_ORDERDATE &lt; CONVERT(DATE,dateadd (mm, 3, '1993-07-01')) &#xD;&#xA;AND EXISTS (SELECT * FROM LINEITEM WHERE L_ORDERKEY = O_ORDERKEY AND L_COMMITDATE &lt; L_RECEIPTDATE)&#xD;&#xA;GROUP BY O_ORDERPRIORITY ORDER BY O_ORDERPRIORITY&#xD;&#xA;" StatementType="SELECT" QueryHash="0x56329087434B9B79" QueryPlanHash="0xE66B258874415AF2"> <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="56" CompileTime="12" CompileCPU="12" CompileMemory="672"> <RelOp AvgRowSize="26" EstimateCPU="0.000118137" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5" LogicalOp="Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="10731.1"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERPRIORITY" /> <ColumnReference Column="Expr1009" /> </OutputList> <MemoryFractions Input="1" Output="1" /> <Sort Distinct="false"> <OrderBy> <OrderByColumn Ascending="true"> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERPRIORITY" /> </OrderByColumn> </OrderBy> <RelOp AvgRowSize="26" EstimateCPU="36.8069" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="10731.1"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERPRIORITY" /> <ColumnReference Column="Expr1009" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1009" /> <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1012],0)"> <Convert DataType="int" Style="0" Implicit="true"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1012" /> </Identifier> </ScalarOperator> </Convert> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="26" EstimateCPU="36.8069" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5" LogicalOp="Aggregate" NodeId="2" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="10731.1"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERPRIORITY" /> <ColumnReference Column="Expr1012" /> </OutputList> <MemoryFractions Input="0" Output="0" /> <Hash> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1012" /> <ScalarOperator ScalarString="COUNT(*)"> <Aggregate AggType="COUNT*" Distinct="false" /> </ScalarOperator> </DefinedValue> </DefinedValues> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERPRIORITY" /> </HashKeysBuild> <BuildResidual> <ScalarOperator ScalarString="[tpch].[dbo].[ORDERS].[O_ORDERPRIORITY] = [tpch].[dbo].[ORDERS].[O_ORDERPRIORITY]"> <Compare CompareOp="IS"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERPRIORITY" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERPRIORITY" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </BuildResidual> <RelOp AvgRowSize="22" EstimateCPU="1339.34" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5722730" LogicalOp="Left Semi Join" NodeId="3" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="10694.3"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERPRIORITY" /> </OutputList> <MemoryFractions Input="1" Output="1" /> <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="30" EstimateCPU="6.29516" EstimateIO="69.1602" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5722730" LogicalOp="Clustered Index Seek" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="75.4553" TableCardinality="150000000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERPRIORITY" /> </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_ORDERPRIORITY" /> </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-07-01'"> <Const ConstValue="'1993-07-01'" /> </ScalarOperator> </RangeExpressions> </StartRange> <EndRange ScanType="LT"> <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> </EndRange> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> <RelOp AvgRowSize="21" EstimateCPU="660.042" EstimateIO="8331.43" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="180011000" LogicalOp="Clustered Index Scan" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="8991.47" TableCardinality="600038000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Index="[L_SHIPDATE_CLUIDX]" IndexKind="Clustered" /> <Predicate> <ScalarOperator ScalarString="[tpch].[dbo].[LINEITEM].[L_COMMITDATE]&lt;[tpch].[dbo].[LINEITEM].[L_RECEIPTDATE]"> <Compare CompareOp="LT"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_COMMITDATE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_RECEIPTDATE" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </ComputeScalar> </RelOp> </Sort> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> <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> </Statements> </Batch> <Batch> <Statements> <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="1476.73" StatementText="/* TPC_H Query 6 - Forecasting Revenue Change */&#xD;&#xA;SELECT SUM(L_EXTENDEDPRICE*L_DISCOUNT) AS REVENUE&#xD;&#xA;FROM LINEITEM WHERE L_SHIPDATE &gt;= '1994-01-01' AND L_SHIPDATE &lt; CONVERT(DATE,dateadd (yy, 1, '1994-01-01'))&#xD;&#xA; AND L_DISCOUNT BETWEEN .06 - 0.01 AND .06 + 0.01 AND L_QUANTITY &lt; 24&#xD;&#xA;" StatementType="SELECT" QueryHash="0x999D0007450A1D7A" QueryPlanHash="0x0F687C82B4F3F147"> <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="24" CompileTime="3" CompileCPU="3" CompileMemory="344"> <RelOp AvgRowSize="15" EstimateCPU="6.83025" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1476.73"> <OutputList> <ColumnReference Column="Expr1004" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1004" /> <ScalarOperator ScalarString="CASE WHEN [Expr1011]=(0) THEN NULL ELSE [Expr1012] END"> <IF> <Condition> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1011" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(0)" /> </ScalarOperator> </Compare> </ScalarOperator> </Condition> <Then> <ScalarOperator> <Const ConstValue="NULL" /> </ScalarOperator> </Then> <Else> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1012" /> </Identifier> </ScalarOperator> </Else> </IF> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="15" EstimateCPU="6.83025" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="1" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="1476.73"> <OutputList> <ColumnReference Column="Expr1011" /> <ColumnReference Column="Expr1012" /> </OutputList> <StreamAggregate> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1011" /> <ScalarOperator ScalarString="Count(*)"> <Aggregate AggType="countstar" Distinct="false" /> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1012" /> <ScalarOperator ScalarString="SUM([Expr1005])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1005" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="15" EstimateCPU="1.13837" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="11383700" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1469.9"> <OutputList> <ColumnReference Column="Expr1005" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1005" /> <ScalarOperator ScalarString="[tpch].[dbo].[LINEITEM].[L_EXTENDEDPRICE]*[tpch].[dbo].[LINEITEM].[L_DISCOUNT]"> <Arithmetic Operation="MULT"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_EXTENDEDPRICE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_DISCOUNT" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="31" EstimateCPU="99.9474" EstimateIO="1261.59" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="11383700" LogicalOp="Clustered Index Seek" NodeId="3" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="1361.54" TableCardinality="600038000"> <OutputList> <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_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="'1994-01-01'"> <Const ConstValue="'1994-01-01'" /> </ScalarOperator> </RangeExpressions> </StartRange> <EndRange ScanType="LT"> <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> </EndRange> </SeekKeys> </SeekPredicateNew> </SeekPredicates> <Predicate> <ScalarOperator ScalarString="[tpch].[dbo].[LINEITEM].[L_DISCOUNT]&gt;=(5.000000000000000e-002) AND [tpch].[dbo].[LINEITEM].[L_DISCOUNT]&lt;=(7.000000000000001e-002) AND [tpch].[dbo].[LINEITEM].[L_QUANTITY]&lt;(2.400000000000000e+001)"> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="GE"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_DISCOUNT" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(5.000000000000000e-002)" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="LE"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_DISCOUNT" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(7.000000000000001e-002)" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Compare CompareOp="LT"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_QUANTITY" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(2.400000000000000e+001)" /> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </ComputeScalar> </RelOp> </StreamAggregate> </RelOp> </ComputeScalar> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> <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> </Statements> </Batch> <Batch> <Statements> <StmtSimple StatementCompId="1" StatementEstRows="7" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="13628.5" StatementText="/* TPC_H Query 8 - National Market Share */&#xD;&#xA;SELECT O_YEAR, SUM(CASE WHEN NATION = 'BRAZIL' THEN VOLUME ELSE 0 END) / SUM(VOLUME) AS MKT_SHARE&#xD;&#xA;FROM (SELECT datepart(yy, O_ORDERDATE) AS O_YEAR, L_EXTENDEDPRICE * (1-L_DISCOUNT) AS VOLUME, N2.N_NAME AS NATION&#xD;&#xA;FROM PART, SUPPLIER, LINEITEM, ORDERS, CUSTOMER, NATION N1, NATION N2, REGION&#xD;&#xA;WHERE P_PARTKEY = L_PARTKEY AND S_SUPPKEY = L_SUPPKEY AND L_ORDERKEY = O_ORDERKEY&#xD;&#xA; AND O_CUSTKEY = C_CUSTKEY AND C_NATIONKEY = N1.N_NATIONKEY AND N1.N_REGIONKEY = R_REGIONKEY&#xD;&#xA; AND R_NAME = 'AMERICA' AND S_NATIONKEY = N2.N_NATIONKEY&#xD;&#xA; AND O_ORDERDATE BETWEEN '1995-01-01' AND '1996-12-31' AND P_TYPE= 'ECONOMY ANODIZED STEEL') AS ALL_NATIONS&#xD;&#xA;GROUP BY O_YEAR ORDER BY O_YEAR&#xD;&#xA;" StatementType="SELECT" QueryHash="0xFCAC4808AFD306DF" QueryPlanHash="0x6B9A8CBF7E42AC29"> <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="208" CompileTime="116" CompileCPU="116" CompileMemory="3120"> <RelOp AvgRowSize="19" EstimateCPU="0.000130675" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="7" LogicalOp="Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="13628.5"> <OutputList> <ColumnReference Column="Expr1024" /> <ColumnReference Column="Expr1028" /> </OutputList> <MemoryFractions Input="1" Output="1" /> <Sort Distinct="false"> <OrderBy> <OrderByColumn Ascending="true"> <ColumnReference Column="Expr1024" /> </OrderByColumn> </OrderBy> <RelOp AvgRowSize="19" EstimateCPU="7E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="7" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="13628.5"> <OutputList> <ColumnReference Column="Expr1024" /> <ColumnReference Column="Expr1028" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1028" /> <ScalarOperator ScalarString="[Expr1026]/[Expr1027]"> <Arithmetic Operation="DIV"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1026" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1027" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="27" EstimateCPU="2.93059" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="7" LogicalOp="Aggregate" NodeId="2" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="13628.5"> <OutputList> <ColumnReference Column="Expr1024" /> <ColumnReference Column="Expr1026" /> <ColumnReference Column="Expr1027" /> </OutputList> <MemoryFractions Input="0" Output="0" /> <Hash> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1026" /> <ScalarOperator ScalarString="SUM(CASE WHEN [tpch].[dbo].[NATION].[N_NAME] as [N2].[N_NAME]='BRAZIL' THEN [tpch].[dbo].[LINEITEM].[L_EXTENDEDPRICE]*((1.000000000000000e+000)-[tpch].[dbo].[LINEITEM].[L_DISCOUNT]) ELSE (0.000000000000000e+000) END)"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <IF> <Condition> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'BRAZIL'" /> </ScalarOperator> </Compare> </ScalarOperator> </Condition> <Then> <ScalarOperator> <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> </Then> <Else> <ScalarOperator> <Const ConstValue="(0.000000000000000e+000)" /> </ScalarOperator> </Else> </IF> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1027" /> <ScalarOperator ScalarString="SUM([Expr1029])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1029" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> </DefinedValues> <HashKeysBuild> <ColumnReference Column="Expr1024" /> </HashKeysBuild> <BuildResidual> <ScalarOperator ScalarString="[Expr1024] = [Expr1024]"> <Compare CompareOp="IS"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1024" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1024" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </BuildResidual> <RelOp AvgRowSize="60" EstimateCPU="3.09299" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="453056" LogicalOp="Inner Join" NodeId="3" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="13625.5"> <OutputList> <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="[NATION]" Alias="[N2]" Column="N_NAME" /> <ColumnReference Column="Expr1024" /> <ColumnReference Column="Expr1029" /> </OutputList> <MemoryFractions Input="0" Output="0" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N2]" Column="N_NATIONKEY" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> </HashKeysProbe> <RelOp AvgRowSize="36" EstimateCPU="0.0001845" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25" LogicalOp="Clustered Index Scan" NodeId="4" 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" /> </IndexScan> </RelOp> <RelOp AvgRowSize="39" EstimateCPU="11.7733" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="596213" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="13622.4"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_EXTENDEDPRICE" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_DISCOUNT" /> <ColumnReference Column="Expr1024" /> <ColumnReference Column="Expr1029" /> </OutputList> <MemoryFractions Input="0.0281812" Output="1" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SUPPKEY" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_SUPPKEY" /> </HashKeysProbe> <RelOp AvgRowSize="39" EstimateCPU="13.8809" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="293489" LogicalOp="Inner Join" NodeId="6" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="13608.6"> <OutputList> <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 Column="Expr1024" /> <ColumnReference Column="Expr1029" /> </OutputList> <MemoryFractions Input="0" Output="0" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[REGION]" Column="R_REGIONKEY" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_REGIONKEY" /> </HashKeysProbe> <RelOp AvgRowSize="36" EstimateCPU="0.0001625" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="7" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032875" TableCardinality="5"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[REGION]" Column="R_REGIONKEY" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[REGION]" Column="R_REGIONKEY" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[REGION]" Index="[PK_R_REGIONKEY]" IndexKind="Clustered" /> <Predicate> <ScalarOperator ScalarString="[tpch].[dbo].[REGION].[R_NAME]='AMERICA'"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[REGION]" Column="R_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'AMERICA'" /> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </IndexScan> </RelOp> <RelOp AvgRowSize="43" EstimateCPU="20.2335" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="2981070" LogicalOp="Inner Join" NodeId="8" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="13594.7"> <OutputList> <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="[NATION]" Alias="[N1]" Column="N_REGIONKEY" /> <ColumnReference Column="Expr1024" /> <ColumnReference Column="Expr1029" /> </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="[CUSTOMER]" Column="C_NATIONKEY" /> </HashKeysProbe> <RelOp AvgRowSize="15" EstimateCPU="0.0001845" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25" 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="[N1]" Column="N_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Alias="[N1]" Column="N_REGIONKEY" /> </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_REGIONKEY" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[NATION]" Index="[PK_N_NATIONKEY]" Alias="[N1]" IndexKind="Clustered" /> </IndexScan> </RelOp> <RelOp AvgRowSize="43" EstimateCPU="169.188" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3919550" LogicalOp="Inner Join" NodeId="10" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="13574.4"> <OutputList> <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="[CUSTOMER]" Column="C_NATIONKEY" /> <ColumnReference Column="Expr1024" /> <ColumnReference Column="Expr1029" /> </OutputList> <MemoryFractions Input="0.394804" Output="0.971819" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> </HashKeysProbe> <RelOp AvgRowSize="43" EstimateCPU="440.701" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3919550" LogicalOp="Inner Join" NodeId="11" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="13148"> <OutputList> <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="[ORDERS]" Column="O_CUSTKEY" /> <ColumnReference Column="Expr1024" /> <ColumnReference Column="Expr1029" /> </OutputList> <MemoryFractions Input="0.809651" Output="0.605196" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_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="43" EstimateCPU="2749.47" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3919550" LogicalOp="Inner Join" NodeId="12" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="12102.4"> <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 Column="Expr1029" /> </OutputList> <MemoryFractions Input="1" Output="0.190349" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_PARTKEY" /> </HashKeysProbe> <RelOp AvgRowSize="35" EstimateCPU="22.0001" EstimateIO="269.885" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="130644" LogicalOp="Clustered Index Scan" NodeId="13" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="291.885" TableCardinality="20000000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> </OutputList> <IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[PART]" Index="[PK_P_PARTKEY]" IndexKind="Clustered" /> <Predicate> <ScalarOperator ScalarString="[tpch].[dbo].[PART].[P_TYPE]='ECONOMY ANODIZED STEEL'"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_TYPE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'ECONOMY ANODIZED STEEL'" /> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </IndexScan> </RelOp> <RelOp AvgRowSize="47" 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_PARTKEY" /> <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 Column="Expr1029" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1029" /> <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="39" 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_PARTKEY" /> <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_PARTKEY" /> </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> <RelOp AvgRowSize="23" EstimateCPU="4.55323" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="45532300" LogicalOp="Compute Scalar" NodeId="20" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="604.884"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> <ColumnReference Column="Expr1024" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1024" /> <ScalarOperator ScalarString="datepart(year,[tpch].[dbo].[ORDERS].[O_ORDERDATE])"> <Intrinsic FunctionName="datepart"> <ScalarOperator> <Const ConstValue="(0)" /> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERDATE" /> </Identifier> </ScalarOperator> </Intrinsic> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="22" EstimateCPU="50.0857" EstimateIO="550.245" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="45532300" LogicalOp="Clustered Index Seek" NodeId="21" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="600.33" TableCardinality="150000000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERDATE" /> </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> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERDATE" /> </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="'1995-01-01'"> <Const ConstValue="'1995-01-01'" /> </ScalarOperator> </RangeExpressions> </StartRange> <EndRange ScanType="LE"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERDATE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="'1996-12-31'"> <Const ConstValue="'1996-12-31'" /> </ScalarOperator> </RangeExpressions> </EndRange> </SeekKeys> </SeekPredicateNew> </SeekPredicates> </IndexScan> </RelOp> </ComputeScalar> </RelOp> </Hash> </RelOp> <RelOp AvgRowSize="15" EstimateCPU="16.5002" EstimateIO="240.731" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="15000000" LogicalOp="Clustered Index Scan" NodeId="26" 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> </Hash> </RelOp> </Hash> </RelOp> <RelOp AvgRowSize="15" EstimateCPU="1.10016" EstimateIO="1.01201" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1000000" LogicalOp="Index Scan" NodeId="30" 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> </Hash> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </ComputeScalar> </RelOp> </Sort> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> <Batch> <Statements> <StmtSimple StatementCompId="1" StatementEstRows="132.981" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="24960.7" StatementText="/* TPC_H Query 9 - Product Type Profit Measure */&#xD;&#xA;SELECT NATION, O_YEAR, SUM(AMOUNT) AS SUM_PROFIT&#xD;&#xA;FROM (SELECT N_NAME AS NATION, datepart(yy, O_ORDERDATE) AS O_YEAR, L_EXTENDEDPRICE*(1-L_DISCOUNT)-PS_SUPPLYCOST*L_QUANTITY AS AMOUNT&#xD;&#xA; FROM PART, SUPPLIER, LINEITEM, PARTSUPP, ORDERS, NATION&#xD;&#xA; WHERE S_SUPPKEY = L_SUPPKEY AND PS_SUPPKEY= L_SUPPKEY AND PS_PARTKEY = L_PARTKEY AND&#xD;&#xA; P_PARTKEY= L_PARTKEY AND O_ORDERKEY = L_ORDERKEY AND S_NATIONKEY = N_NATIONKEY AND P_NAME LIKE '%green%') AS PROFIT&#xD;&#xA;GROUP BY NATION, O_YEAR&#xD;&#xA;ORDER BY NATION, O_YEAR DESC&#xD;&#xA;" StatementType="SELECT" QueryHash="0xAAE1793BEB23CCED" QueryPlanHash="0xBBB5439EBF7AAFE8"> <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="142" CompileCPU="142" CompileMemory="3976"> <RelOp AvgRowSize="44" EstimateCPU="0.00156362" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="132.981" LogicalOp="Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="24960.7"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Column="Expr1020" /> <ColumnReference Column="Expr1022" /> </OutputList> <MemoryFractions Input="1" Output="1" /> <Sort Distinct="false"> <OrderBy> <OrderByColumn Ascending="true"> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </OrderByColumn> <OrderByColumn Ascending="false"> <ColumnReference Column="Expr1020" /> </OrderByColumn> </OrderBy> <RelOp AvgRowSize="44" EstimateCPU="704.23" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="132.981" LogicalOp="Aggregate" NodeId="1" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="24960.7"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Column="Expr1020" /> <ColumnReference Column="Expr1022" /> </OutputList> <MemoryFractions Input="0" Output="0" /> <Hash> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1022" /> <ScalarOperator ScalarString="SUM([Expr1023]-[tpch].[dbo].[PARTSUPP].[PS_SUPPLYCOST]*[tpch].[dbo].[LINEITEM].[L_QUANTITY])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Arithmetic Operation="SUB"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1023" /> </Identifier> </ScalarOperator> <ScalarOperator> <Arithmetic Operation="MULT"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPLYCOST" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_QUANTITY" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> </Arithmetic> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> </DefinedValues> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Column="Expr1020" /> </HashKeysBuild> <BuildResidual> <ScalarOperator ScalarString="[tpch].[dbo].[NATION].[N_NAME] = [tpch].[dbo].[NATION].[N_NAME] AND [Expr1020] = [Expr1020]"> <Logical Operation="AND"> <ScalarOperator> <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> <ScalarOperator> <Compare CompareOp="IS"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1020" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1020" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Logical> </ScalarOperator> </BuildResidual> <RelOp AvgRowSize="60" EstimateCPU="233.907" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="105443000" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="24256.5"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_QUANTITY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPLYCOST" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Column="Expr1020" /> <ColumnReference Column="Expr1023" /> </OutputList> <Merge ManyToMany="false"> <InnerSideJoinColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_SUPPKEY" /> </InnerSideJoinColumns> <OuterSideJoinColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_PARTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPKEY" /> </OuterSideJoinColumns> <Residual> <ScalarOperator ScalarString="[tpch].[dbo].[PARTSUPP].[PS_SUPPKEY]=[tpch].[dbo].[SUPPLIER].[S_SUPPKEY] AND [tpch].[dbo].[PARTSUPP].[PS_PARTKEY]=[tpch].[dbo].[PART].[P_PARTKEY]"> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPKEY" /> </Identifier>