02.소트를 발생시키는 오퍼레이션

(1)Sort Aggregate

sort aggregate는 아래처럼 전체 로우를 대상으로 집계를 수행할 때 나타나는데, 'sort'라는 표현을 사용하지만 실제 소트가 발생하지는 않는다.

Sort Aggregate


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 |
--------------------------------------------

(2)Sort Order By

데이터 정렬을 위해 order by 오퍼레이션을 수행할 때 나타난다.

Sort 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 |       |       |          |
-----------------------------------------------------------------------

(3)Sort Group By

sort group by는 소팅 알고리즘을 사용해 그룹별 집계를 수행할 때 나타난다.

Sort Group 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와
마찬가지로 읽는 로우마다 그룹별로 집계항목을 갱신한다.
다만, 그룹을 찾아가는 방식이 해싱 알고리즘이냐 소팅 알고리즘이냐 차이만 있을 뿐이다.
집계할 대상 로우가 아무리 많아도 그룹개수가 소수일 때는 두 방식 모두 디스크 소트가 전혀 발생하지 않는 것을 통해 이를 알 수 있다.

Group By 결과의 정렬 순서
- 10gR2에서 hash group by가 도입되면서 정렬 순서를 보장하지 않게 되었다고 알려졌지만 사실 오라클은 9i부터 이미 group by 결과가
  보장되지 않는다고 여러 문서를 통해 공식적으로 밝히고 있다.
  'sort group by'의 의미는 "소팅 알고리즘을 사용해 값을 집계한다."는 뜻일 뿐 결과의 정렬을 의미하지 않는다.

정렬된 group by 결과를 얻고자 한다면, 실행계획에 설령 'sort group by'라고 표시되더라도 반드시 order by를 명시해야 한다.

h3.(4)Sort Unique

Unnesting된 서버쿼리가 M족 집합이거나 Unique 인덱스가 없다면, 그리고 세미 조인으로 수행되지도 않는다면 메인 쿼리와 조인되기 전에
sort unique 오퍼레이션이 먼저 수행된다.

Sort Unique Case 1


select /*+ ordered use_nl(dept) */ *
from dept
where deptno in (select /*+ unnest */ deptno
                   from emp where job = 'CLERK');

------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | E-Rows |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |        |       |       |          |
|   1 |  NESTED LOOPS                |         |        |       |       |          |
|   2 |   NESTED LOOPS               |         |      1 |       |       |          |
|   3 |    SORT UNIQUE               |         |   6996 |  3072 |  3072 | 2048  (0)|
|*  4 |     TABLE ACCESS FULL        | EMP     |   6996 |       |       |          |
|*  5 |    INDEX UNIQUE SCAN         | DEPT_PK |      1 |       |       |          |
|   6 |   TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |       |       |          |
------------------------------------------------------------------------------------

만약 PK/Unique 제약 또는 Unique 인덱스를 통해, unnesting 된 서브쿼리의 Uniqueness가 보장되면 sort unique 오퍼레이션은 생략된다.
union, minus, intersect 같은 집합 연산자를 사용할 때도 아래와 같이 sort unique 오퍼레이션이 나타난다.

Sort Unique Case 2


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 오퍼레이션이 사용된다.

Sort Unique Case 3


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 |       |       |          |
-----------------------------------------------------------------------

오라클 10gR2부터는 group by처럼 distinct 연산에서도 order by를 생략하면 hash unique 방식으로 수행된다.

Sort Unique Case 4


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 |       |       |          |
-----------------------------------------------------------------------

_convert_set_to_join 파라미터를 true로 설정하면 minus, intersect 같은 집합 연산에 hash unique 오퍼레이션을 사용한다.
즉, 조인을 통해 두 집합을 연결하고 나서 중복을 제가하는 방식이다.

Sort Unique Case 5


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 |       |       |          |
--------------------------------------------------------------------------------------------

h3.(5)Sort Join

sort join 오퍼레이션은 소트 머지 조인을 수행할 때 나타난다.

Sort Join


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 테이블의 인덱스를 사용하였기 때문에 소트가 한번만 발생하였다.

h3.(6)Window Sort

window sort는 분석함수(Analytic Fuction)를 수행할 때 나타난다.

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 |       |       |          |
-----------------------------------------------------------------------

2. 소트를 발생시키는 오퍼레이션 (by hrson81) [2017.06.23]

02.소트를 발생시키는 오퍼레이션

(1)Sort Aggregate

(2)Sort Order By

(3)Sort Group By