권순용의 실전 SQL Tuning 8강 SORT와 IN/OR 실행 계획을 이해하자
03:12 2017-01-17 화
SQL 8차시 SORT와 IN/OR 실행 계획을 이해하자
1/24
9i까지 group by 에 의해 정렬 수행
10g부터는 order by 로 정렬 수행.
in절에 포함된 컬럼에 정렬이 있다면, order by 를 하지 않고도 정렬이 된다.
in/or를 이용하면 된다.
그런데 그걸 모르는 경우, 이중 정렬을 하여 성능 저하가 일어난다.
2/24
SORT 관련 실행 계획
IN/OR 관련 실행 계획
3/24
SORT 관련 실행 계획 : SORT를 발생시키는 실행 계획. ORDER BY, GROUP BY, DISTINCT 등에 의해 발생함.
IN/OR 관련 실행 계획 : IN절 또는 OR절을 사용하면 CONCETENATION 실행계획 및 INLIST ITERATOR 실행 계획이 생성될 수 있다. 해당 실행 계획을 IN/OR 관련 실행 계획이라 한다.
4/24
SORT 관련 실행 계획
정렬(SORT) => 엄청난 성능 저하
정렬 양이 많다면 디스크 정렬을 수행하기 때문임.
5/24
정렬의 실행계획
(1) SORT(AGGREATE) : 집합 함수에 대한 실행 계획
(2) SORT(UNIQUE) : UNIQUE 정렬에 대한 실행 계획
(3) SORT(GROUP BY) : GROUP BY 에 대한 실행 계획
(4) SORT(ORDER BY) : ORDER BY 에 대한 실행 계획
6/24
(1) SORT(AGGREATE)
SELECT MAX(거래금액)
FROM 거래내역
WHERE 거래일자 = ‘20080801’;
–> 실행계획
SELECT STATEMENT
SORT(AGGREATE)
TABLE ACCESS (FULL) OF ‘거래내역’
ㅁ집합 함수에 따른 수행의 차이
MAX : 실제 정렬은 발생하지 않음. 하나씩 엑세스하며 큰 값 하나만 남기고 나머지 제거
MAIN : 실제 정렬은 발생하지 않음. 하나씩 엑세스하며 작은 값 하나만 남기고 나머지 제거
AVG : 모든 데이터 엑세스하여 평균 추출
SUM : 실제 정렬은 발생하지 않음. 데이터를 엑세스하여 값을 더함
COUNT : 데이터를 한 건 한 건 엑세스하여 COUNT를 누적
7/24
(2) SORT(UNIEUQ)
SELECT 사원번호, 급여
FROM 급여_이력
WHERE 사원번호 IN (SELECT 사원번호 FROM 사원 WHERE 입사일자 LIKE ‘2008%’);
8/24
(2-2) SORT(UNIQUE)
SELECT DISTINCT 사원번호, 급여
FROM 급여
WHERE 부서번호 = ’10’;
정렬에 의한 성능 저하 가능함
9/24
(3) SORT(GROUP BY)
SELECT 고객번호, SUM(거래금액) 총거래금액
FROM 거래내역
GROUP BY 고객번호;
SELECT STATEMENT
SORT(GROUP BY)
TABLE ACCESS (FULL) OF ‘거래내역’
GROUP BY – NO SORT
(실제 정렬은 발생하지 않아도 정렬된 그루핑 데이터를 추출할 수 있다!)
SELECT 고객번호, SUM(거래금액) 총거래금액
FROM 거래내역
GROUP BY 고객번호;
–> 실행계획
SELECT STATEMENT
SORT(GROUP BY NO SORT)
TALBE ACCESS (BY INDEX ROWID) OF ‘거래내역’
INDEX (REANGE SCAN) OF ‘고객번호_IDX’
10/24
(4) SORT(ORDER BY)
실제 정렬이 발생함.
SELECT 거래일자, 고객번호, 거래금액, 상태 FROM 거래내역 ORDER BY 거래일자;
실행 계획1
SELECT STATEMENT SORT(ORDER BY) TABLE ACCESS (FULL) OF ‘거래내역’; => 정렬 발생
실행 계획2
SELECT STATEMENT SORT(NO SORT) TABLE ACCESS (BY INDEX ROWID) OF ‘거래내역’
INDEX (FULL SCAN) OF ‘거래일자_IDX’; => 정렬 발생 안함
11/24
IN/OR 관련 실행 계획
(1) CONCATENATION
(2) INLIST ITERATOR
(3) FULL SCAN
12/24
CONCATENATION 실행계획
두 가지에 관하여 합집합
SELECT 부서번호, 사원번호, 이름 FROM 사원 WHERE 지역 IN(‘서울’, ‘부산’);
최종적으로 UNION ALL 연산자로 바뀌어 출력됨
IN (‘서울’, ‘부산’) 일 경우 부산부터 추출되고, 서울이 뒤에 추출된다.
이를 이용한다면 IN절 SQL에서 ORDER BY에 의한 정렬 감소시킬 수 있음
13/24
INLIST ITERATOR
SELECT 거래일자, 고객번호, 거래금액, 상태
FROM 거래내역
WHERE 거래내역 IN (‘서울’, ‘부산’, ‘대구’);
14/24
CONCATENATION 실행계획 과 INLIST ITERATOR 의 차이
정렬 순서
INLIST ITERATOR 실행계획 : IN절의 앞에서부터 데이터 추출
CONCATENATION 실행계획 : IN절의 뒤에서부터 데이터 추출
생성 조건
INLIST ITERATOR : IN 서브 쿼리를 하는 경우
CONCATENATION : IN 절에 상수를 사용하는 경우
15/25
IN : 선분 조건을 점 조건으로 변경시 사용
추출 순서 지정 가능
(SQL 최적화시 선분 조건을 점 조건으로 변경하고자 할 때 IN 연산자 많이 사용)
OR : OR 연산자는 추가될 때마다 처리 범위 증가
(OR는 UNION ALL로 변환됨)
OR 연산자를 사용한 SQL이 내부적으로 UNION ALL 연산자로 실행된다면
실행 계획에는 CONCATENATION 실행 계획이 생성
SORT(GROUP BY) : GROUP BY 절에 사용한 컬럼에 의해 정렬된 값 출력
HASH(GROUP BY) : 정렬값 추출되지 않음
소트 그룹바이보다는 해쉬 그룹바이가 더 빠름.
문제. SET AUTOT TRACEONLY EXP
SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTIMENT_ID IN (’10’, ’20’, ’30’, ’50’);
-> 답 : 실행계획에 INLIST ITERATOR 포함됨
문제. SORT는 성능 저하를 발생시킵니다.
그렇다면 어떻게하면 SORT를 감소시킬 수 있을까요?
=> ORDER BY 등을 사용하지 않고 정렬된 데이터를 추출하이 위해서는
어딘가에 정렬된 데이터가 존재해야 한다.
(SORT를 제거하고 SQL 결과를 추출하고 싶다면 인덱스 추천
MIN, MAX, AVERAGE –> SORT(AGGREATE)
GROUP BY –> SORT(GROUP BY)
ORDER BY –> SORT[ORDER BY]
최신 버전일수록 해쉬로 전환중
해쉬가 쏘트보다 빠르기 때문임.
생성 조건
INLIST ITERATOR : IN 서브 쿼리를 하는 경우 (IN절의 앞에서부터 데이터 추출)
CONCATENATION : IN 절에 상수를 사용하는 경우 (IN절의 뒤에서부터 데이터 추출)
SORT 실행 계획이 아닌 것은?
1) SORT(UNIQUE)
2) SORT(DISTINCT)
3) SORT(ORDER BY)
4) SORT(GROUP BY)
답 : 2번
IN 에서 발생할 수 없는 실행 계획은?
1) CONCATENATION 실행 계획
2) INLIST ITERAROT 실행 계획
3) FULL SCAN 실행 계획
4) SAMPLE 실행 계획
답 : 4
OR 절에서 발생할 수 있는 실행 계획은?
1) SKIP SCAN 시행 계획
2) CONCATENATION 실행 계획
3) SORT(UNIQUE) 실행 계획
4) STOPKEY 실행 계획
답 : 2번
(OR연산자는 테이블 FULL SCAN 실행 계획 또는 CONCATENATION 실행계획으로
실행 계획이 생성될 수 있습니다.)