집계처리시 실행계획을 확인해 보면 스트림집계연산자와 해시집계 연사자를 볼수 있습니다.
이들이 어떻게 다르고 어떻게 처리되는지 알아 보겠습니다.
먼저 집계처리시 스트림집계는 한번에 하나의 그룹만을 계산하지만, 해시집계는 동시에 모든 그룹을 계산합니다.
이러한 그룹들은 해시테이블에 저장돼 처리 되는데요.
아래는 해시알고리즘에 대한 코드입니다.(해시에대한 자세한 내용은 따로 언급 하지 않겠습니다.)
for each input row
begin
calculate hash value on group by column(s)
check for a matching row in the hash table
if we do not find a match
insert a new row into the hash table
else
update the matching row with the input row
end
output all rows in the hash table
위에 해시알고리즘을 보면 각 입력행이 기존 그룹에 존재하는지를 검사하기위해 해시테이블을 사용하며
존재하면 변경을, 존재하지 않으면 삽입작업을 취합니다.
해시집계처리시에는 추가적인 메모리가필요한데요. 해시집계와 쿼리를 실행하기전에 통계정보의 cardinality 예측을 통해 얼마나 많은 메모리가 필요 할지 예상합니다.
해시조인시 각 행들을 빌드해 저장하고, 전체 필요메모리는 빌드한전체행들의사이즈에비례합니다.
그럼 테스트를 한번 해보겠습니다.
SET SHOWPLAN_ALL ON
SET SHOWPLAN_ALL OFF
CREATE TABLE TBL_Temp(a INT, b INT, c INT);
GO
최적화기는 많은 행과 그룹을 처리 할때 되도록 해시집계로 처리하기를 선호 합니다.(동시에 수행하는게 유리한 경우가 많겠죠.)
하지만 적은 행은 되도록 스트립집계로 처리 됩니다.
먼저 100개행을 10개의그룹으로 데이터를 삽입합니다.
CASE 1) -----------------------------------------------------------------------------------------------------------
SET NOCOUNT ON
DECLARE @i INT
SET @i = 0
WHILE @i < 100
BEGIN
INSERT TBL_Temp VALUES (@i % 10, @i, @i * 3)
SET @i = @i + 1
END
GO
SELECT SUM(b) FROM TBL_Temp GROUP BY a
GO
|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END))
|--Stream Aggregate(GROUP BY:([DB].[dbo].[TBL_Temp].[a]) DEFINE:([Expr1010]=COUNT_BIG([DB].[dbo].[TBL_Temp].[b]), [Expr1011]=SUM([DB].[dbo].[TBL_Temp].[b])))
|--Sort(ORDER BY:([DB].[dbo].[TBL_Temp].[a] ASC))
|--Table Scan(OBJECT:([DB].[dbo].[TBL_Temp]))
100개행을 10개의 그룹으로 집계시 테이블스캔을 하고 a열로 정렬 후 스트림집계를 합니다.
뭐 굳이 데이터가 많지 않으니 하나씩 처리했다는 얘기지요.
TRUNCATE TABLE TBL_Temp
GO
DBCC FREEPROCCACHE
GO
초기화 하고 1000개행을 100개 그룹 으로 데이터를 삽입 합니다.
CASE 2) -----------------------------------------------------------------------------------------------------------
DECLARE @i INT
SET @i = 100
WHILE @i < 1000
BEGIN
INSERT INTO TBL_Temp VALUES(@i % 100, @i, @i * 3)
SET @i = @i + 1
END
GO
SELECT SUM(b) FROM TBL_Temp GROUP BY a
GO
|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END))
|--Hash Match(Aggregate, HASH:([DB].[dbo].[TBL_Temp].[a]), RESIDUAL:([DB].[dbo].[TBL_Temp].[a] = [DB].[dbo].[TBL_Temp].[a]) DEFINE:([Expr1010]=COUNT_BIG([DB].[dbo].[TBL_Temp].[b]), [Expr1011]=SUM([DB].[dbo].[TBL_Temp].[b])))
|--Table Scan(OBJECT:([DB].[dbo].[TBL_Temp
테이블 스캔후 해시집계(Hash Match(Aggregate..))로 처리 되었습니다. 즉 동시에 여러개를 처리 했다는 얘기지요.
(여기서 RESIDUAL 조건자는 해시키값이 동일한지를 비교해 주는 조건자입니다.)
위 작업은 1000개를 정렬해 스트림집계하면 100개를 해시집계하는것보다 많은 메모리가 필요하기 때문에
해시집계로 수행 됐습니다.
하지만 아래와 같이 추가로 정렬작업(ORDER BY)을 명시해 주면.. 스트림집계연산을 하게 됩니다.
CASE 3) -----------------------------------------------------------------------------------------------------------
SELECT SUM(b) FROM TBL_Temp GROUP BY a ORDER BY a
GO
|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END))
|--Stream Aggregate(GROUP BY:([DB].[dbo].[TBL_Temp].[a]) DEFINE:([Expr1010]=COUNT_BIG([DB].[dbo].[TBL_Temp].[b]), [Expr1011]=SUM([DB].[dbo].[TBL_Temp].[b])))
|--Sort(ORDER BY:([DB].[dbo].[TBL_Temp].[a] ASC))
|--Table Scan(OBJECT:([DB].[dbo].[TBL_Temp]))
스트림집계로 처리 됐네요.(정렬작업을 강제 했기 때문이죠. 굳이 정렬이 되있으니 해시로 그룹핑할 필요는 없겠죠.)
다음 데이터를 초기화 하고
TRUNCATE TABLE TBL_Temp
GO
10000개행을 100개의 그룹으로 집계를 합니다.
CASE 4) -----------------------------------------------------------------------------------------------------------
SET NOCOUNT ON
DECLARE @i INT
SET @i = 0
WHILE @i < 10000
BEGIN
INSERT TBL_Temp VALUES(@i % 100, @i, @i * 3)
SET @i = @i + 1
END
GO
SELECT SUM(b) FROM TBL_Temp GROUP BY a ORDER BY a
GO
|--Sort(ORDER BY:([DB].[dbo].[TBL_Temp].[a] ASC))
|--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END))
|--Hash Match(Aggregate, HASH:([DB].[dbo].[TBL_Temp].[a]), RESIDUAL:([DB].[dbo].[TBL_Temp].[a] = [DB].[dbo].[TBL_Temp].[a]) DEFINE:([Expr1010]=COUNT_BIG([DB].[dbo].[TBL_Temp].[b]), [Expr1011]=SUM([DB].[dbo].[TBL_Temp].[b])))
|--Table Scan(OBJECT:([DB].[dbo].[TBL_Temp]))
이는 10000개행을 100개의 그룹으로 그룹핑을 수행하는 예제입니다.
이때는 해시집계후에 정렬작업을 합니다.
즉 10000개행과 100개의 그룹이 있을때, 옵티마이져는 10000개행을 정렬하고 그룹핑 하는것 보다
해시집계 후 집계된 100개 그룹을 정렬하는것이 더 유리 하다고 판단합니다.
아래는 스트림집계와 해시집계를 강제할 수 있는 힌트입니다.
-- 스트립집계
SELECT SUM(b) FROM TBL_Temp GROUP BY a OPTION(ORDER GROUP)
-- 해시집계
SELECT SUM(b) FROM TBL_Temp GROUP BY a OPTION(HASH GROUP)
옵티마이져가 집계처리시 메모리를 최소화 하려는 노력을 보여주고 있다는 얘기네요.