7.인덱스 스캔 효율

I/O 튜닝 핵심 원리

Sequential 액세스 선택도 ↑
Random 액세스 발생량 ↓

1. 비교 연산자 종류와 컬럼 순서에 따른 인덱스 레코드의 군집성

선두컬럼: 인덱스 구성상 앞쪽에 있는 컬럼
선행컬럼: 상대적으로 앞쪽에 있는 컬럼

ex

create table test (col1 number(4), col2 varchar2(10), col3 varchar2(10));
create index idx1 on test (col1, col2);
create index idx2 on test (col2, col1);
이와같은 테이블과 인덱스를 생성했을 때 idx 1 에서의 선두컬럼은 col1 이며 idx 선두컬럼은 col2

첫번째 나타나는 범위검색 조껀까지만 만족하는 인덱스 레코드는 모두 연속되게 모여 있지만, 그 이하 조건까지 만족하는 레코드는 비교 연산자 종류에 상관없이 흩어진다.(우연히 모여있을수는 있음)

2. 인텍스 선행 컬럼이 등치(=) 조건이 아닐 때 발생하는 비효율


select 해당층，평당가, 입력일, 해당동, 매물구분,I연사용일수, 중개업소코드
from 매물아파트매매
where 아파트시세코드=' A01011350900056 '
and 평형 = '59'
and 평형타입 = 'A'
and 인터넷매물 between '1 ' ard '2 '
order by 입력일 desc

인텍스 선행 컬럼이 모두 '=' 조건일 때 필요한 범위만 스캔하고 멈출 수 있는 것은， 조건을 만
족하는 레코드가 모두 한데 모여 있기 때문이다.

인덱스 구성을 인터넷매물 + 아파트시세코드 + 평형 + 평형타입 순으로 바꾸고 나서 같
은 SQL을수행해보면，그림 1-39처럼 인덱스스캔 범위가넓어진다.

선두 컬럼이 적게 잡힐수 있는 인덱스 구조를 채택하는것이 중요.

2. BETWEEN 조건을 IN-List로 바꾸었을 때 인텍스 스캔 효율

select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
frαn 매물아파트매매
where 인터넷매물 in('l', '2')
and 아파트시세코드= 'A01011350900056'
and 평형 = '59'
and 평형타입 = 'A'
order by 입력일 desc

select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
frαn 매물아파트매매
where 인터넷매물 in('l')
and 아파트시세코드= 'A01011350900056'
and 평형 = '59'
and 평형타입 = 'A'
order by 입력일 desc
union all
select 해당층, 평당가, 입력일, 해당동, 매물구분, 연사용일수, 중개업소코드
frαn 매물아파트매매
where 인터넷매물 in('2')
and 아파트시세코드= 'A01011350900056'
and 평형 = '59'
and 평형타입 = 'A'
order by 입력일 desc

in-list 로 변경시 주의 사항
in-list는 union all 을 사용하여 해당 테이블을 한번 더 읽는 방법이므로 list 의 개수가 큰 경우에는 적합하지 않다.만약 해당 테이블에 인덱스를 추가하는데 문제가 없는 상황이라면 인덱스 추가후 힌트로 인덱스를 유도해주는 편이 좋으리라 생각된다.

4. index skip scan 을 이용한 비효율 해소


create table t74
as
select rownurn 고객번호
, '2008' || lpad(ceil(rownurn/100000), 2, '0') 판매월
, deode(mod(rownurn,12),1,'A', 'B') 판매구분
, round(dbms_random.value(1000, 100000) , - 2) 판매금액
from dual
connect by level <= 1200000


select count(*)
from t74 t
where 판매구분 = 'A'
and 판매월 between '200801 ' and '200812 '


create index t74_IDX1 on t74 (판매구분, 판매월);


create index t74_IDX2 on t74 (판매월,판매구분);

인덱스 스킵스캔 사용 시


/*+ index_ss(t74 t74_IDX2)*/

5. 범위검색 조건을 남용할 때 발생하는 비효율

'070' , '0' ,'보급' 조건으로 검색

SELECT 고객ID, 상품명, 지역, ...
FROM 가입상품
WHERE 회사 :com
AND 지역 :reg || ' % '
AND 상품명 LIKE :prod || ' % '

SELECT 고객ID, 상품명, 지역, ...
FROM 가입상품
WHERE 회사 :com
AND 지역 LIKE :reg || ' % '
AND 상품명 LIKE :prod || ' % '

6. 범위검색 조건을 남용할 때 발생하는 비효율

select *
from (
select rownum rnum, 도서번호, 도서명, 가격, 저자, 출판사, isbn
frαn (
select 도서변호, 도서명 , 가격, 저자, 출판사, isbn
from 도서
where 도서명 like :book nm || '%'
order by 도서명
where rownum <= 100
where rnum >= 91

앞쪽 페이지는 원활하게 보이는 편이나 마지막 페이지 쪽 근처로 검색하면 느려진다.

– or-Expansion 을 이용하는 방법과 주의 사항

or 조건에 대한 expansion(union all 분기) 이 일어나면 뒤쪽 조건절이 먼저 실행
(9i기준 10g 의 경우 ordered_predicates 힌트 명시)

– rowid 를 concatenation 하면 결과에 오류 발생

rowid 와 암시적 형변환으로 변경된 rowidtochar(rowid) 는 정렬순서가 다르다.

select greatest( 'AAAH+WAAJAAHxTAA9', 'AAAH+WAAJAAAHxTAA+' ) from dual;
 select greatest( chartorowid('AAAH+WAAJAAAHxTAA9'), chartorowid('AAAH+WAAJAAAHxTAA+')) from dual;

-- 인덱스를 스캔하면서 rowid를 필터링할 때 발생하는 비효율

rowid = 조건은 빠르지만 rowid >등의 범위 조건이면 효율적이지 않다.

7. between 과 like 스캔 범위 비교

between 대신 like 를 사용하는 이유는 단순하고 쉽기 때문
그러나 between 을 사용하면 like 를 사용하는 경우에 비해서 손해를 볼 일은 없다.

8. 선분이력의 인덱스 스캔 효율

선분이력시 pk 를 포함하지 않으면 중복값이 발생하여 효율 저하

시작일 +종료일 구성시 최근시점 조회

select /*+ index_desc(a idx_xOl) */ *
from 고객별연제금액 a
where 고객번호 = '123'
and '최근날짜' between 시작일 and 종료일
and rownum <= 1

시작일+ 종료일 구성시 과거 시점 조회

select /*+ index_desc(a idx_xOl) */ *
from 고객별연제금액 a
where 고객번호 = '123'
and '과거날짜' between 시작일 and 종료일
and rownum <= 1

종료일 +시작일 구성시 최근시점 조회

select *
from 고객별연체금액 a
where 고객번호 = '123'
and '최근날짜' between 시작일 and 종료일
and rownum <= 1

중간 시점 조회


인댁스 구성이 [고책번호 + 시작일 + 종료일] 일 때
select /*+ index_desc (a idx_x01) */ *
from 고객별연체금액 a
where 고객번호 = '123'
and '20031010' between 시작일 and 종료일
and rownum <= 1

최근데이터 주로 조회시 종료일 + 시작일로 인덱스 구성
과거 조회시 시작일 + 종료일 순으로 구성
rownum 을 사용하여 빠르게 스캔 가능

9. Access predicate 와 Filter Predicate

인덱스를 경유해 테이블을 액세스 할 때는 아래와 같이 최대 3가지 Predicate 정보가 나타날 수 있다.
1. 인텍스 단계에서의 Access Predicate (id=4 access 부분)
2. 인텍스 단계에서의 Filter Predicate {id=4 filter 부분)
3. 테이블 단계에서의 Filter Predicate (id=1 filter 부분)

-인덱스를 경유하지 않고 테이블 전체를 스캨ㄴ할 때
4. 테이블 단계에서의 Filter Predicate (jd=3 filter 부분)

수직적 탐색 과정에서 모든 인덱스 컬럼을 비교 조건으로 사용한다.

10. Index Fragmentation (인덱스 단편화)

읽을거리: 인덱스 리빌드를 둘러싼 논쟁 http://ukja.tistory.com/82
요약: delete 작업후 인덱스는 다음 insert 시 정리및 재사용된다.

 SQL> alter index t_idx shrink space compact ;
SQL> alter index t_idx rebuild online;

h5.index rebuild 실행하는 경우

인덱스 분할에 의한 경합이 높을 때
주주 사용되는 인덱스 스캔 효율을 높이고자 할 때. 특히 NL 조인에서 반복 액세스 되는 인덱스의 높이(height) 가 증가했을 때
대량의 delete 작업을 수행한 이후 다시 레코드가 입력되기까지 오랜 기간이 소요될 때
총 레코드 수가 일정한대도 인덱스가 계속 커질 때

131~179