SQL) 쿼리 변환 Ⅲ

jinsung·2026년 1월 8일

SQLP oracle sql 데이터 자격증

SQL

목록 보기

39/46

9. Outer 조인을 Inner 조인으로 변환

Outer 조인문을 작성하면서 일부 조건절에 Outer 기호(+)를 빠뜨리면 Inner 조인할 때와 같은 결과가 나온다.
이럴 때 옵티마이저는 Outer 조인을 Inner 조인문으로 바꾸는 쿼리 변환을 시행한다.

옵티마이저가 굳이 이런 쿼리 변환을 시행하는 이유는 조인 순서를 자유롭게 결정하기 위해서다.
=>Outer 조인 시 Outer 테이블은 (+) 기호가 없는 테이블로 고정됐었기 때문이다.

Outer 조인을 써야 하는 상황이라면 Outer 기호를 정확히 구사해야 올바른 결과집합을 얻을 수 있다.

10. 실체화 뷰 쿼리로 재작성

뷰는 쿼리만 저장하고 있을 뿐 자체적으로 데이터를 갖지는 않는다.
반면, 실체화 뷰(Materialized View = MV)는 물리적으로 실제 데이터를 갖는다.

MV는 과거에 분산 환경에서 실시간 또는 일정 주기로 데이터를 복제하는 데 사용하던 Snapshot 기술을 DW 분야에 적응시킨 것이며, 여전히 데이터 복제 용도로 사용할 수 있다.

MV를 활용하는 이유는 기준 테이블이 그만큼 대용량이기 때문인데, Join View는 같은 데이터를 중복으로 저장하는 비효율이 있어 활용도가 낮고, 주로 Aggregate View 형태로 활용되는 편이다.

✅ MV의 두 가지 특징

Refresh 옵션을 통해서 오라클이 집계 데이터를 자동 관리하도록 할 수 있다.
옵티마이저에 의한 Query Rewrite가 지원된다.

MV의 가장 큰 장점은, 자동으로 쿼리가 재작성된다는 것이다.
MV는 사용자가 집계 테이블의 존재를 몰라도, 옵티마이저가 알아서 MV를 액세스하도록 쿼리를 변환해 준다.

쿼리 재작성 기능이 작동하려면 MV를 정의할 때 enable query rewrite 옵션을 주어야 하고, 세션이나 시스템 레벨에서 파라미터도 변경해 주어야 한다.
9i는 기본 값이 false, 10g 부터는 true 이다.

alter session set query_rewrite_enalbed = true;

11. 집합 연산을 조인으로 변환

intersect, minus 같은 집합 연산을 조인 형태로 변환하는 것을 말한다.

_convert_set_to_join 파라미터를 true로 설정하면 조인으로 변환된다.

12. 기타 쿼리 변환

1. 조인 컬럼에 IS NOT NULL 조건 추가

조인 시 NULL 값은 조인에 실패하기 때문에 is not null 로 필터 조건을 추가해 불필요한 테이블 액세스 및 조인 시도를 줄일 수 있어 쿼리 성능 향상에 도움이 된다.

=> 만약 컬럼 통계를 수집하고 조인 시, 조인 컬럼의 null 값 비중이 5% 이상일 때 옵티마이저가 is not null 조건절을 자동으로 생성해 준다.

이처럼 조인 컬럼에 is not null 조건을 추가해 주면 NL 조인뿐만 아니라 해시 조인, 소트 머지 조인 시에도 효과를 발휘한다.

조인 컬럼에 대한 is not null 조건을 추가한다고 손해 볼 일은 전혀 없지만, 옵타마이저는 null 값 비중이 5%를 넘을때만 이런 쿼리 변환을 시행한다.
따라서 필요하다면 옵티마이저 기능에 의존하지 말고 사용자가 직접 is not null 조건을 추가해줌으로써 불필요한 액세스를 줄일 수 있다.

그리고 조인 컬럼에 만약 null 값이 많을 때, 임의의 default 값으로 채우는 방식으로 설계하면 조인 성능을 떨어뜨릴 수 있다.

2. 필터 조건 추가

select *
from emp
where sal between :mn and :mx;

만약 :mx 값보다 :mn 값이 큰 경우 쿼리 결과는 공집합이다.
이 경우 8i에서는 한참 기다렸어야 결과가 나왔지만 9i부터는 이를 방지하기 위해 옵티마이저가 임의로 필터 조건식을 추가한다.

1 - filter (TO_NUMBER(:MN) <= TO_NUMBER(:MX))

또한 실행계획 상에는 emp 테이블을 Full Scan 하고 나서 필터 처리가 일어나는 것 같지만 실제로는 Table Full Scan 자체를 생략해 버린다.

3. 조건절 비교 순서

select *
from jy
where y = 2
and j = 99;

만약 만약 y컬럼의 2 비중이 크고, j 컬럼의 99 비중이 적을 때는 적은 컬럼부터 조건식을 먼저 평가하는 것이 유리하다.
=> 대부분의 레코드가 j = 99 의 조건을 만족하지 않아 y컬럼의 비교 연산을 수행하지 않아도 되기 때문이다.

select /*+ full(도서) */ 도서번호, 도서명, 가격, 저자, 출판사, isbn
from 도서
where 도서명 > :last_book_nm
and 도서명 like :book_nm || '%';

위의 조건절을 처리할 때도 부등호(>)를 먼저 처리하느냐 like 연산을 먼저 처리하느냐에 따라 일량의 차이가 생긴다.

옵티마이저는, 테이블 전체를 스캔하거나 인덱스를 수평적으로 스캔할 때의 필터 조건식을 평가할 때 선택도가 낮은 컬럼을 먼저 처리하도록 순서를 조정한다.

이런 쿼리 변환이 작동하려면 9i, 10g를 불문하고 옵티마이저에게 시스템 통계를 제공함으로써 CPU 비용 모델을 활성화해야 한다.
I/O 비용 모델에서는 where절에 기술한 순서대로 조건식 비교가 일어난다.
RBO 모드는 where절에 기술한 순서 반대로 조건식 비교가 일어난다.

order_predicates 힌트를 사용하면 CPU 비용 모델에서도 조건절 비교 순서를 제어할 수 있다.
이 힌트를 사용하면 where절에 기술한 순서대로 조건식 비교가 일어난다.