조회 수 23965 추천 수 0 댓글 0

1. 인덱스란?
- SQL의 수행 속도를 향상시키기 위해서 테이블과는 별도로 생성되는 오브젝트
- 테이블 내의 원하는 레코드를 빠르게 찾아갈 수 있도록 만들어진 목차같은 역할을 하는 것.
- 찾으려는 데이터의 주소를 순서대로 가지고 있는 집합
- 해당 테이블에 한개 이상의 컬럼에 대하여 인덱스 생성 가능
- 해당 테이블에 여러 개의 인덱스 생성 가능
- 테이블의 값을 빠르게 엑세스 하도록 하는것이 인덱스의 역할인데 SQL 구문 실행에 있어 속도향상을 기대할 수 있다.
- 그러나 잘못된 index는 오히려 SQL(DML) 속도 저하의 원인이 되므로 인덱스 생성할 때는 신중히 생각하고 만들어야 한다.

- 인덱스가 불필요한 경우는 다음과 같다.
- 데이터가 적은(수천전 미만) 경우
- 조회 보다 삽입, 수정, 삭제 처리가 많은 테이블일 경우
- 조회결과가 전체행의 15% 이상 읽어들일것으로 예상되는 겅우

 

2. 테이블과 인덱스의 관계

1) 인덱스가 걸려있는 table에 어떤 데이터가 삽입되면 데이터를 다시 정렬해서 인덱스 테이블에 새로 들어온 데이터를 추가하게 된다 ( table에 data가 insert되면 index split 과정을 거쳐서 index 테이블에 인덱스가 없다면 테이블에 데이터만 추가되고 말 것이지만, 걸려있는 인덱스가 있다면 인덱스 테이블을 모두 뜯어내어 (index split) 성능을 감소 시킨다는 단점)

 

2) 인덱스가 걸려있는 table에 데이터가 삭제되는 상황
테이블에 데이터가 삭제되면 인덱스는 해당 데이터를 지우는게 아니라 사용안함 표시만 한 상태로 두게된다
즉 row수는 그대로 이다.
삭제후 다시 데이터가 insert될 경우에 index split가 일어나며 insert되고,다시 삭제해도 index에서는 사용안한다는 표시만 하고 삭제되지 않는다.
이와같은 작업이 반복되다보면 예를들어 실제 데이터는 10만건 인데 index에는 데이터가 100만건 있는 결과를 낳을 수도 있을 것이다.
이렇게 되면 더이상 인덱스는 제역할을 못하는 상황이되는 것이다.
이런상황 에서  실행계획이 index range scan이라면 index가 무용지물 이므로, 차라리 강제로 hint를 주어 full scan하는 것이 나을 것이다.


3) 인덱스가 걸려잇는 테이블에 update를 하는 상황
delete(데이터 삭제)의 개념과는 다르게 update는 이전 데이터가 삭제되고 그자리에 새 데이터가 들어오는 개념이다.
그렇기 때문에 index에 delete과정과 insert 과정 모두가 나타난다.
즉 변경 전 데이터는 삭제 되지 않고, insert로 인한 index split도 일어나게 된다.

 

 

3. 인덱스 생성 과정 및 작동 원리
- 인덱스는 일단 DATA에 아무도 접근 못하도록 한 후 정렬작업을 수행한다. 정렬작업이 끝난 후 block에 기록하게 된다
- 사용자가 SQL문을 날리게 되면 그 table의 해당 컬럼의 인덱스 유/무 부터 검사 한다.
- 인덱스가 존재한다면 그 인덱스에서 주소를 찾은 다음, 바로 그 주소에 쓰여진 부분만 조회 한다.

 

4. INDEX 구성요소
- INDEX 컬럼의 값(인덱스 key값): 인덱스를 생성하는 컬럼 값
- ROWID : 해당 로우를 엑세스할 수 있는 주소 값

 

5. 인덱스 종류 및 생성
1) B 트리 인덱스
- 인덱스 제일 위는 root로 인덱스의 다음 레벨을 가리키는 엔트리를 포함하고 있다
- 다음레벨은 branch 블록으로 마찬가지로 다음 레벨의 인덱스 블록을 가리킴
- 최하층 레벨은 leaf노드로 테이블의 행을 가리키는 인덱스 엔트리를 포함하고 잇음
- leaf 블록은 키 값의 내림차순, 오름차순의 키 값으로 인덱스를 검색하는데 편리하도록 양방향으로 연결

- 값이 주어지면 리스트 중간점에 있는 값과 비교해서 그값이 더 크면 리스트 아래쪽 반을 버리고 그값이 작으면 위쪽 반을 버린다
- 마지막 하나의 값이 발결될 때까지 또는 리스트가 끝날떄까지 같은 작업을 다른 반대쪽에서도 반복한다.
- 테이블에서 순차적으로 데이터를 찾는 것보다 트리 구조의 인덱스에서 데이터를 더 빠르게 찾을 수 있기 때문에 빠른 조회 성능 제공
- 주민번호, ID번호, 상품번호등 고유한 데이터 값의 종료가 많은 경우에 효율적
- 반대로 고유한 데이터 값의 종류가 몇개 없을 때(예를들어 성별 등)에는 오히려 속도가 늦어 질수도 있다.
- 인덱스 생성

CREATE INDEX column_index ON table(column) ; ( column_index 이 table의 column에서 사용될 것이다. )

 

2) bitmap 인덱스
- 테이블에 대해 고유한 데이터 값의 종류가 적은 경우에  가장 잘 동작한다.
- 테이블이 매우 크거나 수정/변경이 잘 일어나지 않는 경우 사용 (DW 환경)
- B-Tree 인덱스는 하나의 테이블에 서로 독립적으로 각각의 인덱스를 구성한 경우 둘 중 하나의 인덱스만 사용하게 된다.
- 반면 bitmap 인덱스는 여러 개의 인덱스 생성 시, 상황만 적절하면 조건 추출 시 모두 이용 할 수 있다.

- 이유는 스캔에 의한 데이터 추출이 아니라 연산에 의한 데이터 추출 방식을 가지고 있기 때문이다.
- 비교와 데이터를 추출하는 방식이 비트 연산을 따르므로 AND 조건이든 OR 조건이든 모두 동일한 성능을 보이게 된다.
- 비트맵 인덱스의 경우에는 각각의 비트에 해당컬럼의 고유값을 부여한 후 해당 값인지 아닌지를 check해 놓는 방식이다.
- 만약 조건절에 여성 이라는 조건이 들어 왔다면 여성에 대한 비트만을 읽기 때문에 빠른 속도로 데이터의 결과를 찾을 수 있다.
  (조건절에 의해 검색되는 데이터의 분포도가 나쁜 컬럼에 사용하면 속도 향상)
- OLTP 또는 동시에 여러 세션에 의해 빈번하게 UPDATE되는 환경에는 적합하지 않다.
- 하나의 세션에서 인덱스 엔트리를 수정하게 되면 그 인덱스가 포인터 하고 있는 모든 로우를 락킹한다.(동시성 저하)
- 인덱스 생성
CREATE BITMAP INDEX colmun_bitmap_index ON table(column);

 

3) BITMAP JOIN INDEX
- 2개 이상의 테이블이 조인되어 검색되는 컬럼에 대해 비트맵을 만들어 검색시 성능 향상과 공간 관리가 효율적
- 자주 조인되는 sql문에 생성
- 저장공간이 적게 사용되며 대용량 데이터베이스 환경에 적합
- 따로 인덱스를 생성해야 하며 비트맵에 포함되는 컬럼 값이 변경되면 비트맵 인덱스를 재생성 해야 하므로 비용이 높다.
- 인덱스 생성
CREATE BITMAP INDEX bit_join_index on table(t1.column1)
FROM table t1, table2 t2 where t1.column1=t2=column2;

 

4)concatenated 인덱스
- 하나 이상의 컬럼으로 생성된 인덱스
- 한 잘의에 의해 선택된 모든 컬럼이 결합인덱스에 있을 경우, table을 access하지 않고 결합 인덱스 만으로 원하는 값을 가져올 수 있는 점이 장점이다.
- 결합인덱스는 두 컬럼을 비교하여 검색 할 때 많이 쓰임
- index on table (A,B,C) 의 경우 앞에서 부터 정렬, 즉 a->b->c의 순서로 정렬 되고, 즉 검색시 중요 조건(컬럼)부터 인덱스 생성시 앞에 두어야 한다.
- 결합 인덱스의 첫번째 컬럼을 조건에 사용하지 않으면 그 인덱스는 사용 되지 않으므로, 첫번째 컬럼은 항상 사용되는 컬럼을 선택해야 한다.
- 첫번째 컬럼 선택
. 항상 '='로 비교되는 컬럼을 첫번째 컬럼으로 선택
. 분포도가 좋은 컬럼을 우선적으로 선택
. 모든 컬럼이 where절에 동일하게 자주 사용되지만 데이터가 한 컬럼에 대해 물리적으로 정렬되어 있는 경우 그 컬럼을 결합 인덱스 첫번째로 구성

예)  SELECT * FROM table WHERE A=20 AND B=30 AND C=10; 일 경우 빠르다.
      SELECT * FROM table WHERE A=20 일 경우 빠르다.
      SELECT * FROM table WHERE C=20 AND B=30 AND A=10; 일 경우 INDEX의 효과가 없다.

- 인덱스 생성
CREATE INDEX comp_index on table(A,B,C);

 

5) REVERSE KEY INDEX
- 리프블록에 저장되는 키 값을 역순으로 뒤집어 구성하는 인덱스
- 인덱스를 구성할 컬럼인 컬럼의 키 값에 대해 reverse 구성을 한뒤, 인덱스의 형태로 인덱스를 구성
- reverse ket index로 생성하면 순차적이었떤 숫자들이 reverse 되면서 뒤죽박죽 변하게 되고, 이러한 키 값들은 정렬되어 리프 블록에 자리 잡는다.
- 기존의 b-tree에서 순차적으로 리프 블록에 들어가야 할 키 값들이 여러 리프 블록에 분할 된다.
- 분산을 시키는 이유는 경합을 막기 위해서 인데, 일반적인 b-tree 구조의 경우 해당 컬럼에 순차적인 데이터들이 대량으로 insert/update가 발생되면 보통 새로 들어오는 작업에
대해 좌측의 리프 블록 위주로 index spilit 작업이 발생하게 된다. 이러면 insert/update 되는 블록에 대해서 락이 발생하게 되는데, 이때 다른 유저가 이 블록에 해당하는 데이터에
접근(insert/update) 하고자 한다면 대기가 생기게 된다. 하지만 분산 시킴으로써, 경합이 생길 확률을 줄여주게 된다.( 좌측 리프 블록에 대한 경합 해소)
- 특정 리프 블록 단위에만 집중되는 인덱스의 수정이 많이 일어나 성능이 저하되는 것을 회피
( 컬럼에 대해 순차적인 값의 insert나 update가 여러곳에서 일어날 경우 경합이 발생된다.)
예:항공권 구입을 할때 특정 날짜에 있는 (주말, 여름휴가 기간) 항공권에 여행사에서 항공권 예약이 몰릴 경우 )
- insert시 인덱스에서 리프 키 값이 다른 블록들로 모두 고르게 분포
- 범위 스캔시 인덱스 엑세스 불가.( 키 값이 전환된 채로 정렬되기 때문에 기존에 같은 범위에 있던 키 값들이 분산되어 버리기 때문)
- range scan 대신 fefch-by-key 또는 full-table scan 가능

- 인덱스 생성
CREATE INDEX rever_index ON table(column) REVERSE;

 

6) function based INDEX
- 확장된 인덱스의 개념
- 기존 인덱스의 제한사항을 다소 완솨시킨 인덱스
- 인덱스의 컬럼을 가공해야 하는 경우나 계산된 값으로 driving을 해야 하는 경우에 많은 비효율이 발생하게 된되는데 함수기반 인덱스가 그 단점을
다소 완화 시킬 수 있다.
- 함수기반 인덱스는 인덱스를 생성하고자 하는 테이블의 하나 이상의 컬럼을 포함하는 함수나 expression에 인덱스를 생성할수 있으며,
함수나 expression을 미리 계산하여 index에 저장
- 함수기반 인덱스를 생성하기 위한 requirement
. parameter 설정
- QUERY_REWRITE_INTEGRITY=TRUSTED
- QUERY_REWRITE_ENAVLED=TRUE
. 사용자는 QURERY REWRITE , GKOBAL QUERY REWRITE 권한을 가져야 한다.
- 함수 기반 인덱스를 사용하기 위한 REQUIREMENT
. 인덱스를 생성한 후 테이블의 통계정보를 생성
. NULL은 인덱스에 저장되지 않기 때문에 표현식에서 NULL 값을 사용할 필요가 없어야 한다.
- 인덱스 생성
CREATE INDEX fun_index on table (nvl(column,0));


List of Articles
번호 분류 제목 글쓴이 날짜 조회 수
공지 Q&A Oracle관련 게시물만 Sean 2014.04.09 84506
160 Q&A 오라클 복구 질문 희달이 2015.05.08 854
159 TM 11G new feature SQL performance analyzer file Sean 2013.01.09 14194
158 TM MEMORY TARGET 도로시 2013.01.03 19298
157 TM check whether crs auto start is enable or disable 담벼락 2012.10.12 25923
156 TM ARDCI file 이현정 2012.10.09 12458
155 TM (10gR2)Full UNDO tablespace Tech1 2012.04.04 23760
154 TM audit 관련 2 이현정 2012.03.29 23980
» TM 테이블과 인덱스의 관계 및 인덱스 종류 및 생성 방법. Tech1 2012.03.28 23965
152 TM windows에서 Opatch 진행시 oci.dll 에러 조치 1 이현정 2012.03.26 36852
151 TM 테이블 생성방법 및 각종옵션 Tech1 2012.03.20 19719
150 TM upgrade 시 sorting 문제 담벼락 2012.03.16 16325
149 TM 11 newfeature(Upgrade) 10.2.0.5 to 11.2.0.3 1 file 윤현 2012.01.12 18259
148 TM TDE (column, tablesapce) 1 file 윤현 2011.12.27 13174
147 TM 11g newfeature (dbca) 2 file 윤현 2011.12.27 13856
146 TM 11g 데이타베이스 에러수집방법 2 perfstat 2011.11.02 19345
145 TM long type column 1 담벼락 2011.10.11 17412
144 TM 백업이 필요없는 대용량 table skip하고 export 받기 고구마 2011.06.14 18225
143 TM TDE tablespace 1 윤현 2011.06.09 17209
142 TM openvms command perfstat 2011.05.31 17175
141 TM Oracle Silent Install Guide 3 file 송기성 2011.03.13 20062
Board Pagination Prev 1 2 3 4 5 6 7 8 Next
/ 8