[SQL] SQL 고급문법 - index

안녕하세요 과외선생J 입니다. 

 

고급문법 마지막으로 인덱스에 대해서 공부해보도록 하겠습니다. 

 

* 본 포스팅은 중부기술교육원 수업과 한빛미디어의 "오라클로 배우는 데이터베이스 개론과 실습 2판" 책을 기본바탕으로 공부한 뒤 작성되었음을 알립니다. 

 

https://coupa.ng/bMLpSU

오라클로 배우는 데이터베이스 개론과 실습

“파트너스 활동을 통해 일정액의 수수료를 제공받을 수 있음"

1. B-tree

인덱스란 검색을 쉽게 하기 위한 데이터 구조로 B-tree ( balanced-tree) 구조로 이뤄져 있습니다. 

 

B- tree는 루트 노드(root node), 내부노드(internal node), 리프노드(leaf node)로 이뤄져 있으며 리프노드는 모두 같은 레벨에 존재한다는 특징이 있습니다. 

 

 

각 노드는 키값, 포인터 가지며 키값은 오름차순으로 저장이 됩니다. 

 

실제 검색을 할 때 순서를 살펴보면 루트노드 -> 내부노드 -> 리프 노드로 검색을 하게 됩니다. 

찾고자 하는 값과 각 노드값을 비교하여 다음 노드로 진행하는 방식으로 검색하게 되며 최종 리프노드 도달 시 찾고자하는 값의 저장위치에 대응하는 rowid(RID)로 행을 검색하게 됩니다. 

 

트리 구조 이용 시 많은 데이터가 있더라도 3~4번 정도 디스크의 블록을 읽으면 찾을 수 있어서 접근시간 단축한다는 장점이 있습니다. 

 

인덱스 특징

 - 한개 이상의 속성을 이용하여 생성

 - 빠른 검색 , 효율적인 레크도 접근

 - 테이블보다 작은 공간 차지 ( 속성과 데이터 위치만 보유하기때문)

 - 저장 값들은 테이블의 부분집합

 - B-tree 형태

 - 데이터의 수정, 삭제 등 변경 발생 시 인덱스 재구성 필요

---

2. 오라클 인덱스

오라클에서 인덱스는 B-tree를 변형 구조로 사용이 되지만 이름은 동일하게 B-tree라고 합니다. 

리프 노드 블록에 연속된 키 값의 레코드에 대한 RID를 저장하여 실제 데이터는 RID를 통해 저장위치 찾을 수 있게 합니다. 

 

ex) RID = 3-1 인 경우 3번째 블록 1번행의 의미를 갖는다. 

 

오라클 인덱스 종류 

B-tree 인덱스 : 기본 인덱스, 하니의 리프노드는 하나의 데이터의 대응

IOT(Index Organized Table) : B-tree 구조, 리프노드에 실제 데이터 저장 = 인덱스+테이블 일체

Bitmap index : 하나의 엔트리가 여러행 가르킬수 있는 구조

 Function-base Index : 행과 열에 대한 함수 결과를 저장한 인덱스

---

3. 인덱스 생성, 수정, 삭제

 

인덱스 생성을 잘해야 데이터 검색이 빨라지며 데이터 양이 적거나 데이터값이 몇 종류 안될 경우 인덱스가 더 느릴 수 있습니다. 그래서 다음과 같은 고려사항을 생각하고 인덱스를 만들어줘야 합니다. 

 

인덱스 생성 시 고려사항

- where절에 자주사용되는 속성

- 조인에 자주 사용되는 속성

- 테이블 당 4~5개의 인덱스 권장

- 속성 가공 시 인덱스 사용하지 않음

- 선택도가 낮을 때 유리 

 

* 선택도란 1/서로다른값의 개수

 

인덱스 생성 기본 문법은 다음과 같습니다. 

 

CREATE [REVERSE] [UNIQUE] INDEX 인덱스이름 ON 테이블이름 (컬럼[ASC|DESC]);

* 대괄호([ ])는 생략 가능 

 

예시

CRETE INDEX ix_Book ON Book(Bookname);

 

 

인덱스 재구성이 필요할 경우 ALTER INDEX 문법을 사용합니다.

다만 수정, 삭제, 삽입이 잦을 경우 노드 갱신이 주기적으로 발생 -> 단편화현상

* 단편화 : 삭제된 레코드의 인덱스 값 자리가 비게됨, 성능저하로 이어짐

 

기본문법

ALTE [REVERSE] [UNIQUE] INDEX 인덱스이름 [ON {ONLY} 테이블이름 (컬럼이름,컬럼이름)] REBUILD;

* 대괄호([ ])는 생략 가능 

 

예시

ALTER INDEX ix_Book REBUILD;

 

삭제는 DROP문을 이용합니다. 

 

예시

DROP INDEX ix_Book;

---

인덱스에 대해 간략하게 알아보았습니다.

 

오늘도 공부하신다고 고생하셨습니다 ^^