인덱스의 선정

인덱스의 추가는 관련 컬럼을 사용하는 모든 경우에 영향을 미칠 수 있으므로 신중 해야 한다.
실측자료를 토대로 액세스의 빈도, 처리범위의 크기, 분포도, 테이블의 크기, 액세스 유형 등을 감안하여 종합적이고 전략적으로 결정.

가. 인덱스 대상 테이블의 선정

MULTI BLOCK I/O 때문에 작은(5~6 Block 이하)테이블은 인덱스가 없어도 무방
하지만 연결고리 컬럼, 참조무결정 컬럼, LOOP 내 사용하는 컬럼에서는 필요
많은 인덱스는 DML 시 부하의 원인 이므로 인덱스의 개수, 생성 시점은 중요(SQL*Loader)
랜덤 액세스가 빈번하거나, 범위의 데이터나, 특정 순서로 스캔이 요구되는 경우, 다른 테이블과 Nested Join 이 발생하는 경우 필요
인덱스 대신 클러스터링 이나 해쉬 사용

나. 인덱스 컬럼의 선정

(1) 분포도와 손익분기점

컬럼의 분포도가 10~15% 를 넘지 않아야 한다.
그 이상인 경우 TABLE FULL SCAN 이 나음 (인덱스의 ROWID 를 통한 RANDOM ACCESS 비용)

!p32.jpg! (32 페이지 그림)


SQL> SELECT SUM(EMPNO) FROM OC_EMP_1000 WHERE DEPT < 100;

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |     1 |    26 |    49   (7)| 00:00:01 |
|   1 |  SORT AGGREGATE    |             |     1 |    26 |            |          |
|*  2 |   TABLE ACCESS FULL| OC_EMP_1000 |  7975 |   202K|    49   (7)| 00:00:01 |
----------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
       1074  recursive calls
          0  db block gets
        428  consistent gets
        217  physical reads
          0  redo size
        430  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         19  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SELECT /*+ INDEX(OC_EMP_1000 OC_EMP_1000_S1) */ SUM(EMPNO) FROM OC_EMP_1000 WHERE DEPT < 100;

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    26 |  7561   (1)| 00:01:31 |
|   1 |  SORT AGGREGATE              |                |     1 |    26 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| OC_EMP_1000    |  7975 |   202K|  7561   (1)| 00:01:31 |
|*  3 |    INDEX RANGE SCAN          | OC_EMP_1000_S1 |  7975 |       |    23   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
       1074  recursive calls
          0  db block gets
       7840  consistent gets
        235  physical reads
          0  redo size
        430  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         19  sorts (memory)
          0  sorts (disk)
          1  rows processed

어느정도 분포도에 옵티마이저가 반응할까? (on)

10g(10.2.0.4)


-- 분포도 약 10% 테스트 TABLE ACCESS FULL
DROP TABLE OC_EMP_10;

CREATE TABLE OC_EMP_10 AS
SELECT LEVEL AS EMPNO,
       ROUND(DBMS_RANDOM.VALUE(1, 10)) AS DEPT
  FROM DUAL CONNECT BY LEVEL < 100001;

CREATE INDEX OC_EMP_10_S1 ON OC_EMP_10 (DEPT);

SELECT SUM(EMPNO), COUNT(*) FROM OC_EMP_10 WHERE DEPT = 1;

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    26 |    46   (7)| 00:00:01 |
|   1 |  SORT AGGREGATE    |           |     1 |    26 |            |          |
|*  2 |   TABLE ACCESS FULL| OC_EMP_10 |  5657 |   143K|    46   (7)| 00:00:01 |
--------------------------------------------------------------------------------


-- 분포도 약 1% 테스트 TABLE ACCESS FULL
DROP TABLE OC_EMP_100;

CREATE TABLE OC_EMP_100 AS
SELECT LEVEL AS EMPNO,
       ROUND(DBMS_RANDOM.VALUE(1, 100)) AS DEPT
  FROM DUAL CONNECT BY LEVEL < 100001;

CREATE INDEX OC_EMP_100_S1 ON OC_EMP_100 (DEPT);

SELECT SUM(EMPNO), COUNT(*) FROM OC_EMP_100 WHERE DEPT = 1;

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     1 |    26 |    46   (7)| 00:00:01 |
|   1 |  SORT AGGREGATE    |            |     1 |    26 |            |          |
|*  2 |   TABLE ACCESS FULL| OC_EMP_100 |   495 | 12870 |    46   (7)| 00:00:01 |
---------------------------------------------------------------------------------


-- 분포도 약 0.1% 테스트 INDEX RANGE SCAN
DROP TABLE OC_EMP_1000;

CREATE TABLE OC_EMP_1000 AS
SELECT LEVEL AS EMPNO,
       ROUND(DBMS_RANDOM.VALUE(1, 1000)) AS DEPT
  FROM DUAL CONNECT BY LEVEL < 100001;

CREATE INDEX OC_EMP_1000_S1 ON OC_EMP_1000 (DEPT);

SELECT SUM(EMPNO), COUNT(*) FROM OC_EMP_1000 WHERE DEPT = 1;

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     1 |    26 |    33   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |                |     1 |    26 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| OC_EMP_1000    |    51 |  1326 |    33   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | OC_EMP_1000_S1 |    51 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

9i(9.2.0.1)


-- 분포도 약 10% 테스트 (TABLE ACCESS (FULL))
DROP TABLE OC_EMP_10;

CREATE TABLE OC_EMP_10 AS
SELECT LEVEL AS EMPNO,
       ROUND(DBMS_RANDOM.VALUE(1, 10)) AS DEPT
  FROM DUAL CONNECT BY LEVEL <= 100001;

CREATE INDEX OC_EMP_10_S1 ON OC_EMP_10 (DEPT);

ANALYZE TABLE OC_EMP_10 COMPUTE STATISTICS;

SELECT SUM(EMPNO), COUNT(*) FROM OC_EMP_10 WHERE DEPT = 1;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=60 Card=1 Bytes=6)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'OC_EMP_10' (Cost=60 Card=10000 Bytes=60000)


-- 분포도 약 1% 테스트 (TABLE ACCESS (FULL))
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=60 Card=1 Bytes=6)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'OC_EMP_100' (Cost=60 Card=1000 Bytes=6000)


-- 분포도 약 0.1% 테스트 (TABLE ACCESS (FULL))
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=64 Card=1 Bytes=7)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'OC_EMP_1000' (Cost=64 Card=100 Bytes=700)

-- 분포도 약 0.05% 테스트 INDEX (RANGE SCAN)
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=50 Card=1 Bytes=7)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'OC_EMP_2000' (Cost=50 Card=50 Bytes=350)
   3    2       INDEX (RANGE SCAN) OF 'OC_EMP_2000_S1' (NON-UNIQUE) (Cost=3 Card=50)

8i(8.1.6.0)


-- 분포도 약 1% 테스트 (TABLE ACCESS (FULL))
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=1 Bytes=6)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'OC_EMP_100' (Cost=28 Card=1001 Bytes=6006)


-- 분포도 약 0.1% 테스트 (TABLE ACCESS (FULL))
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=29 Card=1 Bytes=7)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'OC_EMP_1000' (Cost=29 Card=101 Bytes=707)


-- 분포도 약 0.05% 테스트 (TABLE ACCESS (FULL))
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=30 Card=1 Bytes=7)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'OC_EMP_2000' (Cost=30 Card=51 Bytes=357)


-- 분포도 약 0.02% 테스트 INDEX (RANGE SCAN)
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=20 Card=1 Bytes=7)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'OC_EMP_5000' (Cost=20 Card=21 Bytes=147)
   3    2       INDEX (RANGE SCAN) OF 'OC_EMP_5000_S1' (NON-UNIQUE) (Cost=1 Card=21)

(2) 인덱스 머지(Merge)

!p34.jpg! (34 페이지 그림)


DROP TABLE OC_EMP_MERGE;

CREATE TABLE OC_EMP_MERGE AS
SELECT LEVEL AS EMPNO,
       ROUND(DBMS_RANDOM.VALUE(1, 1000)) AS DEPT1,
       ROUND(DBMS_RANDOM.VALUE(1, 1000)) AS DEPT2
  FROM DUAL CONNECT BY LEVEL <= 100001;

CREATE INDEX OC_EMP_MERGE_S1 ON OC_EMP_MERGE (DEPT1);
CREATE INDEX OC_EMP_MERGE_S2 ON OC_EMP_MERGE (DEPT2);


SELECT AVG(EMPNO)
  FROM OC_EMP_MERGE
 WHERE DEPT1 = 62
   AND DEPT2 = 231;

-----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                 |     1 |    39 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                   |                 |     1 |    39 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID     | OC_EMP_MERGE    |    13 |   507 |     5   (0)| 00:00:01 |
|   3 |    BITMAP CONVERSION TO ROWIDS    |                 |       |       |            |          |
|   4 |     BITMAP AND                    |                 |       |       |            |          |
|   5 |      BITMAP CONVERSION FROM ROWIDS|                 |       |       |            |          |
|*  6 |       INDEX RANGE SCAN            | OC_EMP_MERGE_S1 |       |       |     1   (0)| 00:00:01 |
|   7 |      BITMAP CONVERSION FROM ROWIDS|                 |       |       |            |          |
|*  8 |       INDEX RANGE SCAN            | OC_EMP_MERGE_S2 |       |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
       1136  recursive calls
          0  db block gets
        278  consistent gets
        232  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         19  sorts (memory)
          0  sorts (disk)
          1  rows processed


SELECT /*+ INDEX(OC_EMP_MERGE OC_EMP_MERGE_S1) */ AVG(EMPNO)
  FROM OC_EMP_MERGE
 WHERE DEPT1 = 62
   AND DEPT2 = 231;

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |     1 |    39 |   129   (0)| 00:00:02 |
|   1 |  SORT AGGREGATE              |                 |     1 |    39 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| OC_EMP_MERGE    |    13 |   507 |   129   (0)| 00:00:02 |
|*  3 |    INDEX RANGE SCAN          | OC_EMP_MERGE_S1 |   206 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
        738  recursive calls
          0  db block gets
        318  consistent gets
        242  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         15  sorts (memory)
          0  sorts (disk)
          1  rows processed


SELECT /*+ AND_EQUAL(OC_EMP_MERGE OC_EMP_MERGE_S1 OC_EMP_MERGE_S2) */ AVG(EMPNO)
  FROM OC_EMP_MERGE
 WHERE DEPT1 = 62
   AND DEPT2 = 231;

------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |     1 |    39 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |                 |     1 |    39 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| OC_EMP_MERGE    |    13 |   507 |     2   (0)| 00:00:01 |
|   3 |    AND-EQUAL                 |                 |       |       |            |          |
|*  4 |     INDEX RANGE SCAN         | OC_EMP_MERGE_S2 |   181 |       |     1   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN         | OC_EMP_MERGE_S1 |   206 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
       1136  recursive calls
          0  db block gets
        540  consistent gets
        232  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         19  sorts (memory)
          0  sorts (disk)
          1  rows processed

(3) 결합 인덱스의 특징 (r) 컬럼 순서가 중요(r)

첫번째 컬럼 : 사용빈도 > 분포도 ==> 아예 인덱스를 못쓰는 경우가 없도록
사용되는 모든 조건이 = 일경우 분포도 차이가 있는 컬럼의 순서는 관계 없음
어떤 순서의 컬럼이 가장 많은 = 조건이 먼저 사용될지 고민 (BETWEEN, LIKE, <, > .. 점과 선의 문제)
- 분포도 보다 = 조건이 많이 사용 되는것을 고려
- 어쩔 수 없는경우
  - COL2 + COL1 인덱스
    X) WHERE COL2 BETWEEN '111' AND '112' AND COL1 = 'A'
    O) WHERE COL2 IN ('111', '112') AND COL1 = 'A'
    O) WHERE (COL2 = '111' AND COL1 = 'A') OR (COL2 = '112' AND COL1 = 'A')
  - COL1 + COL2 + COL3 인덱스
    X) WHERE COL1 = 'A' AND COL3 = '10'
    O) WHERE COL1 = 'A' AND COL2 IN ('1', '2', '3') AND COL3 = '10'
  - 후보선수
    > COL1 조건 ) 랜덤엑세스 n회(n: 인덱스에서 COL1 비교 성공한 횟수)
    > COL1, COL3 조건 ) 랜덤엑세스 m회 (m: 인덱스 내에서 COL3 까지 비교 성공한 횟수, n보다 작음)
B*TREE 구조의 인덱스는 전체의 로우수 보다는 이분화해 가는 깊이에 더 많은 영향을 받게 된다.
- 분포도가 좋은컬럼 먼저 비교가 좋음
5개 컬럼까지만 이분화 수행, 그 이상의 컬럼은 INDEX ROW SCAN 통해서 비교
- 상위 5개 컬럼의 순서가 중요
  - 지나친 컬럼 분할은 저성능을 불러옴 예) YYYY|MM|DD, LCAT|MCAT|SCAT,
PK LOOP RANDOM ACCESS - CRITICAL ACCESS PATH

(4) 결합인덱스의 컬럼순서 결정 방법

항상 사용되는가?
항상 = 로 사용되는가?
분포도 좋은 컬럼 우선
- 분명히 1), 2) 보다 우선 순위가 떨어짐
자주 사용되는 정렬의 순서 (부분범위처리 사용가능)

반복적용
추가컬럼
- 랜덤 엑세스 줄이기
- 인덱스 엑세스로 끝내기

(5) 손익분기점 이하의 분포도를 가진 컬럼의 인덱스 생성

10 ~ 15 % 가 손익분기점 이지만, H/W 조건이나 사용조건을 고려 해서 손익분기점 기준을 만듦
- 부분범위목적의 경우 예외 (전체 범위시에는 강제로 인덱스를 못쓰도록)
- 훌륭한 분포도에만 단일컬럼 인덱스

(6) 손익분기점 이상의 분포도를 가진 컬럼의 인덱스 생성


- EMP.DEPT 는 20% 의 분포도로 가정
- 전체범위
SELECT DEPT, SUM(SAL)
  FROM EMP
 WHERE JOB = 'SALESMAN'
 GROUP BY DEPT;

- 전체범위 (JOB_INDEX 없음)
SELECT DEPT, ENAME, SAL
  FROM EMP
 WHERE JOB = 'SALESMAN'
 ORDER BY JOB DESC;

- 부분범위처리 (수행속도향상)
SELECT /*+ INDEX_DESC(A JOB_INDEX) */ DEPT, ENAME, SAL
  FROM EMP A
 WHERE JOB = 'SALESMAN';


DROP TABLE OC_EMP_P51;

CREATE TABLE OC_EMP_P51 AS
SELECT LEVEL AS EMPNO,
       ROUND(DBMS_RANDOM.VALUE(1, 5)) AS DEPT
  FROM DUAL CONNECT BY LEVEL <= 10000;

SELECT EMPNO, DEPT
  FROM OC_EMP_P51
 WHERE DEPT = 1
 ORDER BY DEPT DESC;

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |  1253 | 32578 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| OC_EMP_P51 |  1253 | 32578 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
        999  recursive calls
          0  db block gets
        282  consistent gets
         39  physical reads
          0  redo size
      22298  bytes sent via SQL*Net to client
       1313  bytes received via SQL*Net from client
         85  SQL*Net roundtrips to/from client
         18  sorts (memory)
          0  sorts (disk)
       1253  rows processed

CREATE INDEX OC_EMP_P51_S1 ON OC_EMP_P51 (DEPT);

SELECT /*+ INDEX_DESC(OC_EMP_P51 OC_EMP_P51_S1) */
       EMPNO, DEPT
   FROM OC_EMP_P51
  WHERE DEPT = 1;

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |  1253 | 32578 |    15   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | OC_EMP_P51    |  1253 | 32578 |    15   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN DESCENDING| OC_EMP_P51_S1 |  1253 |       |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
       1054  recursive calls
          0  db block gets
        384  consistent gets
         45  physical reads
          0  redo size
      22298  bytes sent via SQL*Net to client
       1313  bytes received via SQL*Net from client
         85  SQL*Net roundtrips to/from client
         19  sorts (memory)
          0  sorts (disk)
       1253  rows processed

다. 인덱스 선정 절차

해당 테이블의 가능한 모든 액세스 형태의 수집
대상 컬럼 선정 및 분포도 조사
반복 수행되는 액세스 경로(Critical Access Path)의 해결
클러스터링 검토
인덱스 컬럼의 조합 및 순서의 결정
시험 생성 및 테스트
수정이 필요한 애플리케이션 조사 및 수정
일괄 적용

(1) 설계단계의 인덱스 선정

1) 해당 테이블의 액세스 형태 수집

반복 수행되는 액세스 형태를 찾는다.
- PK, FK, SubQuery, Loop
- SALENO, ITEM, CUSTNO, SALEDEPT 등
분포도가 아주 양호한 컬럼들을 발췌하여 액세스 유형을 조사한다.
- CUSTNO, SALEDATE, STATUS(단 특정값 외)
자주 넓은 범위의 조건이 부여되는 경우를 찾는다.
- 범위별최대크기, 정렬의순서, 처리유형(ORDER BY, GROUP BY)
- SALEDATE, SALEDEPT
조건에 자주 사용되는 주요 컬럼들을 추출하여 액세스 유형을 조사한다.
- 업무적인 시각에서 파악, 과거 시스템 참조
- SALEDATE, SALEDEPT, CUSTNO, STATUS, SALETYPE, ITEM, AGENTNO
자주 결합되어 사용되는 컬럼들의 조합형태 및 정렬순서를 조사한다.
- 중심이 되는 컬럼을 먼저 선정 : 매출일자
- 중심이 되는 컬럼의 분포도가 충분히 좋으면 단일컬럼 그렇지 않으면 효율적인 결합인덱스들 선정
- 매출일자 + 부서코드, 거래선코드, 매출형태
- 혼자 사용되는 컬럼 선정(분포도를 확인 해서 결합 인덱스 고려)
- 결합도 : 매출일자 + 부서코드 > 매출일자 + 거래선코드
- 결합조건 : "매출일자 =, 부서코드 =" , "매출일자 LIKE, 부서코드 =" 구분
- 정렬순서 : "매출일자 LIKE, 부서코드 =, ORDER BY 부서코드"
역순으로 정렬하여 추출되는 경우를 찾는다.
- 최종 사용자는 /넓은범위로/최근부터/빠르게/ 보기를 원함
- INDEX_DESC 부분범위처리
일련번호를 부여하는 경우를 찾는다.
- 테이블 전체 ROW 대상 UNIQUE 일련번호 : SEQUENCE
- 조건에 따른 컬럼별 일련번호 : INDEX_DESC + ROWNUM = 1 (부분범위처리)
통계자료 추출을 위한 액세스 유형을 조사한다.
- 클러스터링, 잘조합된 결합인덱스(?)

2) 대상 컬럼의 선정 및 분포도의 조사

분포도 조사 대상 컬럼
- 액세스 유형에 자주 등장하는 컬럼
- 인덱스의 첫번째 컬럼으로 지정해야 할 컬럼
- 수행속도에 영향을 미칠 것으로 예상되는 컬럼
결합인덱스의 경우 결합된 상태의 분포도도 조사
SALEDATE LIKE 라면...
- SALEDATE 의 분포도 조사
- SALEDATE LIKE 조회시 자주 사용되는 조건(일?, 주?, 월?)의 분포도 조사

3) 반복 수행되는 액세스 경로의 해결

Critical Access Path 는 수행속도 * 반복횟수 = 총수행속도
- 0.02초 \-> 0.01초 (1/ 2) * 10만번 1000초 절약
- 10초 \-> 1초 (1/10) * 10번 90초 절약
SALENO + ITEM 인덱스 생성 후 ITEM 조건만을 위해서는 ITEM 단일 컬럼 인덱스 X, 다른 컬럼과 결합인덱스 O (오동작)

4) 클러스터링 검토

인덱스의 최대 단점 : 인덱스를 경유한 넓은 범위의 데이터 조회시 랜덤 액세스로 인한 성능저하
클러스터링할 컬럼은 분포도가 넓은 것이 유리
테이블당 하나만 가능
Query 성능 향상, DML 성능 저하

5) 인덱스 컬럼의 조합 및 순서의 결정

인덱스는 DML 부하를 불러 오므로 테이블에 목적에 따라서 결정

6) 시험생성 및 테스트

7) 수정이 필요한 애플리케이션 조사 및 수정

8) 일괄적용

어플리케이션 수정과 인덱스 적용은 한방에

(2) 튜닝단계의 인덱스 선정

(on) 선 인덱스 후 어플리케이션

1) 해당 테이블의 액세스 형태 수집

어플리케이션 소스코드 에서 SQL 추출 후 ACCESS PATH 선정

2) 대상 컬럼의 선정 및 분포도의 조사

주요컬럼 : 액세스 유형에 자주 등장, 인덱스의 첫번째 컬럼으로 지정해야 할, 수행속도에 영향을 미칠 것으로 예상되는 컬럼
종류, 평균, 최대, 최소, 예외(특별히 넓은 분포의) Rows

3) 반복 수행되는 액세스 경로의 해결

4) 클러스터링 검토

5) 인덱스 컬럼의 조합 및 결정

누적 데이터로 분포도 검토

6) 시험생성 및 테스트

이 자료는 (주)엔코아정보컨설팅 에서 발행된 대용량 데이터베이스 솔루션 I 의 내용을 참고 했습니다.

가. 인덱스 대상 테이블의 선정

나. 인덱스 컬럼의 선정

(1) 분포도와 손익분기점

(2) 인덱스 머지(Merge)

(3) 결합 인덱스의 특징 (*r) 컬럼 순서가 중요(*r)

(4) 결합인덱스의 컬럼순서 결정 방법

(5) 손익분기점 이하의 분포도를 가진 컬럼의 인덱스 생성

(6) 손익분기점 이상의 분포도를 가진 컬럼의 인덱스 생성

다. 인덱스 선정 절차

(1) 설계단계의 인덱스 선정

1) 해당 테이블의 액세스 형태 수집

2) 대상 컬럼의 선정 및 분포도의 조사

3) 반복 수행되는 액세스 경로의 해결

4) 클러스터링 검토

5) 인덱스 컬럼의 조합 및 순서의 결정

6) 시험생성 및 테스트

7) 수정이 필요한 애플리케이션 조사 및 수정

8) 일괄적용

(2) 튜닝단계의 인덱스 선정

1) 해당 테이블의 액세스 형태 수집

2) 대상 컬럼의 선정 및 분포도의 조사

3) 반복 수행되는 액세스 경로의 해결

4) 클러스터링 검토

5) 인덱스 컬럼의 조합 및 결정

6) 시험생성 및 테스트

(3) 결합 인덱스의 특징 (r) 컬럼 순서가 중요(r)