[SQLD] SQL 기본 및 활용 Section 03. SQL 최적화의 원리

---

1. 옵티마이저(Optimizer)와 실행 계획

 

---

01. 옵티마이저

• SQL 개발자가 SQL을 작성하고 실행할 때, 옵티마이저가 SQL을 어떻게 실행할 것인가를 계획함
• 즉, SQL 실행 계획(Execution Plan)을 수립하고 SQL을 실행함
• 옵티마이저는 SQL의 실행 계획을 수립하고 SQL을 실행하는 DBMS의 소프트웨어
• 결과가 같은 SQL문도 어떻게 실행하느냐에 따라 성능이 달라지므로 옵티마이저의 실행 계획은 SQL 성능에 아주 중요한 역할을 함

 

 

---

02. 옵티마이저 특징

• 데이터베이스에 관한 모든 메타데이터를 가지고 있는 데이터 딕셔너리(Data Dictionary)에 있는 오브젝트 통계, 시스템 통계 등의 정보를 사용해 예상되는 비용을 산정
• 여러 개의 실행 계획 중에서 최저 비용을 가지고 있는 계획을 선택해 SQL을 실행함

 

 

---

03. 옵티마이저의 실행 계획 확인

• 옵티마이저는 SQL 실행 계획을 PLAN_TABLE에 저장함

 

SELECT * FROM PLAN_TABLE;

 

 

또는 SQL 실행환경마다 실행 계획을 보여주는 페이지가 있다

 

 

 

---

2. 옵티마이저 종류

 

항목	규칙 기반 옵티마이저	비용 기반 옵티마이저
개념	사전에 정의된 규칙 기반	최소 비용 계산 실행 계획 수립
기준	실행우선 순위(Ranking)	액세스 비용(Cost)
인덱스	인덱스 존재 시 가장 우선	Cost에 의해 결정
성능	사용자 SQL 작성 숙련도	옵티마이저 예측 성능
장점	판단이 매우 규칙적 실행 예상 가능	통계 정보를 통한 현실 요소 적용
단점	예측 통계정보 요소 무시	최소 성능 보장 계획의 제어 어려움

 

 

---

01. 규칙 기반 옵티마이저 (Rule Base Optimizer, RBO)

• 규칙 기반 옵티마이저는 실행 계획을 수립할 때 15개의 우선 순위를 기준으로 실행 계획을 수립함
• 오라클 10 이후의 최신 버전들에서는 공식적으로 비용 기반 옵티마이저를 사용함

 

옵티마이저 우선 순위

우선 순위	설명
1	ROWID를 사용한 단일 행인 경우
2	클러스터 조인에 의한 단일 행인 경우
3	유일하거나 Primary Key를 가진 해시 클러스터 키에 의한 단일 행인 경우
4	유일하거나 Primary Key에 의한 단일 행인 경우
5	클러스터 조인인 경우
6	해시 클러스터 조인인 경우
7	인덱스 클러스터 키인 경우
8	복합 칼럼 인덱스인 경우
9	단일 칼럼 인덱스인 경우
10	인덱스가 구성된 칼럼에서 제한된 범위를 검색하는 경우
11	인덱스가 구성된 칼럼에서 무제한 범위를 검색하는 경우
12	정렬-병합(Sort Merge) 조인인 경우
13	인덱스가 구성된 칼럼에서 MAX 혹은 MIN을 구하는 경우
14	인덱스가 구성된 칼럼에서 ORDER BY를 실행하는 경우
15	전체 테이블을 스캔(FULL TABLE SCAN)하는 경우

 

🔍 클러스터

• 오라클에서는 서로 연관된 테이블의 데이터를 동일한 데이터 블록 내에 물리적으로 함께 저장할 수 있는 클러스터라는 구조를 제공함
• 연관된 테이블 간의 조인 연산을 할 때 성능 향상을 꾀할 수 있음
• 따라서 동일한 데이터 블록 내에 있는 두 테이블을 조인 연산하는 것을 클러스터 조인이라고 하며 I/O연산을 줄일 수 있기 때문에 조인 연산의 성능을 크게 향상시킴

 

 

---

02. 비용 기반 옵티마이저

• 비용 기반 옵티마이저는 옵티마이저에서 실행 계획을 최대 2천 개까지 세운 뒤 비용이 최소한으로 나온 실행 계획을 수립함
• 비용을 예측하기 위해 규칙 기반 옵티마이저에서는 사용하지 않는, 데이터 딕셔너리의 오브젝트 통계 및 시스템 통계를 사용해 실행 계획을 수립함

 

 

 

---

3. 인덱스 (INDEX)

 

---

01. 인덱스

• 데이터를 빠르게 검색할 수 있는 방법을 제공
• 인덱스 키로 정렬되어 있으므로 원하는 데이터를 빠르게 조회할 수 있음
• 오름차순, 내림차순 탐색이 가능함
• 하나의 테이블에 여러 개의 인덱스를 생성할 수 있고 하나의 인덱스는 여러 개의 칼럼으로 구성될 수 있음
• 테이블을 생성할 때 Primary Key는 자동으로 인덱스가 만들어지며 인덱스의 이름은 SYSXXXX임
• 인덱스는 Root Block, Branch Block, Leaf Block으로 구성되고 Root Block은 인덱스 트리에서 가장 상위에 있는 노드를 의미하며 Branch Block은 다음 단계의 주소를 가지고 있는 포인터로 되어 있음
• Leaf Block은 인덱스 키 + ROWID로 구성되며 인덱스 키는 정렬되어 저장되어 있음

 

 

 

 

---

02. 인덱스 생성

 

CREATE INDEX IND_EMP ON EMP(EMPNO DESC, SALARY DESC);

SELECT /*+ INDEX(EMP IND_EMP) */ * FROM EMP;

 

 

인덱스를 생성할 때는 CREATE INDEX [인덱스 명] ON [테이블 명](칼럼명 오름차순 OR 내림차순)으로 만들 수 있다.

 

 

---

03. 고유 인덱스 생성

• Primary key로 설정한 칼럼에는 자동적으로 고유 인덱스(UNIQUE INDEX)가 만들어진다. 고유 인덱스는 데이터 무결성을 보장하고 애플리케이션 로직을 간소화하는데 사용된다
• PK를 생성할 때 자동적으로 생성되므로 굳이 만들 필요는 없지만 필요에 따라 만들 수 있다

 

CREATE UNIQUE INDEX IND_EMP_2 ON EMP(EMPNO);

SELECT /*+ INDEX(EMP IND_EMP_2) */ * FROM EMP;

 

 

 

---

04. IOT (Indexed Organized Table)

• 기본적으로 테이블은 행 기반 구조로 데이터를 저장하지만 IOT는 인덱스 구조로 데이터를 저장함
• 디스크 공간 사용량은 줄어들며 데이터 접근 속도가 향상될 수 있음
• 데이터와 인덱스를 같은 저장소에 저장함

 

CREATE TABLE iot_test
(
    TESTNO NUMBER(20) PRIMARY KEY,
    TESTNAME VARCHAR2(50)
) ORGANIZATION INDEX;

INSERT INTO iot_test VALUES (1, '고깃집');
INSERT INTO iot_test VALUES (2, '김치만두');

SELECT * FROM iot_test;

 

 

 

이때 일반적인 힙 구조 테이블에서는 TABLE FULL SCAN을 한 것과 달리 인덱스 구조로 데이터를 저장한 IOT에서는 INDEX FAST FULL SCAN이 수행된 것을 알 수 있다.