대용량 데이터베이스솔루션 1 (2009년)
인덱스의 선정 0 0 66,630

by 구루비스터디 인덱스 선정 인덱스 머지 결합 인덱스 인덱스의 활용 [2009.04.29]


인덱스의 선정

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

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

  • 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. 항상 = 로 사용되는가?
  3. 분포도 좋은 컬럼 우선
    • 분명히 1), 2) 보다 우선 순위가 떨어짐
  4. 자주 사용되는 정렬의 순서 (부분범위처리 사용가능)
  • 반복적용
  • 추가컬럼
    • 랜덤 엑세스 줄이기
    • 인덱스 엑세스로 끝내기

(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

다. 인덱스 선정 절차

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

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

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

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

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 의 내용을 참고 했습니다.

"구루비 데이터베이스 스터디모임" 에서 2009년에 "대용량 데이터베이스 솔루션 1" 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/2465

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입