Oracle SQL INDEX(기초)

1. IT Story/DB 2012. 2. 29. 21:44

인덱스(Index)

인덱스의 정의
- 인덱스는 테이블이나 클러스트에서 쓰여지는 선택적인 객체로서, 오라클 데이터베이스 테이블내의 원하는 레코드를 빠르게 찾아갈 수 있도록
  
  만들어진 데이터 구조이다.

- 자동 인덱스 : 프라이머리 키 또는 UINQUE 제한 규칙에 의해 자동적으로 생성되는 인덱스.

- 수동 인덱스 : CREATE INDEX 명령을 실행해서 만드는 인덱스

원본 위치 <http://www.gurubee.net/pages/viewpage.action?pageId=983179>

인덱스 사용 목적

테이블의 검색 속도를 향상시키는 선택적 구조

화면 캡처: 2011-12-26 오전 11:28
- 인덱스의 장점
  
  성능 향상 효과
  
  - 일반적으로 테이블의 일부분만 활용하는 쿼리가 대부분이기 대문에 FTS 에 비해 물리적인 디스크 I/O가 적음
  
  - INDEX를 이용하여 별도의 정렬 없이 인덱스 순서대로 결과 추출 가능함
- 인덱스의 단점
  
  성능 저하
  
  - 부정형의 질의문에는 사용되지 않음
  
  - 비트맵 인덱스에 비하면 저장 공간을 많이 차지함
  
  - 빈번한 DML 이 일어나면 트리의 밸런스가 부분적으로 저하되는 단점
인덱스의 종류

화면 캡처: 2011-12-26 오전 10:33

인덱스를 생성하는 것이 좋은 컬럼

① WHERE절이나 join조건 안에서 자주 사용되는 컬럼

② null 값이 많이 포함되어 있는 컬럼

③ WHERE절이나 join조건에서 자주 사용되는 두 개 이상의 컬럼들

다음과 같은 경우에는 인덱스 생성이 불필요 하다.

① 테이블이 작을 때

② 테이블이 자주 갱신될 때
B-tree(binary search tree)
오라클 인덱스는 B-tree(binary search tree)에 대한 원리를 기반으로 하고 있다. B-tree 인덱스는 컬럼안에 독특한 데이터가 많을 때 가장 좋은 효과를 낸다.

CREATE INDEX (index_name) ON (table)(컬럼) +@(tablespace~)

화면 캡처: 2011-12-26 오전 11:24

이 알고리즘 원리는

① 주어진 값을 리스트의 중간점에 있는 값과 비교한다.만약 그 값이 더 크면 리스트의 아래쪽 반을 버린다. 만약 그 값이 더 작다면 위쪽 반을 버린다.

② 하나의 값이 발견될 때 까지 또는 리스트가 끝날 때까지 그와 같은 작업을 다른 반쪽에도 반복한다.

원본 위치 <http://www.gurubee.net/pages/viewpage.action?pageId=12517389>

비트맵 인덱스

화면 캡처: 2011-12-26 오전 10:23

원본 위치 <http://www.gurubee.net/pages/viewpage.action?pageId=3902465>

비트맵 인덱스는 각 컬럼에 대해 적은 개수의 독특한 값이 있을 경우에 사용하는 것이 좋다.(ex 남,여의 값을 가지는 성별 컬럼)
비트맵 인덱스는 B-tree 인덱스가 사용되지 않을 경우에서 성능을 향상 시킨다.
테이블이 매우 크거나 수정/변경이 잘 일어나지 않는 경우에 사용할 수 있다.

비트맵 인덱스는 lock에 의한 DML 부하가 심한 것이 단점이다. 레코드 하나만 변경되더라도 해당 비트맵 범위에 속한

모든 레코드에 lock이 걸린다. OLTP성 환경에 비트맵 인덱스를 쓸 수 없는 이유가 여기에 있다.

원본 위치 <http://www.gurubee.net/pages/viewpage.action?pageId=3902465>

SQL> CREATE BITMAP INDEX bitmap_index ON bitmap_index_test(test_id);

Bitmap_index_test 테이블에 test_id 컬럼을 이용하여 bitmap_index를 생성.

화면 캡처: 2011-12-26 오전 10:58

UNIQUE 인덱스
UNIQUE 인덱스는 인덱스를 사용한 컬럼의 중복값들을 포함하지 않고 사용할 수 있는 장점이 있다.
프라이머리키 와 UNIQUE 제약 조건시 생성되는 인덱스는 UNIQUE 인덱스이다.

SQL> CREATE UNIQUE INDEX unique_index ON unique_index_test(test_id);

unique_index_test 테이블에 test_id 컬럼을 이용하여 unique_index를 생성.

화면 캡처: 2011-12-26 오전 11:08

NON-UNIQUE 인덱스
NON-UNIQUE 인덱스는 인덱스를 사용한 컬럼에 중복 데이터 값을 가질 수 있다.

SQL> CREATE UNIQUE INDEX non_unique_index ON non_unique_index_test(test_id);

Non_unique_index_test 테이블에 test_id 컬럼을 이용하여 non_unique_index를 생성.

화면 캡처: 2011-12-26 오전 11:12

결합 인덱스
복수개의 컬럼에 생성할 수 있으며 복수키 인덱스가 가질 수 있는 최대 컬럼값은 16개이다

SQL> CREATE UNIQUE INDEX composte_index ON composite_index_test(test_id, name);

composite_index_test 테이블에 test_id, name 컬럼을 이용하여(결합) composite를 생성.

화면 캡처: 2011-12-26 오전 11:16

인덱스의 삭제
- 인덱스의 구조는 테이블과 독립적이므로 인덱스의 삭제는 테이블의 데이터에는 아무런 영향도 미치지 않는다.
- 인덱스를 삭제하려면 인덱스의 소유자 이거나 DROP ANY INDEX권한을 가지고 있어야 한다.
- 인덱스는 ALTER를 할 수 없다.

SQL> DROP INDEX bitmap_index;

화면 캡처: 2011-12-26 오전 11:40

인덱스 데이터사전
- 인덱스에 대한 정보는 USER_INDEXES 뷰 또는 USER_IND_COLUMNS뷰를 통해 검색할 수 있다.

SQL> SELECT index_name, index_type
FROM USER_INDEXES
WHERE table_name='COMPOSITE_INDEX_TEST';

INDEX_NAME

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

COMPOSITE_INDEX

INDEX_TYPE
-----------
NORMAL

화면 캡처: 2011-12-26 오전 11:36

옵티마이저의 인덱스 선택 기준
- = 조건으로 매칭되는 WHERE 절에 컬럼의 매칭률이 높은 것
- 인덱스 컬럼의 매칭률이 같은 경우 단일컬럼 인덱스 보다 결합인덱스를 사용
- 인덱스 컬럼의 매칭률과 인덱스의 종류(결합인덱스나 단일 컬럼인덱스)가 같을 경우 최근 생성된 인덱스를 사용
인덱스 UNIQUE SCAN 과 RANGE SCAN
- UNIQUE 인덱스의 구성 컬럼 모두에 대한 WHERE 조건절에서 'EQUAL(=) 상수' 를 받을 때 UNIQUE SCAN을 함
- UNIQUE 인덱스의 구성 컬럼 무도와 'EQUAL(=) 상수' 를 만족하지 못하면 RANGE SCAN 함
- NON UNIQUE 인덱스는 RANGE SCAN을 사용함
INDEX 와 CLUSTER 의 사용 비교
Clustered 여부에 따른 분류

Clustered Index	넓은 분포도를 가지고 있어 Index 활용이 어려운 경우 생성
Non-Clustered Index	일반적인 Index

Clustered란 ?

1. 지정된 컬럼 값의 순서대로 로우를 저장시키는 방법

2. 엑세스 기법이 아니라 엑세스 효율향상을 위한 물리적 저장기법

3. INDEX 와 CLUSTER 의 사용 비교

Index 사용 시

일반적인 인덱스를 사용하는 경우 인덱스를 구성하는 컬럼들의 모든 값들이 인덱스에 존재한다.

인덱스를 Search한 후 rowid로 table를 acess하고 또다시 다음 인덱스 로우에 대하여 테이블 로우를 rowid 로 access한다.

Clustered 사용 시

인덱스를 구성한 컬럼의 한개의 값만 CLUSTER INDEX에 존재하며 CLUSTER INDEX 의 rowid로 table 을 access 한 후 계속적으로 테이블을

scan한는 방식

사용시 유리한 경우

☞ 다량범위 데이터에대한 검색을 자주하는 경우

☞ 넓은 분포도를 가지고 있어 INDEX 활용이 어려운 경우

☞ 수정이 자주 발생하는 않는 컬럼

☞ 여러개의 테이블이 자주 JOIN 하는 경우

☞ ORDER BY, GROUP BY, UNION, DISTINCT 가 자주 발생하는 컬럼
사용시 주의점

☞ INSERT, UPDATE, DELETE 처리시 OVERHEAD 발생

☞ CLUSTER KEY 의 최대 컬럼수는 16

☞ CLUSTER KEY 의 수정은 자주 발생하는 않아야 함

☞ INDEX 사용이 가능한 범위의 데이터는 효과가 크지 않음

☞ CLUSTER를 생성하면 기존의 INDEX는 재구성

☞ CLUSTER KEY 별 ROW 수의 편차가 심하지 않을 것

☞ CLUSTER 된 테이블 JOIN 시 ROW 수의 연순으로 FROM 절에 기술 할 것

☞ INDEX 와 CLUSTER 의 ACCESS 역할 분담 명확화

인덱스 사용시 고려사항
인덱스의 적용대상

1. 중.대 구모 테이블(6블럭 이상)

2. 대상 데이터의 10% 이내의 데이터 요구인 경우

3. 스캔의 범위를 줄이고자 할때

4. 정렬을 필요로 하는 경우

5. 테이블에 접근하지 앟고 인덱스만으로 데이터 요구를 해결하고자 하는 경우
인덱스 적용시 고려사항

1. 인덱스를 위한 추가적 기억공간 필요

2. 인덱스와 테이블 I/O 수의 합계가 전체 테이블을 스캔하는 I/O 수 보다 적은 경우만 성능향상에 기여(손익분기점:10~15%)

3. 여러 컬럼을 결합하여 인덱스를 구성하는 경우 컬럼의 순서가 중요

4. 입력, 삭제, 혹은 인덱스 컬럼의 값을 수정하는 경우 인덱스 데이터도 변경되어야 하므로 그에 따른 오버헤드 고려필요

- 테이블당 인덱스 수는 4개가 적당

5. 새로 추가된 인덱스는 기존 SQL문의 실행계획에 영향을 미칠 수 있음
인덱스 대상 테이블 선정 기준

1. 크기가 5~6 블럭 이상되는 테이블 : 이하는 FULL SCAN 해동 무방

2. 무작위접근(Random Access) 이 빈번한 테이블 : 단순보관용 또는 전체조회용일 경우 인덱스 생성X

3. 특정범위/순서의 데이터 조회가 필요한 테이블

4. Join 의 연결고리가 되는 컬럼의 경우

5. 참조모결성을 지정한 경우 연결고리가 되는 컬럼의 테이블
인덱스 대상 컬럼 선정 기준

1. 인덱스를 생성하고자하는 컬럼의 분포도는 5%를 넘지 않아야함.

분포도 = 1/컬럼값의종류*100 = 데이터별 평균로우 수 / 테이블의 총 로우수 * 100

2. 인덱스 머지(Index Merge) : 일반적으로 가장 좋은 하나의 인덱스만 사용하는 것이 여러개 인덱스를 사용하는 것보다 유리

3. 결합 인덱스(Composite Index) : 사용빈도 => 동등조건(=)비교여부 => 분포도 => 자주 사용되는 정렬 순서
Index Fast Full Scan vs Index Full Scan
Index Full Scan

개념 : Full scan은 Full scan으로 인한 부하가 발생하는 Access Operation이지만 Index 순서로 데이터를 추출 하므로 정렬을 하지 않아도 되는 장점을 가지고 있다.

고려 사항

Table의 대부분을 Access하여 정렬하는 경우라면 Index Full Scan을 통해 정렬된 결과를 추출할 수 있으므로

성능을 보장해 줄 수 있다. 하지만 Table의 일부분만을Scan할 경우에 Index Full Scan을 수행하게 되면 모든

Row를 가져와서 버리는 결과를 초래, 디스크 I/O 발생 및 Check하여 버리는 부하가 증가 하게 된다
Index Fast Full Scan

개념 : - Index Full Scan은 Single Block I/O를 지원하게 되지만 Index Fast Full Scan은 Multi Block I/O를 한다

Sort의 기능은 지원하지 않는다.

- Multi Block의 수는 db_file_multiblock_read_count 파라미터에 의해 정의된다.

1) 인덱스 트리 구조를 무시하고 인덱스 세그먼트 전체를 multiblock read 방식으로 스캔

2) 인덱스가 파티션 돼 있지 않더라도 병렬 쿼리 가능

3) 인덱스 리프 노드가 갖는 연결 리스트 구조를 이용하지 않기 때문에 얻어진 결과집합이 인덱스 키 순서대로 정렬되지 않음

<참고문헌>

대용량 데이터베이스 솔루션

Oracle Database 11g : Adminstration Workshop

Oracle Database 10g Documentation Library - councepts

원본 위치 <http://www.oracleclub.com>

원본 위치 <http://tistory.izyou.net/2460909>

원본 위치 <http://www.oracleclub.com/lecture/1035>

원본 위치 <http://blog.daum.net/_blog/hdn/ArticleContentsView.do?blogid=0LDC4&articleno=4653786&looping=0&longOpen=>

원본 위치 <http://www.gurubee.net/display/DBSTUDY/INDEX+FAST+FULL+SCAN>

'1. IT Story > DB' 카테고리의 다른 글

Oracle SQL과 옵티마이져 (0)	2012.02.29
Oracle SQL Sub-query(기초) (0)	2012.02.29
Oracle HINT(기초) (0)	2012.02.29
Oracle SQL Driving Table & JOIN(기초) (0)	2012.02.29
Oracle SQL Trace[기초] (0)	2012.02.29
아카이브 로그 모드 Archive Log Mode (0)	2012.02.29
Oracle TableSpace (0)	2012.02.29
Oracle Undo seg (0)	2012.02.29