제 5장 인덱스와 조인
Index Range Scan Descending
로그 버퍼 
인덱스만 읽고 처리
IOT의 활용
제 1절 인덱스 기본 원리
1. 인덱스 구조
2. 다양한 인덱스 스캔 방식
3. 인덱스 종류
제 2절 인덱스 튜닝
1. 인덱스 튜닝 기초
2. 테이블 Random 액세스 최소화
3. 인덱스 스캔범위 최소화
4. 인덱스 설계
제 3절 조인 기본 원리
1. Nested Loop Join
2. Sort Merge Join
3. Hash Join
4. Scalar Subquery
제 4절 고급 조인 기법
1. 인라인 뷰 활용
2. 배타적 관계의 조인
3. 부등호 조인
4. Between 조인
5. ROWID 활용
???
제 1절 인덱스 구조 및 종류의 이해
1. 인덱스 구조
Root Node : 가장 상위 노드 / 하위의 Branch Node 수 만큼의 Row
Branch Node : Root와 Leaf의 연결 고리 / 자기 하위의 Leaf Node 수 만큼의 Row
Leaf Node : Key + RowID로 구성 / Key 순서대로 정렬 / 이전, 이후 Leaf의 Chain
수직적 탐색
Root -Branch -Leaf
읽고자 하는 시작점 검색
Random Access
수평적 스캔
Leaf Block의 시작점부터 종료점 까지 (종료보다 하나 더 읽고 없으면 멈춤)
Sequential Access
2. 인덱스 기본원리
인덱스 사용이 불가능 하거나 범위스캔(Range Scan)이 불가능한 경우
인덱스 컬럼의 가공(좌변 가공)
NULL의 검색
묵시적 형변환 (문자가 변함 숫자나 날짜로, Like는 숫자가 문자로 바뀜)
부정검색
3. 다양한 인덱스 스캔방식
Index Range Scan
인덱스를 구성하는 선두 컬럼을 조건절에 사용
Index Full Scan
조회 조건의 인덱스가 있으나, 선두 컬럼이 아니지만 Full Table Scan보다 이익이 있을때
Index Unique Scan
수직적 스캔만 발생
Unique 인덱스이고 = 조건일 경우만 사용
Index Skip Scan
인덱스 선두 컬럼이 아니며, 선두 컬럼의 Distinct가 매우 낮을 때 사용
인덱스 선두 컬럼이 Between, Like, 부등호 일 때 사용 가능
In-List Iterator
Index Fast Full Scan
Multi-Block I/O로 전체 Index를 Full Scan (속도가 빠름)
Index의 논리적 순서와 상관없이 물리적 순서대로 Read
결과는 인덱스 키 컬럼의 순서와 무관함
찾고자 하는 끝점을 찾아서 거꾸로 찾아 올라감
Index Combine
4. Oracle DBMS 구조
Insert, Delete, Update문
DB 복구 시 위의 SQL을 재 실행하기 위해 필요한 정보
DML로 수정된 Before, After 의미
DML 발생 시 DB Buffer Cache 보다 앞서 저장
User Commit 등의 상화에 Redo Log File에 저장
Append 방식으로 소량의 Write만으로 데이터 정합성 보장 가능 (퍼스트 커밋의 원리)
5. 테이블 랜덤 엑세스 부하
RowID의 구조
데이터 오브젝트 번호(6자리)
데이터 파일 번호(3자리)
블록번호(6자리)
로우번호(3자리)
Latch의 획득의 부하
Buffer Block 대기
LRU 알고리즘에 의해 메모리에서 Age Out 되었을 경우 LRU Latch 획득 필요
Buffer Pinning - Logical Read count(메모리에서 블록 읽은 횟수)로 잡히지 않음
다음 Read 시 현재 읽은 동일 Block을 Read 할 경우 Age-Out되지 않도록 Pin을 걸어두고, DB Block Address가 가리키는 메모리 번지수를 PGA에 저장하여 바로 찾아감
Clustering Factor
인덱스 오픈
변수 선언
인덱스를 순차적으로 읽어 이전 RowId블록과 다음 RowID 블록이 상이할때 +1 증가
비용 = blevel + -- 인덱스 수직적 탐색 비용
(리프 블록 수 * 유효 인덱스 선택도) + -- 인덱스 수평적 탐색 비용
(클러스터링 팩터 * 유효 테이블 선택도) -- 테이블 Random 엑세스 비용
blevel : 리프 블록에 도달하기 전까지 읽게 될 루트 및 브랜치 블록 개수
유효 인덱스 선택도 : 전체 인덱스 레코드 중에서 조건절을 만족하는 레코드를 찾기 위해 스캔 할 것으로 예상되는 비율(%)
유효 테이블 선택도 : 전체 레코드 중에서 인덱스 스캔을 완료하고서 최종적으로 테이블을 방문 할 것으로 예상되는 비율(%)
I/O 튜닝의 핵심 원리
Sequential 엑세스의 선택도를 높임
Random 엑세스 발생량을 줄임
6. 테이블 Random 엑세스 최소화 튜닝
인덱스 컬럼 추가
PK 인덱스에 컬럼 추가
컬럼 추가에 따른 클러스터링 팩터 변화
7. IOT와 클러스터
크기가 작고 NL조인으로 반복 룩업하는 테이블
폭이 좁고 긴 테이블
넓은 범위를 주로 검색하는 테이블
데이터 입력과 조회 패턴이 서로 다른 테이블
클러스터 인덱스
키 값이 같은 레코드가 한 블록에 모이도록 저장하는 구조
한 블록에 모두 담을 수 없을 때는 새로운 블록을 할당해 클러스터 체인으로 연결
다중 테이블 인덱스 클러스터
물리적으로 같은 블록에 여러 테이블의 레코드가 조인 상태로 저장 가능
일반적인 경우 하나의 데이터 블록이 여러 테이블에 의해 공유될 수 없음
인덱스와 데이터 건수는 1:N관계임(인덱스는 유니크함)
8. 인덱스 스캔 효율
인덱스 매칭도
비교 연산자 종류와 컬럼 순서에 따른 인덱스 레코드의 군집성(인덱스 매칭도)
조회 조건이 =이 아닌 칼럼은 가급적 인덱스 뒤쪽으로(=, In 조건이 아닌 조건 이후는 무조건 체크 조건)
결합 인덱스 우선순위 결정
항장(자주) 사용되는지
"=" 조건
Cardinality(분포도)
소트연산 대체
인덱스 선행 컬럼이 등치(=) 조건이 아닐 때 발생하는 비효율(인덱스 매칭도)
Sequential 엑세스 효율은 선택도에 의해 결정(얼마나 적은 레코드를 읽는지)
인덱스 컬럼이 조건절에 모두 등치(=) 조건일 때 가장 높음
리프 블록을 스캔하면서 읽은 레코드는 모두 필터링되 지 않고, 테이블 액세스(비효율 0)
인덱스 컬럼중 조건절 생략되거나, = 조건이 아니라도 그것이 뒤쪽 컬럼일 때 비효율이 없음
인덱스 선두 컬럼이 between 조건이면 조건을 만족하지 않는 레코드까지 스캔 후 버리는 비효율 발생
Between 조건을 In-List로 바꾸었을 때 인덱스 스캔 효율
IN-List 개수가 많지 않아야 함
수직적 탐색이 IN-List 횟수 만큼 발생
수평적 스캔의 비효율보다 수직적 탐색에 대한 비효율이 더 클 수 있음
수직적 탐색 : Random Access
수평적 스캔 : Sequential Access, Leaf Block에는 테이블 Block과 달리 많은 레코드 저장
인덱스 높이가 높을 때 비효율 증가
Index Skip Scan을 이용한 비효율 해소
Root블록까지가 아니고 적당한 Branch까지 읽음
수직적 탐색이 많은 In-List보다 Index Skip Scan이 근소하게 유리
범위조건을 남용할 때 발생하는 비효율
특정조건을 Like로 처리하여 데이터 정제
Union All이나 NVL을 활용하여 NULL인 조건을 대처
같은 컬럼에 두 개의 범위검색 조건 사용 시 주의 사항
수직적 탐색 X
체크조건에 사용
읽은곳 바로 뒤를 읽는 패턴
Between과 Like 스캔 범위 비교
between을 사용하는 것이 정확한 방식이나, 개발자들의 편리에 의해 like 사용(성능적으로 손해일 경우 있음)
like가 마지막 조건으로 드라이빙이면 like써도 무방함
선분 이력의 인덱스 스캔 효율
선분이력 : 시작지점과 종료지점을 관리
점이력 : 시작점만 관리
최근 데이터를 주로 읽을 때 : 인덱스(종료일자 + 시작일자)
과거 데이터를 주로 읽을 때 : 인덱스(시작일자 + 종료일자)
인덱스 수정 불가한 상황 : Index_Desc 힌트
중간 지점을 읽을 때 : 어떠한 인데스 든 비효율이 발생하지만 rownum <= 1 조건 활용
Access Predicate와 Filter Predicate
Index Fragmentation
9. 인덱스 종류
클러스터 인덱스
클러스터 인덱스도 일반적인 B-tree 구조
해당 키 값을 저장하는 첫 번째 데이터 블록만 가리킨다는 점은 상이
키 값은 항상 Unique
키 값과 테이블 레코드는 1:M 관계 유지 / 일반 인덱스는 1:1 관계
넚은 범위 검색에 유리(C.F 매우 좋음)
클러스터 인덱스의 키는 아래의 경우는 좋지 않음
새로운 값이 자주 입력 -> 새로운 클러스터 할당
수정이 자주 발생 -> 클러스터의 잦은 이동
B-Tree 인덱스
Unbalanced Index
B-tree의 B는 Balanced이므로 일반적인 상황에서는 Unbalanced 현상이 발생하지 않음
Index Skew 현상
대량의 삭제 후 발생 현상
빈 블록은 free-list로 등록되지만 반환하지 않음
재사용 가능하나, 다시 채워 질 때까지 인덱스 스캔 효율 저하
Index Sparse
대량의 삭제 작업 후 발생
인덱스의 밀도가 낮은 상황
Skew 현상과 같이 완전히 빈 블록은 재 사용되지만, Index Sparse는 Empty Block이 거의 없어 데이터가 채워질 떄까지 인덱스 비효율 발생
총 레코드 건수가 일정한데도 인덱스 공간 사용량 지속 증가는 주로 Index Sparse임
인덱스 재생성
인덱스 분할에 의한 경합이 현저히 높을 때
자주 사용되는 인덱스 스캔 효율을 높이고자 할 때, 특히 NL Join에서 반복 엑세스되는 인덱스 높이 가 증가했을 때
대량의 delete 작업을 수행한 이후 다시 레코드가 입력되기까지 오랜기간이 소요될 때
총 레코드 수가 일정한데도 인덱스가 계속 커질 때
비트맵 인덱스
Distinct Value 개수가 적을 때
적은 용량을 차지하므로 인덱스가 여러 개 필요한 대용량 테이블에 유용
다양한 분석관점을 가지 팩트성 테이블에 주로 사용
테이블 Random 액세스 발생으로 B-tree 인덱스와 동일하여 좋은 성능을 기대하기 어려움
단독으로 쓰임새는 없으나, 여러 비트맵 인덱스를 동시 활용 시 대용량 데이터 검색 성능 향상에 효과
DML 부하 과다(트랜잭션이 발생(Lock))이므로 정형화되지 않은 임의 질의(ad-hoc query) 환경에 사 용(DW)
함수기반 인덱스
NVL을 사용해서 주문 수량이 null인 레코드에 0으로 채워진 인덱스 생성
대소문자를 구분해서 입력 받은 데이터를 대소.소문자 구분 없이 조회 할 때 흔히 사용
데이터 입력, 수정 시 함수를 적용해야 하기에 다소 부하 발생 가능
사용된 함수가 User-Defined 함수일 경우 부하 극심
남용하지 말고 꼭 필요한 경우 활용 필요
리버스 키 인덱스
일련번호, 주문일시 등의 컬럼에 인덱스를 만들 경우
입력되는 값이 순차적 증가로 리프 블록에만 데이터가 적재
Right Growing 현상
Insert가 심할 때 인덱스 블록 경합으로 초당 트랜잭션 처리량 감소
입력된 값을 거꾸로 변환해서 저장 -> 데이터 고르게 분포
equal[=] 검색만 가능하며, 부등호, between, like등의 범위 검색 조건에 사용 불가
클러스터 인덱스 (?)
클러스터형 인덱스 / IOT
10. 인덱스 설계
결합 인덱스 구성을 위한 기본 공식
조건절에 항상 사용되는가?
=조건으로 사용되는가?
카디널리티가 좋은가?
소트 오퍼레이션을 생략 가능한가?
추가적인 고려 사항
쿼리 수행 빈도
업무상 중요도
클러스터링 팩터
데이터량
DML 부하(= 기존 인덱스 개수, 초당 DML 발생략, 자주 갱신되는 컬럼 포함 여부 등)
저장공간
인덱스 관리비용 등
인덱스 최종 정리
테이블 Random Access > 수직적 탐색 > 수평적 스캔
제 2절 조인의 원리와 활용
1. Nested Loop 조인(인덱스 가지고 사용)
Random Access 위주의 조인 방식
조인을 한 레코드씩 순차적으로 진행(작은쪽에서 큰쪽으로 조인)
대용량 처리 시 치명적인 한계점 발생
인덱스 구성 전략이 매우 중요
온라인 트랜잭션 환경에 적합
Look-up 테이블의 조인 컬럼은 주로 인덱스 필요
인덱스 Prefetch
읽을 것으로 예상 되는 Leaf Block을 미리 읽어 Buffer Cache에 적재하는 것
불필요한 Branch Block Read로 Prefetch 미 작동 대비 Logical Read가 미세하게 더 많이 발생
Disk I/O 부하 감소를 한 기능
인덱스 Prefetch와 테이블 Prefetch
db file parallel read 대기 이벤트 발생(Single block I/O는 db file sequential read 대기 이벤트)
테이블 Prefetch
테이블 Lookup Prefetch 또는 데이터 블록 Prefetch 라고도 불림
인덱스 경유 테이블 접근 시 디스크에서 Buffer Cache로 블록 적재 할 때 다른 블록까지 미리 적재
Random Access 성능 향상
버퍼 Pinning
테이블Prefetch
디스크 I/O 대기 횟수 감소
Prefetch 결론
Disk I/O 부하를 감소시키기 위한 기능
Multiblock I/O도 Prefetch 기능 중 하나
한 번에 여러 개 Single Block I/O를 동시에 수행하는 것
미리 적재하고 사용되었다면 효율적이지만, 그렇지 않을 경우 비효율
CKPT 프로세스가 Pretech 된 블록 사용여부 감시
Multiblock I/O
하나의 익스텐트 내의 인접한 블록을 동시에 읽는 것
Pretech
서로 다른 익스텐트에 위치한(인접하지 않는) 블록을 배치 방식으로 미리 적재하는 것 -> 읽어야 하 는 블록들이 서로 다른 디스크에 존재할 경우 성능 향상은 더 증가
Hint : nlj_prefetch [lookup table], no_nlj_prefetch
Batch I/O의 수행 방식
선행 테이블의 인덱스 -> 테이블 랜덤 엑세스
Buffer Cache에서 읽을 경우 조인 진행
Disk IO 필요 시 임시 저장 후 적당량이 쌓이면 한 번에 Disk IO 후 조인 수행
Hint : nlj_batching[lookup table], no_nlj_batching
조인과 무관한 Batch Access : batch_table_access_by_rowId [대상테이블]
Batch I/O의 결과 집합 정렬 순서
기존과 동일 : 선행 테이블의 데이터를 100% Buffer Cache에서 찾는 경우
상이 : 선행 테이블의 데이터를 Buffer Cache에서 찾지 못하고 한 Block이라도 Disk I/O를 하는 경 우
prefetch VS batch I/O
장점 : Disk I/O 회수 감소 가능성 높음
특이사항 : 미세한 Block Read량 증가 VS 선행 Table의 Random Access에서 Disk I/O 발생 시 정렬 순서 상이
2. Sort-merge 조인
소트 단계 : 양쪽 집합을 조인 컬럼 기준으로 정렬
머지 단계 : 정렬된 양쪽 집합을 merge
조인을 위해 실시간으로 인덱스를 생성하는 것과 같은 효과
양쪽 집합을 정렬 수 NL 조인과 같은 방식으로 진행
PGA 영역에서 처리로 빠른 속도(merge쪽)
소트 부하만 감수한다면 버퍼 캐시에서 조인하는 NL 조인보다 유리
조인 컬럼에 인덱스 유무와 상관 없음
조인 하기 전에 양쪽 집합을 정렬
부분적으로, 부분범위 처리가 가능
테이블별 검색 조건에 의해 전체 일량이 좌우된다.
스캔 위주의 방식
효율적인 활용
드라이빙 테이블은 PGA 사용 필요가 없음
Inner 테이블은 PGA에 갖다 놓음
First 테이블에 소트 연산을 대체할 인덱스가 있을 때
조인할 First 집합이 이미 정렬되어 있을 때
조인 조건식이 등치 조건이 아닐 때
3. Hash 조인
특징
두 개의 테이블 중 작은 집합을 읽어 Hash Area에 적재(Build Input)
반대쪽 큰 집합을 읽어 해시 테이블을 탐색(Probe Input)
해시 테이블을 탐색할 때 해시 함수를 사용
등치(=) 조건만 사용 가능
NL 조인 처럼 Random 액세스 부하 없음
소트머지 조인처럼 소트부하도 없음
단 Build Input이 Hash Area 크기 안에 들어가는 것이 성능의 성패를 좌우한다.
NL 조인은 Inner 쪽 테이블 버퍼 캐시 탐색을 위해 래치를 획득하지만, 해시조인은 래치 획득과정이 없는 PGA에서 처리하는 바, 빠른 탐색과정
사용 기준
한쪽 테이블이 Hash Area에 담길 정도로 충분히 작아야 함
Build Input 해시 키 컬럼에 중복 값이 거의 없어야 함
조인 컬럼에 적당한 인덱스가 없어 NL조인이 비효율 적일 때
조인 컬럼에 인덱스가 있더라도 NL 조인 드라이빙 집합에서 Inner 쪽 집합으로의 조인 엑세스 량이 많 아 Random 엑세스 부하가 심할 때
소트머지 조인하기에는 두 테이블이 너무 커 소트 부하가 심할 때
수행빈도가 낮고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 조인 할 때(배치)
수행빈도가 높고, OLTP 환경하의 SQL을 성능향상 시키기 위해 사용해서는 안됨
CPU와 메모리 사용률이 크게 증가하여 전반적으로 자원 사용률을 높임
Hash 조인 실행 계획 - 등호형
Hash 조인 실행 계획 - 계단형
4. 조인 순서의 중요성
Sort Merge 조인의 순서
Disk sort가 필요한 경우 : 큰 테이블 Driving이 유리(Disk I/O 회수 감소 유도)
PGA Sort Area안에 담길 경우 : 적은 테이블 Driving이 유리(Join 회수 감소)
Hash 조인의 순서
Hash Area에 충분히 담길정도로 적은 테이블이 Driving(Build Input) 되어야함
5. Outer 조인
Outer NL 조인
(+)의 반대 쪽이 드라이빙 테이블로 선택
Leading 힌트로도 순서변경 불가능 -> 논리적 모순
Outer Sort Merge 조인
(+)의 반대쪽이 드라이빙 테이블로 선택
Leading 힌트로도 순서 변경 불가능 -> 논리적 모순
Outer Hash 조인
Right Outer Hash 조인
6. 스칼라 서브쿼리를 이용한 조인
Scalar Sub-Query : 함수와 같이 하나의 값을 반환하는 Sub Query
주로 Select 절에 많이 나오지만, 대부분의 칼럼이 위치할 곳에 작성 가능
스칼라 서브쿼리는 수행회수 최소화를 위한 캐싱 기능
스칼라 서브쿼리를 위한 캐시
10g 이후는 _query_execution_cache_max_size 만큼(11g의 경우 65,536개)
입력되는 값(메인쿼리의 결과 값)이 캐시 수보다 적어야 함
코드성과 같이 동일 값이 자주 나타나야 효과적
주로 메인쿼리가 M측이며, 스칼라 서브쿼리는 1측을 조회할 때 효과적
배치보단 OLTP에서 유리, 같은 데이터 반복 엑세스에 유리, DW에서 사용 X
7. 조인을 내포한 DML 튜닝
bypass_ujvc 힌트
Updatetable Join View는 1:M 관계에서 M쪽만 변경 가능
1쪽 집합에 Unique 인덱스가 없다면, DBMS는 어느 테이블이 1쪽 집합인지 알 수 없음
ORA-01779: cannot modify a column which maps to a non key-preserved table
(Update를 위해 참조하는 테이블이 1쪽 집합임을 확인할 수 없을 때 발생하는 에러 메시지)
emp, dept 테이블에서 수정 가능한 Vuew[인라인 뷰 포함]가 되기 위한 조건
dept 테이블에 deptno로 된 PK 설정 필요
emp 테이블은 "키-보존 테이블[Key-Preserved Table]"
dept 테이블은 "비 키-보존 테이블[Non Key-Preserved Table]"
즉 키-보존 테이블만 변경 가능
bypass_ujvc [bypass Updatetable Join View Check] : 수정가능 뷰 체크를 생략
DBMS는 확인 불가능 해도, 사람에게 Check를 넘기는 힌트
반드시 Update르르 위해 참조하는 집합에 중복 레코드가 없을[1쪽 집합]때만 사용 필요
8. 고급조인 테크닉
(1) 누적 매출 구하기
(2) 선분 이력 끊기
(3) 데이터 복제를 통한 소계 구하기
(4) 상호배타적 관계의 조인
(5) 최종 출력
(6) 징검다리 테이블 조인을 이용한 튜닝
(7) 점이력 조회
댓글
댓글 쓰기