ROW_NUMBER () OVER (PARTITION BY B, A ORDER BY C)는 (A, B, C)에서 인덱스를 사용하지 않습니다 INTO [dbo].[T] ([A],[B],[C]) VALUES (10, 20, 30), (10, 21,

다음 두 가지 기능을 고려하십시오.

ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C)

ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C)

내가 이해하는 한, 그들은 정확히 같은 결과를 낳습니다. 즉, PARTITION BY절에 열을 나열하는 순서는 중요하지 않습니다.

인덱스가 있으면 (A,B,C)옵티마이 저가 두 인덱스에서이 인덱스를 사용할 것으로 예상했습니다.

그러나 놀랍게도 옵티마이 저는 두 번째 변형에서 추가 명시 적 정렬을 수행하기로 결정했습니다.

SQL Server 2008 Standard 및 SQL Server 2014 Express에서 확인했습니다.

여기 내가 그것을 재현하는 데 사용한 전체 스크립트가 있습니다.

Microsoft SQL Server 2014에서 시도 – 12.0.2000.8 (X64) 2014 년 2 월 20 일 20:04:26 저작권 (c) Windows NT 6.1 (빌드 7601 : 서비스 팩 1)의 Microsoft Corporation Express Edition (64 비트)

및 Microsoft SQL Server 2014 (SP1-CU7) (KB3162659)-12.0.4459.0 (X64) 2016 년 5 월 27 일 15:33:17 저작권 (c) Windows NT 6.1 (빌드 7601 : 서비스의 Microsoft Corporation Express Edition (64 비트)) 팩 1)

사용하여 이전 및 새 리티 견적에 OPTION (QUERYTRACEON 9481)OPTION (QUERYTRACEON 2312).

테이블, 인덱스, 샘플 데이터 설정

CREATE TABLE [dbo].[T](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [A] [int] NOT NULL,
    [B] [int] NOT NULL,
    [C] [int] NOT NULL,
    CONSTRAINT [PK_T] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_ABC] ON [dbo].[T]
(
    [A] ASC,
    [B] ASC,
    [C] ASC
)WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
GO

INSERT INTO [dbo].[T] ([A],[B],[C]) VALUES
(10, 20, 30),
(10, 21, 31),
(10, 21, 32),
(10, 21, 33),
(11, 20, 34),
(11, 21, 35),
(11, 21, 36),
(12, 20, 37),
(12, 21, 38),
(13, 21, 39);

쿼리

SELECT -- AB
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) AS rnAB
FROM T
ORDER BY C
OPTION(RECOMPILE);

SELECT -- BA
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) AS rnBA
FROM T
ORDER BY C
OPTION(RECOMPILE);

SELECT -- both
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) AS rnAB
    ,ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) AS rnBA
FROM T
ORDER BY C
OPTION(RECOMPILE);

실행 계획

A, B의 참여

AB

B, A의 참여

학사

양자 모두

양자 모두

보시다시피, 두 번째 계획에는 추가 정렬이 있습니다. B, A, C 주문. 옵티마이 저는 데이터와 PARTITION BY B,A동일 PARTITION BY A,B하고 다시 정렬 한다는 것을 알기에 충분히 똑똑하지 않습니다 .

흥미롭게도 세 번째 쿼리에는 두 가지 변형이 ROW_NUMBER있으며 추가 정렬이 없습니다! 계획은 첫 번째 쿼리와 동일합니다. (시퀀스 프로젝트에는 출력 열에 추가 열에 대한 추가 표현식이 있지만 추가 정렬은 없습니다). 따라서 이보다 복잡한 경우에는 옵티마이 저가 PARTITION BY B,A와 동일한 것을 깨달을만큼 똑똑한 것으로 보입니다 PARTITION BY A,B.

첫 번째 쿼리와 세 번째 쿼리에서 Index Scan 연산자에는 Ordered : True 속성이 있으며 두 번째 쿼리에서는 False입니다.

더 흥미로운 것은 다음과 같이 세 번째 쿼리를 다시 작성하면 (두 열 바꾸기) :

SELECT -- both
    ID,A,B,C
    ,ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) AS rnBA
    ,ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) AS rnAB
FROM T
ORDER BY C
OPTION(RECOMPILE);

여분의 정렬이 다시 나타납니다!

누군가 빛을 비출 수 있을까? 옵티 마이저에서 무슨 일이 일어나고 있습니까?



답변

개발자가 아니고 내부를 알지 못한다면 “최적화 기에서 무슨 일이 일어나고 있는지”라는 질문에 대한 확실한 “답변”이없는 것 같습니다.

여기에 의견을 정리하겠습니다.

전반적으로 쿼리의 최종 결과가 정확하기 때문에 버그라고 부르기가 너무 힘들 것 같습니다. 경우에 따라 실행 계획이 최적이 아닌 경우도 있습니다. ypercubeᵀᴹ , Martin Smith , Aaron Bertrand 는이를 “최적화 결여”라고 부릅니다.

  • 처럼 보인다 GROUP BY a,bGROUP BY b,a동일한 계획을 얻을 수 있지만, PARTITION BY같은 변환을 사용할 수 없습니다

  • 동일한 스펙을 가진 창 기능을 가진 선택 사항이 선택 목록에서 다른 스펙을 가진 것으로 분리 될 경우 추가 정렬 조작을 가질 수있는 다른 누락 된 최적화도 있습니다.

  • 예, 이것은 또 다른 누락 된 최적화처럼 보이며 많은 것들이 있습니다. 옵티마이 저는 인간이 작성했으며 완벽하지 않습니다.


다소 관련 기사 내림차순 색인이 있습니다. Itzik Ben-Gan의 인덱스 순서, 병렬 처리 및 순위 계산 . Itzik은 내림차순 인덱스에 대해 설명하고 인덱스 정의 방향이 파티션의 창 기능에 어떻게 영향을 미치는지에 대한 예를 제공합니다. 그는 ROW_NUMBER옵티마이 저가 피할 수있는 추가 정렬 연산자가있는 쿼리 및 생성 된 계획의 예를 보여줍니다 .


나에게 실용적인 결과는이 최적화의 특이성을 명심하는 것입니다. PARTITION BY창 함수에서 사용할 때는 항상 열을 PARTITION BY나열하는 순서와 색인에 나열된 순서를 일치 시키십시오 . 중요하지 않지만.

이주의 사항의 또 다른 측면은 색인을 검토하고 색인 정의에서 일부 열을 교체하기로 결정할 때입니다. 영향을받지 않아야하는 일부 기존 쿼리에 실수로 영향을 줄 수 있습니다. 이것이 실제로 옵티마이 저의이 특이성을 발견 한 방법입니다.

그렇지 않으면 옵티마이 저가 인덱스를 최대한 활용하지 못할 수 있습니다. 옵티마이 저가 최적의 계획을 선택하더라도 SELECT명령문 의 컬럼 순서 변경과 같이 쿼리에 약간의 순진한 변경이있을 경우 이러한 계획은 최적이 아닌 것으로 변경 될 수 있습니다 .


답변