SQL 옵티마이저와 인덱스 기본

옵티마이저는 사용자가 질의한 SQL문에 대해 최적의 실행 방법을 결정하는 역할을 수행합니다. 옵티마이저는 크게 규칙 기반 옵티마이저(RBO)와 비용 기반 옵티마이저(CBO)로 나뉩니다. 현재 대부분의 관계형 DB는 비용기반 옵티마이저만을 제공합니다.

 

 

1. 규칙기반 옵티마이저

RBO는 우선순위를 기반으로 실행계획을 생성합니다. 오라클의 규칙기반 옵티마이저에는 총 15가지 규칙이 있으며 가장 기본은 RowID를 통해 테이블에서 하나의 행을 엑세스 하는 방식입니다.

 

 

규칙기반 옵티마이저는 인덱스를 이용한 방식이 전체 테이블 엑세스 방식보다 우선순위가 높습니다. 그렇기 때문에 인덱스가 존재한다면 항상 인텍스를 사용한 실행계획을 생성합니다.

 

 

조인 순서를 결정할 때는 조인 칼럼 인덱스 존재 유무가 중요한 판단 기준입니다. 양쪽 테이블에 다 조인컬럼에 대한 인덱스가 존재한다면 우선순위가 높은 테이블을, 한쪽에만 인덱스가 존재한다면 인덱스가 없는 테이블 선행 테이블(Driving Table)로 선택합니다. 만약 양쪽 모두 인덱스가 없다면 FROM 절 뒤에 나온 테이블을 선행 ㅔ이블로 선택합니다.

 

 

조인 기법의 경우 양쪽에 모두 인덱스가 없다면 Sort Merge Join을, 둘 중 하나라도 인덱스가 있다면 NL Join을 사용합니다.

 

 

2. 비용 기반 옵티마이저

비용기반 옵티마이저는 예상되는 소요시간 또는 자원 사용량 등 비용이 가장 적은 실행계획을 선택하는 방식입니다. CBO는 RBO에서는 사용하지 않는 통계 정보를 이용합니다.

 

 

철저하게 비용을 계산해서 실행계획을 선택하기 때문에 인덱스 사용보다 전체 테이블 스캔이 비용이 더 적다고 판단하면 전체 테이블 스캔 방식으로 실행계획을 생성합니다. 실행계획에 Cost, Card, Bytes 등 최적화 정보가 있다면 해당 실행계획은 비용기반 옵티마이저로 생성된 것임을 알 수 있습니다.

 

---

앞서 '인덱스'라는 표현이 많이 나왔는데, 인덱스에 대해 짚고 넘어가보겠습니다.

 

 

인덱스는 원하는 데이터를 쉽게 찾을 수 있도록 하는 색인 개념입니다. 테이블에 인덱스 생성은 필수가 아니기 때문에 생성하지 않아도 되고 여러 개를 생성해도 괜찮습니다. 검색 조건을 만족하는 데이터를 인덱스를 통해 효과적으로 찾을 수 있지만 Insert, Update, Delete 등 DML 작업 시 테이블과 인덱스를 함께 변경해야 하기 때문에 오히려 느려질 수도 있습니다. 예외적으로 Update 작업에서는 부하가 없을 수도 있습니다.

 

 

기본 인덱스는 PK와 마찬가지로 UNIQE & NOT NULL 제약조건을 가지며 보조 인덱스는 중복 데이터 입력이 가능합니다. 

 

 

1. B-트리 인덱스

DBMS에서 가장 일반적인 인덱스로 리프 블록과 브랜치 블록으로 구성됩니다. 브랜치 블록은 분기를 목적으로 하는 블록으로 다음 단계의 블록을 가리키는 포인터를 갖고 있습니다. 리프 블록은 인덱스를 구성하는 칼럼 데이터와 해당 데이터를 갖고 있는 행의 위치를 가리키는 레코드 식별자로 구성되어 있습니다.

 

 

2. 클러스터형 인덱스

클러스터형 인덱스는 리프 페이지가 곧 데이터 페이지이므로 테이블 탐색에 필요한 레코드 식별자가 리프 페이지에 없습니다. 클러스터형 인덱스의 리프 페이지를 탐색하면 해당 테이블의 모든 칼럼 값을 곧바로 얻을 수 있기 때문에 클러스터형 인덱스를 사전에 비유하기도 합니다. 클러스터형 인덱스는 한 테이블 당 한개만 생성할 수 있습니다.

 

 

3. BITMAP 인덱스

하나의 인덱스 키 엔트리가 많은 행에 대한 포인터를 저장하는 것으로 시스템에서 사용될 질의를 시스템 구현 시에 모두 알 수 없는 경우인 DW, AD-HOC 질의 환경을 위해 설계됩니다.