[sql] Stream Aggregate 언제? 왜? (실행계획, aggregate, hash match)

SELECT COUNT(idx) AS idx
FROM table_A
WHERE idx = 6

# 왜 Stream Aggregate 가 나타날까?

idx 컬럼은 PK. SELECT 결과는 항상 결과가 하나인데 왜 Stream Aggregate 하지?

위와 같은 단순한 쿼리에서 시작한 질문으로 aggregate와 관련된 실행계획을 공부했다(아직 진행중...)

 

 

# Stream Aggregate 공부를 해보자

• Stream Aggregate
  • 적절하게 정렬된 스트림에서, 행 그룹에 대한 요약 값을 계산합니다.

• Hash Match
  • 정렬되지 않은 스트림에서, 최상위 입력의 각 행으로 해시 테이블을 작성하고, 최하위 입력의 각 행으로 해시 테이블을 검색하여, 일치하는 모든 행을 출력합니다.
  • [ Sort -> Stream Aggregate ] 로 대체되기도 함.

 

 

# 샘플 데이터로 직접 살펴보자.

-- PK 	 : idx
-- INDEX : language_id, severity
CREATE TABLE dbo.table_A
(
    idx INT IDENTITY(1,1) NOT NULL,
    language_id INT NOT NULL,
    severity INT,
    text VARCHAR(1000),
    CONSTRAINT PK_table_A PRIMARY KEY CLUSTERED (idx)
);
GO

INSERT INTO dbo.table_A (language_id, severity, text)
SELECT DISTINCT TOP 1000
	m.language_id, m.severity, m.text
FROM sys.messages m
ORDER BY m.language_id, m.severity, m.text
OPTION (MAXDOP 1);

INSERT INTO dbo.table_A (language_id, severity, text)
SELECT DISTINCT TOP 200
	m.language_id, m.severity, m.text
FROM sys.messages m
ORDER BY m.language_id, m.severity, m.text
OPTION (MAXDOP 1);
GO

CREATE INDEX idx_table_A ON table_A (language_id, severity)

 

# GROUP BY 에 PK 주면 Aggregate 필요 없음.(복합키도 동일)

• PK 로 GROUP BY 하면 항상 정렬할 필요가 없으므로.

SELECT COUNT(severity)
FROM table_A
GROUP BY idx

 

# GROUP BY 에 INDEX 주면 Stream Aggregate

• INDEX의 정렬을 이용해서 Aggregate 처리.

SELECT COUNT(severity) 
FROM table_A
GROUP BY language_id

 

# GROUP BY 에 일반 컬럼주면 Hash Match 또는 Sort -> Stream Aggregate

• 미정렬 스트림의 경우 Hash 처리하거나 Sort 처리 후 Aggregate 처리.

SELECT COUNT(severity)
FROM table_A
GROUP BY text

 

# GROUP BY + WHERE 조건

-- GROUP BY 컬럼이 인덱스지만, WHERE 조건이 PK 이므로 Aggregate 안함.
SELECT COUNT(severity)
FROM table_A
WHERE idx = 6
GROUP BY language_id

-- GROUP BY 인덱스지만, WHERE 조건이 PK 아닌 인덱스므로 Stream Scalar
SELECT COUNT(severity)
FROM table_A
WHERE language_id = 1028
GROUP BY language_id

 

 

# GROUP BY 하지 않을 경우, GROUP BY * 과 같아서 모든 경우에 Stream Aggregate

-- WHERE 조건이 없어도 Stream Aggregate
SELECT COUNT(idx) AS idx
FROM table_A

-- WHERE 조건 PK 줘도 Stream Aggregate
SELECT COUNT(idx) AS idx
FROM table_A
WHERE idx = 6

-- WHERE 조건 IDX 또는 일반컬럼 줘도 Stream Aggregate
SELECT COUNT(idx) AS idx
FROM table_A
WHERE language_id = 1028 AND text IS NOT NULL