1. 소트 연산에 대한 이해
SQL 수행 도중 가공된 테이터 집합이 필요할 때, 오라클은 PGA와 Temp 테이블 스페이스를 활용한다. (소트머지조인, 해시조인, 데이터 소트와 그룹핑등이 대표적!)
지금부터 데이터를 소트하거나 그룹핑하는 과정을 살펴보자.
🔎 소트 수행 과정
- 소트는 기본적으로 PGA에 할당한 Sort Area에서 이루어진다.
- Sort Area(메모리 공간)가 다 차면, 디스크 Temp 테이블스페이스를 활용한다.
Sort Area에서 작업을 완료할 수 있는지에 따라 소트를 두 가지 유형에 나눈다.
1. 메모리 소트( In-Memory Sort )
- 'Internal Sort'라고도 함
- 전체 데이터의 정렬 작업을 메모리 내에서 완료하는 것
2. 디스트 소트 (To-Disk Sort )
- 'External Sort'라고 함
- 할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 것
⚙️ 디스크 소트 과정
소트할 대상 집합을 SGA 버퍼캐시를 통해 읽어들이고, 1차적으로 Sort Area에서 정렬을 시도한다.
Sort Area 내에서 데이터 정렬을 마무리하는 것이 최적이나, 양이 많을 때는 정렬된 중간집합을 Temp 테이블 스페이스에서 임시 세그먼트를 만들어 저장한다.
Sort Area가 찰 때마다 Temp 영역에 저장해 둔 중간단계의 집합을 'Sort Run'이라고 부른다.
정렬된 최종 결과집합을 얻으려면 이를 다시 Merge 해야 한다. 각 Sort Run 내에서는 이미 정렬된 상태이므로 Merge 과정은 어렵지 않다. 오름차순 정렬이라면 각각에서 가장 작은 값부터 PGA로 읽어 들이다가 PGA가 찰 때마다 쿼리 수행 다음 단계로 전달하거나 클라이언트에게 전송하면 된다.
✨ 소트 연산
- 메모리 집약적(Memory-intensive)이고, CPU 집약적(CPU-intensive)이다.
- 처리할 데이터량이 많을 때는 디스크 I/O까지 발생하므로 쿼리 성능을 좌우하는 매우 중요한 요소이다.
- 디스크 소트가 발생하는 순간 SQL 수행 성능은 나빠진다.
- 많은 서버 리소스를 사용하고 디스트 I/O가 발생하는 것도 문제지만, 부분범위 처리를 불가능하게 함으로써 OLTP 환경에서 애플리케이션 성능을 저하시키는 주요인이 되기도 한다.
- 가능한 소트가 발생하지 않도록 SQL을 작성해야 하고, 소트가 사용해야 한다면 메모리내에서 수행을 완료할 수 있도록 해야한다.
🔎 소트 오퍼레이션
1. Sort Aggregate
- 전체 로우를 대상으로 집계를 수행할 때 나타난다.
- Sort라는 표현을 사용하나, 실제 데이터를 정렬하진 않는다.
- Sort Area를 사용한다는 의미로 이해하면 된다.
select sum(sal), max(sal), min(sal), avg(sal) from emp;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 3 (0)|
| 1 | SORT AGGREGATE | | 1 | 4 | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 56 | 3 (0)|
---------------------------------------------------------------------------| 🚩데이터를 정렬하지 않고 SUM, MAX, MIN, AVG 값 구하는 절차 ① Sort Area에 SUM, MAX, MIN, COUNT 값을 위한 변수를 각각 하나씩 할당한다. ② EMP 테이블 첫 번째 레코드에서 읽은 SAL값을 SUM, MAX, MIN변수에 저장하고, COUNT 변수에는 1을 저장한다. ③ EMP 테이블에서 레코드를 하나씩 읽어 내려가면서 SUM 변수에는 값을 누적하고, MAX 변수에는 기존보다 큰 값이 나타날때마가 값을 대체하고, MIN 변수에는 기존보다 작은 값이 나타날 때 마다 값을 대체한다. COUNT 변수에는 SAL값이 NULL이 아닌 레코드를 만날때마다 1씩 증가한다. ④ EMP 레코드를 다 읽고나면 SUM, MAX, MIN 값은 변수에 담긴 값을 그대로 출력하고, AVG는 SUM값을 COUNT값으로 나눈 값을 출력한다. |
 |
 |
2. Sort Order by
- 데이터를 정렬할 때 나타난다.
select * from emp order by sal desc;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 4 (25)| |
| 1 | SORT ORDER BY | | 14 | 518 | 4 (25)| |
| 2 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (25)| |
---------------------------------------------------------------------------3. Sort Group By
- 소팅 알고리즘을 사용해 그룹별 집계를 수행할 때 나타난다.
select deptno, sum(sal), max(sal), min(sal), avg(sal)
from emp
group by deptno
order by deptno;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 165 | 4 (25)|
| 1 | SORT GROUP BY | | 11 | 165 | 4 (25)|
| 2 | TABLE ACCESS FULL| EMP | 14 | 210 | 3 (0)|
---------------------------------------------------------------------------⚙️ Sort Group By 예시
- 부서가 4개이고, 부서코드가 10,20,30,40일때, 컴퓨터의 도움을 받지 않거 부서별 급여를 집계해라
(집계하고자 하는 항목은 급여에 대한, 합계, 최대값, 최소값, 평균값이다.)
각 메모지에 SUM, MAX, MIN, COUNT를 적을 수 있도록 입력란을 두고, 메모지를 부서번호 순으로 정렬해 놓는다.
각 사원의 급여 정보를 읽기 시작하고, 읽은 각 사원의 부서번호에 해당하는 메모지를 찾는다.
메모지를 찾았으면, SUM, MAX, MIN, COUNT 값을 갱신한다.(Sort Aggregate에서 사용했던 방식으로 동일하게 사용)
부서 개수를 미리 알 수 없다면, 급여 대장을 읽다가 새로운 부서가 나타날 때마다 새로 준비한 메모지를 정렬 순서에 맞추어 중간에 끼워넣는 방식을 사용한다.
부서가 많지 않다면 Sort Area가 클 필요가 없고, 집계할 대상 레코드가 아무리 많아도 Temp 테이블 스페이스를 쓰지 않는다는 뜻!
Group By 절 뒤에 Order By절을 명시하지 않으면 대부분 Hash Group By 방식으로 처리한다.
select deptno, sum(sal), max(sal), min(sal), avg(sal)
from emp
group by deptno;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 165 | 4 (25) |
| 1 | HASH GROUP BY | | 11 | 165 | 4 (25) |
| 2 | TABLE ACCESS FULL | EMP | 14 | 210 | 3 (0) |
--------------------------------------------------------------------------------------------4. Sort Unique
- 서브쿼리 Unnesting : 옵티마이저가 서브쿼리를 풀어 일반 조인문으로 변환하는것
- Unnesting된 서브쿼리가 M쪽 집합이면, 메인쿼리와 조인하기 전에 중복 레코드부터 제거해야 함!
select /*+ ordered use_nl(dept) */* from dept
where deptno in (select /*+ unnest */ deptno
from emp where job = 'CLERK');
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 165 | 4 (25)| |
| 1 | SORT GROUP BY | | 11 | 165 | 4 (25)| |
| 2 | TABLE ACCESS FULL| EMP | 14 | 210 | 3 (0)| |
---------------------------------------------------------------------------- 단, PK/Unique 제약 또는 Unique 인덱스를 통해 Unnesting된 서브쿼리의 유일성이 보장된다면, Sort Unique 오퍼레이션은 생략된다.
- Union, Minus, Intersect 같은 집합 연산자를 사용할 때 Set Unique 오퍼레이션이 나타난다.
select job, mgr from emp where deptno = 10
union
select job, mgr from emp where deptno = 20;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 150 | 8 (63)| |
| 1 | SORT UNIQUE | | 10 | 150 | 8 (63)| |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS FULL| EMP | 5 | 75 | 3 (0) | |
| 4 | TABLE ACCESS FULL| EMP | 5 | 75 | 3 (0) | |
---------------------------------------------------------------------------
select job, mgr from emp where deptno = 10
minus
select job, mgr from emp where deptno = 20;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 150 | 8 (63)| |
| 1 | MINUS | | 10 | 150 | 8 (63)| |
| 2 | SORT UNIQUE | | | | | |
| 3 | TABLE ACCESS FULL| EMP | 5 | 75 | 3 (0) | |
| 4 | SORT UNIQUE | | 5 | 75 | | |
| 5 | TABLE ACCESS FULL| EMP | 5 | 75 | 3 (0) | |
---------------------------------------------------------------------------- Distinct 연산자를 사용해도 Sort Unique 오퍼레이션이 나타난다.
select distict deptno from emp order by deptno;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 9 | 4 (25)| |
| 1 | SORT UNIQUE | | 3 | 9 | 4 (25)| |
| 2 | TABLE ACCESS FULL| EMP | 14 | 42 | 3 (0)| |
---------------------------------------------------------------------------- 오라클 10gR2부터는 Distinct연산에도 아래와 같이 Hash Unique 방식을 사용한다.
- Group By와 마찬가지로 Order By 생략할 때 그렇다.
select distict deptno from emp;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 9 | 4 (25)| |
| 1 | HASH UNIQUE | | 3 | 9 | 4 (25)| |
| 2 | TABLE ACCESS FULL| EMP | 14 | 42 | 3 (0)| |
---------------------------------------------------------------------------5. Sort Join
- Sort Join 오퍼레이션은 소트 머지 조인을 수행할 때 나타난다.
select /*+ ordered use_merge(e) */*
from dept d, emp e
where d.deptno = e.deptno;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 770| 4 (25)| |
| 1 | MERGE JOIN | | 14 | 770| 4 (25)| |
| 2 | SORT JOIN | | 4 | 72| 4 (25)| |
| 3 | TABLE ACCESS FULL| DEPT | 4 | 72| 3 (0) | |
| 4 | SORT UNIQUE | | 14 | 518| 4 (25)| |
| 5 | TABLE ACCESS FULL| EMP | 14 | 518| 3 (0) | |
---------------------------------------------------------------------------6. Window Sort
- 윈도우 함수를 수행할 때 나타난다.
select empno, ename, job, mgr, sal
,avg(sal) over (partition by deptno)
from emp;
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 406 | 4 (25) |
| 1 | WINDOW SORT | | 14 | 406 | 4 (25) |
| 2 | TABLE ACCESS FULL | EMP | 14 | 406 | 3 (0) |
--------------------------------------------------------------------------------2. 소트가 발생하지 않도록 SQL 작성
SQL 작성할 때 불필요한 소트가 발생하지 않도록 주의해야하며, Union, Minus, Distinct 연산자는 중복 레코드를 제거하기 위한 소트 연산을 발생시키므로 꼭 필요한 경우에만 사용해야하고, 성능이 느리다면 소트 연산을 피할 방법이 있는지 찾아야 한다.
🔎 Union VS Union All
SQL에 Union을 사용하면 옵티마이저는 상단과 하단 두 집합 간 중복을 제거하려고 소트 작업을 수행한다.
반면, Union All은 중복을 확인하지 않고 두 집합을 단순히 결합하므로 소트 작업을 수행하지 않는다.
따라서 될 수 있으면 Union All을 사용해야 한다.
그런데 Union을 Union All로 변경하려다 결과 집합이 달라질 수 있으니 주의가 필요하며, Union 대신 Union All을 사용해도 되는지 정확히 판단하려면 데이터 모델에 대한 이해와 집합적 사고가 필요하다.
그런 능력이 부족하면 알 수 없는 데이터 중복, 혹시 모를 데이터 중복을 우려해 중복 제거용 연산자를 불필요하게 자주 사용하게 된다.
- 아래 SQL은 결제수단코드 조건절에 다른 값을 입력했기 때문에 Union상단과 하단 집합 사이에 인스턴스 중복 가능성이 없다. → But, Union을 사용함으로 인해 소트 연산을 발생시키고 있다.
SELECT 결제번호, 주문번호, 결제금액, 주문일자 …
FROM 결제
WHERE 결제수단코드 = 'M' AND 결제일자 = '20180316'
UNION
SELECT 결제번호, 주문번호, 결제금액, 주문일자 …
FROM 결제
WHERE 결제수단코드 = 'C' AND 결제일자 = '20180316'
Execution Plan
--------------------------------------------------------------
SELECT STATEMENT Optimizer=ALL_ROWS (Cost-4 Card=2 Bytes=106)
SORT (UNIQUE) (Cost-4 Card-2 Bytes=106)
UNION-ALL
TABLE ACCESS (BY INDEX ROWID) OF INDEX (RANGE SCAN) OF 결제'
INDEX (RANGE SCAN) OF '결제_N1'
TABLE ACCESS (BY INDEX ROWID) OF INDEX (RANGE SCAN) OF '결제_N1'
INDEX (RANGE SCAN) OF '결제_N1'- 위 아래 두 집합이 상호배타적이므로 Union 대신 Union All을 사용해도 된다.
- 소트 연산이 일어나지 않도록 Union All을 사용하면서도 데이터 중복을 피하려면, 아래와 같이 작성한다.
SELECT 결제번호, 주문번호, 결제금액, 주문일자 …
FROM 결제
WHERE 결제일자 = '20180316'
UNION
SELECT 결제번호, 주문번호, 결제금액, 주문일자 …
FROM 결제
WHERE 주문일자 = '20180316'
AND 결제일자 <> '20180316'
Execution Plan
--------------------------------------------------------------
SELECT STATEMENT Optimizer=ALL_ROWS
UNION-ALL
TABLE ACCESS (BY INDEX ROWID) OF 결제
INDEX (RANGE SCAN) OF '결제_N2'
TABLE ACCESS (BY INDEX ROWID) OF 결제
INDEX (RANGE SCAN) OF '결제_N3'- 참고로 결제일자가 Null허용 컬럼이면 맨 아래 조건절을 아래와 같이 변경하면 된다.
and (결제일자 <> '20180316' or 결제일자 is null)
--아래와 동일
and LNNVL(결제일자='20180316')🔎 Exists 활용
- 중복 레코드를 제거할 목적으로 Distinct 연산자를 사용하는데, 해당 연산자를 사용하면 조건에 해당하는 데이터를 모두 읽어서 중복을 제거해야 한다.
- 부분범위 처리는 당연히 불가능하고, 모든 데이터를 읽는 과정에 많은 I/O가 발생한다.
- 아래 쿼리는 상품 유형코드 조건절에 해당하는 상품에 대해 계약일자 조건 기간에 발생한 계약 데이터를 모두 읽는 비효율이 있다.(상품 수는 적고, 상품별 계약 건수가 많을수록 비효율)
SELECT DISTINCT P.상품번호, P.상품명, P.상품가격, ...
FROM 상품 P, 계약 C
WHERE P.상품유형코드 = :pclscd
AND C.상품번호 = P.상품번호
AND C.계약일자 BETWEEN :DT1 AND :DT2
AND C.계약구분코드 = :CTPCD
Execution Plan
--------------------------------------------------------------------
0 SELECT STATEMENT OPTIMIZER=ALL_ROWS
1 0 HASH (UNIQUE)
2 1 FILTER
3 2 NESTED LOOPS
4 3 NESTED LOOPS
5 4 TABLE ACCESS (BY INDEX ROWID) OF '상품'
6 5 INDEX (RANGE SCAN) OF '상품_X1' (INDEX)
7 4 INDEX (RANGE SCAN) OF '계약_X2' (INDEX)
8 3 TABLE ACCESS (BY INDEX ROWID) OF '계약' (TABLE)- 위의 쿼리를 아래와 같이 바꾸면 Exists 서브쿼리는 데이터 존재 여부만 확인하면 되기 때문에 조건절을 만족하는 데이터를 모두 읽지 않는다!
SELECT P.상품번호, P.상품명, P.상품가격, ...
FROM 상품 P
WHERE P.상품유형코드 = :pclscd
AND EXISTS ( SELECT 'X' FROM 계약 C
WHERE C.상품번호 = P.상품번호
AND C.계약일자 BETWEEN :DT1 AND :DT2
AND C.계약구분코드 = :CTPCD)
Execution Plan
--------------------------------------------------------------------
0 SELECT STATEMENT OPTIMIZER=ALL_ROWS
1 0 FILTER
2 1 NESTED LOOPS (SEMI)
3 2 TABLE ACCESS (BY INDEX ROWID) OF '상품' (TABLE)
4 3 INDEX (RANGE SCAN) OF '상품_X1' (INDEX)
5 2 TABLE ACCESS (BY INDEX ROWID) OF '계약' (TABLE)
6 5 INDEX (RANGE SCAN) OF '계약_X2' (INDEX)- 데이터가 한건이라도 존재하는지 확인하고, Distinct 연산자를 사용하지 않았으므로 상품 테이블에 대한 부분범위 처리도 가능하다.
- Distinct, Minus 연산자를 사용한 쿼리는 대부분 Exists 서브쿼리로 변환 가능하다.
🔎 조인 방식의 변경
- 조인문일 경우 조인방식도 잘 선택해줘야 한다!
- 아래 SQL문에서 계약_X01 인덱스가 지점ID+계약일시 순이면 소트 연산을 생략할 수 있지만, 해시 조인이기 때문에 Sort Order By가 나타난다.
select c.계약번호, c.상품코드, p.상품명, p.상품구분코드, c.계약일시, c.계약금액
from 계약 c, 상품 p
where c.지점ID = :brch_id
and p.상품코드 = c.상품코드
order by c.계약일시 desc
Execution Plan
--------------------------------------------------------------------
0 SELECT STATEMENT OPTIMIZER=ALL_ROWS
1 0 SORT (ORDER BY)
2 1 HASH JOIN
3 2 TABLE ACCESS (FULL) OF '상품' (TABLE)
4 2 TABLE ACCESS (BY INDEX ROWID) OF '계약' (TABLE)
5 4 INDEX (RANGE SCAN) OF '계약_X01' (INDEX)아래와 같이 계약 테이블 기준으로 상품 테이블과 NL 조인하도록 조인 방식을 변경하면 소트 연산을 생략할 수 있어 지점ID 조건을 만족하는 데이터가 많고, 부분범위 처리 가능한 상황에서 큰 성능 개선 효과를 얻을 수 있다.
select /*+ leading(c) use_nl(p) */
c.계약번호, c.상품코드, p.상품명, p.상품구분코드, c.계약일시, c.계약금액
from 계약 c, 상품 p
where c.지점ID = :brch_id
and p.상품코드 = c.상품코드
order by c.계약일시 desc
Execution Plan
--------------------------------------------------------------------
0 SELECT STATEMENT OPTIMIZER=ALL_ROWS
1 0 NESTED LOOPS
2 1 NESTED LOOPS
3 2 TABLE ACCESS (BY IDNEX ROWID) OF '계약' (TABLE)
4 3 INDEX (RANGE SCAN DESCENDING) OF '계약_X01' (INDEX)
4 2 INDEX (UNIQUE SCAN) OF '상품_PK' (INDEX)
5 1 TABLE ACCESS (BY INDEX ROWID) OF '상품' (TABLE)정렬기준이 조인 키 컬럼이면 소트머지조인도 Sort Order By 연산을 생략할 수 있다!
3. 인덱스를 이용한 소트 연산 생략
인덱스는 항상 키 컬럼 순으로 정렬된 상태를 유지하므로, 이를 활용하면 SQL에 Order By 또는 Group By 절이 있어도 소트 연산을 생략할 수 있다!
Top N 쿼리 특성을 결합하면 온라인 트랜잭션 처리 시스템에서 대량 데이터를 조회할 때 매우 빠른 응답 속도를 낼 수 있으며, 특정 조건을 만족하는 최소값 또는 최대값도 빨리 찾을 수 있어 이력 데이터를 조회할 때 매우 유용하다.
🔎 Sort Order By 생략
- 인덱스 선두 컬럼을 [종목코드 + 거래일시] 순으로 구성하지 않으면, 아래 쿼리에서 소트 연산을 생략할 수 없다.
select 거래일시, 체결건수, 체결수량, 거래대금
from 종목거래
where 종목코드 = 'KR123456'
order by 거래일시- 종목코드 = 'KR123456' 조건을 만족하는 레코드를 인덱스에서 모두 읽어야 하고, 그만큼 많은 테이블 랜덤 액세스가 발생한다.
- 모든 데이터를 다 읽어 거래일시 순으로 정렬을 마치고서야 출력을 시작하므로 OLTP환경에서 요구되는 빠른 응답 속도를 내기 어렵다.
- 아래는 인덱스로 소트 연산을 생략할 수 없을 때 나타나는 실행계획이다.
---------------------------------------------------------------------------
| Id | Operation | Name
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | SORT ORDER BY |
| 2 | TABLE ACCESS BY INDEX ROWID | 종목
| 3 | INDEX RANGE SCAN | 종목거래_N1
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access ("종목코드" ='KR123456')- 인덱스 선두 컬럼을 종목코드 + 거래일시순으로 구성하면 소트 연산을 생략할 수 있으며, 아래가 해당 실행계획이다.
- SQL문에 Order By절이 있는데도 옵티마이저가 Sort Order By 오퍼레이션을 생략한 사실을 확인 할 수 있다.
---------------------------------------------------------------------------
| Id | Operation | Name
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | TABLE ACCESS BY INDEX ROWID | 종목
| 2*| INDEX RANGE SCAN | 종목거래_pk
---------------------------------------------------------------------------
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
2 - access ("종목코드" ='KR123456')- 소트 연산을 생략함으로써 종목코드 ='KR123456' 조건을 만족하는 전체 레코드를 읽지 않고도 바로 결과집합 출력을 시작할 수 있게 된다. (= 부분범위 처리 가능한 상태가 됨)
- 이 원리를 잘 활용하면, 소트해야 할 대상 레코드가 무수히 많은 상황에서 극적인 선능 개선 효과를 얻을 수 있다!
🔎 Top N 쿼리
- 전체 결과집합 중 상위 N개 레코드만 선택하는 쿼리이다.
1. SQL Sever을 이용한 Top N 쿼리
-- SQL Server
SELECT TOP 10 거래일시, 체결건수, 체결수량, 거래대금
FROM 종목거래
WHERE 종목코드 = 'KR123456'
AND 거래일시 >= '20180304'
ORDER BY 거래일시2. IBM DB2sms Row Limiting을 제공
-- IBM DB2
SELECT 거래일시, 체결건수, 체결수량, 거래대금
FROM 종목거래
WHERE 종목코드 = 'KR123456'
AND 거래일시 >= '20180304'
ORDER BY 거래일시
FETCH RISRT 10 ROWS ONLY3, 오라클은 인라인뷰를 사용
-- 오라클
SELECT *
FROM ( SELECT 거래일시, 체결건수, 체결수량, 거래대금
FROM 종목거래
WHERE 종목코드 = 'KR123456'
AND 거래일시 >= '20180304'
ORDER BY 거래일시 )
WHERE ROWNUM <= 10- 아래 실행계획에서는 Sort Order By 오퍼레이션이 보이지 않는다.
- COUNT(STOPKEY) : 조건절에 부합하는 레코드가 아무리 많아도 그 중 ROWNUM으로 지정한 건 수 만큼 결과 레코드를 얻으면 거기서 바로 멈춘다는 뜻
Execution Plan
-----------------------------------------------------------------------------
SELECT STATEMENT Optimizer=ALL_ROWS
1 0 COUNT(STOPKEY)
2 1 VIEW
3 2 TABLE ACCESS (BY INDEX ROWID) OF ‘종목거래’ (TABLE)
4 3 INDEX (RANGE SCAN) OF ‘종목거래_PK’ (INDEX (UNIQUE))🔎 페이징 처리
3-Tier 환경에서 부분범위 처리를 어떻게 응용할까?
답은 페이징 처리에 있으므로, 페이징 처리에 대한 내용을 설명해보자!
- Top N 쿼리는 ROWNUM으로 지정한 건수만큼 결과 레코드를 얻으면 거기서 바로 멈춘다.
- 뒤쪽 페이지로 이동할수록 읽는 데이터량도 많아지는 단점도 있지만, 보통 앞쪽 일부 데이터만 확인하므로 문제가 되지 않는다.
3-Tier 환경에서 부분범위 처리를 활용하려면 아래와 같이 만들 수 있다.
1. 부분범위 처리 가능하도록 SQL을 작성. 부분 범위 처리가 잘 동작하는지 쿼리툴로 테스트.
2. 작성한 SQL 문을 페이징 처리용 표준 패턴 SQL Body 부분에 붙여 넣는다.
더보기
⚙️ '부분범위 처리 가능하도록 SQL을 작성한다'는 의미는 무엇일까?
인덱스를 사용 가능하도록 조건절을 구사하고, 조인은 NL조인 위주로 처리하고 Order By절이 있어도 소트 연산을 생략할 수 있도록 인덱스를 구성해 주는 것을 의미한다.
아래는 완성된 페이지 처리 SQL이며, 실행계획에 소트 연산이 없고, 세번째 라인 Count 옆에 Stopkey라고 표시된 부분을 확인해라!
SELECT *
FROM ( SELECT ROWNUM NO, A.*
FROM ( SELECT 거래일시, 체결건수, 체결수량, 거래대금
FROM 종목거래
WHERE 종목코드 = 'KR123456'
AND 거래일시 >= '20180304'
ORDER BY 거래일시
) A
WHERE ROWNUM <= (:page * 10)
)
WHERE NO >= (:page-1) * 10 + 1
---------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS
1 0 VIEW
2 1 COUNT(STOPKEY) -> NO SORT + STOPKEY
3 2 VIEW
4 3 TABLE ACCESS (BY INDEX ROWID) OF '종목거래' (TABLE)
5 4 INDEX (RANGE SCAN) OF '종목거래_PK' (INDEX)🔎 페이징 처리 ANTI 패턴
SELECT *
FROM ( SELECT ROWNUM NO, A.*
FROM ( SELECT 거래일시, 체결건수, 체결수량, 거래대금
FROM 종목거래
WHERE 종목코드 = 'KR123456'
AND 거래일시 >= '20180304'
ORDER BY 거래일시
) A
WHERE ROWNUM <= (:page * 10)
)
WHERE NO BETWEEN (:page-1) * 10 + 1 AND (:page * 10)
---------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS
1 0 | VIEW
2 1 | COUNT -> NO SORT + NO STOP
3 2 | VIEW
4 3 | TABLE ACCESS (BY INDEX ROWID) OF '종목거래' (TABLE)
5 4 | INDEX (RANGE SCAN) OF '종목거래_PK' (INDEX)불필요해보인다고 ROWNUM조건절을 제거하면 실행계획이 위처럼 바뀐다.
Sort Order By 오퍼레이션은 나타나지 않지만, Count 옆에 Stopkey가 없다. 이것은
소트 생략 가능하도록 인덱스를 구성했으므로 소트 생략은 가능하지만, Stopkey가 작동하지 않아 전체범위를 처리한다는 뜻이다!
🔎 최소값/최대값 구하기
- 최소값 또는 최대값을 구하는 SQL 실행계획을 보면 아래와 같이 Sort Aggregate 오퍼레이션이 나타난다.
SELECT MAX(SAL) FROM EMP;
---------------------------
0 SELECT STATEMENT Optimizer==ALL_ROWS
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMP' (TABLE)- 인덱스는 정렬돼 있으므로 이를 이용하면 전체 데이터를 읽지 않고도 최소 또는 최대값을 쉽게 찾을 수 있다.
- 인덱스 맨 왼쪽으로 내려가서 첫 번째 일는 값이 최소값이고, 맨 오른쪽으로 내려가서 첫 번째 읽는 값이 최대값이다.
아래는 인덱스를 이용해 최대값을 찾을 때의 실행계획이다.
CREATE INDEX EMP_X1 ON EMP(SAL);
SELECT MAX(SAL) FROM EMP;
-------------------------------------
0 SELECT STATEMENT Optimizer==ALL_ROWS
1 0 | SORT (AGGREGATE)
2 1 | INDEX (FULL SCAN (MIN/MAX)) OF 'EMP_X1' (INDEX)
⚙️ 인덱스를 이용해 최소/최대값을 구하기 위한 조건
전체 데이터를 읽지 않고 인덱스를 이용해 최소 또는 최대값을 구하려면, 조건절 컬럼과 MIN/MAX 함수 인자 컬럼이 모두 인덱스에 포함되어 있어야 한다. 즉. 테이블 액세스가 발생하지 않아야 한다.
- 인덱스 [DEPTNO + MGR + SAL] 순으로 구성된 경우
- 조건절 MAX 컬럼이 모두 인덱스에 포함되어있고, 인덱스 선두컬럼인 DEPTNO, MGR가 모두 '=' 조건이므로, 두 조건을 만족하는 범위 가장 오른쪽에 있는 값 하나를 읽는다.
- FIRST ROW는 조건을 만족하는 레코드 하나를 찾았을때 바로 멈춘다는 의미 (Fisrt Row Stopkey알고리즘 작동)
CREATE INDEX EMP_X1 ON EMP(DEPTNO, MGR, SAL);
SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30 AND MGR = 7698;
--------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer==ALL_ROWS
1 0 | SORT (AGGREGATE)
2 1 | FIRST ROW
3 2 | INDEX (RANGE SCAN(MIN/MAX)) OF 'EMP_X1' (INDEX)- 인덱스 [DEPTNO+ SAL+ MGR] 순으로 구성된 경우
- DEPTNO 조건을 만족하는 MAX값을 쉽게 찾을 수 있는 구성이다.
- DEPTNO는 액세스 조건, MGR은 필터 조건이며, 조건절 컬럼과 MAX 컬럼이 모두 인덱스에 포함되어 있으므 로 First Row StopKey 알고리즘이 작동한다.
CREATE INDEX EMP_X1 ON EMP(DEPTNO, SAL, MGR);
SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30 AND MGR = 7698;
--------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer==ALL_ROWS
1 0 | SORT (AGGREGATE)
2 1 | FIRST ROW
3 2 | INDEX (RANGE SCAN(MIN/MAX)) OF 'EMP_X1' (INDEX)- 인덱스 [SAL+ DEPTNO+ MGR]순으로 구성된 경우
- 조건절 컬럼이 둘 다 인덱스 선두컬럼이 아니므로 Index Range Scan이 불가능하다.
- DEPTNO, MGR는 모두 필터조건이며, 조건절 컬럼과 MAX 컬럼이 모두 인덱스에 포함되어 있으므로 First Row StopKey 알고리즘이 작동한다.
CREATE INDEX EMP_X1 ON EMP(SAL, DEPTNO, MGR);
SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30 AND MGR = 7698;
--------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer==ALL_ROWS
1 0 | SORT (AGGREGATE)
2 1 | FIRST ROW
3 2 | INDEX (FULL SCAN(MIN/MAX)) OF 'EMP_X1' (INDEX)- 인덱스 [DEPTNO + SAL]순으로 구성된 경우
- 조건절 컬럼과 MAX 컬럼중 어느 하나가 인덱스에 포함되지 않는 경우이며, MGR 컬럼이 인덱스에 없으므로 MGR = 7698 조건은 테이블에서 필터링 해야한다.
- 전체 레코드를 읽고 테이블에서 MGR조건을 필터링한 후 MAX 값을 구하므로, First Row StopKey 알고리즘이 작동한다.
CREATE INDEX EMP_X1 ON EMP(DEPTNO, SAL);
SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30 AND MGR = 7698;
--------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer==ALL_ROWS
1 0 | SORT (AGGREGATE)
2 1 | TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE)
3 2 | INDEX (RANGE SCAN) OF 'EMP_X1' (INDEX)🔎 Top N 쿼리 이용해 최소/최대값 구하기
- TOP N 쿼리를 통해서도 최소 또는 최대값을 쉽게 구할 수 있으며, 아래와 같이 ROWNUM<=1 조건을 이용해 Top 1 레코드를 찾으면 된다.
create index emp_x1 on emp(deptno, sal);
select *
from (
select sal
from emp
where deptno = 30
and mgr = 7698
order by sal desc
)
where rownum <= 1;
-------------------------------------------------------
0 | SELECT STATEMENT OPTIMIZER=ALL_ROWS
1 0 | COUNT (STOPKEY)
2 1 | VIEW
3 2 | TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE)
4 3 | INDEX (RANGE SCAN DESCENDING) OF 'EMP_X1' (INDEX)> Top N 쿼리에 작동하는 'Top N StopKey' 알고리즘은 모든 컬럼이 인덱스에 포함되어 있지 않아도 잘 작동한다.
즉, 위 SQL에서 MGR 컬럼이 인덱스에 없지만, 가장 큰 SAL 값을 찾기 위해 DEPTNO=30 조건을 만족하는 가장 오른쪽에서 부터 역순으로 스캔하면서 테이블을 액세스 하다가 MGR-7698 조건을 만족하는 레코드 하나를 찾았을 때 바로 멈춘다.
🔎 이력조회
- 일반 테이블은 각 컬럼의 현재 값만 저장하므로 변경되기 이전 값을 알 수 없으며, 값이 어떻게 변경되어 왔는지 과거 이력을 조회할 필요가 있다면, 이력 테이블을 따로 관리해야 한다.
⚙️ 가장 단순한 이력 조회
- 이력 데이터 조회할 때, First Row StopKey 또는 Top N StopKey 알고리즘이 작동할 수 있게 인덱스 설계 및 SQL을 구현해야 한다.
- 아래는 장비구분코드가 A001인 장비 목록을 조회하는 쿼리이며, 상태코드가 현재 값으로 변경된 날짜는 상태변경이력에서 조회하고 있다.
select 장비번호, 장비명, 상태코드,
(select max(변경일자)
from 상태변경이력
where 장비번호=p.장비번호) 최종변경일자
from 장비 P
where 장비구분코드 = 'A001'
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 10 |
| 2 | FIRST ROW | | 10 |
| 3 | INDEX RANGE SCAN (MIN/MAX) | 상태변경이력_PK | 10 |
| 4 | TABLE ACCESS BY INDEX ROWID | 장비 | 1 |
| 5 | INDEX RANGE SCAN | 장비_N1 | 1 |
--------------------------------------------------------------------------------------- 위 SQL문에서는 상태변경이력_PK인덱스가 [장비번호+변경일자+변경순번]으로 구성되어 있기 때문에 스칼라 서브쿼리 부분에서 'First Row Stopkey' 알고리즘이 작동하고 있는 것을 알 수 있다.
🔎 점점 복잡해지는 이력 조회
만약 최종 변경순번까지 이력 테이블에서 읽어야 한다면, 어떻게 쿼리해야 할까?
SELECT 장비번호, 장비명, 상태코드,
SUBSTR(최종이력, 1, 8) 최종변경일자,
TO_NUMBER(SUBSTR(최종이력, 9, 4)) 최종변경순번
FROM (
SELECT 장비번호, 장비명, 상태코드
, (SELECT MAX(H.변경일자 || LPAD(H.변경순번, 4))
FROM 상태변경이력 H
WHERE H.장비번호 = P.장비번호) 최종이력
FROM 장비 P
WHERE 장비구분코드 = 'A001'
)
-------------------------------------------------------------------------------------
Id | Operation | Name | Starts |
--------------------------------------------------------------------------------------
0 | SELECT STATEMENT | | 1 |
1 | SORT AGGREGATE | | 10 |
2 | INDEX RANGE SCAN | 상태변경이력_PK | 10 |
3 | TABLE ACCESS BY INDEX ROWID | 장비 | 1 |
4 | INDEX RANGE SCAN | 장비_N1 | 1 |
------------------------------------------------------------------------------------- 인덱스 컬럼을 가공했으므로, First Row Stopkey 알고리즘이 작동하지 않는다.
- 장비별 상태변경 이력이 많아 성능에 문제되는 쿼리라면 First Row Stopkey 알고리즘을 사용하는 것이 좋다.
더보기
⚙️ INDEX_DESC 힌트 활용
단순히 쿼리 성능을 높이기 위해 인덱스를 역순으로 읽도록 index_desc힌트를 사용하고, 첫번째 레코드에서 바로 멈추도록 ROWNUM<= 조건절을 사용했다.
이 방식은 성능은 확실히 좋지만, 문제는 인덱스 구성이 완벽해야만 쿼리가 잘 작동한다.
🔎 Sort Group By 생략
아래 SQL에 region이 선두 컬럼인 인덱스를 이용하면, Sort Group By 연산을 생략할 수 있으며, 실행계획에서는 'Sort Group By Nosort'라고 표시된 부분을 확인하면 된다.
SELECT REGION, AVG(AGE), COUNT(*)
FROM CUSTOMER
GROUP BY REGION
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25 |
| 1 | SORT GROUP BY NOSORT | | 25 |
| 2 | TABLE ACCESS BY INDEX ROWID | CUSTOMER | 1000K |
| 3 | INDEX FULL SCAN | CUSTOMER_X01 | 1000K |
--------------------------------------------------------------------------------------
그림과 같이 보면 위 실행계획을 어떻게 수행하는지 쉽게 이해할 수 있다.
1. 인덱스에 'A' 구간을 스캔하면서 테이블을 액세스 하다가 'B'를 만나는 순간, 그때까지 집계한 값을 운반단위에 저장한다.
2. 계속해서 'B' 구간을 스캔하다가 'C'를 만나는 순간, 그때까지 집계한 값을 운반단위에 저장한다.
3. 계속해서 'C' 구간을 스캔하다가 'D'를 만나는 순간, 그때까지 집계한 값을 운반단위에 저장한다. Array Size가 3이므로 지금까지 읽은 A, B, C에 대한 집계결과를 클라이언트에게 전송하고 다음 Fetch Call이 올 때까지 기다린다.
4. 클라이언트로부터 다음 Fetch Call이 오면, 1~3 과정을 반복한다.
이처럼 인덱스를 이용해 Nosort 방식으로 Group By를 처리하면, 부분범위 처리가 가능해진다.
4. Sort Area를 적게 사용하도록 SQL 작성
소트 연산이 불가피하다면 메모리 내에서 처리를 완료할 수 있도록 노력해야 하며, Sort Area 크기를 늘리는 방법이 있지만, 그전에 Sort Area를 적게 사용할 방법부터 찾는 것이 순서이다.
🔎 소트 데이터 줄이기
- 특정기간에 발생한 주문 상품 목록을 파일로 내리고자 한다. 아래 두 SQL 중 어느 쪽이 Sort Area를 적게 사용할까?
-- 1번 쿼리
-- 레코드당 107(30+30+10+20+17) 바이트로 가공한 결과집합을 Sort Area에 담는다.
select lpad(상품번호, 30) || lpad(상품명, 30) || lpad(고객id, 10)
|| lpad(고객명, 20) || to_char(주문일시, 'yyyymmdd hh24:mi:ss')
from 주문상품
where 주문일시 between :start and :end
order by 상품번호
---------------------------------------------------------------------------
-- 2번 쿼리
-- 가공하지 않은 상태로 정렬을 완료 한 후 최종 출력할 때, 가공한다.
select lpad(상품번호, 30) || lpad(상품명, 30) || lpad(고객id, 10)
|| lpad(고객명, 20) || to_char(주문일시, 'yyyymmdd hh24:mi:ss')
from (
select 상품번호, 상품명, 고객id, 고객명, 주문일시
from 주문상품
where 주문일시 between :start and :end
order by 상품번호
)> 2번 쿼리가 Sort Area를 훨씬 적게 사용한다.
이유는 1번 SQL은 레코드 당 107(30+30+10+20+17) 바이트로 가공한 결과 집합을 Sort Area에 담고, 2번 SQL은 가공하지 않은 상태로 정렬을 완료하고 나서 최종 출력할 때 가공하기 때문이다.
- 아래 두SQL 중 어느 쪽이 Sort Area를 더 적게 사용할까?
-- 1번 쿼리
-- 모든 컬럼을 Sort Area에 저장한다. (716MB / 14.41sec)
select * from 예수금원장 order by 총예수금 desc
-- 2번 쿼리
-- 계좌번호, 총예수금만 Sort Area에 저장한다. (17M / 1.2sec)
select 계좌번호, 총예수금 from 예수금원장 order by 총예수금 desc> 2번 쿼리가 적게 사용한다.
이유는 1번 SQL은 모든 컬럼을 Sort Area에 저장하고, 2번 SQL은 계좌번호, 총예수금만 저장하기 때문이다.
🔎 Top N 쿼리의 소트 부하 경감 원리
인덱스로 소트 연산을 생략할 수 없을 때, Top N 쿼리가 어떻게 작동하는지 확인해보자.
전교생 1000명 중 가장 큰 학생 열명을 선발하려고 한다. 어떤 방법이 있을까?
1. 전교생을 운동장에 집합시킨다.
2. 맨 앞줄 맨 왼쪽에 있는 학생 열 명을 단상 앞으로 불러 키 순서대로 세운다.
3. 나머지 990명을 한 명씩 교실로 들여보내면서 현재 Top 10 위치에 있는 학생과 키를 비교한다. 더 큰 학생이 나타나면, 현재 Top 10 위치에 있는 학생을 교실로 들여보낸다.
4. Top 10에 새로 진입한 학생 키에 맞춰 자리를 재배치한다.
전교생이 다 교실로 들어갈 때까지 3번과 4번 과정을 반복하면, 최종적으로 그 학교에서 가장 키 큰 학생 열 명만 운동장에 남는다 이것이 'Top N 소트 알고리즘'이다.
select *
from (
select ronum no, a.*
from
(
select 거래일시, 체결건수, 체결수량, 거래대금
from 종목거래
where 종목코드 = 'KR123456'
and 거래일시 >= '20180304'
order by 거래일시
) a
where ronum <= (:page * 10)
)
where no >= (:page-1)*10+1아래 인덱스로 소트 연산을 생략할 수 없어 Table Full Scan 방식으로 처리할때의 SQL 트레이스다.
실행계획에 Sort Order By 오퍼레이션이 나타났다. 왜냐하면 Table Full Scan 대신 종목코드가 선두인 인덱스를 사용할 수 있지만, 바로 뒤 컬림이 거래일시가 아니면 소트 연산을 생략할 수 없기때문이다.
Sort Order By 옆에 'Stopkey' 가 표시를 보면, Sort Order By 오퍼레이션을 수행하지만, 'Top N 소트' 알고리즘이 작동한다는 표시를 볼 수 있다.
이 방식으로 처리하면, 대상 집합이 아무리 커도 많은 메모리 공간을 필요로 하지 않다. 이것이 'Top N 소트' 알고리즘이 소트 연산 횟수와 Sort Area 사용량을 줄여주는 원리이다!
🔎 Top N 쿼리가 아닐때 발생하는 소트 부하
- SQL을 더 간결하게 표시하기 위해 아래와 같이 Order by 아래쪽 ROWNUM 조건절을 제거하고 수행해 보자.
select *
from (
select ronum no, a.*
from
(
select 거래일시, 체결건수, 체결수량, 거래대금
from 종목거래
where 종목코드 = 'KR123456'
and 거래일시 >= '20180304'
order by 거래일시
) a
)
where no between (:page-1)*10+1 and (:page * 10)
---------------------------------------------------------------
0 STATEMENT
10 VIEW
10 COUNT
10 VIEW
10 SORT ORDER BY
49857 TABLE ACCESS FULL 종목거래실행 계획을 보면, StopKey가 사라졌으며, 이것은 Top N 소트 알고리즘이 작동하지 않았다는 뜻이다!
그 결과로 Physical Read와 Physical Write가 발생했다!
🔎 분석함수에서의 Top N 소트
- 윈도우 함수 중 rank 나 row_number 함수는 max 함수보다 소트 부하가 적은데 그이유는 Top N 소트 알고리즘이 작동하기 때문이다.
'Study > 친절한 SQL 튜닝' 카테고리의 다른 글
| [DB] 6장. DML 튜닝(2) (0) | 2025.04.20 |
|---|---|
| [DB] 6장. DML 튜닝(1) (0) | 2025.03.30 |
| [DB] 4장. NL조인 (0) | 2025.02.25 |
| [DB] 3장. 인덱스 튜닝(2) (0) | 2025.01.31 |
| [DB] 3장. 인덱스 튜닝(1) (0) | 2025.01.31 |