소트조인(1/4)

5.1 소트 연산에 대한 이해

5.1.1 소트 수행 과정

소트는 PGA에 할당한 Sort Area에서 이루어짐. 메모리 공간인 Sort Area가 다 차면, 디스크 Temp 테이블스페이스를 활용한다.

1. 메모리 소트 : 전체 데이터 정렬 작업을 메모리 내에서 완료
2. 디스크 소트 : 할당받은 Sort Area내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 경우, ‘External Sort’라고도 함.

소트 대상 집합을 SGA 버퍼캐시를 통해 읽고, 일차적으로 Sort Area에서 정렬을 시도한다. Sort Area에서 정렬을 마무리하는 것이 최적이지만, 양이 많을 때는 Temp 테이블스페이스에서 임시 세그먼트를 만들어 저장한다.

정렬된 최종 집합합을 얻으려면(오름차순 경우) 가장 작은 값 부터 PGA로 읽다가 Full 되면 쿼리 수행 다음 단계 전달 받거나 클라이언트에 전송.

소트 연산은 메모리 집약적, CPU 집약적이기도 함. 처리할 데이터가 많으면 디스크I/O 까지 발생하므로 쿼리 성능이 나빠질 수 밖에 없다. 부분범위 처리가 불가능하게 함으로써 OLTP 환경에서 애플리케이션 성능을 저하시키는 원인

5.1.2 소트 오퍼레이션

1. Sort Aggregate

   전체 로우를 대상으로 집계를 수행할 때 나타남. ‘Sort’ 표현이지만 실제로는 데이터를 정렬하지는 않고, Sort Area를 사용한다는 의미로 이해하면 됨.

    SELECT SUM(SAL), MAX(SAL), MIN(SAL), AVG(SAL) FROM EMP;
       
    0  SELECT STATEMENT
    1    SORT AGGREGATE
    2      TABLE ACCESS FULL
    ...
   

   (1) Sort Area에 SUM, MAX, MIN, COUNT 값을 위한 변수 하나씩 할당.

   (2) EMP 테이블 첫 번째 레코드에서 읽은 SAL 값을 SUM, MAX, MIN 변수에 저장하고, COUNT에는 1 저장.

   (3) 레코드를 하나씩 읽어 가면서 SUM 값에는 누적, MAX값에는 기존보다 큰 값이 나타날때 대체, MIN값에는 작은 값이 나타나면 대체. COUNT 변수에는 1씩 증가한다.

   (4) 다 읽고 나면 SUM, MAX, MIN 값은 변수에 담긴 값을 출력하고 AVG는 SUM 값을 COUNT 값으로 나눈 값을 출력.

2. Sort Order By

   데이터를 정렬할 때 나타냄.

    select * from emp order by sal desc;
       
    0 SELECT STATMENT 
    1  SORT ORDER BY ..
    2    TABLE ACCESS FULL ..
    ..
   

   5.1 내용 확인

3. Sort Group by

   소팅 알고리즘을 사용해 그룹별 집계를 수행

    select deptno, sum(sal), max(sal), min(sal) avg(sal)
    from emp
    group by deptno
    order by deptno;
       
    0 SELECT STATMENT 
    1  SORT GROUP BY ..
    2    TABLE ACCESS FULL ..
    ..
       
    * deptno : 부서번호
   

   부서번호가 몇개 없다면 group 된 부서번호에 max, min, sum, count 각각 수행하면 되서 Sort Area가 크지 않아도 된다. 각각 부서번호 별 Sort Aggregate 방식을 똑같이 사용한다.

   부서번호가 많지 않다면 집계 대상이 아무리 많아도 Temp 테이블스페이스 사용할 일 이 없다.

   ---

   ###HASH GROUP BY

   SORT GROUP BY 방식은

   10 - 20 - 30 -40 이라면

   HASH GROUP BY 방식은

   40 - 20 - 10 -30 방식으로, 해싱 알고리즙을 사용한다.

   읽는 레코드마다 GROUP BY 컬럼의 해시 버킷을 찾아 그룹별로 집계항목을 갱신하는 방식.

   정렬된 그룹핑 결과를 얻고 싶으면 ‘SORT GROUP BY’ 라고 표기 되더라고 ORDER BY를 반드시 명시 해야한다.

4. Sort Unique

   옵티마이저가 서브쿼리를 풀어 일반 조인문으로 변환한 것을 ’서브쿼리 Unnesting’ 이라고 한다. Unnesting된 서브쿼리가 M쪽 집합이면, 메인 쿼리와 조인 전 중복레코드 제거해야하는데

   이떄 Sort Unique 오퍼레이션이 나온다.

   • Unnesting 된 경우

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

   • Union, Minus, Interect 같은 집합 연산자의 경우

    select .. from emp where a = 2
    union 
    select .. from emp where a = 1;
       
    select .. from emp where a = 2
    minus
    select .. from emp where a = 1;
   

   • Distinct 연산자
     • Distinct 연산에도 Hash Unique 방식을 사용한다. Order by를 생략한 경우.

    select distinct deptno from emp order by deptno;
       
    select distinct deptno from emp;
   

5. Sort Join

   소트 머지 조인을 수행할 때 나타난다.

    select /*+ ordered use_merge(e) */
    from dept d, emp e
    where d.deptno = e.deptno;
       
    ------------------------------------
    0 SELECT STATEMENT
    1  MERGE JOIN
    2   SORT JOIN
    3    TABLE ACCESS FULL
    4   SORT JOIN
    5    TABLE ACCESS FULL
   

6. Window Sort

   윈도우 함수(=분석함수) 를 수행할 때 나타난다.

    select empno, job, mgr, sal,
    				over (partition by deptno)
    from emp;