728x90
Sort Aggregate
- Sort aggregate는 아래처럼 전체 로우를 대상으로 집계를 수행할 때 (집계함수 사용)나타나는,
'sort'라는 표현을 사용하지만 실제 소트가 발생하지는 않는다.
select sum(sal), max(sal), min(sal) from emp;
--------------------------------------------
| Id | Operation | Name | E-Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS FULL| EMP | 7013 |
--------------------------------------------
Sort Order by
- 데이터 정렬을 위해 order by 오퍼레이션을 수행할 때 나타난다.
select * from emp order by sal desc;
-----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | SORT ORDER BY | | 7013 | 14336 | 14336 |12288 (0)|
| 2 | TABLE ACCESS FULL| EMP | 7013 | | | |
-----------------------------------------------------------------------
Sort Group by
- sort group by는 소팅 알고리즘을 사용해 그룹별 집계를 수행할 때 나타난다.
( group by 와 order by 같이 사용 시)
select deptno, job, sum(sal), max(sal), min(sal)
from emp
group by deptno, job
order by deptno, job;
-----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | SORT GROUP BY | | 4 | 3072 | 3072 | 2048 (0)|
| 2 | TABLE ACCESS FULL| EMP | 7013 | | | |
-----------------------------------------------------------------------
Hash Group By와 비교
10gR2에서 hash group by 방식이 도입되면서, order by절을 함께 명시하지 않으면 대부분 hash group by 방식으로 처리된다.
select deptno, job, sum(sal), max(sal), min(sal)
from emp
group by deptno, job
-----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | HASH GROUP BY | | 4 | 780K| 780K| 481K (0)|
| 2 | TABLE ACCESS FULL| EMP | 7013 | | | |
-----------------------------------------------------------------------
hash group by는 정렬을 수행하지 않고 해싱 알고리즘을 사용해 데이터를 그룹핑한다.
읽는 로우마다 group by 컬럼의 해시 값으로 해시 버킷을 찾아 그룹별로 집계항목을 갱신하는 방식이다.
sort group by 라고 해서 모든 데이터를 정렬하고 나서 집계하는 것은 아니며, hash group by와
마찬가지로 읽는 로우마다 그룹별로 집계항목을 갱신한다.
다만, 그룹을 찾아가는 방식이 해싱 알고리즘이냐 소팅 알고리즘이냐 차이만 있을 뿐이다.
집계할 대상 로우가 아무리 많아도 그룹개수가 소수일 때는 두 방식 모두 디스크 소트가 전혀 발생하지 않는 것을 통해 이를 알 수 있다.
Hash Group by와 비교
- 10gR2에서 Hash group by 방식이 도입되면서, order by절을 함께 명시하지 않으면
대부분 hash group 방식으로 처리된다.
Group by 결과의 정렬 순서
- 오라클은 9i부터 이미 group by 결과가 보장되지 않는다고 여러 문서를 통해
공식적으로 밝히고 있다.
- 실행계획에서 'sort group by'의 의미는 '소팅 알고리즘을 사용해 값을 집계한다'는 뜻일 뿐
결과의 정렬을 의미하지는 않는다. 물론 쿼리에 order by절을 명시하면 정렬 순서가
보장 되지만, 이때도 실행계획은 똑같이 'sort group by'로 표시된다.
- 10gR2에서 sort group by에서 sort group by가 나타나는 경우는 distinct , count 함수를
만났을 땐 항상 sort group by 방식으로 수행한다. 여기서도 결과는 정렬되지 않는다.
Sort ( Unique )
- Unnesting된 서브쿼리가 M쪽 집합이거나 Unique 인덱스가 없다면, 그리고 세미 조인으로
수행되지도 않느다면 메인 쿼리와 조인되기 전에 sort unique 오퍼레이션이 먼저 수행된다.
( 여기서 M쪽 집합이 Unnesitng 된다고 했는데 이 튜닝을 하기 전에 고려해야 할 것은
M쪽 집합 서브쿼리가 Unnesting 되면 조인 형식이 되어서 경로가 바뀔 수 있다. )
- 만약 PK/Unique 제약 또는 Unique 인덱스를 통해, Unnesting 된 서브쿼리의 Uniqueness가
보장된다면 sort unique 오퍼레이션은 생략된다.
select /*+ leading( b@subq ) use_nl(a) */ *
from hr.DEPARTMENTS a
where a.DEPARTMENT_ID = 10
and exists ( select /*+ unnest qb_name(subq) */ 1 from hr.employees b where b.department_id = a.department_id )
;
*************************[Explain Plan Time: 2025/08/07 16:47:12]*************************
Execution Plan
-----------------------------------------------------------
0 7 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=24)
1 6 0 NESTED LOOPS (Cost=3 Card=1 Bytes=24)
2 4 1 NESTED LOOPS (Cost=3 Card=1 Bytes=24)
3 2 2 SORT (UNIQUE) (Cost=1 Card=1 Bytes=3)
4 1 3 INDEX (RANGE SCAN) OF 'HR.EMP_DEPARTMENT_IX' (INDEX) (Cost=1 Card=1 Bytes=3)
5 3 2 INDEX (UNIQUE SCAN) OF 'HR.DEPT_ID_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)
6 5 1 TABLE ACCESS (BY INDEX ROWID) OF 'HR.DEPARTMENTS' (TABLE) (Cost=1 Card=1 Bytes=21)
-----------------------------------------------------------
Predicate information (identified by operation id):
-----------------------------------------------------------
4 - access("B"."DEPARTMENT_ID"=10)
5 - access("B"."DEPARTMENT_ID"="A"."DEPARTMENT_ID")
5 - filter("A"."DEPARTMENT_ID"=10)
-----------------------------------------------------------
select /*+ leading( b@subq ) use_nl(a) */ *
from hr.DEPARTMENTS a
where a.DEPARTMENT_ID = 10
and a.DEPARTMENT_ID in ( select /*+ unnest qb_name(subq) */ department_id from hr.employees b where b.department_id = a.department_id )
;
*************************[Explain Plan Time: 2025/08/07 16:45:59]*************************
Execution Plan
-----------------------------------------------------------
0 7 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=24)
1 6 0 NESTED LOOPS (Cost=3 Card=1 Bytes=24)
2 4 1 NESTED LOOPS (Cost=3 Card=1 Bytes=24)
3 2 2 SORT (UNIQUE) (Cost=1 Card=1 Bytes=3)
4 1 3 INDEX (RANGE SCAN) OF 'HR.EMP_DEPARTMENT_IX' (INDEX) (Cost=1 Card=1 Bytes=3)
5 3 2 INDEX (UNIQUE SCAN) OF 'HR.DEPT_ID_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)
6 5 1 TABLE ACCESS (BY INDEX ROWID) OF 'HR.DEPARTMENTS' (TABLE) (Cost=1 Card=1 Bytes=21)
-----------------------------------------------------------
Predicate information (identified by operation id):
-----------------------------------------------------------
4 - access("DEPARTMENT_ID"=10)
5 - access("A"."DEPARTMENT_ID"="DEPARTMENT_ID")
5 - filter("A"."DEPARTMENT_ID"=10)
-----------------------------------------------------------
- union, minus, intersect 같은 집합연산자를 사용할 때도 아래와 같이 sort unique 오퍼레이션이
나타난다.
select job, mgr from emp where deptno = 10
union
select job, mgr from emp where deptno = 20;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | SORT UNIQUE | | 7014 | 3072 | 3072 | 2048 (0)|
| 2 | UNION-ALL | | | | | |
|* 3 | TABLE ACCESS FULL | EMP | 7013 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | | | |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 1 | | | |
--------------------------------------------------------------------------------------------
select job, mgr from emp where deptno = 10
union
select job, mgr from emp where deptno = 20;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE | | 7013 | 3072 | 3072 | 2048 (0)|
|* 3 | TABLE ACCESS FULL | EMP | 7013 | | | |
| 4 | SORT UNIQUE | | 1 | 73728 | 73728 | |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | | | |
|* 6 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 1 | | | |
--------------------------------------------------------------------------------------------
- distinct 연산을 위해서도 sort unique 오퍼레이션이 사용된다.
- 오라클 10gR2부터는 group by처럼 distinct 연산에서도 order by를 생략하면
hash unique 방식으로 수행된다.
select distinct deptno from emp order by deptno;
-----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | SORT UNIQUE | | 1 | 3072 | 3072 | 2048 (0)|
| 2 | TABLE ACCESS FULL| EMP | 7013 | | | |
-----------------------------------------------------------------------
select distinct deptno from emp;
-----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | HASH UNIQUE | | 1 | 1518K| 1518K| 286K (0)|
| 2 | TABLE ACCESS FULL| EMP | 7013 | | | |
-----------------------------------------------------------------------
- 참고로, 10gR2에서 아래처럼 _convert_set_to_join 파라미터를 true로 설정하면 minus,
intersect 같은 집합 연산에 hash unique 오퍼레이션을 사용한다. 즉, 조인을 통해 두 집합을
연결하고 나서 중복을 제거하는 방식이다.
* 이 부분 연관하여 4장 11절 내용이 언급되는데 4장 11절은 null 허용 컬럼이기 때문에
그런 쿼리가 되는 건지와 distinct가 결과 출력 모양상 필요한 것인지 테스트할 필요가 있다.
alter session set "_convert_set_to_join" = true;
select job, mgr from emp where deptno = 10
minus
select job, mgr from emp where deptno = 20;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | HASH UNIQUE | | 22 | 1115K| 1115K| 550K (0)|
|* 2 | HASH JOIN RIGHT ANTI | | 5610 | 1594K| 1594K|83968 (0)|
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | | | |
|* 4 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 1 | | | |
|* 5 | TABLE ACCESS FULL | EMP | 7013 | | | |
--------------------------------------------------------------------------------------------
Sort Join
- sort join 오퍼레이션은 소트 머지 조인을 수행할 때 나타난다.
- outer 테이블에 pk 제약이나 unique 제약이 있을 때는 sort가 한 번만 발생한다.
select /*+ordered use_merge(e) */*
from dept d, emp e
where d.deptno = e.deptno;
------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | MERGE JOIN | | 7013 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | | | |
| 3 | INDEX FULL SCAN | DEPT_PK | 4 | | | |
|* 4 | SORT JOIN | | 7013 | 14336 | 14336 |12288 (0)|
| 5 | TABLE ACCESS FULL | EMP | 7013 | | | |
------------------------------------------------------------------------------------
DEPT 테이블의 인덱스를 사용하였기 때문에 소트가 한번만 발생하였다.
Window Sort
- window sort는 분석함수를 수행할 때 나타난다.
select empno, ename, job, mgr, sal
, avg(sal) over (partition by deptno)
from emp;
-----------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | WINDOW SORT | | 114 | 11264 | 11264 |10240 (0)|
| 2 | TABLE ACCESS FULL| EMP | 114 | | | |
-----------------------------------------------------------------------728x90
반응형
'Oracle > Tunning' 카테고리의 다른 글
| [Oracle] Array Processing (0) | 2025.03.05 |
|---|---|
| [Oracle ] 중첩 서브 쿼리 동작 방식 (1) | 2025.02.28 |
| [Oracle] 부분 범위 처리에 따른 INDEX FULL SCAN 성능 (1) | 2024.08.12 |
| [Oracle] Tunning 관련 파라미터 정리 (2) | 2024.08.12 |
| [Oracle]OR-Expansion (1) | 2024.08.12 |
