h1.인덱스 설계

{*}인덱스 설계는 공식이 아닌 전략과 선택의 문제{*}

- 인덱스 설계시 중요한 두 가지 선택 기준
1. 조건절에 항상 사용되거나, 자주 등장하는 컬럼들을 선정한다.
2. '='조건으로 자주 조회되는 칼럼들을 앞쪽에 둔다.

• {*}인덱스 설계시 시나리오를 위한 예시{*}

시나리오 상황 : 총고객수 = 100만명 , 상품 = 10만개 , 거래일자 범위는 고정적이지 않음

• {*}Index 설계 유형 케이스{*}
  

• {*}유형 분석{*}

스타일 A : '=' 조건 컬럼을 모두 선두에 둠으로써 모든 조건에 대해 인덱스 스캔 효율을 100% 달성하는 스타일. DML부하,관리비용,신규생성시 문제가발생
스타일 B : 고객번호별 조회와 상품번호별 조회를 둘 다 중요한 액세스 경로로 판단. 넓은 거래 일자 조건을 입력시 상품번호 필터링때문에 다소 비효율.
스타일 C : 스타일 B와 거의 같은 전략이지만 X1인덱스에 상품번호를 추가함으로서 검색3번일때에서 불필요한 테이블 액세스를 감안하여 작성한 차이가 보임.
스타일 D : 이역시 스타일 B와 기본적으로 같은 전략이며, 다만 XN2 인덱스에 선두컬럼이 상품번호이기에 변별력이 스타일B에 비해 떨어진다.
스타일 E : 범위검색 조건을 최선두에 둔다면 나머지 조건은 거의 인덱스 필터 역활만 하므로 둘 중 하나는 제거해도 무방. J스타일과 동일해진다.
스타일 F : 시나리오 예시를 보면 만약 날자 범위가 넓게 잡혀있다면 없으나 마나한 Index나 마찬가지이다.
스타일 G : 조건 3과 같은 경우 좋은 변별력을 갖겠지만 그외 조건같은 상황이라면 기간선두 컬럼으로 인해 변별력이 좋지 않아 비효율성이 커질수 밖에없다.
스타일 H : 검색조건 2 와 4에대한 대비가 없고, 고객번호로 조회하는 검색하는 조건1에 대해서도 2번째 컬럼의 미존재로인해 비효율이 크다. 
스타일 I : X2인덱스 선두에 거래일자를 둠으로써 비효율은 생기지만 항상 사용되는 조건이기에 범용적으로 사용될수 있다. 일자 범위가 넓지만 않다면 실용적.
스타일 J : 하나의 인덱스로 모든 조건절을 해결할 의도가 담겨있으며, 근본적인 비효율을 안고 있어 신중히 선택해야할 설계방식

• {*}인덱스 효율성 비교 분석{*}

100%효율성을 보이는 목적의 스타일A와 스타일 I 비교 분석

- 검색조건1  
SELECT *
FROM   거래테이블
WHERE  고객번호 = 1
AND    거래일자 BETWEEN :B1 AND :B2;

풀이: 조건에 해당하는 1번 고객의 2번과 3번 블록에 흩어져 있으며, 고객번호와 거래일자 외에 정보가 필요하다면
두가지 스타일 모두 2번의 테이블 랜덤 액세스가 발생하게 된다.

- 검색조건2  
SELECT *
FROM   거래테이블
WHERE  상품번호 = 'A'
AND    거래일자 BETWEEN :B1 AND :B2;

풀이: 조건에 해당하는 'A' 상품번호가 각각의 블록에 흩어져 있으며, 스타일 A의 경우 X2 Index가 사용되어 조건부분 외에 정보가 필요하다면
테이블 랜덤 액세스가 3번 일어나게 되며, 스타일 I의 경우에도 Index X2가 사용되어 Index정보 외의 정보가 필요하다면 테이블 랜덤 액세스는 
동일하게 3번 호출하게 되나 일자가 선두컬럼으로 형성되어 있기 때문에 불필요한 상품의 정보까지 읽는 비효율성을 보이게 된다.

- 검색조건3  
SELECT *
FROM   거래테이블
WHERE  고객번호 =  1
AND    상품번호 = 'A'
AND    거래일자 BETWEEN :B1 AND :B2;

풀이: 조건에 만족하는 데이터는 2번 블럭에 단 한건만 존재하는 상황이다. 스타일 A에서는 X3 Index를 사용하여 한건의 RowID를 가지고,
Index외의 정보가 필요시에 테이블 랜덤 액세스가 발생하게 되나, 스타일 I의 경우에는 X1인덱스를 사용하면 상품번호가 Filter작용을 하여
테이블 랜덤액세스가 2회 일어나게 되며 X2의 사용시 테이블 랜덤 액세스는 1회 일어나게 되나 거래일자 범위에 따라 스캔 비효율이 크게 작용할수도
적게 작용할수도 있게된다. 

- 검색조건3 4
SELECT *
FROM   거래테이블
WHERE  거래일자 BETWEEN :B1 AND :B2;

풀이: 이 와 같은 조회의 경우 스타일A의 X4 Index나 스타일 I의 X2 Index나 테이블 랜덤 액세스는 동일 하게 1~3까지 모두 랜덤 엑세스를 일으키게 된다.
이 와같은 조회문이 자주 사용하게 된다면 거리일자를 키로한 Range 파티션 구성을 고려해야하며, Index설계시 파티션 설계를 병행해야 하는 이유가 된다.

{+}정리{+} : 성능에 결정적인 영향을 미치는 테이블 Random 액세스 측면에서 보면 위의 케이스들의 풀이를 결과 스타일 I가 약간 불리한 면이 나타난다.
인덱스 효율 측면에서 보면, 검색 조건 2번과 3번에 대해서도 스타일 I가 약간 비효율적이기는 하나. 인덱스 블록 하나당 수백개 레코드가 담기므로 차이가
크지않다. 그리고 저자는 인덱스가 리프 블록당 500개가 담긴다하여 두가지의 스타일을 비교하였을때 스타일I가 10블럭정도만 더 읽게 될뿐이다라고 정의하고
있는데, 만약 데이터의 상황이 위의 예제가 아닌 대용량 테이블이며 조회하는 범위가 한달 또는 그 이상의 기간을 조회하게되었을 경우에 과연 스타일 I의 경우
책의 말처럼 차이가 크지 않다 말할수 있을까???(저자도 거래일자 구간에 상수 값을 입력했고 그 구간에 속한 데이터가 일정량을 넘지 않는다고 가정하에 정의를 한 것이기 때문에 좀더 깊게 생각해볼 필요가 있다.)

• {*}스캔 효율성 이외의 판단 기준{*}
  실제 인덱스를 설계할 때는 시스템 전체적인 관점에서의 추가적인 요소들을 고려해야 하며, 아래와 같다

1. 쿼리 수행 빈도
2. 업무상 중요도
3. 클러스터링 팩터
4. 데이터량
5. DML부하
6. 저장 공간
7. 인덱스 관리 비용 등
 

또한, 인덱스 설계는 공식이 아닌 전략과 선택의 문제이므로 여러가지의 경우를 생각하여 상황에 맡게 설계를 하여야한다.

• {*}인덱스 전략 수립을 위한 훈련{*}
  

테이블 구성 상황
고객 : 100만명
거주지역 : 15곳(서울 ,부산 ,광주 ,대구 ,대전 ,인천 ,경기 ,강원 등등) 분포 균일
수행빈도 : 쿼리 1, 4 높음
           쿼리 2, 3 높지 않음

이와 같을때 인덱스 설계를 어떻게 하겠는가?

고객 테이블
X1 : 고객번호 + 거주지역
X2 : 연령 + 거주지역 + 성별

주문 테이블
X1 : 상품번호 + 거래일자 + 고객번호

고객 테이블에 X1으로 1,4의 자주 사용되는 쿼리문의 효율을 높이기 위함이며, 4번 쿼리시 Driving되는 테이블인 주문테이블에 X1인덱스를 추가하였으며
자주 사용되지 않는 2,3을 위해 고객 테이블에 X2 Index를 추가하여 연령을 선두컬럼에 두어 쿼리3에서는 약간의 비효율이 발생하나 범용성을 위함.

{*}결합 인덱스 컬럼 순서 결정시, 선택도 이슈{*}

• {*}선택도가 액세스 효율에 영향을 주지 않는 경우{*}

1. '=' 조건으로 사용되는 컬럼럼들을 앞쪽으로 위치되었을때, 선택도가 낮은 컬럼일 경우 의미 없거나 오히려 손해일수 있다.(SKIP SCAN을 발생시킴)
   

위와 같이 Index를 구성되었을때 Between 조건으로 사용되는 거래일자 뒤의 거래유형 과 상품번호는 필터 조건으로 사용되므로 변별력을 따질 이유는 없다.
하면, 거래일자 앞에 두컬럼에 대해서는 변별력에 따라 위치를 바꿔 줄 필요가 있는 것일까?(즉, 변별력 좋은 고객번호를 앞으로 두어야하지 않을까?)
수직적 탐색 과정에선 몇몇 경우를 제외하고 어느 것을 앞에 두더라도 스캔 시작 지점은 어느 것을 앞에 두더라도 동일하며, 선행 컬림이 모두 '='조건이면
첫 번째 범위 검색 조건까지의 인덱스 레코드들은 모두 한 곳에 모여있다. 따라서 스캔 범위는 최소화 될 것이고 , 인덱스 액세스 효율에 전혀 영향을 미치지 않는다.

• {*}선택도가 '높은 컬럼'을 앞쪽에 두는 것이 유리한 경우{*}
  1.오히려 고객등급을 선두에 두면 조건에서 고객등급이 누락시 Skip Scan이 발생하여 조건절에 누락되거나 범위검색 조건으로 조회되더라도 효과적으로 활용가능
  2.또한 인덱스 압축 기능을 고려하더라도 고객등급을 앞쪽에 두는 것이 유리하다. 선택도가 높은 컬럼을 앞쪽에 두어야 인덱스 압축률이 더 좋아지기 때문.
  3.변별력이 좋은 칼럼을 선두에 두는 것이 좋다는 상식은 오라클 5버전에서 사용하던 인덱스 압축 방식 때문이고 이러한 특징은 오라클 6에서 로우 단위 Lock이 구현되면서 사라졌다.(톰 카이트 말씀)

• {*}상황에 따라 유.불리가 바뀌는 경우{*}

!유불리가 바뀌는경우.JPG!
1.선택도가 높은 컬럼을 선두에 두면 나중에 범위검색조건이 사용되거나 조건절에 누락되더라도 ISS 또는 IN-List를 활용할 수 있어 유리
2.선택도가 낮은 컬럼을 선두에 두면 범위검색조건으로 조회할 때는 불리하나, 입력값의 범위가 좁다면 비효율이 크지않아 ISS나 IN-List를 활용못해도 오히려 유리할 수 있다.

• {*}선택도가 '낮은 컬럼'을 앞쪽에 두는 것이 유리한 경우{*}
  1.범위검색 조건을 사이에 둔 컬림끼리는 선택도가 낮은 컬럼을 앞쪽에 두는것이 유리

상황 예)
WHERE 고객번호 = :a
AND   상품번호 = :b
AND   거래일자 BETWEEN :c 
              AND     :d;

고객수     : 100만
상품개수   :   1만 

위와 같은 상황일때
X01 : 고객번호 + 거래일자 + 상품번호
X02 : 상품번호 + 거래일자 + 고객번호
이 두 Index 설계 전략중 X01로 설계하는 것이 현명하다.
그 이유는 거래일자 이후는 필터역활을 하는데 변별력이 좋은 고객 번호가 필터역활을 하는 것보다 고개번호가 선두컬럼으로 위치하여 변별력을 높이는게
유리하기 때문이다.

• {*}선택도가 낮은 컬럼을 '선택'하는 것이 유리한 경우{*}

상황 예)
<검색조건1> WHERE 거래일자 =:a AND 상품번호 =:b
<검색조건2> WHERE 거래일자 =:a AND 고객번호 =:b

거래일자를 선두로 둔 Index를 설계해야 하는 상황이며, 거래일자 뒤에 상품번호 or 고객번호를 둔 Index 하나만 생성해야 한다면?
또한 두 조건의 사용빈도는 동일하다.

하나를 선택해야 한다면 고객번호를 후행에 두는것이 현명하다. 그 이유는 변별력이 좋은 고객 번호를 두어 테이블 랜덤 액세스를 최소화 하는것이 유리하기
때문이다.
하지만 이런 식으로 후행 컬럼의 선택도를 비교해 둘 중 하나를 선택하는 일은 없다.

*결론적으로 결합 인덱스 컬럼 간 순서를 정할 때의 판단기준*
1. 개별 컬럼의 선택도 보다는 조건절에서 어떤 형태로 자주 사용되는가
2. 사용빈도는 어느 쪽이 높은가
3. 데이터를 빠르게 검색하는 데에 어느 쪽 효용성이 높은가

소트 오퍼레이션을 생략하기 위한 컬럼 추가

1.인덱스는 항상 정렬상태를 유지한다. 인덱스를 이용하면 우리가 select절에 기술한 order by, group by의 연산을 생략할 수도 있다.

SQL> create table t
as select rownum a, rownum b, rownum c, rownum d, rownum e
from dual
connect by level <= 100000;
테이블이 생성되었습니다.

SQL> create index t_idx on t(a, b, c, d);
인덱스가 생성되었습니다.

{+}소트오퍼레이션 생략{+}

select * from t where a=1 order by a, b, c;
select * from t where a=1 and b=1 order by c, d;
select * from t where a=1 and c=1 order by b, d;
select * from t where a=1 and b=1 order by a, c, b, d;

SQL> explain plan for
  2  select * from t
  3  where a between 1 and 2
  4  and b not in (1, 2)
  5  and c between 2 and 3
  6  order by a, b, c, d;

해석되었습니다.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    24 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    24 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

SQL> explain plan for
  2  select * from t
  3  where a between 1 and 2
  4  and c between 2 and 3
  5  order by a, b, c;

해석되었습니다.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    24 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    24 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

SQL> explain plan for
  2  select * from t
  3  where a between 1 and 2
  4  and b <> 3
  5  order by a, b, c;

해석되었습니다.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    24 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    24 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

{+}Index Full Scan 방식으로 정렬작업 생략{+}

SQL> explain plan for
  2  select /*+ index(t) */ * from t
  3  where b between 2 and 3
  4  order by a, b, c, d;

해석되었습니다.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3778778741

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    24 |   434   (2)| 00:00:06 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    24 |   434   (2)| 00:00:06 |
|*  2 |   INDEX FULL SCAN           | T_IDX |     1 |       |   433   (2)| 00:00:06 |
-------------------------------------------------------------------------------------

{+}인덱스로 소트오퍼레이션 대체 불가{+}

(CASE 1)

SQL> explain plan for
  2  select * from t where a=1 order by c;

해석되었습니다.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1454352066

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |    24 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY               |       |     1 |    24 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T     |     1 |    24 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_IDX |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

(CASE 2)

SQL> explain plan for
  2  select * from t
  3  where a=1
  4  and b between 1 and 2
  5  order by c, d;

해석되었습니다.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1454352066

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |    24 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY               |       |     1 |    24 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T     |     1 |    24 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_IDX |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

(CASE 3)

SQL> explain plan for
  2  select * from t
  3  where a=1
  4  and b between 1 and 2
  5  order by a, c, b;

해석되었습니다.

SQL>  select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1454352066

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |    24 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY               |       |     1 |    24 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T     |     1 |    24 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_IDX |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

• 단, 정렬연산을 생략할 수 있다는 것이지, 항상 그런건 아니다. 옵티마이저 판단에 의해 Full Table Scan을 선택하거나, 다른 인덱스를 선택한다면 얘기는 또 달라진다.