2-5 INDEX 인덱스 정리

인덱스 적용

===========

용도 : 빠른 조회를 사용하게 됨. 이렇게 될 때, Disk I/O를

개선시키는 수단이 됨.

㈜ 인덱스의 개수가 많아질수록 입력/수정/삭제와 같은 작업은

오히려 퍼포먼스가 나빠진다.

* Balanced Tree(B*Tree) Structure

인덱스에 대한 데이터 저장 방식(algorithm)이라고 할 수 있다.

이로 인해 B*Tree 인덱스는 데이터를 sort를 해서 저장하게 된다.

----

이와 같이 sort를 해놓고 있어야만 빠른 조회가 가능하게 된다.

인덱스 = [인덱스를 구성하는 columns의 값 + ROWID]

인덱스 칼럼들의 값은 오름(또는 내림)차순으로 정렬되어 있음.

인덱스의 ROWID는 다만 테이블에있는 해당 row를 찾기 위해 사용

되는 데이터 저장 주소이다.

인덱스를 생성하고 난 후, 인데스의 구조적인 균형이 무너지게 되는데

이럴 경우에는 인덱스의 구조적인 문제를 해결해야만 한다.

SQL> ALTER INDEX index명 REBUILD

UNRECOVERABLE

NOLOGGING

PARALLEL 10;

㈜ NULL 값을 저장하지 않고 있음.

○ 참고 : 대용량 데이터 로딩할 때

① ALTER INDEX BIG_TAB_IDX UNUSABLE;

② ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE;

③ INSERT /*+ APPEND */ INTO BIG_TAB SELECT * FROM SALES;

④ ALTER INDEX BIG_TAB_IDX REBUILD NOLOGGING;

※ 인덱스의 이러한 구조적 특징(즉, sort)을 이용하면,

-----

① ORDER BY에 대한 대체효과를 얻을 수 있으며

즉, ORDER BY에 의한 소트를 피할 수 있으며

② /*+ INDEX_DESC(table명 index명) */ 또는

/*+ INDEX_ASC (table명 index명) */ 과 ROWNUM = 1 조건의

활용으로 MAX/MIN의 효율화를 얻을 수 있다.

* ROWID의 정보 확인 (확장형) ~ Base-64 인코딩

: A~Z, a~z, 0~9, +, / → 64자

[확장형 ROWID]

① ② ③ ④

- ROWID의 구성 = Object#.File#.Block#.Row#

(6) (3) (6) (3)

(참고) DBMS_ROWID (system package)

① ROWID_OBJECT(ROWID)

② ROWID_RELATIVE_FNO(ROWID)

③ ROWID_BLOCK_NUMBER(ROWID)

④ ROWID_ROW_NUMBER(ROWID)

eg. SELECT DBMS_ROWID.ROWID_OBJECT(ROWID) AS OBJECT_ID,

DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) AS FILE_ID,

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS BLOCK_NO,

DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) AS ROW_NO,

ENAME

FROM EMP

WHERE DEPTNO = 20;

TAB_NO FILE_NO BLOCK_NO ROW_NO ENAME

---------- ---------- ---------- ---------- --------------------

20113 4 233264 0 SMITH

20113 4 233264 3 JONES

20113 4 233264 7 SCOTT

20113 4 233264 10 ADAMS

20113 4 233264 12 FORD

SQL>

eg. SELECT OBJECT_NAME FROM USER_OBJECTS

WHERE OBJECT_ID = 20113;

OBJECT_NAME

-----------------

EMP

eg. SELECT FILE_NAME, TABLESPACE_NAME

FROM DBA_DATA_FILES

WHERE FILE_ID = 4;

FILE_NAME TABLESPACE_NAME

------------------------------------------- ----------------

C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF USERS

★★★★★

Q. 인덱스 스캔을 하면 무조건 빠른가? (그렇지 않다!)

Index Scan vs. Full Table Scan

|-------------------------------------|

(0%) ↑ (100%)

(10~15%)

○ 일반적으로 많게는 10 ~ 15% 까지의 테이블 데이터를 처리할

경우 인덱스를 사용하면 효율적이라고 알려져 있으며, 그 이상

의 테이블 데이터를 처리할 경우에는 Full Table Scan이 더욱

좋은 성능을 보임.

단, 이와 같은 기준은 테이블의 row 수가 많아질수록 그 기준을

더 낮게 설정해야 한다.

그리고 테이블 별로 그 기준을 서로 다르게 생각해야 한다.

특히, 조건에 의한 처리범위가 넓어짐으로 인해 분포도가

나빠지는 경우가 있는데 이러한 경우엔 인덱스 스캔을 하는

것 보다는 FULL TABLE SCAN을 하는 것이 바람직하다.

왜냐하면 FULL TABLE SCAN 시엔 한번 I/O 때 마다 여러 개의

데이터 블럭(db_file_multiblock_read_count에 의해 정의)을

읽어 오기 때문에 I/O 횟수가 줄어들기 때문이다.

Q. Full Table Scan을 더욱 빠르게 하려면 어떻게 해야 하나?

1) DB_FILE_MULTIBLOCK_READ_COUNT의 갯수를 기존보다 더 높게 설정.

SQL> ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT = 16;

2) Parallel Processing을 사용하면 더욱 빨라질 수 있다.

단, 어떠한 경우이든 데이터 처리시 인덱스가 사용되면

안 된다.

* 힌트 : /*+ PARALLEL(tab명, process개수) */

SQL> ALTER SESSION ENABLE PARALLEL DML;

SQL> INSERT /*+ PARALLEL(A, 4) */ INTO EC_APPLY_2 A

SELECT /*+ PARALLEL(B, 4) */ * FROM EC_APPLY B;

특히 주의할 점은 지나치게 많이 사용하게 되는 경우엔 memory와 cpu

사용에 문제를 일으키게 되어 사용하지 않은 것보다 못하게 될 수 있다.

○ 인덱스 선정 절차

① 모든 테이블에 대한 Access path 조사

② 칼럼 선정 및 분포도 조사

1

* 분포도 ≒ --------------------------- × 100(%)

[distinct한 칼럼 값의 종류]

[조건을 만족하는 평균 데이터 건수]

≒ ---------------------------------- × 100(%)

[테이블의 전체 데이터 건수]

* 참고

- 인덱스에 포함해야 할 칼럼

(예) > WHERE절에서 조건으로 자주 사용되는 칼럼

> 테이블 간의 조인을 위해서 연결고리로 자주 사용되는 칼럼

→ Nested Loops 조인을 위해서임)

> 분포도가 좋은 칼럼

> SELECT에서 MIN/MAX를 구하고자 하는 칼럼

> ORDER BY를 위한 기준으로 자주 사용하는 칼럼

- 인덱스에 포함하지 않아야 할 칼럼

(예) > 분포도가 좋지 않은 칼럼

> 수정이 빈번한 칼럼

> 테이블의 전체 block개수가 5개 이하일 때

③ Critical access path 조사

④ 결합인덱스 칼럼 선정 및 순서 결정

⑤ 시험 생성 및 테스트

⑥ 프로그램에 반영

⑦ 실제 적용

○ 인덱스 선정 기준

① 분포도가 좋은 칼럼들

② 자주 조합되어 사용되는 칼럼들

③ 인덱스간 역할 정의를 통한 범용성 확보

④ 수정이 빈번하면 제외함

⑤ Foreign key로 사용된 칼럼들

⑥ Min/Max 처리, Order by기준으로 자주 사용되는 칼럼들

○ 인덱스 추가 또는 변경시 고려할 사항

① 기존 프로그램의 동작에 영향성 검토

② 필요할 때마다 인덱스 생성시 DML에 대한 속도의 저하여부

③ 분포도가 좋지 않은 칼럼에 대해서는 인덱스를 생성치 않음

④ 비록 개별 칼럼의 분포도가 좋지 않을 지라도 다른 칼럼과

결합하여 자주 사용되고, 결합할 경우에 분포도가 양호하다

면 결합인덱스를 긍정적으로 검토

⑤ 분포도가 양호한 칼럼일지라도 'LIKE'나 'BETWEEN'의 조건

으로 자주 사용된다면 조회되는 범위가 넓어짐으로써 퍼포먼스

가 떨어지게 된다.

○ 인덱스 사용이 가능한 SQL문 vs. 인덱스 사용이 불가능한 SQL문

① 인덱스 사용이 가능한 경우 :

인덱스 칼럼에 대해 '=', '<', '>', '<=', '>=', LIKE,

BETWEEN 사용, OR & IN 조건, NOT BETWEEN 검색 등

㈜ LIKE에 대해서 '%LES%' 또는 '__LES%' 와 같은 조건은

인덱스를 사용할 수 없게 함.

② 인덱스 사용이 불가능한 경우

- 인덱스 칼럼에 대해 '<>', '!=', 'NOT LIKE', 'NOT IN',

- IS NULL, IS NOT NULL 사용

- Optimizer의 취사선택 (이로 인한 문제는 힌트로 해결함)

- External/Internal Suppressing

* Suppressing이란

→ 인덱스 칼럼에 변형을 가함으로써 해당 칼럼을 첫 번째로하는

결합 인덱스 및 해당 칼럼만 갖는 단일칼럼 인덱스를 사용하지

못하게 하는 것을 의미한다.

이러한 개념을 활용할 경우, 힌트가 아니어도 사용하기를

원하는 인덱스를 사용할 수 있게 할 수 있다.

모든 DBMS에 대해 사용할 수 있는 방법이다.

* 개발자에 의한 의도적인 External Suppressing(외부적 변형)

은 튜닝 기법으로써 활용될 수 있다.

㈜ 조인순서 제어 방법(4 가지)

① 힌트 (eg. /*+ ORDERED */, /*+ LEADING(tab명) */)

☞ ② Suppressing 활용

Example]

SELECT A.RESNO, A.GRENTDT, B.WEDDT

FROM EMP_MASTER A, EMP_DETAIL B

WHERE A.RESNO = B.RESNO

☞ AND RTRIM(A.GRENTDT) LIKE '1990%'

AND B.WEDDT LIKE '200210%';

SELECT A.RESNO, A.GRENTDT, B.WEDDT

FROM EMP_DETAIL B, EMP_MASTER A

WHERE A.RESNO = B.RESNO

☞ AND RTRIM(A.GRENTDT) LIKE '1990%'

AND B.WEDDT LIKE '200210%';

③ View 활용

④ FROM절의 테이블 순서 변경(단, Rule base인 경우에만)

㈜ 규칙이 동일한 테이블일 경우에 한해서, Rule base

옵티마이저는 FROM절을 기준으로 멀리 있는 테이블로부터

데이터를 먼저 처리하게 된다.

㈜ Internal Suppressing은 서로 다른 자료형을 비교할 때

데이터베이스 내부에서 자동적으로 발생하는 서프레싱을

말한다.

결합 인덱스

===========

○ Index merge : 서로 다른 인덱스 간의 결합

이로 인한 문제를 해결할 수 있는 차원에서 결합인덱스

(composite index) 생성을 고려함.

○ 결합인덱스 사용 법칙

결합인덱스의 첫 번째 칼럼이 WHERE절에서 누락되거나, 첫 번째

칼럼에 대해서 인덱스를 사용할 수 없게 하는 조건을 사용한다면

해당 결합인덱스는 사용될 수 없다.

㈜ Skip Scanning (v9i 이상, Cost Base일 경우)

결합인덱스의 첫 번째 칼럼을 제외하고 두 번째 칼럼부터

WHERE절에 조건으로 기술하고 있다면, 이 때도 인덱스를 사용

할 수도 있는데, 이를 Skip Scanning 이라고 함.

① INDEX_SS(tab명 idx명)

② INDEX_SS_ASC(tab명 idx명)

③ INDEX_SS_DESC(tab명 idx명)

○ 결합인덱스의 칼럼에 대한 '='의 해석 : 범위제한 조건 vs. 체크 조건

→ '='에 대해서 효율성이 있을 때와 효율성이 없을 때를 구분

하는데 효율성이 있을 때 "범위제한 조건"이라 하며 효율성이 없을

때 "체크조건"이라 부른다.

1st 2nd 3rd

(예) 결합인덱스 : ( 시 + 구 + 동 )

WHERE 시 = '서울시'; ☜ 범위제한 조건

WHERE 시 = '서울시' ☜ 범위제한 조건

AND 구 = '강남구'; ☜ 범위제한 조건

WHERE 시 = '서울시' ☜ 범위제한 조건

AND 구 = '강남구' ☜ 범위제한 조건

AND 동 = '역삼동'; ☜ 범위제한 조건

WHERE 시 LIKE '서%' ☜

AND 구 = '강남구' ☜ 체크 조건

AND 동 = '역삼동'; ☜ 체크 조건

WHERE 시 = '서울시' ☜ 범위제한 조건

AND 구 LIKE '강%' ☜

AND 동 = '역삼동'; ☜ 체크 조건

WHERE 시 = '서울시' ☜ 범위제한 조건

AND 동 = '역삼동'; ☜ 체크 조건

1st 2nd 3rd

(예) 결합인덱스 : ( 급여년월 + 급여코드 + 사원번호 )

① WHERE 급여년월 LIKE '2002%'

AND 급여코드 = '정기급여';

② WHERE 급여년월 BETWEEN '200201' AND '200212'

AND 급여코드 = '정기급여'; ☜

③ WHERE 급여년월 >= '200201' AND 급여년월 <= '200212'

AND 급여코드 = '정기급여'; ☜

★★★★★

튜닝포인트 : 체크 조건 → 범위제한 조건

* 튜닝 전

WHERE 급여년월 LIKE '2002%'

AND 급여코드 = '정기급여'; ☜ 체크조건

* 튜닝 후

WHERE 급여년월 IN ('200212','200211','200210',

'200209','200208','200207',

'200206','200205','200204',

'200203','200202','200201')

AND 급여코드 = '정기급여';

㈜ IN으로 조건을 표현할 때, 직접 값을 나열하는 경우엔 1,000개로

제한되어 있다. 이러한 문제를 해결 위해서는 날짜정보를 갖고

있는 테이블이 있다고 할 때, 조인 또는 서브쿼리를 통해서 해

결하면 된다.