[Oracle] sort group by, hash group by 차이

내부 Algorithm 

example table

CREATE TABLE t2 (id VARCHAR2(30), amount NUMBER);
INSERT INTO t2 VALUES ('A', 10);
INSERT INTO t2 VALUES ('C',  5);
INSERT INTO t2 VALUES ('B',  1);
INSERT INTO t2 VALUES ('B',  2);
INSERT INTO t2 VALUES ('A',  3);
INSERT INTO t2 VALUES ('C',  1);
INSERT INTO t2 VALUES ('A',  7);

SELECT id, sum(amount)
  FROM t2
 GROUP BY id;

 

Sort group by

전체 테이블을 group by 절의 컬럼으로 정렬 후 sub(amount) 같은 집계 함수를 실행한다.

ID   Amount SUM
A    10
A     3
A     7     10+3+7=20

B     1
B     2     1+2=3

C     5
C     1     5+1=6

 

Hash group by 

테이블을 한 번 스캔하고 각 행에 대한 해시 값을 계산한 다음 해당 행을 버킷(메모리 또는 디스크)에 넣는다.

SELECT id, ora_hash(id, 4), amount from t2;

ID Bucket  Amount  Hash table
A     2      10    Bucket#2: A=10
C     4       5    Bucket#4: C=5
B     2       1    Bucket#2: A=10, B=1
B     2       2    Bucket#2: A=10, B=1+2
A     2       3    Bucket#2: A=10+3, B=1+2
C     4       1    Bucket#4: C=5+1
A     2       7    Bucket#2: A=10+3+7, B=1+2

 

모든 값을 버킷에 넣은 후 해시 테이블을 스캔하여 집계를 계산한다.

Bucket#2: A=10+3+7, B=1+2
Bucket#4: C=5+1

 

성능

example big table 

CREATE TABLE t AS 
SELECT RPAD(object_type, 3000, 'x') as gby, o.* 
  FROM all_objects o WHERE rownum <= 50000; COMMIT;
INSERT INTO t SELECT * FROM t; COMMIT;
EXEC dbms_stats.gather_table_stats(user, 't');

 

 

/*+ use_hash_aggregation */  힌트를 사용해 hash group by 사용을 유도할 수 있다. 

SELECT /*+ USE_HASH_AGGREGATION */ gby, count(*)
  FROM t
 GROUP BY gby;

 

/*+ no_use_hash_aggregation */  힌트를 사용하면 sort group by 사용을 유도할 수 있다.

SELECT /*+ NO_USE_HASH_AGGREGATION */ gby, count(*)
  FROM t
 GROUP BY gby;

 

SELECT * FROM v$sql WHERE sql_text LIKE '%USE_HASH_AGGREGATION%'; 

SELECT * FROM v$sql_workarea WHERE sql_id IN ('663t56n1tdr59','fp5z7z1fyz42p');

OPERATION_TYPE  EST_OPT_SIZE LAST_MEM_USED ACTIVE_TIME MAX_TEMP
GROUP BY (HASH)       697344       1519616      325145        -
GROUP BY (SORT)       145408        129024      460975        -

 

 

GROUP BY HASH는 1519616바이트의 메모리가 필요하고 0.325145초 만에 실행된 반면, GROUP BY SORT는 캐시를 10분의 1 미만으로 사용했지만 약간 더 오래 실행되었다. 둘 다 메모리에서 완전히 실행되었다.

 

메모리가 부족해 디스크를 사용하게 된다면(여기서 메모리 제한을 인위적으로 낮추어 강제로 수행할 수 있음) max_tempseg_size 열의 값이 보인다.

 

ALTER SESSION SET workarea_size_policy = MANUAL;
ALTER SESSION SET sort_area_size = 10000;

OPERATION_TYPE  EST_OPT_SIZE LAST_MEM_USED ACTIVE_TIME  MAX_TEMP
GROUP BY (HASH)       697344        623616    22756184 268435456
GROUP BY (SORT)       103424         43008     1064479   4194304

 

 

만약 메모리가 부족해 disk를 사용한다면 HASH  GROUP BY 는 256MB 디스크가 필요하고 22.7초 만에 실행된 반면, GROUP BY SORT는 4MB 디스크만 필요하고 1.1초 만에 실행되었습니다. 즉 HASH GROUP BY는 Disk 가 사용되는 순간 성능이 매우 안좋아지는것을 확인할 수 있다.