Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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 |
|