공정쿼리

공정쿼리란 ?

공정쿼리란 무엇을 조회할지에 대한 결과뿐 아니라 어떻게 조회할지에 대한 쿼리 과정까지 염두에 두고 접근하는 방식을 말한다. 무엇을 어떻게 조회할지에 대한 내용을 모두 포함해서 쿼리를 작성하므로 공정쿼리로 작성한 쿼리에서는 쿼리 결과뿐 아니라, 생성해야 할 인덱스 정보와 접근되어야 하는 실행계획 정보까지 모두 알 수 있다.

 

1.2 오라클 CBO 방식과 통계정보

오라클 CBO(Cost Based Optimizer) 방식은 통계정보를 이용해서 비용을 계산한 후 SQL을 수행한다. 통계정보가 정확하다면 오라클은 대부분의 쿼리에서 올바르고 적절한 실행계획을 우리에게 보여주지만, 존재하지 않다면 정확한 실행계획을 제공할 수 없다. 오라클 CBO 방식에서 비용이란 물리적인 비용이 아니라 논리적인 비용을 말한다. 

 

1.3 테이블 접근 순서 규칙

1.3.1 진입형 테이블을 결정하라(사원 테이블 선택 접근 시)

쿼리의 조건 중에서 가장 선택도가 좋은 컬럼의 테이블을 최초 진입형 테이블로 결정한다.

SELECT *
FROM 사원, 교육계획, 과목, 강사, 교육평가
WHERE 사원.사원번호 = 교육계획.사원번호
AND 교육계획.과목번호 = 과목.과목번호
AND 교육계획.강사번호 = 강사.강사번호(+)
AND 교육계획.교육번호 = 교육평가.교육번호
AND 사원.성명 = '홍길동'
AND 과목.과목명 = '수학'
AND 과목평가.평점 = 'A+'

위 쿼리에서 사원 테이블의 성명 컬럼을 최초 진입형 조건으로 결정 했을 때 인덱스 생성도는 다음과 같다.

사원 -> 교육계획 -> ??? -> ??? -> ???

 

1.3.2 OUTER JOIN 보다 INNER JOIN을 우선하라

쿼리의 조인절에서 OUTER JOIN 보다 INNTER JOIN 테이블을 먼저 접근한다.

사원 -> 교육계획 -> ??? -> ??? -> 강사

 

1.3.3 연결 확장형보다는 연결 축소형 테이블을 우선하라

쿼리의 조인절에서 연결 확장형보다 연결 축소형 테이블을 먼저 접근한다. 위 쿼리에서는 과목과 교육평가 테이블 중에서 조건(A+) 필터로 인해 레코드 축소가 일어날 가능성이 더 높은 교육평가  테이블의 접근을 우선으로 한다.

사원 -> 교육계획 -> 교육평가 -> 과목 -> 강사

1.4 인덱스 생성도와 공정쿼리 재작성하기

인덱스 생성도를 통해서 테이블 접근 순서와 각 테이블의 인덱스 생성 포인트를 알 수 있다. 위의 인덱스 최종본을 통해 공정쿼리를 재작성해 보면 다음과 같다.

SELECT *
FROM 사원, 교육계획, 교육평가, 과목, 강사    --------- 테이블 접근 순서대로 나열
WHERE 사원.사원번호 = 교육계획.사원번호      --------- 조인절 우측편에 인덱스 생성
AND 교육계획.교육번호 = 교육평가.교육번호    --------- 조인절 우측편에 인덱스 생성
AND 교육계획.과목번호 = 과목.과목번호        --------- 조인절 우측편에 인덱스 생성
AND 교육계획.강사번호 = 강사.강사번호(+)     --------- 조인절 우측편에 인덱스 생성
AND 사원.성명 = '홍길동'                     --------- 첫 번째 조건절에 인덱스 생성
AND 과목.과목명 = '수학'
AND 과목평가.평점 = 'A+'

 

* 스토리 DB 문제

아래 쿼리를 보고 공정쿼리와 인덱스 생성도를 그려봐라.

SELECT *
FROM 고객, 주문
WHERE 주문.고객번호 = 고객.고객번호(+)
AND 고객.고객명(+) = ?
AND 주문.주문일자 BETWEEN ? AND ?
ORDER BY 주문.주문일자, 주문.상품코드

 

- ANSWER

1. 인덱스구성도

 

2. 공정쿼리

SELECT *
FROM 주문, 고객
WHERE 주문.고객번호 = 고객.고객번호(+)
AND 주문.주문일자 BETWEEN ? AND ?
AND 고객.고객명(+) = ?
ORDER BY 주문.주문일자, 주문.상품코드

 

 

---

2. 만능 쿼리와 한방 쿼리

2.1 정적쿼리와 동적쿼리

정적쿼리란 조건들이 고정되어 있으며 실행계획도 고정되어 있으므로 성능 문제 발생 시 조치와 대응이 쉬운 쿼리이다. 이에 반해 동적 쿼리는 애플리케이션 소스상에서 사용할 조건들을 가변적으로 구성하는 쿼리를 말한다. 

 

2.2 동적 쿼리와 만능 쿼리에 대한 예제

-- 동적 쿼리 구문
SELECT *
FROM 고객정보
WHERE 1 = 1
IF ? THEN ( AND 고객아이디 = ? ) END IF
IF ? THEN ( AND 주민등록번호 = ? ) END IF


-- 만능 쿼리 구문
SELECT *
FROM 고객정보
WHERE 고객아이디 LIKE ?
AND 주민등록번호 LIKE ?

위 예제에서는 2가지 조건절이 있지만, N가지 조건절이 있다고 가정했을 경우에는 옵티마이저가 2^N가지의 모든 쿼리에 대해 실행계획을 제공하지만, 모든 실행계획의 성능을 보장하지는 못한다. 이에 더해 만능 쿼리는 동적 쿼리보다 더 심각한데, 만능 쿼리는 조건절이 가변적이지가 않아서 옵티마이저에게는 하나의 쿼리로 인식이 되고, 하나의 실행계획만 제공하는데, 조건값에 따라서는 실제로 2^N 가지의 쿼리로 구분될 수 있지만 오직 하나의 실행계획만으로 실행됨으로써 심각한 문제를 초래할 수 있다. 따라서 만능 쿼리는 사용하지 않는게 좋다.

---

3. 오라클 옵티마이저 CBO와 RBO 이해하기

3.1 CBO 방식과 RBO 방식

CBO 방식은 비용 기반 옵티마이저이고, RBO 방식은 규칙 기반 옵티마이저이다. 오라클 11g부터 RBO에 대한 기술 지원이 중단되어서 현재는 CBO와 비교를 논하기에는 무의미하지만, 지나간 기술을 되짚어 보는 것도 현재의 기술을 이해하는 데 도움이 되므로 간단한 개념을 알아두는 것도 좋다.

RBO 방식은 규칙 기반 옵티마이저로 미리 정해진 우선 순위 규칙에 따라 접근 경로를 결정한다. 순위가 높은 규칙이 순위가 낮은 규칙보다 우선 적용된다. 비록 잘못된 우선 순위의 규칙이 적용되더라도 예측이 가능하며, 안정적이고 실행계획의 제어가 쉬운 장점이 있다. 반면 CBO 방식은 비용 기반 옵티마이저로서 통계정보에 따른 비용을 계산해 가장 최소한의 비용이 소모되는 접근 경로를 결정한다. (I/O 뿐 아니라 CPU 연산 및 비용 및 메모리 비용 포함) 여기서 비용은 논리적 비용을 말한다. 

3.2 CBO 방식 : 옵티마이저와 통계정보, 실행계획

CBO 방식은 옵티마이저와 통계정보, 실행계획에 대해 알아야 한다. 옵티마이저는 사용자가 요청한 SQL을 가장 효율적이고 빠르게 수행할 수 있는 최적의 처리 경로를 제공하는 오라클 엔진이다. 옵티마이저는 주어진 환경(통계정보, SQL)을 통해 최적의 실행계획을 우리에게 제공한다. (항상 그런것은 아니다. 그럴 경우 힌트절을 통해 바로 잡을 수 있음)

옵티마이저가 올바른 판단을 할 수 있도록 다음과 같은 부분을 올바르게 제공하거나 제어해야 한다.

- 최적의 인덱스 구성

- 올바른 SQL문 작성

- 주기적으로 최신의 통계정보로 갱신

- 힌트절을 추가해 옵티마이저 제어

 

* 스토리 DB 문제

다음에 나열된 쿼리 중에서 소트가 발생하지 않는 쿼리를 모두 고르시오. (결합인덱스 A + B + C + D로 구성되어 있다.)

 

1) SELECT * FROM 테이블 WHERE A = ? AND B = ? ORDER BY D -> 3번

2) SELECT * FROM 테이블 WHERE A = ? AND C = ? ORDER BY B 

3) SELECT * FROM 테이블 WHERE A = ? AND B = ? ORDER BY D, C -> 2번

4) SELECT * FROM 테이블 WHERE A = ? AND C = ? ORDER BY B, D

5) SELECT * FROM 테이블

6) SELECT * FROM 테이블 WHERE ORDER BY A, B, C, D -> 1번

7) SELECT * FROM 테이블 WHERE A = ? AND B = ? ORDER BY C

8) SELECT * FROM 테이블 WHERE C = ? AND D = ? ORDER BY A, B -> 1번

9) SELECT * FROM 테이블 WHERE A = ? AND B BETWEEN ? AND ? ORDER BY C, D -> 3번

 

ANSWER 

 

소트가 발생하는 경우는 다음 3가지로 요약할 수 있다.

1. ORDER BY 절이 있으면서 인덱스를 타지 않는 경우

2. ORDER BY 절의 컬럼 위치와 결합인덱스의 컬럼 위치가 뒤바뀐 경우

3. 결합인덱스 컬럼에서 Equal 조건절 컬럼을 제거하고, ORDER BY 절의 컬럼을 제거한 후 남은 컬럼이 결합인덱스의 중간 컬럼일 경우

---

참고

http://www.yes24.com/Product/Goods/61583758

개발자를 위한 인덱스 생성과 SQL 작성 노하우 - YES24