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

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의 참여

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);

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

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

답변