oracle10g R2 group by / order by

oracle10g R2 group by / order by 

oracle10g R2 부터 group by 시 자동정렬되지 않는다.

Oracle 8i Database : Character Sort GROUP BY

Oracle 9i Database : Binary Sort GROUP BY

Oracle 10g Database : Hash based GROUP BY

개선효과: 

메모리 충분할때(in memory sort) 효과적

높은 cardinality(row distinct가 많은 경우) 특히 효과적

faster CPU 더욱 효과적

적은 column 조회시 특히 효과적

이전 버전의 group by 시 자동정렬 기능을 유지하려면

1. Optimizer Mode를 Rule로 사용

2. OPTIMIZER_FEATURES_ENABLE을 10.1로 사용

3. init.ora 

   "-gby_hash_aggregation_enabled"=FALSE 로 변경

   alter system set "_GBY_HASH_AGGREGATION_ENABLED"=false;

   alter session set "_GBY_HASH_AGGREGATION_ENABLED"=false;

3번 주로 사용하나, 파라미터 변경을 권장하지 않고 정렬이 필요하면 프로그램에서 order by 를 추가해야 한다.

query 변경 방법

1. order by 표기

select deptno, count(*), avg(sal)

from emp

group by deptno

order by detpno

2. OPT_PARAM 힌트

select /*+ OPT_PARAM('_gby_hash_aggregation_enabled=false') */ 

deptno, count(*), avg(sal)

from emp

group by deptno

3. 힌트 

select /*+ NO_USE_HASH_AGGREGATION */ 

deptno, count(*), avg(sal)

from emp

group by deptno