실행계획에 따른 GROUP BY 와 ORDER BY (p.339 상단)

-- SORT (GROUP BY)
SELECT deptno, sum(sal), max(sal), min(sal), avg(sal) FROM EMP
GROUP BY deptno
ORDER BY deptno
;

-- HASH (GROUP BY)
SELECT deptno, job, sum(sal), max(sal), min(sal), avg(sal) FROM EMP
GROUP BY deptno, job
;

-- SORT (ORDER BY)
SELECT * FROM EMP
ORDER BY deptno
;

• SORT (GROUP BY) : 그룹핑 or 그룹핑 + 정렬
  • 실행계획만으로 정렬이 이뤄졌는지 판단하면 안된다.
• HASH (GROUP BY) : 그룹핑
• SORT (ORDER BY) : 정렬

union, minus, intersect, distinct 같은 집합연산자에서 SORT

select job, mgr from emp where deptno = 10
union 
select job, mgr from emp where deptno = 20
;

SELECT STATEMENT
	SORT UNIQUE
		UNION ALL
			TABLE ACCESS FULL
			TABLE ACCESS FULL

select job, mgr from emp where deptno = 10
minus
select job, mgr from emp where deptno = 20
;

SELECT STATEMENT
	MINUS
		SORT UNIQUE
			TABLE ACCESS FULL
		SORT UNIQUE
			TABLE ACCESS FULL

select distinct deptno from emp;

SELECT STATEMENT
	SORT UNIQUE
		TABLE ACCESS FULL

-- 10gR2 부터		
SELECT STATEMENT
	HASH UNIQUE
		TABLE ACCESS FULL		

SORT MERGE JOIN, WINDOW SORT

• 소트머지 조인시 나타남
  • INNER.학생번호 = OUTTER.학생번호
  • ORDER BY 학생번호
• 윈도우함수 (=분석함수)를 수행할 때 나타남

실행계획만 가지고 정렬을 파악하긴 어렵다

p.339 p.341의 내용 표로 정리

| 쿼리문 | ORDER BY | GROUP BY (과거) | GROUP BY (10gR2 ~ 현재) | GROUP BY ORDER BY | | --- | --- | --- | --- | --- | | 실행계획 | SORT (ORDER BY) | SORT (GROUP BY) | HASH (GROUP BY) | SORT (GROUP BY) | | 결과집합 | 정렬 | 정렬X | 정렬X | 정렬 |