Plan zorlama senaryosu: use plan kullanarak JOIN algoritması zorla
Bu örnek zorlar SQL Server belirleyen bir iç birleştirmek kullanma planı kullanmak için mantıksal işleç ve birleştirmek Katıl fiziksel işleci için sorgu.
Örnek
USE tempdb;
GO
CREATE TABLE t1(i INT)
CREATE TABLE t2(j INT)
GO
SELECT * FROM t1, t2 WHERE t1.i = t2.j OPTION (USE PLAN N'
<ShowPlanXML xmlns="https://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="0.5" Build="9.00.938">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="SELECT * FROM t1, t2 WHERE t1.i = t2.j" StatementId="1"
StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.0514796"
StatementEstRows="1" StatementOptmLevel="FULL">
<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true"
ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
<QueryPlan CachedPlanSize="20">
<RelOp NodeId="0" PhysicalOp="Merge Join" LogicalOp="Inner Join" EstimateRows="1"
EstimateIO="0.000313" EstimateCPU="0.00564738" AvgRowSize="15"
EstimatedTotalSubtreeCost="0.0514796" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t1]" Column="i" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t2]" Column="j" />
</OutputList>
<Merge ManyToMany="1">
<InnerSideJoinColumns>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t2]" Column="j" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t1]" Column="i" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[tempdb].[dbo].[t2].[j]=[tempdb].[dbo].[t1].[i]">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t2]" Column="j" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t1]" Column="i" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Residual>
<RelOp NodeId="1" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="1" EstimateIO="0.01625"
EstimateCPU="0.000100011" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0227581" Parallel="0"
EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t1]" Column="i" />
</OutputList>
<MemoryFractions Input="1" Output="0.5" />
<Sort Distinct="0">
<OrderBy>
<OrderByColumn Ascending="1">
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t1]" Column="i" />
</OrderByColumn>
</OrderBy>
<RelOp NodeId="2" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="1"
EstimateIO="0.0063285" EstimateCPU="7.96e-005" AvgRowSize="11"
EstimatedTotalSubtreeCost="0.0064081" Parallel="0" EstimateRebinds="0"
EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t1]" Column="i" />
</OutputList>
<TableScan Ordered="0" ForcedIndex="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t1]" Column="i" />
</DefinedValue>
</DefinedValues>
<Object Database="[tempdb]" Schema="[dbo]" Table="[t1]" />
</TableScan>
</RelOp>
</Sort>
</RelOp>
<RelOp NodeId="3" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="1"
EstimateIO="0.01625" EstimateCPU="0.000100011" AvgRowSize="11"
EstimatedTotalSubtreeCost="0.0227581" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t2]" Column="j" />
</OutputList>
<MemoryFractions Input="0.5" Output="0.5" />
<Sort Distinct="0">
<OrderBy>
<OrderByColumn Ascending="1">
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t2]" Column="j" />
</OrderByColumn>
</OrderBy>
<RelOp NodeId="4" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="1"
EstimateIO="0.0063285" EstimateCPU="7.96e-005" AvgRowSize="11"
EstimatedTotalSubtreeCost="0.0064081" Parallel="0" EstimateRebinds="0"
EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t2]" Column="j" />
</OutputList>
<TableScan Ordered="0" ForcedIndex="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t2]" Column="j" />
</DefinedValue>
</DefinedValues>
<Object Database="[tempdb]" Schema="[dbo]" Table="[t2]" />
</TableScan>
</RelOp>
</Sort>
</RelOp>
</Merge>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>')
Ayrıca bkz.