08 인덱스 설계
(1)가장 중요한 두 가지 기준
   1. 조건절에 항상 사용되거나, 자주 등장하는 컬럼들을 선정한다.
   2. '=' 조건으로 자주 조회되는 컬럼들을 앞쪽에 둔다
  
(2)인덱스 설계는 공식이 아닌 전략과 선택의 문제
   ▶간단한 설계 예제 - 전제조건 총 고객수 100만명, 상품은 10만개
     ------------------------------------------------------------------
     | 검색조건 1 | WHERE 고객번호 = 1                                |
     |            |   AND 거래일자 BETWEEN '20090101' AND '20091231'  |
     ------------------------------------------------------------------
     | 검색조건 2 | WHERE 상품번호 = 'A'                              |
     |            |   AND 거래일자 BETWEEN '20090101' AND '20091231'  |
     ------------------------------------------------------------------
     | 검색조건 3 | WHERE 고객번호 = 1                                |
     |            |   AND 상품번호 = 'A'                              |
     |            |   AND 거래일자 BETWEEN '20090101' AND '20091231'  |
     ------------------------------------------------------------------
     | 검색조건 4 | WHERE 거래일자 BETWEEN '20090101' AND '20091231'  |
     ------------------------------------------------------------------
  
   ▶Review(p182 참조)
     A : '=' 조건 컬럼을 모두 선두에 사용할려는 인덱스 스캔 효율을 100% 달성하려는 스타일
         나중에 새로운 액세스 유형이 도출되었을 때 문제가 될 수 있다.
     C : 검색조건 3일 때도 불필요한 테이블 액세스가 발생하지 않도록 하려고 X1 인덱스에
         상품번호를 추가한 점이 좋은 전략이라고 평가할 수 있다.
     (중간 생략...)
     I : X2 인덱스 선두에 거래일자를 둠으로 약간의 비효율은 생기겠지만 모든 검색조건에
         범용적으로 사용될 수 있다. 또한 모든 검색 조건을 만족하면서도 인덱스 개수를
         최소화 했다는 측면에서 높이 평가할 수 있따.
  
   ▶효율성 비교 분석 - Data상황 및 전제조건 참조 (그림 1-55 참조)
     -----------------------------------------------------------------------------
      [전략1 : 스타일 A]                    | [전략2 : 스타일 I]
     -----------------------------------------------------------------------------
      X1 : 고객번호 + 거래일자              | X1 : 고객번호 + 거래일자
      X2 : 상품번호 + 거래일자              | X2 : 거래일자 + 상품번호 + 고객번호
      X3 : 상품번호 + 고객번호 + 거래일자   |
      X4 : 거래일자                         |
     -----------------------------------------------------------------------------
    
     --------------------------------------------------------------------------------------
     | 검색조건 1 | 1번 고객의 거래 데이터가 2,3 블럭에 흩어져 있다.
     |            | 따라서 전략1,2 모두 X1 인덱스를 사용함으로 동일함
     --------------------------------------------------------------------------------------
     | 검색조건 2 | 상품 A가 1,2,3 블럭에 흩어져 있다.
     |            | 전략 1,2 모두 X2 인덱스 사용. Random Access또한 3번 동일
     |            | 다만 전략 2의 x2는 불필요한 상품번호까지 스캔(인덱스의 구성차이)
     --------------------------------------------------------------------------------------
     | 검색조건 3 | 조건을 만족하는 데이터는 2번 테이블 블록에 단 한건 존재
     |            | 전략 1의 X3 인덱스는 단 한 블록만 Random Access
     |            | 전략 2의 x1 인덱스 이용시는 테이블 Access가 2번 발생
     |            | 거래일자 범위가 넓지 않다면 인덱스 비효율을 감소하더라도
     |            | X2 인덱스를 사용해 불필요한 테이블 Random Access를 고려할 수 있음
     --------------------------------------------------------------------------------------
     | 검색조건 4 | 전략 1의 X4 인덱스나 전략 2의 X2 인덱스 둘다 1~3 블럭을 동일하게
     |            | 처리함. Random Access 횟수도 똑같음
     --------------------------------------------------------------------------------------
  
(3)결함 인덱스 컬럼 순서 결정 시, 선택도 이슈
   선택도(Selectivity) : 전체 레코드 중에서 조건절에 의해 선택되는 비율. 여기서는 인덱스
                         컬럼이 모드 '='로 조회할 때 평균적으로 선택되는 비율
                        
  
   인덱스 생성 여부를 결정할 때는 선택도가 충분히 낮은지가 중요한 판단기준임이 틀림없지만
   결합 인덱스 컬럼 간 순서를 정할 때 변별력이 높은 쪽을 앞에 두는 것이 유리한지는 상황에
   따라 다르다.
  
   ▶선택도가 액세스 효율에 영향을 주지 않는 경우
     eg) IDX01 : 고객등급 + 고객번호 + 거래일자 + 거래유형 + 상품번호
     예를 들어 고객번호와 고객등급은 '=' 조건으로, 거래일자는 between으로 사용된다.
 

     '=' 조건으로 항상 사용되는 컬럼들을 앞쪽에 위치시켰다면, 그 중 선택도가 낮은 것을 앞쪽에
     두려는 노력은 의미가 없거나 오히려 손해힐 수 있다. 여기선 첫번째 범위 검색(Range Scan)
     조건(위에선 거래일자 between으로 Range Scan)까지의 인덱스 레코드들은 모두 한 곳에 모여
     있기에 스캔 범위는 최소화될 것이고 인덱스 액세스 효율에 영향을 미치지 않는다.
    
   ▶선택도가 '높은 컬럼'을 앞쪽에 두는 것이 유리한 경우
     위의 예제에서 [고객등급]을 선두에 두면 나중에 이 조건절이 누락되거나 범위검색으로 조회
     되는 일이 생기더라도 Index Skip Scan을 효과적으로 할용할 수 있어서 유리하다.
    
     참고로 Index Skip Scan은 선행 컬럼의 Distinct Value 개수가 적고, 후행 컬럼의 Distinct
     Value 개수가 많아야 효율적이다. 또한 ISS(Index Skip Scan)가 아니더라도 첫 컬럼에 대해 In-List로 값을
     제공할 수 있기에 튜닝의 여지가 생긴다.
    
   ▶상황에 따라 바뀌는 경우
     위의 예제에선 두 컬럼 모두가 '=' 조건인 경우에 대해서 살펴 봄
     아래는 두 컬럼 모두가 '='가 아닌 최소한 한 컬럼이 Range로 비교되는 경우는 상황에 따라 정리
    
     CREATE TABLE TRADE_T
     NOLOGGING
     AS
     SELECT CEIL(DBMS_RANDOM.VALUE(1, 50000))     AS CUST_ID
          , DBMS_RANDOM.STRING('U', 1)            AS CUST_GR
          , SYSDATE - DBMS_RANDOM.VALUE(1, 365*3) AS TR_DATE
       FROM DUAL
     CONNECT BY LEVEL < 1000001
     ;
    
     CREATE INDEX TRADE_T_IDX01 ON TRADE_T(CUST_ID, CUST_GR, TR_DATE) NOLOGGING;
    
     CREATE INDEX TRADE_T_IDX02 ON TRADE_T(CUST_GR, CUST_ID, TR_DATE) NOLOGGING;

     DROP TABLE GRADE_T;
     CREATE TABLE GRADE_T NOLOGGING
     AS
     SELECT CHR(64 + ROWNUM) AS CUST_GR FROM DUAL CONNECT BY LEVEL < 27
     ;
    
     SELECT * FROM TRADE_T WHERE CUST_ID = 2000;
     -------------------------------------------------------------------------------------
          조 건 절    |                           인덱스  구성
     -----------------|-------------------------------------------------------------------
     고객번호|고객등급| 고객번호 + 고객등급 + 거래일자  | 고객등급 + 고객번호 + 거래일자
     --------|--------|---------------------------------|--------------------------------
             |        | 스캔범위가 넓어 불리하지만 고객 | 유리                          
             |        | 번호에 입력범위가 좁을 때는     |                         
        >=   |    =   | 비효율이 크지 않음. 입력범위가  |
             |        | 넓을 때가 문제이고 ISS 또는     |
             |        | In-List 기법을 활용하지 못하는  |
             |        | 단점이 있음                     |
     --------|--------|---------------------------------|---------------------------------
             |        | 유리                            | 선택도가 높은 고객등급 스캔범위
         =   |   >=   |                                 | 가 결정되어 불리하지만, ISS 또는
             |        |                                 | In-List를 활용한다면 큰 비효율은
             |        |                                 | 없음
     --------|--------|---------------------------------|---------------------------------
             |        | 고객번호에 입력범위가 좁을 때는 | 선택도가 높은 고객등급 스캔범위
             |        | 비효율이 크지 않음. 입력범위가  | 가 결정되어 불리하지만, ISS 또는
        >=   |   >=   | 넓을 때가 문제이고 ISS 또는     | In-List를 활용한다면 큰 비효율은
             |        | In-List 기법을 활용하지 못하는  | 없음                           
             |        | 단점이 있음                     |
     --------|--------|---------------------------------|---------------------------------
         =   | (누락) | 유리                            | ISS 또는 In-List 활용가능
     --------|--------|---------------------------------|---------------------------------
      (누락) |    =   | Full Table Scan 이 유리         | Full Table Scan 이 유리
     -------------------------------------------------------------------------------------
     종합해 보면, 선택도가 높은 컬럼(예, 고객등급)을 선두에 두면 나중에 범위검색 조건이 사용되거나
     아예 조건절에서 누락되더라도 ISS(Index Skip Scan) 또는 In-List를 활용할 수 있어 유리하다.
                                                                  
   ▶선택도가 [낮은 컬럼]을 앞쪽에 두는 것이 유리한 경우 - 실무에서 많이 적용됨
     범위검색 조건을 사이에 둔 컬럼끼리는 선택도가 낮은 컬럼을 앞쪽에 두는 것이 유리하다
    
     eg)고객 : 100만명, 상품 : 1만개
     WHERE 고객번호 = :a
       AND 상품번호 = :b
       AND 거래일자 BETWEEN :c
                        AND :d
    
     X01 : 고객번호 + 거래일자 + 상품번호
     X02 : 상품번호 + 거래일자 + 고객번호   
    
     중간에 놓인 거래일자가 BETWEEN 조건이어서 변별력이 좋은 컬럼에 의해 스캔범위가 최소화
     되기에 X01이 유리하다.                                                     
                                                                  
   ▶선택도가 [낮은 컬럼]을 [선택]하는 것이 유리한 경우 - 비 현실적이나 참고할 만함
  
   결론적으로 [결합 인덱스] 컬럼 간 순서를 정할 때는, 개별 컬럼의 선택도보다는 조건절에서
   어떤 형태로 자주 사용되는지, 사용빈도는 어느 쪽이 높은지, 데이터를 빠르게 검색하는 데에
   어느 쪽 효용성이 높은지 등이 더 중요한 판단기준이다.
 

(4)소트 오퍼레이션을 생략하기 위한 컬럼 추가
   인덱스는 항상 절력 상태를 유지하므로 ORDER BY, GROUP BY를 위한 소트 연산을 생략할 수
   있도록 해준다. 따라서 조건절에 사용하지 않은 컬럼이더라도 소트 연산을 대체할 목적으로
   인덱스 구성에 포함시킴으로써 성능 개선을 도모할 수 있다.
  
   CREATE TABLE T
   NOLOGGING
   AS
   SELECT ROWNUM A, ROWNUM B, ROWNUM C, ROWNUM D, ROWNUM E
     FROM DUAL
   CONNECT BY LEVEL <= 100000;
  
   CREATE INDEX T_IDX ON T(A, B, C, D);
      
   SELECT * FROM T WHERE A = 1           ORDER BY A, B, C
   --UNION ALL
   SELECT * FROM T WHERE A = 1 AND B = 1 ORDER BY C, D
   --UNION ALL
   SELECT * FROM T WHERE A = 1 AND C = 1 ORDER BY B, D
   --UNION ALL
   SELECT * FROM T WHERE A = 1 AND B = 1 ORDER BY A, B, C, D;
 

   ▶PLAN을 살펴보면 SORT ORDER BY가 없음
   -------------------------------------------------------------------------------------
   | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
   -------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT            |       |     1 |    65 |     3   (0)| 00:00:01 |
   |   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    65 |     3   (0)| 00:00:01 |
   |*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     2   (0)| 00:00:01 |
   -------------------------------------------------------------------------------------
  
   단, ORDER BY 절의 순서가 INDEX 순서와 다를 경우는 SORT 발생함
   SELECT * FROM T WHERE A = 1           ORDER BY C, B, A;    
   --------------------------------------------------------------------------------------
   | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
   --------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT             |       |     1 |    65 |     4  (25)| 00:00:01 |
   |   1 |  SORT ORDER BY               |       |     1 |    65 |     4  (25)| 00:00:01 |
   |   2 |   TABLE ACCESS BY INDEX ROWID| T     |     1 |    65 |     3   (0)| 00:00:01 |
   |*  3 |    INDEX RANGE SCAN          | T_IDX |     1 |       |     2   (0)| 00:00:01 |
   --------------------------------------------------------------------------------------

  
   또한, 아래처럼 ORDER BY 절에 기술한 순서가 인덱스 순서와 일치하기만 한다면 조건절에서
   어떤 연산자로 비교되더라도 정렬작업이 생략될 수 있다
   --------------------------------------------------------------------
         INDEX RANGE SCAN(A)  |            INDEX FULL SCAN(B)
   --------------------------------------------------------------------
    SELECT * FROM T           | SELECT /*+ INDEX(T, T_IDX) */ * FROM T
     WHERE A BETWEEN 1 AND 2  |  WHERE B BETWEEN 2 AND 3
       AND B NOT IN (1, 2)    |  ORDER BY A, B, C, D;
       AND C BETWEEN 2 AND 3  |
     ORDER BY A, B, C, D;     |
                              |
    SELECT * FROM T           |
     WHERE A BETWEEN 1 AND 2  |
       AND C BETWEEN 2 AND 3  |
     ORDER BY A, B, C;        |
                              |
    SELECT * FROM T           |
     WHERE A BETWEEN 1 AND 2  |
       AND B <> 3             |
     ORDER BY A, B, C;        |
   --------------------------------------------------------------------
   ▶PLAN을 살펴보면 SORT ORDER BY가 없음
     위 쿼리들에 대해 정렬작업을 생략할 수 있다는 것이지 옵티마이저가 항상 그런 선택을
     한다는 뜻은 아니다. 통계정보를 기반으로 비용을 계산한 결과 옵티마이져가 Table Full Scan을
     선택하거나 다른 인덱스를 선택한다면 정렬작업은 별도로 수행된다.
    
   ▶CASE A - 3번째 컬럼 기준
   -------------------------------------------------------------------------------------
   | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
   -------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT            |       |    13 |   845 |     3   (0)| 00:00:01 |
   |   1 |  TABLE ACCESS BY INDEX ROWID| T     |    13 |   845 |     3   (0)| 00:00:01 |
   |*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     2   (0)| 00:00:01 |
   -------------------------------------------------------------------------------------
   
   Predicate Information (identified by operation id):
   ---------------------------------------------------
      2 - access("A">=1 AND "A"<=2)
          filter("B"<>3)
  
   ▶CASE B
   -------------------------------------------------------------------------------------
   | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
   -------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT            |       |     5 |   325 |   430   (1)| 00:00:06 |
   |   1 |  TABLE ACCESS BY INDEX ROWID| T     |     5 |   325 |   430   (1)| 00:00:06 |
   |*  2 |   INDEX FULL SCAN           | T_IDX |   456 |       |   428   (1)| 00:00:06 |
   -------------------------------------------------------------------------------------  

   Predicate Information (identified by operation id):
   ---------------------------------------------------
      2 - access("B">=2 AND "B"<=3)
          filter("B">=2 AND "B"<=3)  
    
   ▶아래의 SQL은 인덱스로 소트오퍼레이션 대체 불가한 경우의 예제이다. 즉 ORDER BY에 기술된
     컬럼의 순서가 INDEX 순서와 맞질 않거나, 조건절의 인덱스 컬럼이 ORDER BY절에 생략된 경우이다.

   SELECT * FROM T WHERE A = 1                       ORDER BY C;
   SELECT * FROM T WHERE A = 1 AND B BETWEEN 2 AND 3 ORDER BY C, D;
   SELECT * FROM T WHERE A = 1 AND B BETWEEN 1 AND 2 ORDER BY A, C, B

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

(기타참고)
http://blog.naver.com/bacchusl?Redirect=Log&logNo=50002996263

옵티마이저의 인덱스 선택기준
※ 인덱스 매칭률

   Where절에 첫째 컬럼부터 연속된 컬럼에 대해서 '='조건으로 상수가 사용된 인덱스 컬럼의 개수
   -----------------------------------------------------------------------------------------
   인덱스 컬럼의 총 개수

1. '=' 조건으로 매칭되는 where 조건절의 컬럼의 매칭율(컬럼=상수인 컬럼수/인덱스컬럼의 수)이 높은것
   예) 컬럼 3개로 구성된 결합인덱스의 컬럼이 where절에서 2개가 = 로 사용되더라도
       컬럼 1개로 구성된 인덱스컬럼이 '=' 로 사용되면 66%(2/3)와 100%(1/1) 비교로 인해
       컬럼 1개로 구성된 인덱스가 옵티마이져에의해 선택됨
2. 인덱스 컬럼의 매칭율이 동일할 경우 단일컬럼보다는 여러개의 컬럼으로 구성된 결합인덱스를 더선호하게됨
3. 인덱스 컬럼의 매칭율과 컬럼수도 같다면, 최근에 생성된것을 더 선호함

http://www.dator.co.kr/data24hh/textyle/44413

결합 인덱스 생성시 컬럼 순서

특정 테이블에 인덱스를 생성하는 경우, 특히 여러 개의 컬럼을 결합하여 인덱스를 생성할 때
다음과 같은 방법으로 컬럼을 선정하는 것을 권고합니다.

1. 항상 사용되는 컬럼인가?
   ▶ 당연히 사용되지 않는 컬럼을 인덱스에 포함할 필요는 없겠죠?
2. 항상 '=' 조건으로 사용되는 컬럼인가?
   ▶ 인덱스에서 선두 컬럼의 조건이 범위로 들어오는 경우 해당 컬럼 이후의 모든 컬럼들은
      필터로 처리되기 때문에 처리 범위를 줄여줄 수 없습니다.(★★★)
      따라서 가능하면 '=' 조건으로 사용되는 컬럼을 선두로 하는 것이 유리합니다.

   SELECT * FROM T        
    WHERE A = 2
      AND B = 3
   UNION ALL
   SELECT * FROM T        
    WHERE A BETWEEN 1 AND 2
      AND B = 3;
     
   --------------------------------------------------------------------------------------
   | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
   --------------------------------------------------------------------------------------
   |   0 | SELECT STATEMENT             |       |     4 |   260 |     4  (75)| 00:00:01 |
   |   1 |  UNION-ALL                   |       |       |       |            |          |
   |   2 |   TABLE ACCESS BY INDEX ROWID| T     |     1 |    65 |     1   (0)| 00:00:01 |
   |*  3 |    INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:00:01 |
   |   4 |   TABLE ACCESS BY INDEX ROWID| T     |     3 |   195 |     3   (0)| 00:00:01 |
   |*  5 |    INDEX RANGE SCAN          | T_IDX |     1 |       |     2   (0)| 00:00:01 |
   --------------------------------------------------------------------------------------
   
   Predicate Information (identified by operation id):
   ---------------------------------------------------
      3 - access("A"=2 AND "B"=3)
      5 - access("A">=1 AND "B"=3 AND "A"<=2)
          filter("B"=3)  

3. 액세스(Access) 조건으로 사용될 수 있는 컬럼은 어느 컬럼이며 어떤 컬럼이 처리범위를 줄여줄 수 있는가?
   ▶ 인덱스에서 액세스 범위를 줄여줌
4. 자주 정렬되는 컬럼의 순서는?
   ▶ order by에 대한 부담을 제거할 수 있으며, 부분 범위 처리로 유도할 수 있음
5. 부가적으로 추가될 수 있는 컬럼은 무엇인가?
   ▶ 인덱스에서 필터 조건으로 사용될 수 있거나
   ▶ select list절에서 사용될 수 있는지(Covered Index)
   ▶ 인덱스에서 테이블 랜덤 액세스에 대한 횟수를 줄여줄 수 있음