Stream Aggregate
쿼리의 실행 방법을 이해하고 필요없이 사용된 many-to-many join 을 query 재작성을 통해 one-to-many 조인을 이용해 값을 구하는 방법을 보여준다. one-to-many 조인은 temp 를 사용하지 않고, many-to-many join 은 temp 를 사용하기에 결과 양이 많아지면 i/o 성능에 문제가 생기기도 한다. 그러나, 실행계획을 이해하라고 글을 번역해 놓은것이지 성능을 높이기 위해 이러한 방법을 쓰라는 것은 아니다. 성능 튜닝은 다른 방법이 더욱 효율적일 수 있다.
Stream Aggregate
GROUP BY 절이 있을때, 보통 aggregate 를 수행하기 위해 SQL Server 는 두개의 물리 연산자를 가지고 있다. 하나는 저번주
에 보았던 stream aggregate 이다. 나머지 하나는 hash aggregate 이다. 이번 포스트에서는 stream aggregate 의 동작을 자
세히 살펴 보겠다.
The algorithm (알고리즘) -_- 이런건 번역하는게 이상하지만....
Stream aggregate 는 group by 컬럼(들)의 정렬된(sort) 데이터에 의존한다. 만약 하나 이상의 컬럼들이 그룹되면, 해당 모
든 컬럼들의 어떤 정렬 순서를 선택한다. 예를들면, 컬럼 a b 를 그룹하면, (a, b) 나 (b, a) 로 정렬할 수 있다. 이 정렬은
group by 컬럼을 위한 같은값을 가지는 행의 집합이 서로 인접하게 만들어 준다.
이것은 stream aggregate 알고리즘을 위한 슈도코드이다.
clear the current aggregate results
clear the current group by columns
for each input row
begin
if the input row does not match the current group by columns
begin
output the aggregate results
clear the current aggregate results
set the current group by columns to the input row
end
update the aggregate results with the input row
end
예를들어 sum 값을 계산한다고 가정하면, 각각의 입력행에 대해 입력된 행이 현재 그룹에 속하면, (즉, 입력행의 group by
컬럼(들)이 이전행의 group by 결과와 같으면) 현재 계산중인 total 값에 적당한 값을 sum 해 업데이트 한다. 입력값이 새
로운 그룹에 속하면 (즉, 입력행의 group by 컬럼(들)이 이전행의 group by 결과와 같지 않으면) 현재의 sum 값을 출력하고
sum 값을 0 으로 초기화 하고 새로운 그룹값 계산을 시작한다.
Simple examples (간단한 예제)
create table t (a int, b int, c int)
select sum(c) from t group by a, b
|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END))
|--Stream Aggregate(GROUP BY:([t].[b], [t].[a]) DEFINE:([Expr1010]=COUNT_BIG([t].[c]), [Expr1011]=SUM([t].
[c])))
|--Sort(ORDER BY:([t].[b] ASC, [t].[a] ASC))
|--Table Scan(OBJECT:([t]))
이것은 aggregate 하기전에 sort 가 필요하다는 점을 제외하면, 우리가 예전에 봤던 scalar aggregate SUM 과 기본적으로 같
은 플랜이다. (scalar aggregate 를 전체 행에 대해 하나의 큰 그룹으로 생각 할 수 있다. 그러므로 하나의 scalar
aggregate 는 행을 다른 그룹으로 넣을 필요가 없으므로, sort 가 필요 없다.)
Stream aggregate 는 입력된 행의 정렬순서를 유지한다. 그래서, 만약 group by 컬럼으로 정렬을 요청하거나 group by 컬럼
의 일부로 정렬을 요구하면, 다시 정렬할 필요가 없다.
select sum(c) from t group by a, b order by a
|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END))
|--Stream Aggregate(GROUP BY:([t].[a], [t].[b]) DEFINE:([Expr1010]=COUNT_BIG([t].[c]), [Expr1011]=SUM([t].
[c])))
|--Sort(ORDER BY:([t].[a] ASC, [t].[b] ASC))
|--Table Scan(OBJECT:([t]))
위 실행계획은 바로위 실행계획에 비해 정렬컬럼만 반대로 바뀌었다. 이전 쿼리는 "(a,b)" 나 "(b,a)"로 정렬을 기술하지 않
았다. 이번에는 쿼리에 order by 절이 있고 컬럼 a 로 정렬을 요구했으므로 쿼리에서 컬럼 a 로 정렬을 먼저했다.
만약 우리가 적절한 인덱스가 있다면, 정렬이 필요없다.
create clustered index tab on t(a,b)
select sum(c) from t group by a, b
|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END))
|--Stream Aggregate(GROUP BY:([t].[a], [t].[b]) DEFINE:([Expr1010]=COUNT_BIG([t].[c]), [Expr1011]=SUM([t].
[c])))
|--Clustered Index Scan(OBJECT:([t].[tab]), ORDERED FORWARD)
select distinct
다음 쿼리를 생각해 보자
select sum(distinct b) from t group by a
각각의 group 에서 컬럼 b 의 중복값을 제거가 필요하다. 이전 포스트에서, 이러한 방법중 하나인 sort distinct 를 이용하는 방법을 보았다. 하지만 적절한 인덱스가 있으면, stream aggregate 를 이용해서 중복값을 제거 할 수 있다.
|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END))
|--Stream Aggregate(GROUP BY:([t].[a]) DEFINE:([Expr1010]=COUNT_BIG([t].[b]), [Expr1011]=SUM([t].[b])))
|--Stream Aggregate(GROUP BY:([t].[a], [t].[b]))
|--Clustered Index Scan(OBJECT:([t].[tab]), ORDERED FORWARD)
최상위의 aggregate 를 하는동안 최하위의 stream aggregate 는 중복값을 제거한다.
Multiple distincts (다중 distinct)
마지막으로 다음 쿼리를 생각해 보자
select sum(distinct b), sum(distinct c) from t group by a
|--Merge Join(Inner Join, MANY-TO-MANY MERGE:([t].[a])=([t].[a]), RESIDUAL:([t].[a] = [t].[a]))
|--Compute Scalar(DEFINE:([t].[a]=[t].[a]))
| |--Compute Scalar(DEFINE:([Expr1005]=CASE WHEN [Expr1018]=(0) THEN NULL ELSE [Expr1019] END))
| |--Stream Aggregate(GROUP BY:([t].[a]) DEFINE:([Expr1018]=COUNT_BIG([t].[c]), [Expr1019]=SUM([t].[c])))
| |--Sort(DISTINCT ORDER BY:([t].[a] ASC, [t].[c] ASC))
| |--Clustered Index Scan(OBJECT:([t].[tab]))
|--Compute Scalar(DEFINE:([t].[a]=[t].[a]))
|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1020]=(0) THEN NULL ELSE [Expr1021] END))
|--Stream Aggregate(GROUP BY:([t].[a]) DEFINE:([Expr1020]=COUNT_BIG([t].[b]), [Expr1021]=SUM([t].[b])))
|--Stream Aggregate(GROUP BY:([t].[a], [t].[b]))
|--Clustered Index Scan(OBJECT:([t].[tab]), ORDERED FORWARD)
이전 글에서 살펴본 다중 scalar distinct 예제에서 살펴본 것과 같이, 이 쿼를를 두개의 부분으로 나누어 볼 수 있다. 각 distinct set 에서 하나는, distinct 한 c 값을 구하기 위해 sum(distinct c) 는 정렬이 필요하고, 반면 sum(distinct b) 값을 구할때는 clustered index 와 stream aggregate 를 이용해 정렬없이 구했다. 그리고 마지막 결과를 구하기 위해 group by 컬럼 a 에 대한 각각의 sum 값을 merge join 했다. merge join 을 사용한 것은 각각의 두 입력이 이미 group by 컬럼에 대해 이미 정렬되어 있기 때문이다. (compute scalar 연산인 [t].[a] = [t].[a]”는 내부 목적으로 필요하므로 무시하면 된다.)
aggregate 값은 unique를 보장하기 때문에 one-to-many merge join 이 사용되어야 하고, many-to-many 방법을 사용할 필요가 없다. 이것은 약간의 성능 문제이지 정확도 문제는 아니다. 쿼리를 명시적 join 으로 다시 작성하면 one-to-many join 을 얻을 수 있다.
select sum_b, sum_c
from
(select a, sum(distinct b) as sum_b from t group by a) r
join
(select a, sum(distinct c) as sum_c from t group by a) s
on r.a = s.a
|--Merge Join(Inner Join, MERGE:([t].[a])=([t].[a]), RESIDUAL:([t].[a]=[t].[a]))
|--Compute Scalar(DEFINE:([Expr1009]=CASE WHEN [Expr1020]=(0) THEN NULL ELSE [Expr1021] END))
| |--Stream Aggregate(GROUP BY:([t].[a]) DEFINE:([Expr1020]=COUNT_BIG([t].[c]), [Expr1021]=SUM([t].[c])))
| |--Sort(DISTINCT ORDER BY:([t].[a] ASC, [t].[c] ASC))
| |--Clustered Index Scan(OBJECT:([t].[tab]))
|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1022]=(0) THEN NULL ELSE [Expr1023] END))
|--Stream Aggregate(GROUP BY:([t].[a]) DEFINE:([Expr1022]=COUNT_BIG([t].[b]), [Expr1023]=SUM([t].[b])))
|--Stream Aggregate(GROUP BY:([t].[a], [t].[b]))
|--Clustered Index Scan(OBJECT:([t].[tab]), ORDERED FORWARD)
Next ...
다음번 포스트에는 다른 aggregation 연산자에 대해서 살펴보겠다. (hash aggregate)
by Craig Freedman