계획 강제 적용 시나리오: USE PLAN을 사용하여 인덱스 통합 알고리즘 강제 적용

다음 예에서는 SQL Server에서 쿼리 실행을 위한 인덱스 통합 솔루션을 지정하는 계획을 강제로 사용하도록 합니다.

예

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 &gt; 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]&gt;(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]&gt;(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>')

참고 항목

개념

계획 강제 적용 시나리오 및 예

계획 강제 적용을 사용하여 쿼리 계획 지정

관련 자료

쿼리 성능