01 인덱스 구조
(1) 범위 스캔
인덱스: 대용량 테이블에서 필요한 데이터만 빠르고 효율적으로 액세스할 목적으로 사용하는 오브젝트
범위스캔 (Range Scan): 인덱스는 키 컬럼 순으로 정렬돼 있기 때문에 특정 위치에서 스캔을 시작해 검색 조건에 일치 하지 않는 값을 만나는 순간 멈출 수 있다.
(2) 인덱스 기본 구조
Root 를 포함한 Branch 블록에 저장된 엔트리에는 하위 노드 블록을 찾아가기 위한 DBA (Data Block Address) 정보를 갖고,
최말단 Leaf 블록에는 인덱스 키 컬럼과 함께 해당 테이블 레코드를 찾아가기 위한 ROWID (주소정보) 를 갖는다.
* 리프 노드상의 인덱스 레코드와 테이블 레코드 간에는 1:1 관계
* 리프 노드상의 키 값과 테이블 레코드 키 값은 서로 일치
* 브랜치 노드상의 레코드 개수는 하위 레벨 블록개수와 일치
* 브랜치 노드상의 키 값은 하위 노드가 갖는 값의 범위를 의미
(3) 인덱스 탐색
수평적 탐색: 범위 스캔
수직적 탐색: 수평적 탐색을 위한 시작 지점을 찾는 과정
브랜치 블록 스캔: 브랜치 블록을 따라 수직적 탐색을 진행할 때는 찾고자 하는 값보다 키 값이 작은 엔트리를 따라 내려간다.
결합 인덱스 구조와 탐색: 두번째 리프블록, 두번째 레코드부터 스캔이 시작된다.
(4) ROWID 포맷
테이블 레코드의 물리적위치정보를 포함하며, 인덱스에 저장된다.
* 제한 ROWID 포맷
데이터파일 번호 (4자리), 블록 번호 (8자리), 로우 번호 (4자리)
* 확장 ROWID 포맷
데이터 오브젝트 번호 (6자리), 데이터파일 번호 (3자리), 블록 번호 (6자리), 로우 번호 (3자리)
02 인덱스 기본 원리
(1) 인덱스 사용이 불가능하거나 범위 스캔이 불가능한 경우
인덱스 컬럼을 조건절에서 가공 ex) where substr(업체명, 1, 2) = '대한'
부정형 비교 ex) where 직업 <> '학생'
is not null 조건 ex) where 부서코드 is not null
is null ex) where 연락처 is null
not null 제약이 있는 컬럼은 옵티마이저가 이미 알고 있기 때문에 인덱스 스캔을 통해 공집합을 리턴하여 사용 가능
(2) 인덱스 컬럼의 가공
where substr(업체명,1,2) = '대한' → where 업체명 like '대한%'
where 월급여 * 12 = 36000000 → where 월급여 = 3600000/12
where to_char(일시, 'yyyymmdd') = :dt → where 일시 >= to_date(:dt, 'yyyymmdd')
and 일시 < to_date(:dt, 'yyyymmdd')+1
where 연령 || 직업 = '30공무원' → where 직업 = '공무원'
where 회원번호 || 지점번호 =: str → where 회원번호 = substr(:str,1,2)
and 지점번호 = substr(:str,3,4)
튜닝사례1
where 지수구분코드 || 지수업종코드 in ('1001', '2003') → where (지수구분코드, 지수업종코드) in (('1','001'), ('2', '003'))
튜닝사례2
where decode(정정대상접수번호, lpad(' ',14), 금감원접수번호, 정정대상접수번호) =: 접수번호 →
where 정정대상접수번호 in (:접수번호, lpad(' ',14))
and 금감원접수번호 = decode(정정대상접수번호, lpad(' ',14), :접수번호, 금감원접수번호)
(3) 묵시적 형변환
NL Outer 조인은 조인순서가 고정돼 항상 Outer 테이블이 먼저 드라이빙된다.
varchar2 컬럼에 숫자값을 더하거나 빼는 연산을 가하면 내부적으로 숫자형으로 형변환이 일어난다. (묵시적형변환)
숫자형과 문자형이 비교될 때는 숫자형이 우선시된다.
where y.대상연월(+) = substr(x.파트너지원요청일자,1,6)-1 →
where y.대상연월(+) = to_char(add_months(to_data(x.파트너지원요청일자, 'yyyymmdd'),-1),'yyyymm')
쿼리 옵티마이저에 의해 일어나는 내부적인 형변환 원리를 이해하고
조인 컬럼의 데이터 타입을 일일이 따져보아야 한다.
* 묵시적 형변환 사용시 주의사항
만약 문자형 컬럼에 숫자로 변환할 수 없는 문자열이 들어있으면 쿼리 수행도중 에러 발생
decode(a,b,c,d) 에서 데이터 타입은 c에 의해 결정된다. c가 문자형, d가 숫자형이면 d가 문자형으로 변환, c 가 null 이면 varchar2로 취급
※ 쿼리 성능 뿐만 아니라 올바른 결과 집합을 얻기 위해서라도 명시적으로 변환함수를 사용하는 게 바람직하다.
* 함수기반 인덱스(FBI) 활용
권장할 만한 해법은 못됨 (급한 불 끄기 용도)
1 - filter(TO_NUMBER("V_DEPTNO")=20) → 인덱스 컬럼의 묵시적 형변환 발생
SQL> create index emp_x01 on emp (to_number(v_deptno)) ; → 함수기반 인덱스 생성
03 다양한 인덱스 스캔 방식
(1) Index Range Scan
인덱스 루트 블록에서 리프 블록까지 수직적으로 탐색한 후에 리프 블록을 필요한 범위만 스캔하는 방식
실행계획 상에서 Index Range Scan 이 나타난다고 해서 항상 빠른 속도를 보장하는 것은 아니다.
※ 인덱스를 스캔하는 범위를 얼마만큼 줄일 수 있느냐, 테이블로 액세스 하는 횟수를 얼마만큼 줄일 수 있느냐가 관건
인덱스 설계와 SQL 튜닝의 핵심 원리 중 하나
(2) Index Full Scan
수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식으로,
대개는 데이터 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택
* Index Full Scan 의 효용성
테이블 전체를 스캔하기보다 인덱스 스캔 단계에서 대부분 레코드를 필터링하고,
일부에 대해서만 테이블 액세스가 발생하도록 할 수 있다면 전체적인 I/O 효율 측면에서 이 방식이 유리
* 인덱스를 이용한 소트 연산 대체
옵티마이저는 소트 연산을 생략함으로써 전체 집합 중 처음 일부만을 빠르게 리턴할 목적으로 Index Full Scan 방식을 선택
SQL> select /*+ first_rows * / * from emp
2 where sal > 1000
3 order by ename ;
(3) Index Unique Scan
수직적 탐색만으로 데이터를 찾는 스캔방식으로서, Unique 인덱스를 통해 '=' 조건으로 탐색하는 경우에 작동
(4) Index Skip Scan (CBO 일 때 사용, 정렬이 안되어 있을 수 있다.)
조건절에 빠진 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용
루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건에 부합하는 레코드를 포함할 "가능성이 있는" 리프 블록만 골라서 액세스하는 방식
SQL> select /*+ index_ss(사원 사원_idx)*/ * from 사원 where 연봉 between 2000 and 4000 ;
* 버퍼 Pinning 을 이용한 Skip 원리
브랜치 블록 버퍼를 Pinning 한 채로 리프 블록을 방문했다가 다시 브랜치 블록으로 되돌아와 다음 방문할 리프 블록을 찾는 과정을 반복하는 것
* Index Skip Scan 이 작동하기 위한 조건
Distinct Value 개수가 적은 선두 컬럼이 조건절에서 누락됐고 후행 컬럼의 Distinct Value 개수가 많을 때 효과적
인덱스 맨 선두 컬럼이 누락됐을 때만 작동하는 것은 아니다.
업종별거래_PK: 업종유형코드 + 업종코드 + 기준일자
- 중간 컬럼이 누락된 경우에도 사용
where 업종유형코드 = '01'
and 기준일자 between '20080101' and '20090101'
- 두 개의 선두 컬럼이 모두 누락된 경우에도 사용
where 기준일자 between '20080101' and 20090101'
- 선두 컬럼이 부등호, between, like 같은 범위 검색 조건일 때도 Index Skip Scan 이 사용
* In-List Iterator 와의 비교
and 성별 in ('남', '여')
이렇게 쿼리 작성자가 직접 성별에 대한 조건식을 추가해주면 Index Skip Scan에 의존하지 않고도 빠르게 결과집합을 얻을 수 있다.
단, 이처럼 In-List 를 명시하려면 성별 값의 종류가 더 이상 늘지 않음이 보장 되어야 하고,
이 튜닝 기법이 효과를 발휘하려면 In-List 로 제공하는 값의 종류가 적어야 한다.
(5) Index Fast Full Scan
인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 Multiblock Read 방식으로 스캔하기 때문에 Index Full Scan 방식보다 빠르다.
물리적으로 디스크에 저장된 순서대로 읽어들인다. Index Full Scan 은 인덱스의 논리적 구조를 따라 읽는다.
* Index Fast Full Scan 의 특징
디스크로부터 대량의 인덱스 블록을 읽어야 하는 상황에서 큰 효과를 발휘
대신 인덱스 리프 노드가 갖는 연결 리스트 구조를 이용하지 않기 때문에 얻어진 결과집합이 인덱스 키 순서대로 정렬되지 않는다.
Index Full Scan Index Fast Full Scan
1. 인덱스 구조를 따라 스캔 1. 세그먼트 전체를 스캔
2. 결과집합 순서 보장 2. 결과집합 순서 보장 안됨
3. Single Block I/O 3. Multiblock I/O
4. 병렬스캔 불가 4. 병렬스캔 가능
(파티션 되어 있지 않다면)
5. 인덱스에 포함되지 않은 컬럼 5. 인덱스에 포함된 컬럼으로만
조회시에도 사용가능 조회시 사용 가능
* Index Fast Full Scan 을 활용한 튜닝 사례
select * from 공급업체
where 업체명 like '%네트웍스%'
↓
select /*+ ordered use_nl(b) no_merge(b) rowid(b) */ b.*
from ( select /*+ index_ffs(공급업체 공급업체X01) */ rowid rid
from 공급업체
where instr(업체명, '네트웍스') > 0) a, 공급업체 b
where b.rowid = a.rid
업체명 컬럼에 생성한 공급업체_x01 인덱스를 Fast Full Scan 해서 검색조건에 해당하는 공급업체만을 빠르게 찾아내도록 하였다.
그러고 나서 인덱스로부터 얻은 rowid 를 이용해 테이블을 다시 액세스하는 방식
그리고 like 연산보다 빠른 instr 함수 사용
일반적으로 인덱스 컬럼을 가공해선 안되지만 like 중간 값 검색이면 어차피 Index Range Scan 은 불가능하므로
instr 함수를 사용해 좌변 컬럼을 가공하더라도 나쁠 것 없다.
(6) Index Range Scan Descending
인덱스를 뒤에서부터 압쪽으로 스캔하기 때문에 내림차순으로 정렬된 결과집합을 얻는다는 점만 다르다.
select /*+ index_desc(emp emp_x02) */ sal
(7) And-Equal, Index Combine, Index Join
두 개 이상 인덱스를 함께 사용하는 방법
* And-Equal
And-Equal 은 10g 부터 아예 폐기
select /*+ and_equal(e emp_deptno_idx emp_job_idx) * / *
단일 컬럼의 Non-Unique 인덱스여야 함과 동시에 인덱스 컬럼에 대한 조건절이 '=' 이어야 함
* Index Combine
select /*+ index_combine(e emp_deptno_idx emp_job_idx) * / *
데이터 분포도가 좋지 않은 두 개 이상의 인덱스를 결합해 테이블 Random 액세스 량을 줄이는 데 목적이 있다.
조건절이 '=' 이어야 할 필요가 없고, Non-Unique 인덱스도 필요없다.
비트맵 인덱스를 이용하므로 조건절이 OR 로 결합된 경우에도 유용하다.
* Index Join
한 테이블에 속한 여러 인덱스를 이요해 테이블 액세스 없이 결과집합을 만들 때 사용하는 인덱스 스캔 방식
04 테이블 Random 액세스 부하
인덱스를 효과적으로 활용했을 때 쿼리 성능이 얼마나 빨라지는지
대량의 데이터를 인덱스를 통해 액세스할 때 쿼리 성능이 얼마나 느려지는지
(1) 인덱스 ROWID 에 의한 테이블 액세스
* 메인 메모리 DB 와의 비교
메인메모리 DB: 말 그대로 데이터를 모두 메모리에 로드해 놓고 메모리를 통해서만 I/O 를 수행하는 DB
메인메모리 DB에서 인스턴스를 기동하면 디스크에 저장된 데이터를 버퍼 캐시로 로딩하고 이어서 인덱스를 실시간으로 만든다.
이 때 인덱스는 오라클처럼 디스크 상의 주소정보를 담는 게 아니라 메모리상의 주소정보, 즉 포인터를 담는다.
* rowid 는 우편주소에 해당
rowid 는 우편주소
포인터는 전화번호
* 인덱스 rowid 에 의한 테이블 액세스 구조
인덱스 rowid 는 테이블 레코드와 물리적으로 연결돼 있지 않기 때문에 인덱스를 통한 테이블 액세스는 생각보다 고비용 구조다.
설령 모든 데이터가 메모리에 캐싱돼 있더라도 테이블 레코드를 찾기 위해 매번 DBA 를 해싱하고 래치 획득 과정을 반복해야 하기 때문이며,
동시 액세스가 심할 때는 래치와 버퍼 Lock 에 대한 경합까지 발생한다.
(2) 인덱스 클러스터링 팩터
* 군집성 계수 (= 데이터가 모여 있는 정도)
클러스터링 팩트는 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도
* 클러스터링 팩터 조회
clustering_factor 수치가 테이블 블록에 가까울수록 데이터가 잘 정렬되어 있음을 의미하고, 레코드 개수에 가까울 수록 흩어져 있음을 의미한다.
통계정보 수집 후 user_indexes 의 clustering_factor 확인, table_blocks, num_rows
* 클러스터링 팩터와 물리적 I/O
인덱스 CF 가 좋다 고 하면 인덱스 정렬 순서와 테이블 정렬 순서가 서로 비슷하다는 것을 말한다.
* 클러스터링 팩터와 논리적 I/O
인덱스 CF 는 단적으로 말해, 인덱스를 경유해 테이블 전체 로우를 액세스할 때 읽을 것으로 예상되는 논리적인 블록 개수를 의미한다.
* 버퍼 Pinning 에 의한 논리적 I/O 감소 원리
연속된 인덱스 레코드가 같은 블록을 가리킨다면, 래치 획득 과정을 생략하고 버퍼를 Pin 한 상태에서 읽기 때문에 논리적인 블록읽기 횟수가 증가하지 않는다.
(3) 인덱스 손익분기점
Index Range Scan 에 의한 테이블 액세스가 Table Full Scan 보다 느려지는 지점을 흔히 '손익 분기점' 이라고 부른다.
핵심적인 요인
- 인덱스 rowid 에 의한 테이블 액세스는 Random 액세스인 반면, Full Table Scan 은 Sequential 액세스 방식으로 이루어진다.
- 디스크 I/O 시, 인덱스 rowid 에 의한 테이블 액세스는 Single Block Read 방식을 사용하는 반면, Full Table Scan 은 Multiblock Read 방식을 사용한다.
* 손익분기점을 극복하기 위한 기능들
1. IOT (Index-Organized Table) 로서, 테이블을 인덱스 구조로 생성하는 것
2. 클러스터 테이블 (Clustered Table)
3. 파티셔닝
05 테이블 Random 액세스 최소화 튜닝
(1) 인덱스 컬럼 추가
pk 이외에 detpno + job 으로 구성된 emp_x01 인덱스에
sal 컬럼을 추가 함으로 큰 효과를 거둠
(2) PK 인덱스에 컬럼 추가
PK 제약을 위해 Non-Unique 인덱스를 사용하도록 하는 방법
alter table dept drop primary key ;
create index dept_x01 on dept(deptno, loc) ;
alter table dept add constraint dept_pk primary key (deptno) using index dept_x01 ;
(3) 컬럼 추가에 따른 클러스터링 팩터 변화
변별력이 좋지 않은 컬럼 뒤에 변별력이 좋은 다른 컬럼을 추가할 때는 클러스터링 팩터 변화에 주의를 기울여야 한다.
(4) 인덱스만 읽고 처리
테이블 Random 액세스가 아무리 많더라도 필터 조건에 의해 버려지는 레코드가 거의 없다면
테이블 액세스가 발생하지 않도록 모든 필요한 컬럼을 인덱스에 포함 시키는 방법을 고려해 볼 수 있다.
MS-SQL 용어 로 Covered 인덱스라 한다.
Covered 쿼리: 인덱스만 읽고 처리하는 쿼리
(5) 버퍼 Pinning 효과 활용
오라클은 한번 입력된 테이블 레코드는 절대 rowid 가 바뀌지 않는다.
Random 액세스 비효율은 한 건을 읽기 블록을 통쨰로 읽기 때문에 발생하는 것인데,
위와 같은 쿼리에 버퍼 Pinning 효과까지 나타난다면 한 번 액세스로 블록 안에 있는 모든 레코드를 다 읽어들이는 셈이 된다.
(6) 수동으로 클러스터링 팩터 높이기
인위적으로 CF를 높일 목적으로 테이블을 Reorg 할 때는 가장 자주 사용되는 인덱스를 기준으로 삼아야 하며,
혹시 다른 인덱스를 사용하는 중요한 쿼리 성능에 나쁜 영향을 주지 않는지 반드시 체크해봐야 한다.
* 차세대 시스템 구축시 주의사항
과거 시스템으로부터 데이터를 이관하는 과정에서 CF가 오히려 나빠지지 않았는지 확인해야 한다.
06 IOT, 클러스터 테이블 활용
(1) IOT (Index-Organized Table) 란?
Random 액세스가 발생하지 않도록 테이블을 아예 인덱스 구조로 생성하는 방법
인덱스 리프 블록이 곧 데이터 블록
crerate table index_org_t (a number primary key, b varchar(10) ) organization index ;
일반적으로 사용하는 테이블은 힙 구조 테이블이고 다음과 같이 명시할 수 있음 organization heap ;
SQL 서버나 Sybase 에서 말하는 클러스터 형 인덱스 와 비슷한 개념이다.
다만 오라클 IOT 는 PK 컬럼 순으로만 정렬할 수 있다.
* IOT 의 장점과 단점
장점: 인위적으로 클러스터링 팩터를 좋게 만든다. 같은 값을 가진 레코드들이 100% 정렬된 상태로 모여 있기 때문에
Random 액세스가 아닌 Sequential 방식으로 데이터를 액세스할 수 있고, 이 때문에 넓은 범위를 액세스 할 때 유리하다.
단점: 데이터 입력시 성능이 느리다고 하는데 실제 그정도로 늦지는 않다
(2) IOT, 언제 사용할 것인가?
- 크기가 작고 NL 조인으로 반복 Lookup 하는 테이블
- 폭이 좁고 긴 테이블
- 넓은 범위를 주로 검색하는 테이블
- 데이터 입력과 조회 패턴이 서로 다른 테이블
(3) Partitioned IOT
(4) Overflow 영역
PK 이외 컬럼이 많은 테이블일수록 IOT 로 구성하기에 부적합하다.
(5) Secondary 인덱스
IOT 는 Secondary 인덱스 추가 가능성이 크지 않을 때만 선택하는 것이 바람직하다.
* MS-SQL 서버의 비클러스터형 인덱스 진화과정
*오라클 Logical Rowid
logical rowid = pk + physical guess
* PCT_DIRECT_ACCESS
이 값이 100% 미만이면 오라클은 PK를 이용해 바로 IOT를 탐색한다. 오히려 휘발성 IOT에서 이 값이 100%를 가리킬 때가 더 문제일 수 있다.
* 비 휘발성 IOT에 대한 Secondary 인덱스 튜닝 방안
비 휘발성(읽기 전용이거나 맨 우측 블록에만 값이 입력되어 IOT 레코드 위치가 거의 변하지 않는) 테이블이라면 Direct 액세스 성공률이 높을 것이다.
따라서 PCT_DIRECT_ACCESS 값이 100을 가리키도록 유지하는 것이 효과적인 튜닝 방안이다.
* 휘발성 IOT에 대한 Secondary 인덱스 튜닝 방안
1. 주기적으로 physical guess를 정확한 값으로 갱신해 주는 것으로서, 주로 secondary 인덱스 크기가 작을 때 쓸 수 있는 방법이다
2. 아예 physical guess 가 사용되지 못하도록 pct_direct_access 값을 100 미만으로 떨어뜨리는 것으로서,
인덱스 크기가 커서 주기적으로 physical guess를 갱신해 줄 수 없을 때 쓸 수 있는 방법이다.
인덱스 분할이 어느정도 발생한 상태에서 통계정보를 수집해주면 된다.
* Right_Growing IOT에서 pct_direct_access가 100미만으로 떨어지는 이유
인덱스를 탐색할 때 항상 시작점으로 사용되기 때문이다.
* IOT_REDUNDANT_PKEY_ELIM
(6) 인덱스 클러스터 테이블
* 인덱스 클러스터는 넓은 범위를 검색할 때 유리
* 클러스터 테이블과 관련한 성능 이슈
- Direct Path Loading을 수행할 수 없다.
- 파티셔닝 기능을 함께 적용할 수 없다.
- 다중 테이블 클러스터를 Full Scan 할 때는 다른 테이블 데이터까지 스캔하기 때문에 불리하다.
* SIZE 옵션
한 블록에 여러 클러스터 키가 같이 담기더라도 하나당 가질 수 있는 최소 공간을 미리 예약하는 기능
(7) 해시 클러스터 테이블
해시 함수에서 반환된 값이 같은 데이터를 물리적으로 함께 저장하는 구조다.
- 단일 테이블 해시 클러스터
- 다중 테이블 해시 클러스터
(8) IOT와 클러스터 테이블을 동시에 적용한 튜닝 사례
07 인덱스 스캔 효율
Sequential 액세스는 레코드간 논리적 또는 물리적인 순서를 따라 차례대로 읽어나가는 방식을 말하고,
Random 액세스는 레코드간 논리적, 물리적인 순서를 따르지 않고 한 건을 읽기 위해 한 블록식 접근 (=touch) 하는 방식
* I/O 튜닝의 핵심 원리
1. Sequential 액세스의 선택도를 높인다.
2. Random 액세스 발생량을 줄인다.
(1) 비교 연산자 종류와 컬럼 순서에 따른 인덱스 레코드의 군집성
선두 컬럼: 인덱스 구성상 맨 앞쪽에 있는 컬럼을 지칭할 때 사용하고
선행 컬럼: 상대적으로 앞쪽에 놓인 컬럼을 칭할 때 사용, 선두 컬럼은 당연히 선행 컬럼에 포함
(2) 인덱스 선행 컬럼이 등치 조건이 아닐 때 발생하는 비효율
인덱스 Sequential 액세스에 따른 선택도는 인덱스 컬럼이 조건절에 모두 등치 조건으로 사용될 때 가장 높다.
인덱스 선행컬럼이 모두 '=' 조건일 때 필요한 범위만 스캔하고 멈출 수 있는 것은,
조건을 만족하는 레코드가 모두 한 데 모여있기 때문이다.
(3) Between 조건을 In-List 로 바꾸었을 때 인덱스 스캔 효율
* between 조건을 IN-List 조건으로 바꿀 때 주의사항
In-List 개수가 많을 때는, between 조건 때문에 리프 블록을 추가로 스캔하는 비효율보다
In-List 조건 때문에 브랜치 블록을 반복 탐색하는 비효율이 더 클 수 도 있고, 인덱스 높이가 높을 때 특히 그렇다.
* 결합 인덱스 순서가 중요하다
1. =
2. in
3. like 'A%'
4. between
5. <>
6. like '%A'
where a in (1,2)
and b > 500
and c like 'A%'
and d = '홍길동'
and e between 'a' and 'z'
and f like '%z'
on emp (d, a, c, e, b, f) 순서로 만들어야 한다.
(4) Index Skip Scan 을 이용한 비효율 해소
(5) 범위검색 조건을 남용할 때 발생하는 비효율
(6) 같은 컬럼에 두 개의 범위검색 조건 사용 시 주의사항
* OR-Expansion 을 이용하는 방법과 주의사항
use_cancat 힌트를 사용하면 union all을 사용할 때보다 sql코딩량을 줄일 수 있다.
or 조건에 대한 expansion(union all) 이 일어나면 뒤족 조건절이 먼저 실행된다는 특징을 이용한 것이다.
10g cpu 비용 모델에서는 계산된 카디널리티가 낮은 쪽을 먼저 실행한다.
따라서 10g부터는 뒤쪽부터 처리되도록 하려면 orderd_predicates 힌트를 명시해야 한다.
* rowid 를 concatenation 하면 결과에 오류 발생
* 인덱스를 스캔하면서 rowid 를 필터링할 때 발생하는 비효율
(7) Between 과 Like 스캔 범위 비교
* 범위검색 조건의 스캔 시작점 결정 원리
검색을 위해 입력한 값과 테이블의 실제 데이터 상황에 따라 둘 간의 인덱스 스캔량이 다를 수 있다.
like 가 더 넓은 범위를 스캔하므로 가극적 between 을 사용하여야 한다. 더 가급적으로 In을..
(8) 선분이력의 인덱스 스캔 효율
* 선분이력이란?
이력의 시작시점만을 관리하는 것을 '점이력'
시작시점과 종료시점을 함께 관리하는 것을 '선분이력'
쿼리가 간단하면 성능상 유리할 때가 많다.
대신, 이력이 추가될 때마다 기존 최종 이력의 종료일자도 같이 변경해주어야 하는 불편함이 있다.
또 한가지 단점은, 개체 무결성을 사용자가 직접 관리해주어야 한다.
* 선분이력 기본 조회 패턴
* 선분이력 스캔 효율을 높이는 방법 요약
선분이력처럼 between 검색 조건이 사용될 때는 어느 시점을 주로 조회하느냐에 따라 인덱스 구성 전략을 달리 가져가야 한다.
최근 데이터를 주로 조회한다면 [종료일+시작일] 순으로 구성하는 것이 효과적이며,
오래된 과거 데이터를 주로 조회한다면 [시작일 + 종료일] 순으로 구성하는 것이 효과적이다.
(9) Access Predicate 와 Filter Predicate
수직적 탐색 과정에서 모든 인덱스 컬럼을 비교조건으로 사용한다.
(10) Index Fragmentation
오라클은 인덱스 불균형(unbalanced) 상태에 놓일 수 없다.
루트로부터 모든 리프 블록
* Index Skew
인덱스 엔트리가 왼쪽 또는 오른쪽으로 치우치는 현상
* Index Sparse
인덱스 블록 전반에 걸쳐 밀도가 떨어지는 현상
* Index Rebuild
아래와 같이 예상 효과가 확실할 때만 rebuild 를 고려
- 인덱스 분할에 의한 경합이 현저히 높을 때
- 자주 사용되는 인덱스 스캔 효율을 높이고자 할때, 특히 NL 조인에서 반복 액세스되는 인덱스 높이가 증가했을 때
- 대량의 delete 작업을 수행한 이후 다시 레코드가 입력되기까지 오랜 기간이 소요될 때
- 총 레코드 수가 일정한대도 인덱스가 계속 커질 때
PCTFREE
테이블에서의 pctfree 는 블록에 더 이상 insert 가 발생하지 못하도록 freelist 로부터 제외되는 시점을 지정하는 것이다.
그렇게 남겨진 빈 공간은 나중에 update 를 위해 사용된다.
인덱스에서의 pctfree 는 인덱스가 생성되는 시점에 공간을 꽉 채워두면 나중에 인덱스 분할이 빈번하게 발생하므로 이를 방지하려고 필요하다.
나중에 insert 를 위해 사용된다.
PCTUSED
freelist 에서 제거된 테이블 블록에 빈 공간이 일정 수준 이상 확보됐을 때만 다시 freelist 로 등록되도록 하기 위해서 pctused 가 필요하다.
인덱스에는 pctused 가 없다. 인덱스에서 빈공간은 항상 재사용 가능하기 때문이다.
08 인덱스 설계
(1) 가장 중요한 두 가지 선택 기준
- 조건절에 항상 사용되거나, 자주 등장하는 컬럼
- '=' 조건으로 자주 조회되는 컬럼들을 앞쪽에 둔다.
(2) 인덱스 설계는 공식이 아닌 전략과 선택의 문제
모델링에 모범답안은 있어도 정답은 없다고 흔히 얘기하듯이, 인덱스 설계에도 정답은 없다.
개별 쿼리 기준으로는 어떤 인덱스가 더 좋은지 명확히 구분할 수 있지만
시스템 전체적인 관점에서의 효율은 또 다른 기준,
즉 쿼리 수행 빈도, 업무상 중요도, DML 부하, 저장 공간, 관리 비용 같은 상황적 요소까지 고려해 평가되어야 하기 때문이다.
* 효율적 비교 분석
* 스캔 효율성 이외의 판단 기준
인덱스를 설계할 때 시스템 전체적인 관점에서의 추가적인 요소
- 쿼리 수행 빈도
- 업무상 중요도
- 클러스터링 팩터
- 데이터량
- DML 부하 (= 기존 인덱스 개수, 초당 DML 발생량, 자주 갱신되는 컬럼 포함 여부 등)
- 저장 공간
- 인덱스 관리 비용 등
* 인덱스 설계는 공식이 아닌 전략과 선택의 문제
인덱스 설계가 어려운 이유는, 개별 쿼리 성능뿐만 아니라 그 개수를 최소화함으로써 DML 부하를 줄이는 것까지 목표로 삼아야 하기 때문이다.
따라서 시스템 전체적인 시각에서 대안 전략들을 수립하고, 그 중 최적을 선택할 수 있는 능력이 무엇보다 중요하다.
* 인덱스 전략 수립을 위한 훈련
액세스 효율을 높이는 것도 중요하지만 인덱스 개수를 최소화하는 것도 중요한 목표여야 한다.
(3) 결합 인덱스 컬럼 순서 결정 시, 선택도 이슈
인덱스 생성 여부를 결정할 때는 선택도가 충분히 낮은지가 중요한 판단기준임이 틀림없다.
결합인덱스는 개별 컬럼의 선택도가 고려사항은 될 수 있지만 어느 쪽이 유리한지는 상황에 따라 다르다.
* 선택도가 액세스 효율에 영향을 주지 않는 경우
'=' 조건으로 항상 사용되는 컬럼들을 앞쪽에 위치시켰다면, 그 중 선택도가 낮은 것을 앞쪽에 두려는 노력은 의미 없는 것이거나 오히려 손해일 수 있다.
* 선택도가 '높은 컬럼' 을 앞쪽에 두는 것을 유리한 경우
선택도가 높은 컬럼을 앞쪽에 두어야 인덱스 압축률이 더 좋아진다.
* 상황에 따라 유, 불리가 바뀌는 경우
* 선택도가 '낮은 컬럼' 을 앞쪽에 두는 것이 유리한 경우
범위검색 조건을 사이에 둔 컬럼끼리는 선택도가 낮은 컬럼을 앞쪽에 두는 것이 유리하다.
* 선택도가 낮은 컬럼을 '선택' 하는 것이 유리한 경우
결합 인덱스 컬럼 간 순서를 정할 때는, 개별 컬럼의 선택도보다는 조건절에서 어떤 형태로 자주 사용되는지,
사용빈도는 어느쪽이 높은지, 데이터를 빠르게 검색하는 데에 어느 쪽 효용성이 높은지 등이 더 중요한 판단기준이다.
(4) 소트 오퍼레이션을 생략하기 위한 컬럼 추가
인덱스는 항상 정렬 상태를 유지하므로 order by, group by를 위한 소트 연산을 생략할 수 있도록 해준다.
(5) 인덱스 설계도 작성
* 인덱스 설계도 작성을 통한 튜닝 사례
09 비트맵 인덱스
B*Tree 인덱스의 rowid 에는 중복 값이 없지만 키에는 중복 값이 있을 수 있다.
비트맵 인덱스는 키 값에 중복이 없고, 키 값별로 하나의 비트맵 레코드를 갖는다.
비트맵 상의 각 비트가 하나의 테이블 레코드와 매핑된다.
비트가 1로 설정되어 있으면 상응하는 테이블 레코드가 해당 키 값을 포함하고 있음을 의미한다.
(1) 비트맵 인덱스 기본 구조
비트맵 인덱스는 첫번째와 마지막 비트의 rowid만을 갖고 있다가 테이블 액세스가 필요할 때면
각 비트가 첫 번째 비트로부터 떨어져있는 상대적인 거리를 이용해 rowid값을 환산한다.
* 비트맵 위치와 rowid 매핑
* 키 값의 수가 많을 때
* 키 값별로 로우 수가 많을 때
* 비트맵 압축
(2) 비트맵 인덱스 활용
비트맵 인덱스는 성별처럼 Distinct Value 개수가 적을 때 저장효율이 매우 좋다.
그런 컬럼이라면 B*Tree 인덱스보다 훨씬 적은 용량을 차지하므로 인덱스가 여러개 필요한 대용량 테이블에 유리하다.
(3) Records_per_block
비트맵 인덱스는 여러 인덱스를 동시에 활용할 수 있다는 장점 때문에 다양한 조건절이 사용되는,
특히 정형화되지 않은 임의 질의가 많은 환경에 적합하다.