정렬과 그룹 연산 줄이기

#핵심 요약

집합 연산자 중 UNION ALL만 정렬을 일으키지 않음

DISTINCT는 조건에 맞는 데이터를 모두 읽어 중복을 없애야 하므로 부분범위 처리가 불가능함
존재 여부만 확인하는 EXISTS로 바꾸면 다 읽지 않아도 됨

GROUP BY 컬럼 순서의 인덱스를 Full Scan하면 SORT GROUP BY NOSORT로 정렬 없이 그룹을 만들 수 있고, 부분범위 처리가 가능해짐

#개념과 동작 원리

정렬을 줄이는 네 가지 방법

1. 정렬은 PGA의 Sort Area에서 하고 넘치면 TEMP를 오가므로 소트를 줄이는 것이 첫째 원칙임
2. 같은 결과라면 UNION 대신 UNION ALL, 조인 뒤 DISTINCT 대신 EXISTS처럼 소트가 생기지 않는 문법을 씀
3. ORDER BY나 GROUP BY가 인덱스 컬럼 순서와 맞으면 옵티마이저가 정렬을 생략함
4. 인덱스로 정렬된 상태에서 ROWNUM <= n을 주면 첫 n건만 읽고 멈춤(COUNT STOPKEY)

실습의 DISTINCT와 EXISTS 비교 설명: 둘 다 실행계획은 HASH JOIN SEMI였지만, 튜닝 전에는 위에 HASH UNIQUE가 있었음
SQL이 "조인 후 중복 제거"라는 의미로 쓰였기 때문에 옵티마이저가 같은 deptno에서 매치 1건만 찾고 넘어가는 동작을 충분히 쓰지 못하고 SALESMAN 행을 거의 다 읽었음
EXISTS는 "존재만 확인하라"는 의도를 SQL에 명시함

#필수 명령어 또는 SQL

#소트 오퍼레이션 확인

SELECT MAX(sal) FROM emp;                                          -- SORT AGGREGATE
SELECT * FROM emp ORDER BY sal DESC;                               -- SORT ORDER BY
SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;                  -- 결과: HASH GROUP BY
SELECT DISTINCT deptno FROM emp;                                   -- 결과: HASH UNIQUE
SELECT job, mgr FROM emp WHERE deptno = 10
UNION
SELECT job, mgr FROM emp WHERE deptno = 20;                        -- SORT UNIQUE
SELECT job, ename, ROUND(AVG(sal) OVER (PARTITION BY job)) 직업평균 FROM emp;   -- WINDOW SORT

결과 해석: 개별 실행 출력은 기재 없음

#UNION을 UNION ALL로

SELECT empno, ename, job, sal, hiredate FROM emp WHERE job = 'SALESMAN'
UNION ALL
SELECT empno, ename, job, sal, hiredate FROM emp WHERE job = 'MANAGER';

결과 해석: 두 쿼리의 job 조건이 겹치지 않고 각 행에 empno가 있어 중복이 생길 수 없으므로 UNION ALL로 바꿔도 결과가 같음
두 집합에 같은 행이 나올 수 있으면 결과가 달라짐

#DISTINCT 조인을 EXISTS로

실습 환경: dept_big 400개 부서(지역 4종류), emp_big 8만 명(부서당 200명, 직군 5종류)
인덱스 idx_dept_big_loc(loc), idx_emp_big_dj(deptno, job)

-- 튜닝 전
SELECT DISTINCT d.deptno, d.dname, d.loc
  FROM emp_big e, dept_big d
 WHERE e.deptno = d.deptno AND d.loc = 'CHICAGO' AND e.job = 'SALESMAN';

-- 튜닝 후
SELECT d.deptno, d.dname, d.loc
  FROM dept_big d
 WHERE d.loc = 'CHICAGO'
   AND EXISTS (SELECT 'x' FROM emp_big e
                WHERE e.deptno = d.deptno AND e.job = 'SALESMAN');

결과 해석:

실습에서는 통신사 테이블로 같은 기법을 적용한 확인 문제에서 인덱스 두 개를 만들고 EXISTS로 바꿔 NESTED LOOPS SEMI, 4버퍼로 줄였음

#SORT GROUP BY NOSORT

CREATE INDEX emp_job ON emp (job);

SELECT /*+ no_use_hash_aggregation index(emp emp_job) */ job, ROUND(AVG(sal)), COUNT(*)
  FROM emp
 WHERE job IS NOT NULL
 GROUP BY job;

결과 해석:

실습 결과: INDEX FULL SCAN(EMP_JOB) 위에 SORT GROUP BY NOSORT, Buffers 2
힌트 없이 실행하면 HASH GROUP BY(Buffers 6), no_use_hash_aggregation만 주면 TABLE ACCESS FULL 위에 SORT GROUP BY였음
job IS NOT NULL은 인덱스가 NULL을 담지 않아 필요한 조건임(인덱스 스캔 방식)

연습 문제의 답: GROUP BY job, deptno이면 인덱스를 emp(job, deptno)로 만듦
인덱스 컬럼 순서가 GROUP BY 순서와 같아야 함(퀴즈 답: GROUP BY region, job에는 emp(region, job))

#정렬은 PGA에서, 넘치면 TEMP에서

과도한 정렬은 PGA의 정렬 영역에 다 들어가지 못하면 임시 테이블스페이스로 넘쳐 디스크 I/O를 일으킴
실습에서는 세션의 정렬 영역을 일부러 아주 작게 잡고 큰 테이블을 정렬해, 조회가 거의 멈추고 sar로 디스크 I/O가 늘어나는 것을 확인했음

ALTER SESSION SET workarea_size_policy = MANUAL;
ALTER SESSION SET sort_area_size = 1;
SELECT * FROM emp_big ORDER BY sal DESC;

결과 해석:

실습 기록: 이 상태에서 임시 파일을 운영체제에서 지우면 정렬이 ORA-01116, ORA-01110, ORA-27041(파일을 열 수 없음)로 실패했고, STARTUP FORCE로 다시 띄우자 임시 파일이 다시 보이고 정렬이 진행됐음
DBA는 가능하면 TEMP I/O 없이 메모리에서 정렬되도록 PGA_AGGREGATE_TARGET을 늘리는 것도 검토함(실습 예: ALTER SYSTEM SET pga_aggregate_target = 1G SCOPE = BOTH)

OMem은 메모리 안에서 끝내려면 필요하다고 추정한 양, 1Mem은 1-pass로 처리할 때 필요한 최소량의 추정치이고, 실제 처리 방식은 Used-Mem 옆 괄호 숫자(pass 횟수)로 봄
운영에서는 V$SQL_WORKAREA, V$PGASTAT로 1-pass와 multi-pass 발생 통계를 보고, multi-pass 비율이 0이 아니면 PGA 증설을 검토함

#Sort Area를 적게 쓰도록 SQL 작성

정렬할 데이터의 행 크기를 줄이면 Sort Area가 줄어듦
필요한 컬럼만 고르고, LPAD, TO_CHAR 같은 가공은 정렬이 끝난 뒤 바깥 쿼리에서 함

-- 튜닝 전: 가공된 긴 문자열을 정렬
SELECT LPAD(상품번호, 30) || LPAD(상품명, 30) || LPAD(고객ID, 10)
       || LPAD(고객명, 20) || TO_CHAR(주문일시, 'yyyymmdd hh24:mi:ss')
  FROM 주문상품
 WHERE 주문일시 BETWEEN SYSDATE - 30 AND SYSDATE
 ORDER BY 상품번호;

-- 튜닝 뒤: 원래 컬럼으로 정렬하고 가공은 바깥에서
SELECT LPAD(상품번호, 30) || LPAD(상품명, 30) || LPAD(고객ID, 10)
       || LPAD(고객명, 20) || TO_CHAR(주문일시, 'yyyymmdd hh24:mi:ss')
  FROM (SELECT /*+ no_merge */ 상품번호, 상품명, 고객ID, 고객명, 주문일시
          FROM 주문상품
         WHERE 주문일시 BETWEEN SYSDATE - 30 AND SYSDATE
         ORDER BY 상품번호);

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

결과 해석:

SELECT *로 넓은 행을 정렬하면 필요한 메모리가 PGA 한도를 넘어 1-pass로 바뀌고, TEMP에 쓰고 다시 읽는 I/O가 생김

정렬 기준 컬럼에 가공을 미리 적용하고 정렬하면 정렬 대상이 커지거나 인덱스를 못 써서 오히려 불리함

연습 문제: 거래내역 50만 건을 기간으로 걸러 TO_CHAR, LPAD, RPAD로 가공해 ORDER BY 거래일시, 계좌번호하는 SQL을, 결과 포맷은 그대로 두고 Sort Area를 줄이도록 고침(실습 답은 no_merge 인라인 뷰 안에서 원래 컬럼으로 정렬, Used-Mem 82M (0))

#인덱스로 소트 생략과 ORDER BY 컬럼 가공

인덱스는 키 순으로 정렬돼 있으므로 조건이 인덱스 선두를 =로 잡고 ORDER BY가 인덱스 뒤 컬럼 순서와 같으면 SORT ORDER BY가 생략됨
실행계획에 SORT ORDER BY가 보이면 정렬 비용이 든다는 뜻이므로 성능상 바람직하지 않다고 수업 자료는 설명함

-- 상태변경이력_PK (장비번호, 변경일자, 변경순번)
SELECT * FROM 상태변경이력
 WHERE 장비번호 = 'C' AND 변경일자 = '20180316'
 ORDER BY 변경순번;                 -- 정렬 생략

SELECT * FROM 상태변경이력
 WHERE 장비번호 = 'C'
 ORDER BY 변경일자 || 변경순번;     -- 가공된 값으로 정렬 요청 → SORT ORDER BY 발생

SELECT * FROM 상태변경이력
 WHERE 장비번호 = 'C'
 ORDER BY 변경일자, 변경순번;       -- 튜닝 뒤: 정렬 생략

결과 해석:

인덱스 리프 블록은 양방향 연결 리스트라 오름차순과 내림차순 모두 정렬 생략이 가능함
오름차순은 조건을 만족하는 가장 작은 값으로 수직 탐색한 뒤 오른쪽으로, 내림차순은 가장 큰 값으로 수직 탐색한 뒤 왼쪽으로 수평 탐색함(index_desc 힌트)

ORDER BY에 쓴 이름이 SELECT 목록에서 TO_CHAR(a.주문번호, 'FM00000') AS 주문번호처럼 가공한 별칭을 가리키면, 가공된 값으로 정렬을 요청한 셈이라 인덱스 정렬을 쓸 수 없음
ORDER BY a.주문번호처럼 테이블 컬럼을 직접 가리키면 SORT ORDER BY가 사라짐(실습: 주문_PK (주문일자, 주문번호), 주문일자 = '20260601'과 ROWNUM <= 10)

#사용 조건과 주의사항

실습에서는 SORT GROUP BY NOSORT가 나오면 부분범위 처리가 가능하다고 정리했음
부분범위 처리는 부분 범위 처리와 Top N에서 다룸

실습 이수자 평가 문제(고객, 결제 테이블 인라인 뷰 튜닝)의 답은 기재 없음