ÿþ<?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="9124.15" 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="0xF4B7FC3FA3629490"> <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="13" CompileCPU="13" CompileMemory="760"> <RelOp AvgRowSize="70" EstimateCPU="5.82801E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5.82801" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="9124.15"> <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 [globalagg1019]=(0) THEN NULL ELSE [globalagg1021]/CONVERT_IMPLICIT(float(53),[globalagg1019],0) END"> <IF> <Condition> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Column="globalagg1019" /> </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="globalagg1021" /> </Identifier> </ScalarOperator> <ScalarOperator> <Convert DataType="float" Scale="0" Style="0" Implicit="true"> <ScalarOperator> <Identifier> <ColumnReference Column="globalagg1019" /> </Identifier> </ScalarOperator> </Convert> </ScalarOperator> </Arithmetic> </ScalarOperator> </Else> </IF> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1009" /> <ScalarOperator ScalarString="CASE WHEN [globalagg1023]=(0) THEN NULL ELSE [globalagg1025]/CONVERT_IMPLICIT(float(53),[globalagg1023],0) END"> <IF> <Condition> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Column="globalagg1023" /> </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="globalagg1025" /> </Identifier> </ScalarOperator> <ScalarOperator> <Convert DataType="float" Scale="0" Style="0" Implicit="true"> <ScalarOperator> <Identifier> <ColumnReference Column="globalagg1023" /> </Identifier> </ScalarOperator> </Convert> </ScalarOperator> </Arithmetic> </ScalarOperator> </Else> </IF> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1010" /> <ScalarOperator ScalarString="CASE WHEN [globalagg1027]=(0) THEN NULL ELSE [globalagg1029]/CONVERT_IMPLICIT(float(53),[globalagg1027],0) END"> <IF> <Condition> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Column="globalagg1027" /> </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="globalagg1029" /> </Identifier> </ScalarOperator> <ScalarOperator> <Convert DataType="float" Scale="0" Style="0" Implicit="true"> <ScalarOperator> <Identifier> <ColumnReference Column="globalagg1027" /> </Identifier> </ScalarOperator> </Convert> </ScalarOperator> </Arithmetic> </ScalarOperator> </Else> </IF> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1011" /> <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[globalagg1031],0)"> <Convert DataType="int" Style="0" Implicit="true"> <ScalarOperator> <Identifier> <ColumnReference Column="globalagg1031" /> </Identifier> </ScalarOperator> </Convert> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="98" EstimateCPU="1.69012E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5.82801" LogicalOp="Aggregate" NodeId="2" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="9124.15"> <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="globalagg1019" /> <ColumnReference Column="globalagg1021" /> <ColumnReference Column="globalagg1023" /> <ColumnReference Column="globalagg1025" /> <ColumnReference Column="globalagg1027" /> <ColumnReference Column="globalagg1029" /> <ColumnReference Column="globalagg1031" /> </OutputList> <StreamAggregate> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1004" /> <ScalarOperator ScalarString="SUM([partialagg1014])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="partialagg1014" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1005" /> <ScalarOperator ScalarString="SUM([partialagg1015])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="partialagg1015" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1006" /> <ScalarOperator ScalarString="SUM([partialagg1016])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="partialagg1016" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="Expr1007" /> <ScalarOperator ScalarString="SUM([partialagg1017])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="partialagg1017" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="globalagg1019" /> <ScalarOperator ScalarString="SUM([partialagg1018])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="partialagg1018" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="globalagg1021" /> <ScalarOperator ScalarString="SUM([partialagg1020])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="partialagg1020" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="globalagg1023" /> <ScalarOperator ScalarString="SUM([partialagg1022])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="partialagg1022" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="globalagg1025" /> <ScalarOperator ScalarString="SUM([partialagg1024])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="partialagg1024" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="globalagg1027" /> <ScalarOperator ScalarString="SUM([partialagg1026])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="partialagg1026" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="globalagg1029" /> <ScalarOperator ScalarString="SUM([partialagg1028])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="partialagg1028" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="globalagg1031" /> <ScalarOperator ScalarString="SUM([partialagg1030])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="partialagg1030" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> </DefinedValues> <GroupBy> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_RETURNFLAG" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_LINESTATUS" /> </GroupBy> <RelOp AvgRowSize="98" EstimateCPU="0.000265312" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="23.312" LogicalOp="Sort" NodeId="3" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="9124.15"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_RETURNFLAG" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_LINESTATUS" /> <ColumnReference Column="partialagg1014" /> <ColumnReference Column="partialagg1015" /> <ColumnReference Column="partialagg1016" /> <ColumnReference Column="partialagg1017" /> <ColumnReference Column="partialagg1018" /> <ColumnReference Column="partialagg1020" /> <ColumnReference Column="partialagg1022" /> <ColumnReference Column="partialagg1024" /> <ColumnReference Column="partialagg1026" /> <ColumnReference Column="partialagg1028" /> <ColumnReference Column="partialagg1030" /> </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="98" EstimateCPU="0.0285797" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="23.312" LogicalOp="Gather Streams" NodeId="4" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="9124.14"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_RETURNFLAG" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_LINESTATUS" /> <ColumnReference Column="partialagg1014" /> <ColumnReference Column="partialagg1015" /> <ColumnReference Column="partialagg1016" /> <ColumnReference Column="partialagg1017" /> <ColumnReference Column="partialagg1018" /> <ColumnReference Column="partialagg1020" /> <ColumnReference Column="partialagg1022" /> <ColumnReference Column="partialagg1024" /> <ColumnReference Column="partialagg1026" /> <ColumnReference Column="partialagg1028" /> <ColumnReference Column="partialagg1030" /> </OutputList> <Parallelism> <RelOp AvgRowSize="98" EstimateCPU="735.541" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="23.312" LogicalOp="Compute Scalar" NodeId="5" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="9124.11"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_RETURNFLAG" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_LINESTATUS" /> <ColumnReference Column="partialagg1014" /> <ColumnReference Column="partialagg1015" /> <ColumnReference Column="partialagg1016" /> <ColumnReference Column="partialagg1017" /> <ColumnReference Column="partialagg1018" /> <ColumnReference Column="partialagg1020" /> <ColumnReference Column="partialagg1022" /> <ColumnReference Column="partialagg1024" /> <ColumnReference Column="partialagg1026" /> <ColumnReference Column="partialagg1028" /> <ColumnReference Column="partialagg1030" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="partialagg1020" /> <ScalarOperator ScalarString="[partialagg1014]"> <Identifier> <ColumnReference Column="partialagg1014" /> </Identifier> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="partialagg1022" /> <ScalarOperator ScalarString="[partialagg1018]"> <Identifier> <ColumnReference Column="partialagg1018" /> </Identifier> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="partialagg1024" /> <ScalarOperator ScalarString="[partialagg1015]"> <Identifier> <ColumnReference Column="partialagg1015" /> </Identifier> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="partialagg1026" /> <ScalarOperator ScalarString="[partialagg1018]"> <Identifier> <ColumnReference Column="partialagg1018" /> </Identifier> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="partialagg1030" /> <ScalarOperator ScalarString="[partialagg1018]"> <Identifier> <ColumnReference Column="partialagg1018" /> </Identifier> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="98" EstimateCPU="735.541" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="23.312" LogicalOp="Aggregate" NodeId="6" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="9124.11"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_RETURNFLAG" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_LINESTATUS" /> <ColumnReference Column="partialagg1014" /> <ColumnReference Column="partialagg1015" /> <ColumnReference Column="partialagg1016" /> <ColumnReference Column="partialagg1017" /> <ColumnReference Column="partialagg1018" /> <ColumnReference Column="partialagg1028" /> </OutputList> <MemoryFractions Input="0" Output="0" /> <Hash> <DefinedValues> <DefinedValue> <ColumnReference Column="partialagg1014" /> <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="partialagg1015" /> <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="partialagg1016" /> <ScalarOperator ScalarString="SUM([Expr1012])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1012" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="partialagg1017" /> <ScalarOperator ScalarString="SUM([Expr1013])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1013" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="partialagg1018" /> <ScalarOperator ScalarString="COUNT(*)"> <Aggregate AggType="COUNT*" Distinct="false" /> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="partialagg1028" /> <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="14.7844" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="591375000" LogicalOp="Compute Scalar" NodeId="7" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="8388.57"> <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="162.628" EstimateIO="8211.15" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="591375000" LogicalOp="Clustered Index Seek" NodeId="8" Parallel="true" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="8373.78" 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> </Parallelism> </RelOp> </Sort> </RelOp> </StreamAggregate> </RelOp> </ComputeScalar> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> <Batch> <Statements> <StmtSimple StatementCompId="1" StatementEstRows="100" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="551.115" 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="0xEC44808C1C2E59AF"> <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /> <QueryPlan CachedPlanSize="160" CompileTime="292" CompileCPU="292" CompileMemory="5456"> <RelOp AvgRowSize="185" EstimateCPU="1E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="100" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="551.115"> <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.0294594" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="100" LogicalOp="Gather Streams" NodeId="1" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="551.115"> <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> <Parallelism> <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="185" EstimateCPU="0.896849" EstimateIO="0.00281532" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="100" LogicalOp="TopN Sort" NodeId="2" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="551.086"> <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> <MemoryFractions Input="0.000548847" Output="1" /> <TopSort Distinct="false" Rows="100"> <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="185" EstimateCPU="0.000500624" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="82351.4" LogicalOp="Top" NodeId="4" Parallel="true" PhysicalOp="Top" EstimatedTotalSubtreeCost="550.186"> <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" Rows="1" IsPercent="false" WithTies="true"> <TieColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPLYCOST" /> </TieColumns> <RelOp AvgRowSize="198" EstimateCPU="0.0020025" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="50062.4" LogicalOp="Segment" NodeId="5" Parallel="true" PhysicalOp="Segment" EstimatedTotalSubtreeCost="550.186"> <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" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPLYCOST" /> <ColumnReference Column="Segment1036" /> </OutputList> <Segment> <GroupBy> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> </GroupBy> <SegmentColumn> <ColumnReference Column="Segment1036" /> </SegmentColumn> <RelOp AvgRowSize="198" EstimateCPU="0.896849" EstimateIO="0.00281532" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="50062.4" LogicalOp="Sort" NodeId="7" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="550.184"> <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.124949" Output="0.999451" /> <Sort Distinct="false"> <OrderBy> <OrderByColumn Ascending="true"> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> </OrderByColumn> <OrderByColumn Ascending="true"> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPLYCOST" /> </OrderByColumn> </OrderBy> <RelOp AvgRowSize="198" EstimateCPU="0.274385" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="50062.4" LogicalOp="Repartition Streams" NodeId="8" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="549.284"> <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> <Parallelism PartitioningType="Hash"> <PartitionColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_PARTKEY" /> </PartitionColumns> <RelOp AvgRowSize="198" EstimateCPU="4.07886" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="50062.4" LogicalOp="Inner Join" NodeId="9" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="549.01"> <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.764683" Output="0.669137" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_SUPPKEY" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPKEY" /> </HashKeysProbe> <RelOp AvgRowSize="160" EstimateCPU="0.807262" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="200000" LogicalOp="Bitmap Create" NodeId="10" Parallel="true" PhysicalOp="Bitmap" EstimatedTotalSubtreeCost="17.2192"> <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_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> <Bitmap> <DefinedValues> <DefinedValue> <ColumnReference Column="Opt_Bitmap1033" /> </DefinedValue> </DefinedValues> <HashKeys> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_SUPPKEY" /> </HashKeys> <RelOp AvgRowSize="160" EstimateCPU="0.807262" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="200000" LogicalOp="Repartition Streams" NodeId="11" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="17.2192"> <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_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> <Parallelism PartitioningType="Hash"> <PartitionColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_SUPPKEY" /> </PartitionColumns> <RelOp AvgRowSize="160" EstimateCPU="1.44659" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="200000" LogicalOp="Inner Join" NodeId="12" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="16.4119"> <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_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> <MemoryFractions Input="1" Output="0.235317" /> <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="36" EstimateCPU="0.0285204" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5" LogicalOp="Bitmap Create" NodeId="13" Parallel="true" PhysicalOp="Bitmap" EstimatedTotalSubtreeCost="0.0390239"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </OutputList> <Bitmap> <DefinedValues> <DefinedValue> <ColumnReference Column="Bitmap1034" /> </DefinedValue> </DefinedValues> <HashKeys> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> </HashKeys> <RelOp AvgRowSize="36" EstimateCPU="0.0285204" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5" LogicalOp="Distribute Streams" NodeId="14" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0390239"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </OutputList> <Parallelism PartitioningType="Broadcast"> <RelOp AvgRowSize="36" EstimateCPU="0.0001045" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5" LogicalOp="Inner Join" NodeId="15" 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="16" 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="32" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="20" EstimateRewinds="4" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="17" 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]" TableReferenceId="-1" 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> </Parallelism> </RelOp> </Bitmap> </RelOp> <RelOp AvgRowSize="139" EstimateCPU="0.275039" EstimateIO="14.6513" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1000000" LogicalOp="Clustered Index Scan" NodeId="18" Parallel="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="14.9263" 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" /> <Predicate> <ScalarOperator ScalarString="PROBE([Bitmap1034],[tpch].[dbo].[SUPPLIER].[S_NATIONKEY],N'[IN ROW]')"> <Intrinsic FunctionName="PROBE"> <ScalarOperator> <Identifier> <ColumnReference Column="Bitmap1034" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="N'[IN ROW]'" /> </ScalarOperator> </Intrinsic> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </Hash> </RelOp> </Parallelism> </RelOp> </Bitmap> </RelOp> <RelOp AvgRowSize="52" EstimateCPU="0.118077" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="50062.4" LogicalOp="Repartition Streams" NodeId="20" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="527.712"> <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> <Parallelism PartitioningType="Hash"> <PartitionColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPKEY" /> </PartitionColumns> <RelOp AvgRowSize="52" EstimateCPU="0.546319" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="50062.4" LogicalOp="Inner Join" NodeId="21" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="527.594"> <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> <MemoryFractions Input="0.18231" Output="0.159531" /> <Hash> <DefinedValues /> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> </HashKeysBuild> <HashKeysProbe> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_PARTKEY" /> </HashKeysProbe> <RelOp AvgRowSize="36" EstimateCPU="0.123292" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="82351.4" LogicalOp="Bitmap Create" NodeId="22" Parallel="true" PhysicalOp="Bitmap" EstimatedTotalSubtreeCost="281.908"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_MFGR" /> </OutputList> <Bitmap> <DefinedValues> <DefinedValue> <ColumnReference Column="Opt_Bitmap1032" /> </DefinedValue> </DefinedValues> <HashKeys> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> </HashKeys> <RelOp AvgRowSize="36" EstimateCPU="0.123292" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="82351.4" LogicalOp="Repartition Streams" NodeId="23" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="281.908"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_MFGR" /> </OutputList> <Parallelism PartitioningType="Hash"> <PartitionColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PART]" Column="P_PARTKEY" /> </PartitionColumns> <RelOp AvgRowSize="64" EstimateCPU="5.50004" EstimateIO="269.885" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="82351.4" LogicalOp="Clustered Index Scan" NodeId="24" Parallel="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="275.385" 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]" TableReferenceId="-1" 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> </Parallelism> </RelOp> </Bitmap> </RelOp> <RelOp AvgRowSize="23" EstimateCPU="0.0809435" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="50062.4" LogicalOp="Repartition Streams" NodeId="25" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="245.139"> <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> <Parallelism PartitioningType="Hash"> <PartitionColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_PARTKEY" /> </PartitionColumns> <RelOp AvgRowSize="23" EstimateCPU="0.0523152" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="50062.4" LogicalOp="Inner Join" NodeId="26" Parallel="true" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="245.058"> <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> <NestedLoops Optimized="true" WithUnorderedPrefetch="true"> <OuterReferences> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_PARTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPKEY" /> <ColumnReference Column="Expr1035" /> </OuterReferences> <RelOp AvgRowSize="15" EstimateCPU="22" EstimateIO="80.5209" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="80000000" LogicalOp="Index Scan" NodeId="30" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="102.521" TableCardinality="80000000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_PARTKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPKEY" /> </OutputList> <IndexScan Ordered="false" 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> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Index="[PS_SUPPKEY_IDX]" TableReferenceId="-1" IndexKind="NonClustered" /> <Predicate> <ScalarOperator ScalarString="PROBE([Opt_Bitmap1032],[tpch].[dbo].[PARTSUPP].[PS_PARTKEY],N'[IN ROW]') AND PROBE([Opt_Bitmap1033],[tpch].[dbo].[PARTSUPP].[PS_SUPPKEY],N'[IN ROW]')"> <Logical Operation="AND"> <ScalarOperator> <Intrinsic FunctionName="PROBE"> <ScalarOperator> <Identifier> <ColumnReference Column="Opt_Bitmap1032" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_PARTKEY" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="N'[IN ROW]'" /> </ScalarOperator> </Intrinsic> </ScalarOperator> <ScalarOperator> <Intrinsic FunctionName="PROBE"> <ScalarOperator> <Identifier> <ColumnReference Column="Opt_Bitmap1033" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPKEY" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="N'[IN ROW]'" /> </ScalarOperator> </Intrinsic> </ScalarOperator> </Logical> </ScalarOperator> </Predicate> </IndexScan> </RelOp> <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="50061.4" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="33" Parallel="true" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="124.885" TableCardinality="80000000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPLYCOST" /> </OutputList> <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <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" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_SUPPKEY" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="[tpch].[dbo].[PARTSUPP].[PS_PARTKEY]"> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[PARTSUPP]" Column="PS_PARTKEY" /> </Identifier> </ScalarOperator> <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> </Parallelism> </RelOp> </Hash> </RelOp> </Parallelism> </RelOp> </Hash> </RelOp> </Parallelism> </RelOp> </Sort> </RelOp> </Segment> </RelOp> </Top> </RelOp> </TopSort> </RelOp> </Parallelism> </RelOp> </Top> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> <Batch> <Statements> <StmtSimple StatementCompId="1" StatementEstRows="10" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="1233.47" 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="0x989041001D6A43F3"> <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="104" CompileTime="64" CompileCPU="64" CompileMemory="1448"> <RelOp AvgRowSize="30" EstimateCPU="1E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="1233.47"> <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> <Top RowCount="false" IsPercent="false" WithTies="false"> <TopExpression> <ScalarOperator ScalarString="(10)"> <Const ConstValue="(10)" /> </ScalarOperator> </TopExpression> <RelOp AvgRowSize="30" EstimateCPU="0.0285801" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10" LogicalOp="Gather Streams" NodeId="1" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="1233.47"> <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> <Parallelism> <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="81.8786" EstimateIO="0.00281532" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="10" LogicalOp="TopN Sort" NodeId="2" Parallel="true" PhysicalOp="Sort" EstimatedTotalSubtreeCost="1233.44"> <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="2.14055E-05" 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="33.3887" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3295480" LogicalOp="Aggregate" NodeId="3" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1151.56"> <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.230283" Output="0.999979" /> <Hash> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1011" /> <ScalarOperator ScalarString="SUM([Expr1015])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1015" /> </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="53.4036" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4066930" LogicalOp="Inner Join" NodeId="4" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1118.17"> <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="Expr1015" /> </OutputList> <MemoryFractions Input="0.658252" Output="0.769717" /> <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="4.42002" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4066920" LogicalOp="Bitmap Create" NodeId="5" Parallel="true" PhysicalOp="Bitmap" EstimatedTotalSubtreeCost="404.01"> <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> <Bitmap> <DefinedValues> <DefinedValue> <ColumnReference Column="Opt_Bitmap1016" /> </DefinedValue> </DefinedValues> <HashKeys> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> </HashKeys> <RelOp AvgRowSize="22" EstimateCPU="4.42002" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4066920" LogicalOp="Repartition Streams" NodeId="6" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="404.01"> <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> <Parallelism PartitioningType="Hash"> <PartitionColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> </PartitionColumns> <RelOp AvgRowSize="22" EstimateCPU="21.3128" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4066920" LogicalOp="Inner Join" NodeId="7" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="399.59"> <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.341748" /> <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="11" EstimateCPU="1.83413" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3014090" LogicalOp="Bitmap Create" NodeId="8" Parallel="true" PhysicalOp="Bitmap" EstimatedTotalSubtreeCost="248.49"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> </OutputList> <Bitmap> <DefinedValues> <DefinedValue> <ColumnReference Column="Bitmap1018" /> </DefinedValue> </DefinedValues> <HashKeys> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> </HashKeys> <RelOp AvgRowSize="11" EstimateCPU="1.83413" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3014090" LogicalOp="Repartition Streams" NodeId="9" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="248.49"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> </OutputList> <Parallelism PartitioningType="Hash"> <PartitionColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> </PartitionColumns> <RelOp AvgRowSize="21" EstimateCPU="4.12504" EstimateIO="240.731" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3014090" LogicalOp="Clustered Index Scan" NodeId="10" Parallel="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="244.856" 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> </Parallelism> </RelOp> </Bitmap> </RelOp> <RelOp AvgRowSize="26" EstimateCPU="11.5842" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9471440" LogicalOp="Repartition Streams" NodeId="11" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="129.787"> <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> <Parallelism PartitioningType="Hash"> <PartitionColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> </PartitionColumns> <RelOp AvgRowSize="26" EstimateCPU="2.60469" EstimateIO="114.462" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="9471440" LogicalOp="Clustered Index Seek" NodeId="12" Parallel="true" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="117.066" 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> <StartRange ScanType="GE"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERDATE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="'1994-10-15'"> <Const ConstValue="'1994-10-15'" /> </ScalarOperator> </RangeExpressions> </StartRange> <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> <Predicate> <ScalarOperator ScalarString="[tpch].[dbo].[ORDERS].[O_ORDERDATE]&lt;'1995-04-13' AND PROBE([Bitmap1018],[tpch].[dbo].[ORDERS].[O_CUSTKEY],N'[IN ROW]')"> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="LT"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERDATE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'1995-04-13'" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Intrinsic FunctionName="PROBE"> <ScalarOperator> <Identifier> <ColumnReference Column="Bitmap1018" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="N'[IN ROW]'" /> </ScalarOperator> </Intrinsic> </ScalarOperator> </Logical> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </Parallelism> </RelOp> </Hash> </RelOp> </Parallelism> </RelOp> </Bitmap> </RelOp> <RelOp AvgRowSize="23" EstimateCPU="0.406718" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="16268700" LogicalOp="Compute Scalar" NodeId="14" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="660.76"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> <ColumnReference Column="Expr1015" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1015" /> <ScalarOperator ScalarString="[tpch].[dbo].[LINEITEM].[L_EXTENDEDPRICE]*((1.000000000000000e+000)-[tpch].[dbo].[LINEITEM].[L_DISCOUNT])"> <Arithmetic Operation="MULT"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_EXTENDEDPRICE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Arithmetic Operation="SUB"> <ScalarOperator> <Const ConstValue="(1.000000000000000e+000)" /> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_DISCOUNT" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> </Arithmetic> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="31" EstimateCPU="20.1214" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="16268700" LogicalOp="Repartition Streams" NodeId="15" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="660.353"> <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> <Parallelism PartitioningType="Hash"> <PartitionColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> </PartitionColumns> <RelOp AvgRowSize="34" EstimateCPU="12.2438" EstimateIO="618.193" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="16268700" LogicalOp="Clustered Index Seek" NodeId="17" Parallel="true" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="630.437" 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> <EndRange ScanType="LT"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SHIPDATE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="'1995-09-10'"> <Const ConstValue="'1995-09-10'" /> </ScalarOperator> </RangeExpressions> </EndRange> </SeekKeys> </SeekPredicateNew> </SeekPredicates> <Predicate> <ScalarOperator ScalarString="[tpch].[dbo].[LINEITEM].[L_SHIPDATE]&gt;='1995-03-14' AND PROBE([Opt_Bitmap1016],[tpch].[dbo].[LINEITEM].[L_ORDERKEY],N'[IN ROW]')"> <Logical Operation="AND"> <ScalarOperator> <Compare CompareOp="GE"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SHIPDATE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="'1995-03-14'" /> </ScalarOperator> </Compare> </ScalarOperator> <ScalarOperator> <Intrinsic FunctionName="PROBE"> <ScalarOperator> <Identifier> <ColumnReference Column="Opt_Bitmap1016" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="N'[IN ROW]'" /> </ScalarOperator> </Intrinsic> </ScalarOperator> </Logical> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </Parallelism> </RelOp> </ComputeScalar> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </TopSort> </RelOp> </Parallelism> </RelOp> </Top> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> <Batch> <Statements> <StmtSimple StatementCompId="1" StatementEstRows="5" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="1147.84" 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="0x17D20A72D71A6102"> <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="44" CompileCPU="44" CompileMemory="1040"> <RelOp AvgRowSize="26" EstimateCPU="5E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1147.84"> <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,[globalagg1014],0)"> <Convert DataType="int" Style="0" Implicit="true"> <ScalarOperator> <Identifier> <ColumnReference Column="globalagg1014" /> </Identifier> </ScalarOperator> </Convert> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="30" EstimateCPU="1.45E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5" LogicalOp="Aggregate" NodeId="2" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="1147.84"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERPRIORITY" /> <ColumnReference Column="globalagg1014" /> </OutputList> <StreamAggregate> <DefinedValues> <DefinedValue> <ColumnReference Column="globalagg1014" /> <ScalarOperator ScalarString="SUM([partialagg1013])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="partialagg1013" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> </DefinedValues> <GroupBy> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERPRIORITY" /> </GroupBy> <RelOp AvgRowSize="30" EstimateCPU="0.000234874" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="20" LogicalOp="Sort" NodeId="3" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="1147.84"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERPRIORITY" /> <ColumnReference Column="partialagg1013" /> </OutputList> <MemoryFractions Input="2.43552E-05" Output="1" /> <Sort Distinct="false"> <OrderBy> <OrderByColumn Ascending="true"> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERPRIORITY" /> </OrderByColumn> </OrderBy> <RelOp AvgRowSize="30" EstimateCPU="0.0285037" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="20" LogicalOp="Gather Streams" NodeId="4" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="1147.83"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERPRIORITY" /> <ColumnReference Column="partialagg1013" /> </OutputList> <Parallelism> <RelOp AvgRowSize="30" EstimateCPU="15.2207" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="20" LogicalOp="Partial Aggregate" NodeId="5" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1147.8"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERPRIORITY" /> <ColumnReference Column="partialagg1013" /> </OutputList> <MemoryFractions Input="0" Output="0" /> <Hash> <DefinedValues> <DefinedValue> <ColumnReference Column="partialagg1013" /> <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="78.4602" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5722730" LogicalOp="Left Semi Join" NodeId="6" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1132.58"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERPRIORITY" /> </OutputList> <MemoryFractions Input="1" Output="0.999976" /> <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.30669" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5722730" LogicalOp="Bitmap Create" NodeId="7" Parallel="true" PhysicalOp="Bitmap" EstimatedTotalSubtreeCost="77.0406"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERPRIORITY" /> </OutputList> <Bitmap> <DefinedValues> <DefinedValue> <ColumnReference Column="Bitmap1015" /> </DefinedValue> </DefinedValues> <HashKeys> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> </HashKeys> <RelOp AvgRowSize="30" EstimateCPU="6.30669" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5722730" LogicalOp="Repartition Streams" NodeId="8" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="77.0406"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERPRIORITY" /> </OutputList> <Parallelism PartitioningType="Hash"> <PartitionColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> </PartitionColumns> <RelOp AvgRowSize="30" EstimateCPU="1.57379" EstimateIO="69.1602" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5722730" LogicalOp="Clustered Index Seek" NodeId="9" Parallel="true" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="70.734" 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> </Parallelism> </RelOp> </Bitmap> </RelOp> <RelOp AvgRowSize="15" EstimateCPU="14.5388" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="20221700" LogicalOp="Repartition Streams" NodeId="10" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="977.083"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> </OutputList> <Parallelism PartitioningType="Hash"> <PartitionColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> </PartitionColumns> <RelOp AvgRowSize="21" EstimateCPU="18.5366" EstimateIO="935.919" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="20221700" LogicalOp="Clustered Index Seek" NodeId="11" Parallel="true" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="954.455" TableCardinality="600038000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> </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> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Index="[L_SHIPDATE_CLUIDX]" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <StartRange ScanType="GE"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SHIPDATE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="'1993-06-22'"> <Const ConstValue="'1993-06-22'" /> </ScalarOperator> </RangeExpressions> </StartRange> <EndRange ScanType="LT"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SHIPDATE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="'1994-03-19'"> <Const ConstValue="'1994-03-19'" /> </ScalarOperator> </RangeExpressions> </EndRange> </SeekKeys> </SeekPredicateNew> </SeekPredicates> <Predicate> <ScalarOperator ScalarString="[tpch].[dbo].[LINEITEM].[L_COMMITDATE]&lt;[tpch].[dbo].[LINEITEM].[L_RECEIPTDATE] AND PROBE([Bitmap1015],[tpch].[dbo].[LINEITEM].[L_ORDERKEY],N'[IN ROW]')"> <Logical Operation="AND"> <ScalarOperator> <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> <ScalarOperator> <Intrinsic FunctionName="PROBE"> <ScalarOperator> <Identifier> <ColumnReference Column="Bitmap1015" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="N'[IN ROW]'" /> </ScalarOperator> </Intrinsic> </ScalarOperator> </Logical> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </Parallelism> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Parallelism> </RelOp> </Sort> </RelOp> </StreamAggregate> </RelOp> </ComputeScalar> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> <Batch> <Statements> <StmtSimple StatementCompId="1" StatementEstRows="25" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="3027.38" 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="0xC26C78FFA054BEDC"> <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" /> <QueryPlan CachedPlanSize="160" CompileTime="219" CompileCPU="219" CompileMemory="3472"> <RelOp AvgRowSize="40" EstimateCPU="0.00028115" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25" LogicalOp="Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="3027.38"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Column="Expr1020" /> </OutputList> <MemoryFractions Input="0.5" Output="1" /> <Sort Distinct="false"> <OrderBy> <OrderByColumn Ascending="false"> <ColumnReference Column="Expr1020" /> </OrderByColumn> </OrderBy> <RelOp AvgRowSize="40" EstimateCPU="7.25E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="25" LogicalOp="Aggregate" NodeId="1" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="3027.37"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Column="Expr1020" /> </OutputList> <StreamAggregate> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1020" /> <ScalarOperator ScalarString="SUM([partialagg1025])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="partialagg1025" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> </DefinedValues> <GroupBy> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </GroupBy> <RelOp AvgRowSize="40" EstimateCPU="0.00113648" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="100" LogicalOp="Sort" NodeId="2" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="3027.37"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Column="partialagg1025" /> </OutputList> <MemoryFractions Input="1.35467E-05" Output="0.5" /> <Sort Distinct="false"> <OrderBy> <OrderByColumn Ascending="true"> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </OrderByColumn> </OrderBy> <RelOp AvgRowSize="40" EstimateCPU="0.0285131" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="100" LogicalOp="Gather Streams" NodeId="3" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="3027.36"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Column="partialagg1025" /> </OutputList> <Parallelism> <RelOp AvgRowSize="40" EstimateCPU="14.6117" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="100" LogicalOp="Partial Aggregate" NodeId="4" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="3027.33"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Column="partialagg1025" /> </OutputList> <MemoryFractions Input="0" Output="0" /> <Hash> <DefinedValues> <DefinedValue> <ColumnReference Column="partialagg1025" /> <ScalarOperator ScalarString="SUM([Expr1024])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1024" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> </DefinedValues> <HashKeysBuild> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </HashKeysBuild> <BuildResidual> <ScalarOperator ScalarString="[tpch].[dbo].[NATION].[N_NAME] = [tpch].[dbo].[NATION].[N_NAME]"> <Compare CompareOp="IS"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </BuildResidual> <RelOp AvgRowSize="40" EstimateCPU="21.3373" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="8213430" LogicalOp="Inner Join" NodeId="5" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="3012.72"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Column="Expr1024" /> </OutputList> <MemoryFractions Input="0.0763404" Output="0.109298" /> <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="0.879938" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1000000" LogicalOp="Repartition Streams" NodeId="6" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="2.16699"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_SUPPKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> </OutputList> <Parallelism PartitioningType="Hash"> <PartitionColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[SUPPLIER]" Column="S_SUPPKEY" /> </PartitionColumns> <RelOp AvgRowSize="15" EstimateCPU="0.275039" EstimateIO="1.01201" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1000000" LogicalOp="Index Scan" NodeId="7" Parallel="true" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="1.28705" 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> </Parallelism> </RelOp> <RelOp AvgRowSize="48" EstimateCPU="7.52779" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4511700" LogicalOp="Repartition Streams" NodeId="8" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="2989.21"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SUPPKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Column="Expr1024" /> </OutputList> <Parallelism PartitioningType="Hash"> <PartitionColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SUPPKEY" /> </PartitionColumns> <RelOp AvgRowSize="48" EstimateCPU="255.788" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4511700" LogicalOp="Inner Join" NodeId="9" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="2981.69"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SUPPKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> <ColumnReference Column="Expr1024" /> </OutputList> <MemoryFractions Input="0.622109" Output="0.890688" /> <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="5.07979" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3461420" LogicalOp="Bitmap Create" NodeId="10" Parallel="true" PhysicalOp="Bitmap" EstimatedTotalSubtreeCost="622.322"> <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> <Bitmap> <DefinedValues> <DefinedValue> <ColumnReference Column="Bitmap1031" /> </DefinedValue> </DefinedValues> <HashKeys> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> </HashKeys> <RelOp AvgRowSize="44" EstimateCPU="5.07979" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3461420" LogicalOp="Repartition Streams" NodeId="11" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="622.322"> <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> <Parallelism PartitioningType="Hash"> <PartitionColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> </PartitionColumns> <RelOp AvgRowSize="44" EstimateCPU="45.8785" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3461420" LogicalOp="Inner Join" NodeId="12" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="617.242"> <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.923355" Output="0.301551" /> <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="4.05094" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3000000" LogicalOp="Bitmap Create" NodeId="13" Parallel="true" PhysicalOp="Bitmap" EstimatedTotalSubtreeCost="270.56"> <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> <Bitmap> <DefinedValues> <DefinedValue> <ColumnReference Column="Bitmap1030" /> </DefinedValue> </DefinedValues> <HashKeys> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> </HashKeys> <RelOp AvgRowSize="40" EstimateCPU="4.05094" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3000000" LogicalOp="Repartition Streams" NodeId="14" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="270.56"> <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> <Parallelism PartitioningType="Hash"> <PartitionColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_CUSTKEY" /> </PartitionColumns> <RelOp AvgRowSize="40" EstimateCPU="21.6147" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3000000" LogicalOp="Inner Join" NodeId="15" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="266.509"> <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.92366" Output="0.000304209" /> <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.0285204" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5" LogicalOp="Bitmap Create" NodeId="16" Parallel="true" PhysicalOp="Bitmap" EstimatedTotalSubtreeCost="0.0390239"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </OutputList> <Bitmap> <DefinedValues> <DefinedValue> <ColumnReference Column="Bitmap1029" /> </DefinedValue> </DefinedValues> <HashKeys> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> </HashKeys> <RelOp AvgRowSize="36" EstimateCPU="0.0285204" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5" LogicalOp="Distribute Streams" NodeId="17" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0390239"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NATIONKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[NATION]" Column="N_NAME" /> </OutputList> <Parallelism PartitioningType="Broadcast"> <RelOp AvgRowSize="36" EstimateCPU="0.0001045" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="5" LogicalOp="Inner Join" NodeId="18" 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="19" 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]" IndexKind="Clustered" /> </IndexScan> </RelOp> <RelOp AvgRowSize="32" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="20" EstimateRewinds="4" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="20" 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> </Parallelism> </RelOp> </Bitmap> </RelOp> <RelOp AvgRowSize="15" EstimateCPU="4.12504" EstimateIO="240.731" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="15000000" LogicalOp="Clustered Index Scan" NodeId="21" Parallel="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="244.856" 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" /> <Predicate> <ScalarOperator ScalarString="PROBE([Bitmap1029],[tpch].[dbo].[CUSTOMER].[C_NATIONKEY],N'[IN ROW]')"> <Intrinsic FunctionName="PROBE"> <ScalarOperator> <Identifier> <ColumnReference Column="Bitmap1029" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[CUSTOMER]" Column="C_NATIONKEY" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="N'[IN ROW]'" /> </ScalarOperator> </Intrinsic> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </Hash> </RelOp> </Parallelism> </RelOp> </Bitmap> </RelOp> <RelOp AvgRowSize="19" EstimateCPU="19.5486" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="22755700" LogicalOp="Repartition Streams" NodeId="23" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="300.804"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_ORDERKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> </OutputList> <Parallelism PartitioningType="Hash"> <PartitionColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> </PartitionColumns> <RelOp AvgRowSize="19" EstimateCPU="6.25785" EstimateIO="274.997" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="22755700" LogicalOp="Clustered Index Seek" NodeId="24" Parallel="true" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="281.255" 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> <Predicate> <ScalarOperator ScalarString="PROBE([Bitmap1030],[tpch].[dbo].[ORDERS].[O_CUSTKEY],N'[IN ROW]')"> <Intrinsic FunctionName="PROBE"> <ScalarOperator> <Identifier> <ColumnReference Column="Bitmap1030" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[ORDERS]" Column="O_CUSTKEY" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="N'[IN ROW]'" /> </ScalarOperator> </Intrinsic> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </Parallelism> </RelOp> </Hash> </RelOp> </Parallelism> </RelOp> </Bitmap> </RelOp> <RelOp AvgRowSize="27" EstimateCPU="3.36898" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="134759000" LogicalOp="Compute Scalar" NodeId="26" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="2103.58"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SUPPKEY" /> <ColumnReference Column="Expr1024" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1024" /> <ScalarOperator ScalarString="[tpch].[dbo].[LINEITEM].[L_EXTENDEDPRICE]*((1.000000000000000e+000)-[tpch].[dbo].[LINEITEM].[L_DISCOUNT])"> <Arithmetic Operation="MULT"> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_EXTENDEDPRICE" /> </Identifier> </ScalarOperator> <ScalarOperator> <Arithmetic Operation="SUB"> <ScalarOperator> <Const ConstValue="(1.000000000000000e+000)" /> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_DISCOUNT" /> </Identifier> </ScalarOperator> </Arithmetic> </ScalarOperator> </Arithmetic> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="35" EstimateCPU="192.035" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="134759000" LogicalOp="Repartition Streams" NodeId="27" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="2100.21"> <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> <Parallelism PartitioningType="Hash"> <PartitionColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> </PartitionColumns> <RelOp AvgRowSize="35" EstimateCPU="37.0588" EstimateIO="1871.11" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="134759000" LogicalOp="Clustered Index Seek" NodeId="28" Parallel="true" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="1908.17" TableCardinality="600038000"> <OutputList> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SUPPKEY" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_EXTENDEDPRICE" /> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_DISCOUNT" /> </OutputList> <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false"> <DefinedValues> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SUPPKEY" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_EXTENDEDPRICE" /> </DefinedValue> <DefinedValue> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_DISCOUNT" /> </DefinedValue> </DefinedValues> <Object Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Index="[L_SHIPDATE_CLUIDX]" IndexKind="Clustered" /> <SeekPredicates> <SeekPredicateNew> <SeekKeys> <StartRange ScanType="GE"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SHIPDATE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="'1993-12-19'"> <Const ConstValue="'1993-12-19'" /> </ScalarOperator> </RangeExpressions> </StartRange> <EndRange ScanType="LT"> <RangeColumns> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_SHIPDATE" /> </RangeColumns> <RangeExpressions> <ScalarOperator ScalarString="'1995-06-12'"> <Const ConstValue="'1995-06-12'" /> </ScalarOperator> </RangeExpressions> </EndRange> </SeekKeys> </SeekPredicateNew> </SeekPredicates> <Predicate> <ScalarOperator ScalarString="PROBE([Bitmap1031],[tpch].[dbo].[LINEITEM].[L_ORDERKEY],N'[IN ROW]')"> <Intrinsic FunctionName="PROBE"> <ScalarOperator> <Identifier> <ColumnReference Column="Bitmap1031" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Database="[tpch]" Schema="[dbo]" Table="[LINEITEM]" Column="L_ORDERKEY" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="N'[IN ROW]'" /> </ScalarOperator> </Intrinsic> </ScalarOperator> </Predicate> </IndexScan> </RelOp> </Parallelism> </RelOp> </ComputeScalar> </RelOp> </Hash> </RelOp> </Parallelism> </RelOp> </Hash> </RelOp> </Hash> </RelOp> </Parallelism> </RelOp> </Sort> </RelOp> </StreamAggregate> </RelOp> </Sort> </RelOp> </QueryPlan> </StmtSimple> </Statements> </Batch> <Batch> <Statements> <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="1315.41" 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="0xC5C0D07B1F9DC37B"> <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="32" CompileTime="5" CompileCPU="5" CompileMemory="384"> <RelOp AvgRowSize="15" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1315.41"> <OutputList> <ColumnReference Column="Expr1004" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <ColumnReference Column="Expr1004" /> <ScalarOperator ScalarString="CASE WHEN [globalagg1007]=(0) THEN NULL ELSE [globalagg1009] END"> <IF> <Condition> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Column="globalagg1007" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="(0)" /> </ScalarOperator> </Compare> </ScalarOperator> </Condition> <Then> <ScalarOperator> <Const ConstValue="NULL" /> </ScalarOperator> </Then> <Else> <ScalarOperator> <Identifier> <ColumnReference Column="globalagg1009" /> </Identifier> </ScalarOperator> </Else> </IF> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="23" EstimateCPU="2.9E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Aggregate" NodeId="2" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="1315.41"> <OutputList> <ColumnReference Column="globalagg1007" /> <ColumnReference Column="globalagg1009" /> </OutputList> <StreamAggregate> <DefinedValues> <DefinedValue> <ColumnReference Column="globalagg1007" /> <ScalarOperator ScalarString="SUM([partialagg1006])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="partialagg1006" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="globalagg1009" /> <ScalarOperator ScalarString="SUM([partialagg1008])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="partialagg1008" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="23" EstimateCPU="0.0285032" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4" LogicalOp="Gather Streams" NodeId="3" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="1315.41"> <OutputList> <ColumnReference Column="partialagg1006" /> <ColumnReference Column="partialagg1008" /> </OutputList> <Parallelism> <RelOp AvgRowSize="23" EstimateCPU="1.70756" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="4" LogicalOp="Aggregate" NodeId="4" Parallel="true" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="1315.38"> <OutputList> <ColumnReference Column="partialagg1006" /> <ColumnReference Column="partialagg1008" /> </OutputList> <StreamAggregate> <DefinedValues> <DefinedValue> <ColumnReference Column="partialagg1006" /> <ScalarOperator ScalarString="Count(*)"> <Aggregate AggType="countstar" Distinct="false" /> </ScalarOperator> </DefinedValue> <DefinedValue> <ColumnReference Column="partialagg1008" /> <ScalarOperator ScalarString="SUM([Expr1005])"> <Aggregate AggType="SUM" Distinct="false"> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1005" /> </Identifier> </ScalarOperator> </Aggregate> </ScalarOperator> </DefinedValue> </DefinedValues> <RelOp AvgRowSize="15" EstimateCPU="0.284594" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="11383700" LogicalOp="Compute Scalar" NodeId="5" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1313.67"> <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="24.9868" EstimateIO="1261.59" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="11383700" LogicalOp="Clustered Index Seek" NodeId="6" Parallel="true" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="1286.58" 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> </Scalar