기록하기를 좋아하라, 기억은 흐려지고 생각은 사라진다.

파티션 테이블은 일반적인 테이블에서 사용하는 인덱스로는 사용이 힘들다. 파티션 테이블의 특성상 새로운 세그먼트를 계속해서 생성하여 테이블을 계속 나뉘어야 하지만 인덱스는 나눌 수가 없기 때문이다. 때문에 파티션 데이블에는 다른 방식으로 인덱스를 걸어줘야 한다. 오라클에서는 파티션 데이블에 인덱스를 거는 두 가지 방식이 있다. 바로 로컬 인덱스와 글로벌 인덱스이다. 

로컬 인덱스

파티션 테이블에서 사용하는 가장 일반적인 INDEX 생성 방법으로 테이블과 인덱스가 동일하게 파티션 된 경우를 말한다. 로컬 인덱스는 테이블 파티션 키 컬럼을 똑같이 인덱스로 구성한다. 예를 들어 파티션이 10개라면 인덱스도 똑같이 10개가 된다. 로컬 파티션 인덱스의 경우에 테이블의 파티션 구조가 바뀐다거나 파티션이 삭제가 된다고 하더라도 인덱스 재생성이 필요가 없어 오라클에서 알아서 자동으로 관리를 해주기 때문에 관리측면에서 유리한 측면이 있다. 

글로벌 인덱스

글로벌 인덱스는 파티션 테이블로 이루어져 있는 테이블에 하나의 인덱스가 여러 개의 테이블 파티션과 매핑된다. 글로벌 인덱스는 파티션 키 컬럼을 인덱스로 지정해주어야 하는 로컬 인덱스와는 달리 다른 칼럼들도 자유롭게 인덱스를 걸 수 있다는 장점이 있다.

파티션 테이블의 인덱스를 구성할 수 있는 두가지 방법인 로컬 인덱스, 글로벌 인덱스 위 두 가지 방식 중 로컬 파티션 인덱스를 압도적으로 많이 사용한다. 그 이유는 로컬 인덱스가 관리가 편하기 때문이다. 예를 들어 로그를 저장하는 테이블을 파티션 테이블로 만들었다고 했을 때, 시간이 지나 로그의 보관 의무시간이 종료되어 필요 없는 데이터들을 제거했다고 하며. 이때 파티션 DROP을 수행하게 될 텐데 글로벌 인덱스로 구성되어 있다면 필요 없는 테이블 파티션을 DROP 한 뒤에 인덱스가 깨진다. 이렇게 인덱스가 깨져버리면 Invaild 상태가 되고 이 인덱스를 다시 사용하기 위해서는 인덱스 리빌딩 작업을 다시 해줘야 하는 등 번거로움이 존재한다. 하지만 로컬 인덱스로 구축하였다면 전혀 문제가 발생하지 않는다. 로컬 인덱스 각각의 파티션마다 하나씩 걸려 있기 때문에 파티션을 DROP 할 때 함께 제거되고 남아있는 파티션에는 전혀 지장이 없기 때문이다. 

인덱스

데이터를 빠르게 검색할 수 있게 해주는 객체. 컬럼을 정렬한 후에 데이터를 빠르게 찾을 수 있도록 도와주는 역할을 한다. 책으로 비유하자면 색인을 의미.

인덱스를 생성하면 인덱스를 위한 디스크 공간이 필요, 인덱스를 가진 테이블에 DML 작업을 할 경우 더 많은 비용과 시간이 필요하다. 때문에 인덱스를 생성 시 해당 테이블의 요구를 정확하게 파악한 후에 상황에 맞게 적절한 칼럼으로 Clustered Index와 Non Clustered Index를 구성해야 합니다.

인덱스의 종류

1. 클러스터형 인덱스(Clustered Index)
2. 넌 클러스터형 인덱스(Nonclustered Index)

클러스터 인덱스

• 테이블당 1개씩만 허용된다.
• 물리적으로 행을 재배열한다.
• PK설정 시 그 칼럼은 자동으로 클러스터드 인덱스가 만들어진다.
• 인덱스 자체의 리프 페이지가 곧 데이터이다. 즉 테이블 자체가 인덱스이다. (따로 인덱스 페이지를 만들지 않는다.)
• 데이터 입력, 수정, 삭제 시 항상 정렬 상태를 유지한다.
• 비 클러스형 인덱스보다 검색 속도는 더 빠르다. 하지만 데이터의 입력. 수정, 삭제는 느리다.
• 30% 이내에서 사용해야 좋은 선택도를 가진다.

넌 클러스터 인덱스

• 테이블당 약 240개의 인덱스를 만들 수 있다.
• 인덱스 페이지는 로그파일에 저장된다.
• 레코드의 원본은 정렬되지 않고, 인덱스 페이지만 정렬된다.
• 인덱스 자체의 리프 페이지는 데이터가 아니라 데이터가 위치하는 포인터(RID)이기 때문에 클러스터형보다 검색 속도는 더 느리지만 데이터의 입력, 수정, 삭제는 더 빠르다.
• 인덱스를 생성할 때 데이터 페이지는 그냥 둔 상태에서 별도의 인덱스 페이지를 따로 만들기 때문에 용량을 더 차지한다
• 3% 이내에서 사용해야 좋은 선택도를 가진다.

쉽게 책에 비유하자면 클러스터 인덱스는 페이지를 알기 때문에 바로 그 페이지를 펴는 것이고, 넌 클러스터 인덱스는 뒤에 목차에서 찾고자 하는 내용의 페이지를 찾고 그 페이지로 이동하는 것과 같다. 테이블 스캔은 처음부터 한 장씩 넘기면서 내용을 찾는 것과 같다.

결론

클러스터 인덱스는 데이터 위치를 바로 알기 때문에 그 데이터로 바로 접근할 수 있고, 넌 클러스터 인덱스는 인덱스 페이지를 한번 거쳐서 데이터에 접근하는 방식이다.

 옵티마이저는 가장 효율적인 방법으로 SQL을 수행할 최적의 처리 경로를 생성해주는 DBMS의 핵심 엔진을 뜻함. 컴퓨터의 두뇌가 CPU인 것 처럼 DBMS의 두뇌는 옵티마이저라고 할 수 있다. 

 개발자가 SQL을 작성하고 실행하면 소프트웨어 실행파일처럼 즉시 실행되는 것이 아니라 옵티마이저라는 곳에서 여러가지 실행계획을 세운다. 

옵티마이저의 종류

옵티마이저는 실행 계획을 세우는 방식에 따라 규칙 기반 옵티마이저와 비용 기반 옵티마이저로 나뉜다.

 규칙 기반 옵티마이저(RBO - Rule Base Optimizer)

오라클 8 이하의 버전에서 기본으로 설정된 옵티마이저가 바로 규칙기반 옵티마이저다. 규칙 기반 옵티마이저는  말 그대로 실행 속도가 빠른 순으로 규칙을 먼저 세워두고 우선순위가 앞서는 방법을 채택하는 것이다. 과거에는 옵티마이저의 비용을 예측하는 능력이 그다지 좋지 않아 이러한 방식을 사용하였다. 규칙의 우선순위는 아래와 같다.

비용 기반 옵티마이저

 최근에 많이 사용되고 있는 옵티마이저 방식이며 오라클 10 이후 버전부터는 공식적으로 비용 기반 옵티마이저만 사용한다. 비용 기반 옵티마이저는 옵티마이저에서 실행 계획을 세운 뒤 (최대 2천개까지) 비용이 최소한으로 나온 실행 계획을 수행한다. 비용기반 옵티마이저는 이뵹을 예측하기 위해서 규칙 기반 옵티마이저가 사용하지 않는 테이블, 인텍스, 칼럼 등의 다양한 객체 통계정보와 시스템 통계정보를 이용한다. 통계정보가 없는 경우 비효율적인 샐행계획을 생성할 수 있으므로, 정확한 통계정보를 유지하는 것이 중요하다. 

비용 기반 옵티마이저 모드

비용 기반 옵티마이저는 여러 가지 모드가 있다. 이 모드에 따라 최적의 비용을 구하는 방식이 조금씩 달라진다.

choose: 현재는 잘 사용하지 않지만 SQL이 실행되는 환경에서 통계 정보를 가져올 수 있으면 비용 기반 옵티마이저로 아니라면 규칙 기반 옵티마이저로 작동시키는 모드.

first_rows: 옵티마이저가 처리 결과 중 첫 건을 출력하는데 걸리는 시간을 최소화 할 수 있는 실행 계획을 세우는 모드.

first_rows_n: SQL의 실행 결과를 출력하는데 까지 걸리는 응답속도를 최적화하는 모드.

all_rows: SQL 실행 결과 전체를 빠르게 처리하는데 최적화 된 실행계획을 세우는 모드이다. 마지막으로 출력될 행까지 최소한의 자원을 사용하여 최대한 빨리 가져오게 하며 오라클 10g 이후로는 이 모드가 기본값으로 설정.

옵티마이저 동작 방식

Parser : SQL문장을 분석하여 문법 검사와 구성요소를 파악하고 이를 파싱 해서 파싱 트리를 만듭니다.

Query Transformer : 파싱된 SQL을 보고 같은 결과를 도출하되, 좀 더 나은 실행 계획을 갖는 SQL로 변환이 가능한지를 판단하여 변환 작업을 수행한다. 

Estimator : 시스템 통계정보를 딕셔너리로부터 수집하여 SQL을 실행할 때 소요되는 총비용을 계산한다.

Plan Generator : Estimator를 통해 계산된 값들을 토대로 후보군이 되는 실행계획을 도출한다. 

Row-Source Generator : 옵티마이저가 생성한 실행계획을 SQL 엔진이 실제 실행할 수 있는 코드나 프로시저 형태로 포맷팅 한다.

SQL Engine : SQL을 실행한다.