스타일 A와 I의 설계의 효율 비교 

스타일A
스타일I
X1:고객번호+거래일자
X2:상품번호+거래일자
X3:상품번호+고객번호+거래일자
X4:거래일자
    X1:고객번호+거래일자
    X2:거래일자+상품번호+고객번호
조건
스타일A
스타일I
비고
조건1
    X1
X1
두번의 테이블 Random Access
조건2
    X2
X2-불필요한 상품번호까지 스캔하는 비효율존재
세번의 테이블 Random Access
조건3
    X3-단 한건의 Random Access
X1-두번의 테이블액세스, 거래범위가 좁으면 X2를 이용해 불필요한 Random Access를 없애는게 좋을수도
 
조건4
    X4
X2
동일한 테이블 Random Access, Range파티션 고려
  
테이블 Random 액세스 측면에서 보면, 스타일I가 조건3에서 약간불리. 하지만 고객번호가 변별력이 워낙 좋으니 큰 차이가 안날 수 있다. 부하가 심하면 X2사용하시고!  
인덱스스캔효율 측면에서 보면, 스타일I가 조건2, 조건3에서 비효율적. 하지만 인덱스 블록에는 하나당 수백개의 레코드가 담기므로 괜찮을것이다.
 
스캔 효율성 이외의 판단 기준
* 쿼리 수행 빈도
* 업무상 중요도
* 클러스터링 팩터
* 데이터량
* DML 부하
* 저장 공간
* 인덱스 관리 비용 등 
 
인덱스 설계는 공식이 아닌 전략과 선택의 문제
* 인덱스 설계가 어려운 이유는, 개별 쿼리 성능뿐만 아니라 그 개수를 최소화함으로써 
  DML 부하를 줄이는 것까지 목표로 삼아야 하기 때문이다.
  => 시스템 전체적인 시각에서 대안 전략들을 수립하고, 그 중 최적을 선택할 수 있는 능력이 무엇보다 중요
* 가장 핵심적인 액세스 경로(Critical Access Path) 한두 개를 전략적으로 선택
* 왜 그런 선택을 했는지, 전략적 판단 근거가 무엇인지 답할 수 있어야 한다.
 
인덱스 전략 수립을 위한 훈련
<쿼리1> - 수행빈도가 매우 높음
select * from 고객
where 고객번호 = :no;

<쿼리2> - 수행빈도가 그다지 높지 않음
select * from 고객
where 연령 = :age
and 성별 = :gender
and 이름 like :name || '%';

<쿼리3> - 수행빈도가 그다지 높지 않음
select * from 고객
where 연령 between :age1 and :age2
and 거주지역 = :region
and 등록일 like :rdate || '%';

<쿼리4> - 수행빈도가 매우 높음
select /*+ orderd use_nl(b) */ * from 주문 a, 고객 b
where a.거래일자 = :tdate
and a.상품번호 = :good
and a.고객번호 = b.고객번호
and b.거주지역 = :region;
* 액세스 효율을 높이는 것도 중요하지만 인덱스 개수를 최소화하는 것도 중요한 목표 *
 
(3) 결합 인덱스 컬럼 순서 결정 시, 선택도 이슈??
 
선택도가 액세스 효율에 영향을 주지 않는 경우
'='조건으로 항상 사용되는 컬럼들중에 선택도가 낮은 것을 앞쪽에 두려는 노력은 의미없거나, 더 손해일 수 있다.
IDX01 : 고객등급 + 고객번호 + 거래일자 + 거래유형 + 상품번호 
항상 '=' 조건으로 사용하는 컬럼 : 고객등급, 고객번호(고객등급은 선택도가 높고, 고객번호는 선택도가 낮다.)
between으로 사용하는 컬럼 : 거래일자
Q) 거래일자 앞쪽에 위치한 두 컬럼은 변별력에 따라 위치를 바꿔 줄 필요가 있는 것일까?
   즉, 변별력이 좋은 고객번호를 앞으로 이동시키는 것이 효과적인가? 
A) 선행컬럼이 모두 '='조건이면 범위검색까지의 인덱스 레코드는 한곳에 모여 있으며, 선택도가 높은 컬럼(고객등급)을 앞에두더라도 
   수평적 탐색을 위한 스켄범위는 최소화 될 것이므로 인덱스 엑세스 효율에 영향을 미치지 않는다.
 
선택도가 '높은 컬럼'을 앞쪽에 두는 것이 유리한 경우
- 오히려 선택도가 높은 컬럼(고객등급)을 선두에 두면 오라클 9i부터는 Index Skip Scan을 효과적으로 사용할 수 있어 유리하다.
 (Index Skip Scan은 선행컬럼의 Distinct Value개수가 적고, 후행컬럼의 Distinct Value개수가 많아야 효율적이다.)
- IN-List로 값을 제공함으로써 쉽게 튜닝할 여지가 생긴다.
- 인덱스 압축기능을 고려하더라도 고객등급을 앞으로 두는게 유리하다.
 
상황에 따라 유,불리가 바뀌는 경우
Q) 둘 중 하나가 조건절에서 빠지거나 범위검색 조건으로 사용될 수도 있는 조건에서
   인덱스 개수를 최소화할 목적으로 이 두 컬럼을 인덱스 선두에 나란히 두려 한다면 어느 쪽을 앞에 두는 것이 유리할까?
A) P.193 표 참고
   선택도가 높은 컬럼을 선두에 두면 나중에 범위검색조건이 사용되거나 조건절에 누락되더라도 Index Skip Scan 또는 IN-List를 활용할 수 있어 유리하다.
   선택도가 낮은 컬럼을 선두에 두면 범위검색조건으로 조회할 때는 불리하나, 
   입력값의 범위가 좁다면 비효율이 크지않아 Index Skip Scan나 IN-List를 활용못해도 오히려 유리할 수 있다.
 
선택도가 '낮은 컬럼'을 앞쪽에 두는 것이 유리한 경우
범위조건을 사이에 둔 컬럼은 선택도가 낮은 컬럼을 앞쪽에 두는 것이 유리하다.
    [조건절] where 고객번호 = :a and 상품번호 = :b and 거래일자 between :c and :d
    [인덱스] X01 : 고객번호 + 거래일자 + 상품번호 => X01 인덱스를 택하는 것이 현명하다.
            X02 : 상품번호 + 거래일자 + 고객번호
            => 위 조건절 하나라면 최선의 인덱스 설계는 고객번호 + 상품번호 + 거래일자 이다.
 
선택도가 낮은 컬럼을 '선택'하는 것이 유리한 경우
<검색조건1> where 거래일자 = :a and 상품번호 = :b
<검색조건2> where 거래일자 = :a and 고객번호 = :b 
 
Q) 거래일자를 선두에 둔 단하나의 인덱스를 생성한다고 한다면 후행 컬럼으로서 상품번호와 고객번호 둘 중 어떤 쪽을 택하겠는가?
A) 위 두 검색조건 중 어느 쪽이 더 많이 사용되는지를 보고 결정하는 것이 정답이다.
   만약 사용빈도까지 똑같다면 그때는 선택도가 낮은 고객번호를 선택하는 것이 현명하다.
 
** 결합인덱스 컬럼간 순서를 정할 때는, 
   개별컬럼의 선택도보다는 조건절에서 어떤형태로 자주 사용되는지, 
                        사용빈도는 어느 쪽이 더 높은지, 
                   데이터를 빠르게 검색하는 데에 어느쪽 효용성이 더 높은지 
등이 더 중요한 판단기준이다. **
 
 
(4) 소트 오퍼레이션을 생략하기 위한 컬럼 추가
- 인덱스는 항상 정렬상태를 유지한다. 인덱스를 이용하면 우리가 select절에 기술한 order by, group by의 연산을 생략할 수도 있다.
- 인덱스를 이용하여 소트연산을 대체하려면, 인덱스 컬럼 구성과 같은 순서로 누락없이 order by절에 기술한다. 단, 인덱스 구성 컬럼이 조건절에서 '='조건으로 비교되면, 누락되거나, 다른 순서로 기술해도 상관없다.
 
(5) 인덱스 설계도 작성
- 인덱스 설계시에는 시스템 전체 효율을 고려해야 한다.
- 개별 SQL이 아니라 전체를 보면서 전략을 수립하려면 일단 테이블별로 실제 일어나는 액세스 유횽을 모두 조사하는 과정일 필요하다.
 
Q) P193. 그림 1-57에 문제점은 무엇인가?
A) 액세스 경로가 총 25개인데 인덱스가 무려 15개나 된다.
   대부분 액세스 경로에 입력일 > = 이 포함되어 있는데, 대부분 인덱스의 선두 컬럼이 입력일이다.
   선두컬럼 입력일 조건에 해당하는 범위가 인덱스 전체 레코드에서 95%이상을 차지한다.
 
=> 해결책.
    입력을 컬럼을 모두 제거하는 새로운 인덱스 구성 전략을 수립하여 총 5개로 인덱스를 줄임.
    입력일자를 파티션키로 하는 RANGE 파티션을 구성한 듯 하다.
 



오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
작성자: 장영천(AskZZang)
최초작성일: 2011년 3월 6일
본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. 
http://www.bysql.net
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^