Plan Forcing Scenario: Use USE PLAN to Force an Index Union Algorithm
The following example forces SQL Server to use a plan that specifies an index union solution for executing the query.
Example
USE tempdb;
GO
IF OBJECT_ID('t') IS NOT NULL
DROP TABLE t
GO
CREATE TABLE t(a int, b int, c int, d int, e int, f int, g char(1000))
CREATE CLUSTERED INDEX cidx ON t(a, b, c, d, e, f)
CREATE INDEX idx2 ON t(c)
CREATE INDEX idx3 ON t(d)
GO
INSERT t VALUES(1, 500, 1, 1, 1, 1, 1)
INSERT t VALUES(10, 500, 10, 10, 10, 10, 10)
INSERT t VALUES(999, 500, 999, 999, 999, 999, 999)
GO
SELECT * FROM t WHERE b > 100 AND (c = 999 OR d = 10)
OPTION (USE PLAN N'<ShowPlanXML xmlns="https://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.1352.00">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="SELECT * FROM t WHERE b > 100 AND (c = 999 OR d = 10)" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSubTreeCost="0.0172516" StatementEstRows="2.03388" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound">
<StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false" ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
<QueryPlan CachedPlanSize="24">
<RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="2.03388" EstimateIO="0" EstimateCPU="8.50162e-006" AvgRowSize="1031" EstimatedTotalSubtreeCost="0.0172516" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="g" />
</OutputList>
<NestedLoops Optimized="1">
<OuterReferences>
<ColumnReference Column="Uniq1002" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
</OuterReferences>
<RelOp NodeId="2" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="2.03388" EstimateIO="0" EstimateCPU="2.03393e-006" AvgRowSize="35" EstimatedTotalSubtreeCost="0.0121717" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Column="Uniq1002" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
</OutputList>
<StreamAggregate>
<DefinedValues />
<GroupBy>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
<ColumnReference Column="Uniq1002" />
</GroupBy>
<RelOp NodeId="3" PhysicalOp="Merge Join" LogicalOp="Concatenation" EstimateRows="2.03399" EstimateIO="0" EstimateCPU="0.00560238" AvgRowSize="35" EstimatedTotalSubtreeCost="0.0121696" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
<ColumnReference Column="Uniq1002" />
</OutputList>
<Merge>
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Uniq1002" />
<ColumnReference Column="Uniq1002" />
<ColumnReference Column="Uniq1002" />
</DefinedValue>
</DefinedValues>
<RelOp NodeId="4" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1.01699" EstimateIO="0.003125" EstimateCPU="0.00015813" AvgRowSize="35" EstimatedTotalSubtreeCost="0.00328313" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Column="Uniq1002" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
</OutputList>
<IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Uniq1002" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
</DefinedValue>
</DefinedValues>
<Object Database="[tempdb]" Schema="[dbo]" Table="[t]" Index="[idx2]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(999)">
<Const ConstValue="(999)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[tempdb].[dbo].[t].[b]>(100)">
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(100)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
<RelOp NodeId="5" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1.01699" EstimateIO="0.003125" EstimateCPU="0.00015813" AvgRowSize="35" EstimatedTotalSubtreeCost="0.00328313" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Column="Uniq1002" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
</OutputList>
<IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Uniq1002" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
</DefinedValue>
</DefinedValues>
<Object Database="[tempdb]" Schema="[dbo]" Table="[t]" Index="[idx3]" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(10)">
<Const ConstValue="(10)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[tempdb].[dbo].[t].[b]>(100)">
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(100)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</Merge>
</RelOp>
</StreamAggregate>
</RelOp>
<RelOp NodeId="8" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="1007" EstimatedTotalSubtreeCost="0.00507144" Parallel="0" EstimateRebinds="1.03388" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="g" />
</OutputList>
<IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="g" />
</DefinedValue>
</DefinedValues>
<Object Database="[tempdb]" Schema="[dbo]" Table="[t]" Index="[cidx]" TableReferenceId="-1" />
<SeekPredicates>
<SeekPredicate>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
<ColumnReference Column="Uniq1002" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[tempdb].[dbo].[t].[a]">
<Identifier>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="a" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[tempdb].[dbo].[t].[b]">
<Identifier>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="b" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[tempdb].[dbo].[t].[c]">
<Identifier>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="c" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[tempdb].[dbo].[t].[d]">
<Identifier>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="d" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[tempdb].[dbo].[t].[e]">
<Identifier>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="e" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[tempdb].[dbo].[t].[f]">
<Identifier>
<ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[t]" Column="f" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="[Uniq1002]">
<Identifier>
<ColumnReference Column="Uniq1002" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekPredicate>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>')
See Also
Concepts
Plan Forcing Scenarios and Examples
Specifying Query Plans with Plan Forcing
Other Resources
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
5 December 2005 |
|