Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?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.5" Build="13.0.4001.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
- <BatchSequence>
- <Batch>
- <Statements>
- <StmtSimple StatementCompId="1" StatementEstRows="10000" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="130" StatementSubTreeCost="1.52056" StatementText="SELECT TOP 10000
OBJECT_TYPE,
SEQ_NO,
O.AGR_LINE_NO,
SHORT_DESC,
C02,
C03,
C04,
C05,
C07,
C10,
C52,
N05,
N04,
N02,
N19,
N01,
carConcept.CodeDescription AS CarConcept,
O.[Timestamp] AS OBJ_TIMESTAMP,
O.Record_Timestamp AS OBJ_RECORD_TIMESTAMP,
carConcept.RECORD_TIMESTAMP AS X_RECORD_TIMESTAMP

 FROM [Archive].[TIA_TIA_OBJECT] O

 INNER JOIN [Archive].[TIA_TIA_AGREEMENT_LINE] A 
 ON A.AGR_LINE_NO = O.Agr_Line_No

 INNER JOIN [Archive].[TIA_TIA_PRODUCT_LINE] PL 
 ON PL.PRODUCT_LINE_ID = A.PRODUCT_LINE_ID AND PL.PRODUCT_LINE_VER_NO = A.PRODUCT_LINE_VER_NO

 OUTER APPLY Staging.ufnGetCodeDescription(PL.PRODUCT_LINE_ID, PL.PRODUCT_LINE_VER_NO, PL.TARIFF_TYPE_LIST_VER,
 CASE WHEN O.C52 IS NOT NULL 
 THEN 'XTARIFTYPE'
 ELSE 'ART'
 END, 
 CASE WHEN O.C52 IS NOT NULL 
 THEN O.C52
 ELSE O.C02
 END) carConcept
 
 WHERE O.OBJECT_TYPE = 'BIO01'
 
 AND O.Meta_IsCurrent = 1 
 AND A.Meta_IsCurrent = 1
 AND PL.Meta_IsCurrent = 1" StatementType="SELECT" QueryHash="0x8A100223EE15D2DC" QueryPlanHash="0xD387B44AADF29071" RetrievedFromCache="true">
- <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
- <QueryPlan DegreeOfParallelism="1" MemoryGrant="1240" CachedPlanSize="120" CompileTime="7" CompileCPU="7" CompileMemory="1096">
- <MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="1240" RequiredMemory="1024" DesiredMemory="1240" RequestedMemory="1240" GrantWaitTime="0" GrantedMemory="1240" MaxUsedMemory="320" />
- <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="104857" EstimatedPagesCached="13107" EstimatedAvailableDegreeOfParallelism="2" />
- <RelOp AvgRowSize="28224" EstimateCPU="0.001" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="10000" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="1.52056">
- <OutputList>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Seq_No" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Agr_Line_No" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="OBJECT_TYPE" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="SHORT_DESC" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C02" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C03" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C04" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C05" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C07" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C10" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C52" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N01" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N02" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N04" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N05" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N19" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Timestamp" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Record_Timestamp" />
- <ColumnReference Database="[Datahub]" Schema="[Staging]" Table="[ufnGetCodeDescription]" Alias="[carConcept]" Column="CodeDescription" />
- <ColumnReference Database="[Datahub]" Schema="[Staging]" Table="[ufnGetCodeDescription]" Alias="[carConcept]" Column="Record_Timestamp" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="10000" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="24595" ActualCPUms="24589" />
- </RunTimeInformation>
- <Top RowCount="false" IsPercent="false" WithTies="false">
- <TopExpression>
- <ScalarOperator ScalarString="(10000)">
- <Const ConstValue="(10000)" />
- </ScalarOperator>
- </TopExpression>
- <RelOp AvgRowSize="28224" EstimateCPU="23.3658" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="10000" LogicalOp="Left Outer Join" NodeId="1" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.51956">
- <OutputList>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Seq_No" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Agr_Line_No" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="OBJECT_TYPE" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="SHORT_DESC" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C02" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C03" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C04" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C05" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C07" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C10" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C52" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N01" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N02" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N04" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N05" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N19" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Timestamp" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Record_Timestamp" />
- <ColumnReference Database="[Datahub]" Schema="[Staging]" Table="[ufnGetCodeDescription]" Alias="[carConcept]" Column="CodeDescription" />
- <ColumnReference Database="[Datahub]" Schema="[Staging]" Table="[ufnGetCodeDescription]" Alias="[carConcept]" Column="Record_Timestamp" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="10000" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="24593" ActualCPUms="24587" />
- </RunTimeInformation>
- <NestedLoops Optimized="false">
- <OuterReferences>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C02" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C52" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_PRODUCT_LINE]" Alias="[PL]" Column="PRODUCT_LINE_ID" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_PRODUCT_LINE]" Alias="[PL]" Column="PRODUCT_LINE_VER_NO" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_PRODUCT_LINE]" Alias="[PL]" Column="TARIFF_TYPE_LIST_VER" />
- </OuterReferences>
- <RelOp AvgRowSize="28133" EstimateCPU="1.26193" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="1.45374">
- <OutputList>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Seq_No" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Agr_Line_No" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="OBJECT_TYPE" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="SHORT_DESC" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C02" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C03" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C04" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C05" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C07" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C10" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C52" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N01" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N02" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N04" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N05" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N19" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Timestamp" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Record_Timestamp" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_PRODUCT_LINE]" Alias="[PL]" Column="PRODUCT_LINE_ID" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_PRODUCT_LINE]" Alias="[PL]" Column="PRODUCT_LINE_VER_NO" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_PRODUCT_LINE]" Alias="[PL]" Column="TARIFF_TYPE_LIST_VER" />
- </OutputList>
- <MemoryFractions Input="1" Output="1" />
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="10000" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="206" ActualCPUms="206" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
- </RunTimeInformation>
- <Hash>
- <DefinedValues />
- <HashKeysBuild>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_PRODUCT_LINE]" Alias="[PL]" Column="PRODUCT_LINE_ID" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_PRODUCT_LINE]" Alias="[PL]" Column="PRODUCT_LINE_VER_NO" />
- </HashKeysBuild>
- <HashKeysProbe>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_AGREEMENT_LINE]" Alias="[A]" Column="PRODUCT_LINE_ID" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_AGREEMENT_LINE]" Alias="[A]" Column="PRODUCT_LINE_VER_NO" />
- </HashKeysProbe>
- <ProbeResidual>
- <ScalarOperator ScalarString="[Datahub].[Archive].[TIA_TIA_PRODUCT_LINE].[PRODUCT_LINE_ID] as [PL].[PRODUCT_LINE_ID]=[Datahub].[Archive].[TIA_TIA_AGREEMENT_LINE].[PRODUCT_LINE_ID] as [A].[PRODUCT_LINE_ID] AND [Datahub].[Archive].[TIA_TIA_PRODUCT_LINE].[PRODUCT_LINE_VER_NO] as [PL].[PRODUCT_LINE_VER_NO]=[Datahub].[Archive].[TIA_TIA_AGREEMENT_LINE].[PRODUCT_LINE_VER_NO] as [A].[PRODUCT_LINE_VER_NO]">
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_PRODUCT_LINE]" Alias="[PL]" Column="PRODUCT_LINE_ID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_AGREEMENT_LINE]" Alias="[A]" Column="PRODUCT_LINE_ID" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_PRODUCT_LINE]" Alias="[PL]" Column="PRODUCT_LINE_VER_NO" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_AGREEMENT_LINE]" Alias="[A]" Column="PRODUCT_LINE_VER_NO" />
- </Identifier>
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </ProbeResidual>
- <RelOp AvgRowSize="29" EstimateCPU="0.0002989" EstimateIO="0.00534722" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="129" LogicalOp="Clustered Index Scan" NodeId="3" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00564612" TableCardinality="129">
- <OutputList>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_PRODUCT_LINE]" Alias="[PL]" Column="PRODUCT_LINE_ID" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_PRODUCT_LINE]" Alias="[PL]" Column="PRODUCT_LINE_VER_NO" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_PRODUCT_LINE]" Alias="[PL]" Column="TARIFF_TYPE_LIST_VER" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="129" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="6" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
- </RunTimeInformation>
- <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_PRODUCT_LINE]" Alias="[PL]" Column="PRODUCT_LINE_ID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_PRODUCT_LINE]" Alias="[PL]" Column="PRODUCT_LINE_VER_NO" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_PRODUCT_LINE]" Alias="[PL]" Column="TARIFF_TYPE_LIST_VER" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_PRODUCT_LINE]" Index="[ClusteredIndex-20170808-124641]" Alias="[PL]" IndexKind="Clustered" Storage="RowStore" />
- <Predicate>
- <ScalarOperator ScalarString="[Datahub].[Archive].[TIA_TIA_PRODUCT_LINE].[Meta_IsCurrent] as [PL].[Meta_IsCurrent]=(1)">
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_PRODUCT_LINE]" Alias="[PL]" Column="Meta_IsCurrent" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(1)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Predicate>
- </IndexScan>
- </RelOp>
- <RelOp AvgRowSize="28129" EstimateCPU="0.753501" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="322.481" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.42319">
- <OutputList>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Seq_No" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Agr_Line_No" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="OBJECT_TYPE" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="SHORT_DESC" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C02" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C03" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C04" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C05" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C07" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C10" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C52" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N01" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N02" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N04" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N05" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N19" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Timestamp" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Record_Timestamp" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_AGREEMENT_LINE]" Alias="[A]" Column="PRODUCT_LINE_ID" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_AGREEMENT_LINE]" Alias="[A]" Column="PRODUCT_LINE_VER_NO" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="10000" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="186" ActualCPUms="186" />
- </RunTimeInformation>
- <NestedLoops Optimized="false" WithUnorderedPrefetch="true">
- <OuterReferences>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Agr_Line_No" />
- <ColumnReference Column="Expr1008" />
- </OuterReferences>
- <RelOp AvgRowSize="28115" EstimateCPU="5.14733" EstimateIO="192.131" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="321.957" LogicalOp="Table Scan" NodeId="6" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.356155" TableCardinality="4679240">
- <OutputList>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Seq_No" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Agr_Line_No" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="OBJECT_TYPE" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="SHORT_DESC" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C02" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C03" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C04" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C05" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C07" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C10" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C52" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N01" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N02" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N04" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N05" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N19" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Timestamp" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Record_Timestamp" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="10010" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="60" ActualCPUms="60" ActualScans="1" ActualLogicalReads="7117" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
- </RunTimeInformation>
- <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Seq_No" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Agr_Line_No" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="OBJECT_TYPE" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="SHORT_DESC" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C02" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C03" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C04" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C05" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C07" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C10" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C52" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N01" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N02" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N04" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N05" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="N19" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Timestamp" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Record_Timestamp" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" IndexKind="Heap" Storage="RowStore" />
- <Predicate>
- <ScalarOperator ScalarString="[Datahub].[Archive].[TIA_TIA_OBJECT].[Meta_IsCurrent] as [O].[Meta_IsCurrent]=(1) AND [Datahub].[Archive].[TIA_TIA_OBJECT].[OBJECT_TYPE] as [O].[OBJECT_TYPE]=N'BIO01'">
- <Logical Operation="AND">
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Meta_IsCurrent" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="(1)" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- <ScalarOperator>
- <Compare CompareOp="EQ">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="OBJECT_TYPE" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="N'BIO01'" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Logical>
- </ScalarOperator>
- </Predicate>
- </TableScan>
- </RelOp>
- <RelOp AvgRowSize="24" EstimateCPU="0.000158102" EstimateIO="0.003125" EstimateRebinds="321.956" EstimateRewinds="0.00178894" EstimatedExecutionMode="Row" EstimateRows="1.00163" LogicalOp="Clustered Index Seek" NodeId="7" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="1.05832" TableCardinality="4671660">
- <OutputList>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_AGREEMENT_LINE]" Alias="[A]" Column="PRODUCT_LINE_ID" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_AGREEMENT_LINE]" Alias="[A]" Column="PRODUCT_LINE_VER_NO" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRows="10000" Batches="0" ActualEndOfScans="10009" ActualExecutions="10010" ActualExecutionMode="Row" ActualElapsedms="110" ActualCPUms="110" ActualScans="10010" ActualLogicalReads="30669" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
- </RunTimeInformation>
- <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_AGREEMENT_LINE]" Alias="[A]" Column="PRODUCT_LINE_ID" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_AGREEMENT_LINE]" Alias="[A]" Column="PRODUCT_LINE_VER_NO" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_AGREEMENT_LINE]" Index="[ClusteredIndex-20170808-124714]" Alias="[A]" IndexKind="Clustered" Storage="RowStore" />
- <SeekPredicates>
- <SeekPredicateNew>
- <SeekKeys>
- <Prefix ScanType="EQ">
- <RangeColumns>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_AGREEMENT_LINE]" Alias="[A]" Column="AGR_LINE_NO" />
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_AGREEMENT_LINE]" Alias="[A]" Column="Meta_IsCurrent" />
- </RangeColumns>
- <RangeExpressions>
- <ScalarOperator ScalarString="[Datahub].[Archive].[TIA_TIA_OBJECT].[Agr_Line_No] as [O].[Agr_Line_No]">
- <Identifier>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="Agr_Line_No" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator ScalarString="(1)">
- <Const ConstValue="(1)" />
- </ScalarOperator>
- </RangeExpressions>
- </Prefix>
- </SeekKeys>
- </SeekPredicateNew>
- </SeekPredicates>
- </IndexScan>
- </RelOp>
- </NestedLoops>
- </RelOp>
- </Hash>
- </RelOp>
- <RelOp AvgRowSize="119" EstimateCPU="0.000100157" EstimateIO="0" EstimateRebinds="99.9982" EstimateRewinds="0.000175252" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Table-valued function" NodeId="8" Parallel="false" PhysicalOp="Table-valued function" EstimatedTotalSubtreeCost="0.0101">
- <OutputList>
- <ColumnReference Database="[Datahub]" Schema="[Staging]" Table="[ufnGetCodeDescription]" Alias="[carConcept]" Column="CodeDescription" />
- <ColumnReference Database="[Datahub]" Schema="[Staging]" Table="[ufnGetCodeDescription]" Alias="[carConcept]" Column="Record_Timestamp" />
- </OutputList>
- <RunTimeInformation>
- <RunTimeCountersPerThread Thread="0" ActualRebinds="4790" ActualRewinds="5210" ActualRows="10000" Batches="0" ActualEndOfScans="9999" ActualExecutions="10000" ActualExecutionMode="Row" ActualElapsedms="24356" ActualCPUms="24349" ActualScans="4790" ActualLogicalReads="10000" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
- </RunTimeInformation>
- <TableValuedFunction>
- <DefinedValues>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Staging]" Table="[ufnGetCodeDescription]" Alias="[carConcept]" Column="CodeDescription" />
- </DefinedValue>
- <DefinedValue>
- <ColumnReference Database="[Datahub]" Schema="[Staging]" Table="[ufnGetCodeDescription]" Alias="[carConcept]" Column="Record_Timestamp" />
- </DefinedValue>
- </DefinedValues>
- <Object Database="[Datahub]" Schema="[Staging]" Table="[ufnGetCodeDescription]" Alias="[carConcept]" Storage="RowStore" />
- <ParameterList>
- <ScalarOperator ScalarString="[Datahub].[Archive].[TIA_TIA_PRODUCT_LINE].[PRODUCT_LINE_ID] as [PL].[PRODUCT_LINE_ID]">
- <Identifier>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_PRODUCT_LINE]" Alias="[PL]" Column="PRODUCT_LINE_ID" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator ScalarString="CONVERT_IMPLICIT(decimal(10,4),[Datahub].[Archive].[TIA_TIA_PRODUCT_LINE].[PRODUCT_LINE_VER_NO] as [PL].[PRODUCT_LINE_VER_NO],0)">
- <Convert DataType="decimal" Precision="10" Scale="4" Style="0" Implicit="true">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_PRODUCT_LINE]" Alias="[PL]" Column="PRODUCT_LINE_VER_NO" />
- </Identifier>
- </ScalarOperator>
- </Convert>
- </ScalarOperator>
- <ScalarOperator ScalarString="CONVERT_IMPLICIT(decimal(10,4),[Datahub].[Archive].[TIA_TIA_PRODUCT_LINE].[TARIFF_TYPE_LIST_VER] as [PL].[TARIFF_TYPE_LIST_VER],0)">
- <Convert DataType="decimal" Precision="10" Scale="4" Style="0" Implicit="true">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_PRODUCT_LINE]" Alias="[PL]" Column="TARIFF_TYPE_LIST_VER" />
- </Identifier>
- </ScalarOperator>
- </Convert>
- </ScalarOperator>
- <ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(20),CASE WHEN [Datahub].[Archive].[TIA_TIA_OBJECT].[C52] as [O].[C52] IS NOT NULL THEN 'XTARIFTYPE' ELSE 'ART' END,0)">
- <Convert DataType="nvarchar" Length="40" Style="0" Implicit="true">
- <ScalarOperator>
- <IF>
- <Condition>
- <ScalarOperator>
- <Compare CompareOp="IS NOT">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C52" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="NULL" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Condition>
- <Then>
- <ScalarOperator>
- <Const ConstValue="'XTARIFTYPE'" />
- </ScalarOperator>
- </Then>
- <Else>
- <ScalarOperator>
- <Const ConstValue="'ART'" />
- </ScalarOperator>
- </Else>
- </IF>
- </ScalarOperator>
- </Convert>
- </ScalarOperator>
- <ScalarOperator ScalarString="CONVERT_IMPLICIT(nvarchar(20),CASE WHEN [Datahub].[Archive].[TIA_TIA_OBJECT].[C52] as [O].[C52] IS NOT NULL THEN [Datahub].[Archive].[TIA_TIA_OBJECT].[C52] as [O].[C52] ELSE [Datahub].[Archive].[TIA_TIA_OBJECT].[C02] as [O].[C02] END,0)">
- <Convert DataType="nvarchar" Length="40" Style="0" Implicit="true">
- <ScalarOperator>
- <IF>
- <Condition>
- <ScalarOperator>
- <Compare CompareOp="IS NOT">
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C52" />
- </Identifier>
- </ScalarOperator>
- <ScalarOperator>
- <Const ConstValue="NULL" />
- </ScalarOperator>
- </Compare>
- </ScalarOperator>
- </Condition>
- <Then>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C52" />
- </Identifier>
- </ScalarOperator>
- </Then>
- <Else>
- <ScalarOperator>
- <Identifier>
- <ColumnReference Database="[Datahub]" Schema="[Archive]" Table="[TIA_TIA_OBJECT]" Alias="[O]" Column="C02" />
- </Identifier>
- </ScalarOperator>
- </Else>
- </IF>
- </ScalarOperator>
- </Convert>
- </ScalarOperator>
- </ParameterList>
- </TableValuedFunction>
- </RelOp>
- </NestedLoops>
- </RelOp>
- </Top>
- </RelOp>
- </QueryPlan>
- </StmtSimple>
- </Statements>
- </Batch>
- </BatchSequence>
- </ShowPlanXML>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement