0. 옵티마이저란?

옵티마이저는 SQL문을 실행할 때 최적의 실행 계획을 선택하는 역할을 하는 핵심 구성요소
9g 이하에서는 규칙 기반 옵티마이저로 테이블 풀 스캔보다는 인덱스 스캔을 우선으로 적용
10g 이후는 비용 기반 옵티마이저로 통계 정보를 기반으로 최적의 경로 선택
옵티마이저 힌트

풀 테이블 스캔 강제

SELECT /*+ FULL(emp) */ * FROM emp;

특정 인덱스 사용 강제

SELECT /*+ INDEX(emp emp_idx) */ * FROM emp WHERE emp_no = 100;

옵티마이저 구성

SQL 변환 → 접근 경로 결정 → 조인 방식 결정 → 비용 계산 → 실행 계획 선택 → SQL 실행

1. 인덱스 구조

기본적으로 B*Tree 형식을 가지고 있습니다. 따라서 탐색 방법은 루트에서 리프까지 수직적 탐색, 리프에서 원하는 값을 찾는 수평적 탐색으로 나뉩니다.

최말단 리프 블록은 인덱스 키컬럼과 주소정보(row id)를 갖습니다. 또한 주소 정보(row id)순으로 정렬되어 있기에 필요한 값을 찾을 때 일정 범위만 찾는 범위 스캔을 하는 경우도 있습니다.

cf) Left Most Child

브랜치 노드와 각 엔트리는 키 값과 하위 노드를 가리키는 블록 주소를 갖습니다. 하지만 키 값을 가지지 않는 엔트리가 존재합니다. 가장 첫 노드이고, 이를 lmc라고 부릅니다.

1-1 익덱스 구조와 특징

리프 노드상의 인덱스 레코드와 테이블 레코드 간 1:1관계
리프 노드상의 키값과 테이블 레코드 키 값은 일치
브랜치 노드상의 레코드 개수는 하위 레벨 블록 개수와 일치
브랜치 노드상의 키 값은 하위 노드가 갖는 값의 범위를 의미

궁금한 점 : 리프 노드 상의 엔트리 값이 갱신되더라도 브랜치 노드까지 값이 변경되지 않는다. 하지만 키 값은 하위 노드가 갖는 값의 범위를 안다. 어떻게 알 수 있는 것일까?

브랜치 노드는 리프 노드의 “최소 값(첫 번째 엔트리 키)”만 저장한다.
즉, 브랜치 노드 값은 그 하위 리프 노드에서 가장 작은 값을 대표한다.
리프 노드에서 데이터가 변경되더라도, 해당 노드의 최소 값이 유지되면 브랜치 노드는 변하지 않는 것이다.

1-2 인덱스 탐색

수평적 탐색

인덱스 레코드 간 논리적 순서에 따라 좌측 또는 우측으로 스캔하는 것

수직적 탐색

수평적 탐색을 위한 시작 지점을 찾는 과정으로 루트에서 리프로 아래로 스캔

1-3 인덱스 탐색 방법

브랜치 블록 스캔
결합 엔덱스 구조

인덱스 값을 결합 구조로 가지고 탐색하는 것
인덱스 값이 2개가 있다면 2번째 레코드를 기준으로 먼저 탐색한다.

1-4 ROWID 포맷

물리적 위치정보를 포함한다. row id : 데이터 파일 번호 + 블록 번호 + 로우 번호
테이블 자체에 저장되는 것이 아니라 인덱스에 저장된다.(경로를 찾는 것이기 때문에) : pseudo(슈도) 컬럼이다.

cf) pseudo(슈도) 컬럼 : 실제 테이블에 존재하지 않지만 SELECT 문에서 사용할 수 있는 컬럼으로 테이블의 메타데이터나 특정 정보를 조회할 떄 사용한다. 예제로는 ROWNUM, ROWID, LEVEL, SYSDATE가 존재한다.

rowid의 크기 : 오라클 8부터는 10 byte로 증가 시켰습니다. 이로인해 peta-byte 단위의 데이터를 저장할 수 있게 되었습니다. 다만, 파티션되지 않은 일반 테이블에 생성한 인덱스 및 파티션된 테이블에 생성한 로컬 파티션 인덱스는 6byte입니다.

제한 rowid : 데이터 파일 번호(4자리) + 블록 번호(8자리) + 로우 번호(4자리)
확장 rowid : 데이터 오브젝트 번호(6자리) + 데이터 파일 번호(3자리) + 블록 번호(6자리) + 로우 번호(3자리) | 데이터 오브젝트 번호 : 데이터베이스 세그먼트 식별을 위해 사용되는 번호 | 데이터 파일 번호 : 로우가 속한 데이터 파일 번호로, 데이터베이스 내 유일 값 | 블록번호 : 해당 로우가 저장된 데이터 블록 번호, 데이터 파일 내에서 상대적 번호 | 로우번호 : 블록 내에서 각 로우에 붙여진 일련번호

2. 인덱스 원리

인덱스 사용이나 범위 스캔이 되지 않는 경우

조건 절에서 컬럼을 변경하는 경우
부정형을 사용하는 경우
is not null을 사용하는 경우

다만, is null을 사용할 때 해당 컬럼이 not null이면, 범위 스캔이 정상적으로 적용된다.

생각해보기 !) 그렇다면 null 가능일때, 범위 중 일부만 찾는 select문을 만든다면 어떻게 쿼리를 수정해야 할까?

cf) null 값은 컬럼의 가장 뒤에 쌓인다

cf) NL outer 조인의 경우 조인 순서가 고정돼 항상 outer 테이블이 먼저 드라이빙된다.

and y.대상연월(+) = substr(x.파트너지원요청일자,1,6) -1

이때 x쪽 집합이 먼저 읽히고 y쪽 조인 컬럼에 값을 제공하게 된다.

2-1 묵시적 형변환

묵시적 형변환은 융통성있는 변환을 통해 chr, number 와 같은 형을 매번 변환시켜주지 않아도 되는 것이다. 하지만 이런 묵시적 형변환에 따른 에러가 밸생할 수 있다.

decode(a,b,c,d) : a = b 이면 return 가장 큰 c, a != b 이면 return 가장 큰 d

이때, c가 문자열이면 d가 숫자형이더라도 문자형으로 변경해주고, c 가 null이더라도 varchar로 취급하는 특징이 있다. 따라서 c가 null이면 숫자형 d를 문자형으로 바꿨을때 가장 큰 값을 반환하는 에러가 발생한다. 따라서 데이터 변환값에 맞춰서 데이터를 넣어주는 것은 중요하다.

2-2 함수기반 데이터 변환 방식

함수를 만들어 데이터 값을 수정해서 반환하는 형식으로 권장할만한 방법은 아니지만, 임시 처리 가능하다.

3. 다양한 인덱스 스캔 방식

3-1. Index Range Scan

스캔 범위를 특정해서 비용을 줄이는 방법. 인덱스 스캔 범위를 얼마나 줄일지, 테이블 I/O를 얼마나 줄일지가 관건이다.

create index emp_deptno_idx on emp(deptno);
set autotrace traceonly explain
select * from emp where deptno = 20;
# SELECT STATEMENT Optimizer = ALL_ROWS
# 0  TABLE ACCESS (BY INDEX ROWID) OF 'EMP' TABLE
# 1    INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (INDEX)

3-2. Index Full Scan

최적의 인덱스가 없는 경우에 차선으로 선택되는 방식 수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 탐색하는 방식

전체 테이블의 양보다 적은 양을 select할 때, 최적의 인덱스가 없을 때 적용된다.

3-3. Index Unique Scan

수직적 탐색만으로 데이터를 찾는 스캔 방식 Unique 인덱스를 통해 ‘=’ 조건으로 탐색하는 경우 발생

3-4. Index Skip Scan

인덱스 선두 컬럼의 Distinct Value 개수가 적고, 후행 컬럼의 Distinct Value가 많은 경우 유용하다.

버퍼 Pinning 기법을 활용해서 리프 블록을 방문했다가 다시 브랜치 블록을 되돌아와 다음 리프 블록을 찾는 방법이다. 버퍼 Pinning을 사용하면 추가적인 블록 I/O가 발생하지 않는다.

3-5. Index Fast Full Scan

Index Full Scan 방법보다 빠른 방법. 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock Read 방식으로 스캔하기 떄문이다.

물리적으로 디스크에 저장된 순서대로 인덱스 블록으로 읽는다.

cf) 비교

Index Full Scan과 Index Fast Full Scan

Index Full Scan

인덱스 구조를 따라 스캔
결과 집합 순서 보장
Single Block I/O
병렬 스캔 불가(파티션 안된 경우)
인덱스에 포함되지 않은 컬럼 조회시에도 사용 가능

Index Fast Full Scan

세그먼트 전체를 스캔
결과 집합 순서 보장 안됨
Multiblock I/O
병렬 스캔 가능
인덱스에 포함된 컬럼으로 조회할 때만 사용 가능

따라서 Index Fast Full Scan의 특징에 따라 인덱스에 포함된 컬럼으로 먼저 조회하고 검색조건에 맞는 것을 다시 액세스하는 방법도 있다.

3-6. Index Range Scan Descending

Index Range Scan과 기본적으로 동일한 스캔 방식이다. 다만, 인덱스를 뒤에서 검색해서 결과 또한, 내림차순으로 정렬된 결과집합을 얻는다.

Max와 같은 큰 값을 찾거나 할때 사용된다.

3-7. And-Equal, Index Combine, idnex Join

2개 이상의 테이블을 사용할때도 있다.

And-Equal 10g부터는 폐기된 방법 단일 컬럼을 대상으로 Non-Unique 인덱스, 인덱스 컬럼 조건절이 ‘=’이어야 한다.
Index Combine

비트맵 인덱스를 사용하는 방법

B*Tree 인덱스를 스캔하면서 조건을 만족하는 rowid 목록을 얻는다.
rowid목록으로 비트맵 인덱스 구조를 얻는다.
Bit-Wise 오퍼레이션을 실행한다.
Bit-Wise 오퍼레이션 결과가 ‘true’인 비트 값들을 rowid값으로 환산해 최정 방문 테이블 rowid 목록을 작성한다.
테이블을 엑세스한다.

cf) 비트맵 인덱스 : 비트 연산을 활용한 인덱싱 기법 검색시 ‘T’, ‘F’와 같이 정의되면 같은지 확인할 때 시간이 걸린다. 하지만 비트 (0 or 1)로 저장되어 비교하면 검색 속도가 빠르다. 장점 : 저장 공간 절약, 빠른 검색 성능. 복합 조건 검색 최적화이다. 단점 : DML 성능 저하, OLTP(트랜잭션이 많아서)에는 부적합, 높은 카디널리티 데이터에 비효율적

Index Join

조건 : 쿼리에 사용된 컬럼들이 인덱스에 모두 포함된 경우(한 쪽만 포함되도 된다.)

크기가 상대적 작은 인덱스에서 키 값과 rowid를 읽어 PGA 메모리에 해시 맵을 생성한다.
다른 인덱스를 스캔하며 만든 rowid와 같은 값인 레코드 검색
rowid끼리 조인에 성공한 레코드만 결과집합에 포함시킨다.

4. 테이블 Random 액세스 부하

보통, 쿼리에 참조된 컬럼이 인덱스에 모두 포함된 경우가 아니면 ‘테이블 random 액세스’가 발생합니다.

이때 rowid를 가지고 테이블을 찾는 과정이 생각보다 비용이 큽니다. 어떻게 찾는 지 순서대로 한번 알아보겠습니다.

인덱스에서 하나의 rowid를 읽고 DBA를 해시 함수에 적용해 해시 값을 확인한다.
각 해시 체인은 래치에 보호되므로 해시 값이 가리키는 해시 체인에 대한 래치를 얻으려고 시도한다.
다른 프로세스가 래치를 잡고 있으면 확인을 (2000번 정도) 한다.
그래도 실패하면 CPU를 OS에 반환하고 잠시 대기 상태로 빠진다.
정해진 시간 이후 다시 래치 상태를 확인한다.
래치가 해제되면 원하는 해시 체인으로 진입한다.
데이터 블록을 찾으면 바로 래치를 해제하고 읽지만 못한다면, 또는 다른 프로세스가 Lock을 쥔 상태라면 대기한다.(buffer busy waits)
블록 읽기를 마치고 나면 버펀 Lock을 해제해야하므로 해시 체인 래치를 얻어야 한다.
만약 데이터를 얻지 못했다면 디스크로부터 블록을 올려야 하므로 Free 버퍼를 할당받아야한다. 이를 위해 LRU 리스트를 스캔한다. 이때 cache buffer Iru chain 래치를 얻어야한다.
LRU 리스트를 정해진 임계치만큼 스캔해도 찾지 못하면 Dirty 버퍼를 디스크에 기록해 Free 버퍼를 확보하는 신호를 만든다. (free buffer waits)
Free 버퍼를 할당 후 I/O 서브 시스템에 I/O 요청을 하고 다시 대기상태에 빠진다.(db file sequential read)
마지막으로 읽은 블록을 LRU 리스트 상에서 위치를 옮겨야 하므로 cache buffers Iru chain 래치를 얻어야 한다.

이처럼 rowid를 통한 데이터 검색도 복잡한 처리과정을 통해 처리됩니다.

4-1. 인덱스 클러스터링 팩터

특정 컬럼을 기준으로 같은 값을 가진 데이터가 모여있는 정보를 의미하는 용어 CF가 좋으면 빠르게 찾을 수 있고, CF가 좋지 않으면 찾는 속도가 오래걸린다.

exec dbms_stats.gather_table_stats(user, 'T'); -- 통계정보 수집

select i.index_name, t.blcoks talbe_blocks, i.num_rows, i.clustering_factor
from user_tables t, user_indexes i
where t.talbe_name = 'T'
and i.table_name = t.talbe_name;

이렇게 정의할 때, clustering_factor 수치가 Table_blocks와 가까울수록 CF가 좋은 상태이고, num_rows(레코드 개수)에 가까울수록 CF가 나쁜 상태이다.

4-2. 인덱스 손익분기점

이처럼 rowid를 통한 테이블 액세스는 고비용이므로, index range scan에 의한 테이블 액세스가 table full scan보다 느려지는 지점을 손익분기점이라고 부른다. Full Table Scan은 Sequential 액세스 방식, rowid는 Random 액세스 Full Table Scan은 Multiblock Read 방식, rowid는 Single Block Read 방식이기 떄문이다.

4-3. 손익분기점을 극복하기 위한 기능

IOT로서 테이블을 인덱스 구조로 생성하는 것
클러스터 테이블
파티셔닝

[Oracle]