01:22 2017-01-17 화
권순용의 실전 SQL Tuning
7차시 테이블과 인덱스 관련 실행 계획은 기본이다
1/45
– FULL SCAN 이 인덱스보다 빠를 수도 있다.
2/45
대표적인 실행 계획은 아래와 같다.
– 테이블 관련 실행 계획
– 인덱스 관련 실행 계획
3/45
TABLE FULL SCAN : 테이블을 엑세스하는 방법 중 테이블을 처음부터 끝까지 모두 엑세스하는 실행 계획
INDEX RANGE SCAN : 인덱스를 엑세스하는 경우 인덱스 범위 스캔은 일정 범위를 엑세스 하는 것을 의미함.
보통의 경우 BETWEEN, LIKE 등 부등호 연산자를 사용할 경우 생성되는 실행 계획이다.
Parallel Processing : 이 아키텍처는 테이블을 Full Scan하거나 또는 인덱스를 Full Scan하는 경우에 여러 개의 프로세스를 가동시켜 엑세스하는 아키텍처이다.
4/45
1. 실행 계획의 종류
테이블 관련, 인덱스 관련, 정렬 관련, IN/OR 관련,
집합 연산자 관련
뷰 관련, STOPKEY 관련, 파티션 관련, 조인 관련.
5/45
테이블 엑세스 방법은 여러 개.
인덱스 이용 방법도 여러 개. -> 선택에 따라 많은 성능 차이 발생됨.
6/45
2. 테이블 관련 실행 계획
(1) 종류
(2) FULL SCAN
(3) ROWID SCAN
(4) SAMPLE
(1) 종류
테이블을 엑세스 하는 방법 == 결국, 테이블 관련 실행 계획이 됨.
(1-1) BY INDEX ROWID : 인덱스 스캔을 통한 테이블 엑세스
(1-2) FULL SCAN : 테이블 처음부터 끝까지 엑세스
(1-3) BY USER ROWID : SQL 에 설정된 ROWID에 의한 테이블 엑세스 (ROWID==데이터베이스의 주소임)
(1-4) SAMPLE : 표본 데이터 추출
ㅁ 테이블을 Full Scan하는 경우
– WHERE 조건이 없는 경우
– WHERE 조건이 있지만 INDEX가 없는 경우
– WHERE 조건이 있고 조건에 INDEX가 있지만 옵티마이저에 의해 FULL SCAN 선택
– 힌트에 의해 FULL SCAN 선택
ㅁ FULL SCAN : 테이블의 첫 번째 블록부터 마지막 블록까지 모든 데이터 블록을 엑세스. 그러므로 성능을 고려하여 다중 블록 I/O가 발생됨. 반면, 인덱스를 이용하면 단일 블록 I/O를 수행하게 됨.
ㅁ FULL SCAN
실행계획 : SELECT STATEMENT TABLE ACCESS (FULL) OF ‘TAB1’
특징 1. 일반적으로 테이블은 1개의 프로세스에 의해 엑세스 됨. 이걸 개선하기 위해 Parallel Processing 아키텍처가 고안됨. 여러 개의 프로세스를 기동시켜 엑세스하는 아키텍처. 자원을 최대한 이용, 대용량 테이블을 빠르게 엑세스할 수 있게 됨.
특징 2. 다중 블록 I/O : 한 번에 여러 개의 데이터 블록을 엑세스.(한 번에 엑세스하는 블록 개수는 DB_FILE_MULTI_BLOCK_READ_COUNT 파라미터에 의해 결정됨. 해당 값이 16이면 한 번에 16개의 데이터 블록을 다중 엑세스.)
특징 3. FULL SCAN이 인덱스 사용보다 유리할 수 있음 : 랜덤 엑세스(인덱스 엑세스 후 테이블을 엑세스)하면, 단일 블록 I/O가 수행됨. 테이블에서 많은 데이터를 엑세스하는 경우 FULL SCAN이 성능에 유리함 (대용량 테이블에서3~5% 이상)
9/45
ㅁROWID SCAN
– ROWID는 유니크하다.
– ROWID는 주소값이라고 생각하면 된다.
– 인덱스로 테이블 엑세스 : 인덱스를 통해 ROWID를 획득하고, ROWID로 테이블을 엑세스한다.
ㅁROWID 실행계획
SELECT STATEMENT TABLE ACCESS (BY INDEX ROWID) OF 부서
INDEX (RANGE SCAN) OF 지역_IDX
10/45
ㅁSQL에서 ROWID 제공시
SELECT 고객번호, 고객이름, 입금액
FROM 계좌 A
WHERE ROWID IN (SELECT SUBSTR(최대거래금액11, 16) RID
FROM (SELECT MAX(거래금액||ROWID)
FROM 거래내역
GROUP BY 고객번호)
);
–> SQL 에서 명시적으로 ROWID를 사용할 수 있으며, 실행계획에서는 BY ROWID로 바뀐다.
SELECT STATEMENT
NESTED LOOP
VIEW
SORT (GROUP BY)
INDEX (RANGE SCAN) OF ‘고객번호_IDX’
TABLE ACCESS (BY ROWID) OF ‘계좌’
11/45
ㅁ내부적 ROWID 제공시
SELECT LEVEL, A.EMPNO FROM EMP
CONNECT BY PRIOR EMPNO = MGRNO
START WITH EMPNO = ‘1111’;
SELECT STATEMENT
CONNECT BY (WITHOUT FILTERING)
NESTED LOOPS
INDEX (UNIQUE SCAN) OF ‘EMPNO_IDX’
TABLE ACCESS (BY USER ROWID) OF ‘EMP’
NESTED LOOPS
BUFFER (SORT)
CONNECT BY PUMP
TABLE ACCESS (BY INDEX ROWID) OF ‘EMPNO’
INDEX (RANGE SCAN) OF ‘MGRNO_IDX’
12/45
SAMPLE
SAMPLE 실행 계획은 SQL에 SAMPLE 옵션을 설정했을 경우에 생성됨
SQL> SELECT 거래일자, 고객명, 거래금액
FROM 거래내역 SAMPLE(10);
SELECT STATEMENT
TABLE ACCESS (SAMPLE) OF ‘거래내역’
13/45
3. 인덱스 관련 실행 계획
(1) 종류
(2) 인덱스 유일 스캔 : UNIQUE SCAN
(3) 인덱스 범위 스캔 : INDEX RANGE SCAN
(4) 인덱스 앤드-이퀄 스캔 : INDEX AND-EQUALS SCAN
(5) 인덱스 전체 스캔 : INDEX FULL SCAN
(6) 빠른 인덱스 전체 스캔 : INDEX FAST FULL SCAN
(7) 인덱스 병렬 스캔 : INDEX PARELLEL SCAN
(8) 생략 스캔 : INDEX SKIP SCAN
(9) 인덱스 민/맥스 스캔 : INDEX MIN/MAN SCAN
(10) 인덱스 조인 : INDEX JOIN
(2) 인덱스 유일 스캔 : UNIQUE SCAN
인덱스와 테이블 모두에서 무조건 1건의 데이터만을 엑세스함
UNIQUE SCAN : 한 건의 인덱스 값만 엑세스(점 조건 : =, IN)
RANGE SCAN : 여러 건의 인덱스 값을 엑세스
ㅁUNIQUE SCAN 특징
(1) 인덱스 값 : 인덱스 스캔을 통한 테이블 엑세스
(2) 높은 우선순위 : 실행 계획을 생성하는 옵티마이저에 의해 높은 우선 순위
(3) 정렬 제거 : IN 절에서 발생하는 정렬 제거 기능
– IN 절에서 제공하는 상수 값에 대해 정렬이 발생.
– IN 절은 내부적으로 유니크 정렬하게 됨. (변경 전 SQL이 ‘1’, ‘1’, ‘2’ 라면 ‘1’, ‘2’로 변경됨)
– ※ UNIQUE 인덱스를 효과적으로 이용할 수 있다면, UNIQUE한 값을 생성하기 위해 수행하는
IN 절의 UNIQUE 정렬을 제거할 수 있다.
18/45
ㅁ INDEX의 RANGE SCAN 특징
– 인덱스의 RANGE SCAN : 인덱스 관련 실행계획 중 가장 많이 사용되고 생성됨
– 인덱스 컬럼을 WHERE 조건에서 LIKE, BETWEEN, <, > 사용
– UNIQUE 인덱스를 이용하면서 선분 조건을 사용한 경우
19/45
SELECT 배송일자, 고객번호, 상품, 주소
FROM 배송내역
WHERE 배송일자 LIKE ‘200803%’;
–> 실행계획
SELECT STATEMENT
TABLE ACCESS (BY INDEX ROWID) OF ‘배송내역’
INDEX (RANGE SCAN) OF ‘배송일자_IDX’
위 실행 계획에는 RANGE SCAN와 BY INDEX ROWID 가 같이 등장한다.
여기서 BY INDEX ROWID는 RANGE SCAN 이 발생한 이후에 랜덤 엑세스가 발생했다는 뜻이다.
– RANGE SCAN의 단점 == 랜덤 엑세스의 단점
단점 해결법 1 : 단점을 극복하려면 선분 조건 -> 점 조건으로 변경
22/45
단점 해결법 2 : 선분 조건(BETWEEN, LIKE) -> 점 조건을 위하여 아예 날짜 테이블을 생성하여 활용.
SELECT 사용일자, 대상
FROM 이벤트
WEHRE 사용일자 IN (SELECT 사용일자
FROM CALENDAR
WHERE 사용일자
BETWEEN ‘20080301’ AND ‘20080430’ )
AND 대상 = ‘A’
위의 SQL을 실행하고자 할 때, BETWEEN을 쓰지 말고 아예 날짜 테이블을 셀렉트해서 점 조건으로 만들기.
INSERT INTO CALENDAR
SELECT TO_CHAR(TO_DATE(‘20010101’, ‘YYYYMMDD’) + ROWNUM, ‘YYYYMMDD’) 사용일자
FROM TAB1
WHERE ROWNUM <=3650;
23/45
ㅁ풀 스캔
인덱스의 풀 스캔과 테이블의 풀 스캔의 차이?
– 보통의 경우 인덱스 FULL SCAN 실행 계획이 생성된다면 SQL 최적화 대상이 된다.
– (1) (인덱스 첫 번째 칼럼을 이용하여) 정렬 제거를 할 경우
– (2) 엑세스 하고자 하는 컬럼이 인덱스에 모두 존재할 경우
– 인덱스 FULL SCAN이 일어나게 된다.
– 인덱스의 FULL SCAN은 정렬을 제거하기 위해 사용 가능하지만, 랜덤 엑세스를 발생시킨다면
성능 저하를 발생시키기 때문에 최적화 대상이 됨.
ㅁ인덱스 풀스캔 실행계획
SELECT STATEMENT
INDEX (FULL SCAN) OF ‘EMPNO_IDX’;
25/45
– 인덱스의 FULL SCAN 실행 계획은 엑세스하는 첫 번째 컬럼에 의해 정렬된 데이터가 자동 추출됨
– 첫 번째 컬럼이 동일한 값이라면 두 번째 컬럼에 의해 정렬된 데이터가 추출됨
– 잘 이용하면 ORDER BY 없이 정렬됨 (정렬 제거)
26/45
FAST FULL SCAN
인덱스의 FULL SCAN 계획에서 단일 블록 I/O 부분만 다중 블록 I/O로 변경된 실행 계획
SELECT EMPNO, SAL FROM EMPLOYEES WHERE DEPTNO = ’10’;
–>
SELECT STATEMENT
INDEX (FAST FULL SCAN) OF ‘EMPNO_IDX’;
27/45
FAST FULL SCAN
(1) PARALLEL PROCESSING 사용 가능
(2) 다중 블록 I/O 수행
(3) 정렬 수행 불가
– 정렬 필요없는 상황에서, 인덱스 엑세스만으로 원하는 모든 값을 추출할 수 있을 경우
28/45
테이블의 총 건수를 추출할 경우 매우 유용.
SELECT /*+ PARALLEL_INDEX(TB,TB_PK,4) */ COUNT(*) FROM 거래내역 TB;
(정렬이 필요없으므로 다중 블록 I/O를 쓰면 빠르다.)
29/45
AND-EQUALS 조건
SELECT 거래일자, 고객번호, 거래금액, 상태
FROM 거래내역 A
WHERE 거래일자 = ‘20080425’
AND 가맹점_번호 = ‘1’;
두 컬럼 다 EQUAL(=)로 비교되고, 두 컬럼이 각각 인덱스가 걸려있을 경우, 머지 작업 수행.
30/45
실행계획
SELECT STATEMENT
TABLE ACCESS (BY INDEX ROWID) OF ‘거래내역’
AND EQUALS
INDEX (RANGE SCAN) OF ‘거래일자_IDX’
INDEX (RANGE SCAN) OF ‘가맹점_번호_IDX’
31/45
AND-EQUALS 특징
(1) 성능 저하 예상 – 옵티마이저가 잘 선택하지 않는 계획
(2) 결합 인덱스가 더 효과적
33/45
SKIP SCAN
유용하게 사용 가능한 계획.
SELECT 거래일자, 고객번호, 거래금액, 상태
FROM 거래내역
WHERE 거래일자 = ‘20080801’
인덱스가 가맹점_번호+거래일자 인덱스일 경우 인덱스 타지 않을 것임 (오라클 9i 이전)
이럴 경우는
SELECT 거래일자, 고객번호, 거래금액, 상태
FROM 거래내역
WHERE 거래일자 = ‘20080801’
AND 가맹점_번호 IN (SELECT 가맹점 FROM 가맹점_마스터 WHERE 1=1);
(더미 조건 == 인덱스를 동작시키기 위해 포함시키는 조건)
실행 계획
SELECT STATEMENT
TABLE ACCESS (BY INDEX ROWID) OF ‘거래내역’
INDEX (SKIP_SCAN) OF ‘가맹정_번호_거래일자_IDX’
TABLE ACCESS (BY
35/45
MAX 최적화 전
SELECT MAX(거래금액) 최대금액
FROM 거래내역
WHERE 거래일자 = ‘20080801’;
MAX 최적화 후
SELECT /*+ INDEX_DESC(A,거래일자_거래금액_IDX) */
MAX(거래금액) 최대금액
FROM 거래내역
WHERE 거래일자 = ‘20080801’
AND ROWNUM <= 1;
37/45
INDEX JOIN
SELECT 거래일자, 고객번호, 거래금액, 상태
FROM 거래내역
WHERE 가맹점_번호 = ‘1’
AND 거래일자 > ‘20080801’;
가맹점_번호 + 고객번호 인덱스
거래일자 + 거래금액 인덱스
ㅁINDEX JOIN과 INDEX AND-EQUALS 실행 계획의 차이
– AND-EQUALS : = 연산자로 사용해야 가능, 테이블 엑세스 가능
– JOIN : 부등호 및 연산자 가능, 테이블 엑세스하지 않음
=> 결합 인덱스로 성능 최적화 가능
1. EMPLOYEES에서는 인덱스가 존재하지 않는다. 알맞은 실행 계획은?
(1) FULL
(2) SAMPLE
(3) BY INDEX ROWID
(4) BY ROWID
답 : 1
2. 인덱스 생성 – CREATE UNIQUE INDEX EMPLOYEES_PK ON EMPLOYEES(EMPLOYEE_ID);
(1) RANGE SCAN
(2) UNIQUE SCAN
(3) SKIP SCAN
(4) FULL SCAN
답 : 2
해설 : 실행 계획
SELECT STATEMENT
TALBE ACCESS BY INDEX ROWID
INDEX UNIQUE SCAN
3. 인덱스를 이용하는 방법에 어떤게 존재할까요?
인덱스 유일 스캔 : UNIQUE SCAN
인덱스 범위 스캔 : INDEX RANGE SCAN
인덱스 앤드-이퀄 스캔 : INDEX AND-EQUALS SCAN
인덱스 전체 스캔 : INDEX FULL SCAN
빠른 인덱스 전체 스캔 : INDEX FAST FULL SCAN
인덱스 병렬 스캔 : INDEX PARELLEL SCAN
생략 스캔 : INDEX SKIP SCAN
인덱스 민/맥스 스캔 : INDEX MIN/MAN SCAN
인덱스 조인 : INDEX JOIN
ㅁ요약
1. 테이블을 엑세스 하는 방법 == 테이블 관련 실행 계획
(1) BY INDEX ROWID : 인덱스 스캔을 통한 테이블 엑세스
(2) FULL SCAN : 테이블 처음부터 끝까지 엑세스
(3) BY USER ROWID : SQL 에 설정된 ROWID에 의한 테이블 엑세스 (ROWID==데이터베이스의 주소임)
(4) SAMPLE : 표본 데이터 추출 : 10 이라고 적으면 10% 에 해당하는 표본 추출
2. 인덱스 관련 실행 계획
(1) 인덱스 유일 스캔 : UNIQUE SCAN. 실제 유니크(또는 PK) 속성으로 인덱스 줘야 하고, =(이퀄) 조건을 써야 함.
(2) 인덱스 범위 스캔 : INDEX RANGE SCAN
(3) 인덱스 앤드-이퀄 스캔 : INDEX AND-EQUALS SCAN. 싱글 컬럼 인덱스 2개를 EQUAL로 조인한다. 결합으로 바꿔주는게 효과적. 옵티마이저는 랜덤 엑세스를 줄이기 위해 인덱스 조인(인덱스 앤드-이퀄 스캔)을 감행함.
(4) 인덱스 전체 스캔 : INDEX FULL SCAN. 단일 블록 I/O. 정렬 제거에 쓰임
(5) 빠른 인덱스 전체 스캔 : INDEX FAST FULL SCAN. 다중 블록 I/O. 정렬 안됨. 카운트 셀 때 좋다.
(6) 인덱스 병렬 스캔 : INDEX PARELLEL SCAN
(7) 생략 스캔 : INDEX SKIP SCAN. 인덱스의 첫 번째 컬럼이 WHERE 절에 존재하지 않더라도 이용할 수 있게 하는 실행 계획.
(8) 인덱스 민/맥스 스캔 : INDEX MIN/MAN SCAN
인덱스 조인 : INDEX JOIN
문제
1. 테이블 관련 실행 계획으로 틀린 것은?
(1) BY INDEX ROWNID 실행 계획
(2) FULL 실행 계획
(3) BITMAP CONVERSION 실행 계획
(4) SAMPLE 실행 계획
답 : 3
2. 인덱스 관련 실행 계획으로 틀린 것은?
(1) RANGE SCAN 실행 계획
(2) FULL SCAN 실행 계획
(3) SKIP SCAN 실행 계획
(4) JUMP SCAN 실행 계획
답 : 4
3. SKIP SCAN 실행 계획의 설명으로 올바른 것은?
(1) 인덱스의 첫 번째 컬럼이 WHERE 절에 없어도 인덱스 이용 가능
(2) MIN/MAX 값을 추출하는 실행 계획
(3) 인덱스들을 조인하는 실행 계획
(4) 인덱스들을 머지하는 실행 계획
