10. ORACLE SQL 고급: 인덱스(Index)

---

 

 

1. 데이터베이스의 물리적 저장

컴퓨터 상에서 실제로 데이터가 저장되는 곳은 보조 기억 장치이다.

HDD는 원형의 플레이트로 구성되어 있고, 이 플레이트는 논리적으로 트랙이 나뉘며, 그 트랙은 다시 몇 개의 섹터로 나뉜다.

HDD에 저장된 데이터를 읽는 데 걸리는 시간은 (탐색 시간 + 회전 지연 시간+ 데이터 전송 시간)이다.

데이터베이스에서 사용하는 데이터는 물리적으로 HDD/SDD와 같은 보조 기억 장치에 저장되어 사용/관리된다.

DBMS나 개발 도구 등은 OS 위에 올라가는 프로세서로서 존재한다.

오라클은 아래와 같은 주요 파일들로 구성되어 있다.

데이터 파일

• 운영체제 상에 물리적으로 존재한다.
• 사용자 데이터, 개체를 저장한다.
• 테이블, 인덱스로 구성된다.

온라인 리두 로그

• 데이터의 모든 변경사항들을 기록한다.
• 데이터베이스 복구에 쓰이는 로그 정보를 저장한다.
• 최소 2개의 온라인 리두 로그 파일 그룹을 가진다.

컨트롤 파일

• 오라클이 필요로 하는 다른 파일들(데이터 파일, 로그 파일 등)의 위치 정보를 저장한다.
• 데이터베이스 구조 등 변경사항이 있을 때 자동으로 업데이트된다.
• 오라클 DB의 마운트, 오픈의 필수 파일이다.
• 복구 시 동기화 정보를 저장한다.

 

---

 

2. 인덱스(Index)와 B-tree

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

인덱스의 특징은 다음과 같다.

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

B-tree는 인덱스를 사용하여 원하는 데이터를 빨게 찾아갈 수 있도록 디자인된 트리 구조이다.

 

---

 

3. 오라클 B-tree 인덱스

오라클 인덱스는 B-tree를 변형하여 사용하며, 명칭은 동일하다.

오라클에서 사용하는 인덱스의 종류로는 다음과 같다.

 

---

 

4. 인덱스 생성

B-tree 인덱스 생성 시 고려사항은 다음과 같다.

• 인덱스는 WHERE 절에서 자주 사용되는 속성이어야 한다.
• 인덱스는 JOIN에 자주 사용되는 속성이어야 한다.
• 단일 테이블에 인덱스가 많으면 속도가 느려질 수 있다.
• 속성이 가공되는 경우, 사용하지 않는 것이 좋다.
• 속성 선택 후 결과의 개수가 작을 때 유리하다. (속성의 모든 값이 다를 때 유리하다)
• 데이터 수가 적은 경우는 설치하지 않는 것이 나을 수 있다.

BIT MAP 인덱스는 속성의 예상 결괏값이 적을 때 유리하다. (EX. 남/녀)

인덱스 생성 문법은 다음과 같다.

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

인덱스 생성은 사용자가 하지만, DBMS가 질의 처리를 효율적으로 하기 위해 사용은 시스템이 알아서 한다.

PRIMARY KEY, UNIQUE 등의 제한 조건을 설정하면 자동으로 인덱스가 생성된다.

 

4.1) REVERSE 키워드

연속된 값을 입력하면 특정 리프 노드만 계속 생성되는 문제를 막기 위해 키 값을 역변환하여 인덱스로 사용하는 방법이다.

키 값이 101, 102, 103일 경우, REVERSE 키워드를 사용하면 키 값이 101, 201, 301로 변경된다.

LIKE 문을 사용하면 인덱스를 사용할 수 없다.

범위 연산 사용 시 인덱스를 사용할 수 없다.

 

4.2) DESC 키워드

데이터가 커지는 순서대로 인덱스가 생성된다.

 

---

 

5. 인덱스 재구성과 삭제

인덱스 생성 후, 데이터 삽입과 삭제가 빈번해지면 인덱스 구성이 비효율적으로 변한다.

이때, 인덱스를 고쳐서 쓰는 것보다 다시 생성하는 것이 더 효율적일 수 있다.

인덱스 재구성은 ALTER INDEX 명령을 사용한다.

기본 문법 구성은 다음과 같다.

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

더 이상 인덱스가 필요 없다고 판단되면 삭제할 수 있다.

인덱스를 삭제하는 문법 구성은 다음과 같다.

DROP INDEX 인덱스이름

 

---

 

---

수고하셨습니다!

---