오라클 SQL / 인덱스(INDEX)

*인덱스란

도서의 색인이나 사전과 같이 데이터를 쉽게 빠르게 찾을 수 있도록 만든 데이터 구조이다.

B-tree의 구조

 

*인덱스의 특징

• 인덱스는 테이블에서 한 개 이상의 속성을 이용하여 생성함
• 빠른 검색과 함께 효율적인 레코드 접근이 가능함
• 순서대로 정렬된 속성과 데이터의 위치만 보유하므로 테이블보다 작은 공간을 차지함
• 저장된 값들은 테이블의 부분집합이 됨
• 일반적으로 B-tree형태의 구조를 가짐
• 데이터의 수정, 삭제 등의 변경이 발생하면 인덱스의 재구성이 필요함

 

*오라클 B-tree 인덱스

오라클 인덱스는 B-tree를 변형하여 사용한다.

오라클 인덱스의 예

 

 

*오라클 인덱스의 종류

• B-Tree인덱스
  - 오라클에서 사용하는 기본적인 인덱스, 하나의 리프노드는 하나의 데이터에 대응
     ex) CREATE INDEX t_ix ON T(key1);
  
  
• IOT(Index Organized Table, 인덱스 구조 테이블)
  - B*Tree구조로 키 값이 정렬되면서 인덱스 리프노드에 실제 데이터가 같이 저장되는 테이블(인덱스+테이블 일체)
     ex) CREATE TABLE T (key1 NUMBER, BDATA VARCHAR2(10)) ORGANIZATION INDEX;
• Bitmap Index
  - 비트-맵을 사용하여 하나의 엔트리가 여러 행을 가르킬 수 있도록 생성
     ex) CREATE BITMAP INDEX bt_ix ON Emp(gender);
  
  
• Function-Base Index
  - 행과 열에 대한 함수의 결과를 저장한 인덱스
     ex) CREATE INDEX fbi_ix ON T(UPPER(name));

 

*인덱스 생성 시 고려사항

• 인덱스는 WHERE절에 자주 사용되는 속성이어야한다.
• 인덱스는 조인에 자주 사용되는 속성이어야한다.
• 단일 테이블에 인덱스가 많으면 속도가 느려질 수 있다.(테이블 당 4~5개 권장)
• 속성이 가공되는 경우 사용하지 않음
• 속성의 선택도가 낮을 때 유리함(속성의 모든 값이 다른 경우

 

*인덱스의 생성 문법

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

예시) 비 클러스터 인덱스 ix Book 생성

 

예시) 클러스터 인덱스 cix Customer 생성

 

 

*인덱스의 재구성 문법

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

예시

 

*인덱스 삭제 문법

DROP INDEX 인덱스이름

예시

 

 

*인덱스 정렬하기

빨간박스처럼 코드를 주면 인덱스는 차례로 정렬이된다. ORDER BY랑 비슷하다고 보면되는데,

여기서 짚고 넘어가야할점은 SQL에서는 ORDER BY를 지양하는것이 좋고 위와 같이 INDEX를

활용하여 정렬을 사용하는것을 권장한다.