[SQL] SQL 고급문법 - index
안녕하세요 과외선생J 입니다.
고급문법 마지막으로 인덱스에 대해서 공부해보도록 하겠습니다.
* 본 포스팅은 중부기술교육원 수업과 한빛미디어의 "오라클로 배우는 데이터베이스 개론과 실습 2판" 책을 기본바탕으로 공부한 뒤 작성되었음을 알립니다.
오라클로 배우는 데이터베이스 개론과 실습
COUPANG
www.coupang.com
“파트너스 활동을 통해 일정액의 수수료를 제공받을 수 있음"
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;
인덱스에 대해 간략하게 알아보았습니다.
오늘도 공부하신다고 고생하셨습니다 ^^