[Oracle] 서브 쿼리에 대해서

보통 자주 사용하는 조인 방식과 다르게 서브 쿼리를 통해 얻는 이점이 있다.

메인 쿼리에서 반환되는 데이터의 양이 적은 경우, 후행 쿼리(=서브쿼리)의 조회 연산이 줄어들 수 있고

만약 서브 쿼리의 input 값이 어느정도 일정한 경우, caching이 되기 때문이다. (Filter Operation)

 

펀드 상품 기준가 쿼리 작성시에 성능 이점을 볼 수 있었다.

특정 기간에 가입한 상품을 찾고 당시의 상품 기준가와 가입 금액을 반환하는 쿼리였다.

1-계좌 테이블, 2-상품 테이블, 3-기준가 테이블이 서로 조인되어 있었다.

Nested Loop Join의 경우 같은 값을 가지는 칼럼에 대해 반복 조회를 수행하기 때문에 N x N x N번 수행하는 것으로 보였다.

 

하지만 펀드 상품의 경우, 같은 날에는 상품별로 하나의 기준가만 등록된다.

여러 명의 고객이 같은 상품을 가입해도 상품 기준가를 고객별로 반복해서 N번 조회할 필요가 없다.

어차피 상품별 기준가는 같은 값일테니 한 번만 조회하는 방법이 없을까 고민을 했었다.

그러던 와중 조인 대신 서브쿼리(scalar)를 이용하면 캐싱이 가능하다는 것을 알고 쿼리를 수정했더니 N x N x 1로 수행되었다.

 

이처럼, 후행 테이블 조회 연산에 대해 일정한 값이 기대되는 경우 서브쿼리를 사용하면 캐싱 처리를 수행하기 때문에 효과적이다.

또한, 선행 테이블 조회(메인쿼리)에서 추출된 데이터가 적은 경우, 조인과 같은 교차 연산이 불필요하기 때문에 효과적으로 볼 수 있다.

두번째 사례에 대한 경험은 해보지 못했다.

하지만 서적에서는 두번째 사례인 경우에 조인을 서브쿼리로 변환해보는 것을 시도하라고 한다. 두번째 사례가 좀 더 원칙적인 방향이고, 첫번째 사례는 부수적 효과로 볼 수 있다.

나의 경우, 특수한 상황일 때의 보완책으로 서브쿼리를 사용해서 성능의 이점을 본 것이다.

 

한편, NO_UNNEST 힌트를 줘서 명시적으로 조인 대신 메인쿼리 > 서브쿼리를 주는 방법이 있다.

Plan을 확인했을 때, TABLE ACCESS 이후에 FILTER가 명시되어 있으면 의도대로 수행된 것이다.

만약, 메인 쿼리에서 추출한 데이터 건수가 많다고 하면 서브 쿼리에서 반복 수행되므로 서브 쿼리의 칼럼에 인덱스가 필요하다.

 

메인쿼리에서 추출된 데이터 건수가 너무나도 많다고 하면 조인 방식이 효과적일 수 있다. 

이 경우에는 UNNEST 힌트를 줘야 한다.

 

위에서 언급했던 서브쿼리는 SELECT절, WHERE절에 해당한다.

FROM절에 서브쿼리를 작성하는 경우 인라인 뷰 형태로 구성이 된다.

하지만 이 경우에도 옵티마이저가 두 개의 테이블로 조인 연산을 수행할 수 있다.

앞서 언급한 서브쿼리 방식으로 동작하기 위해서는 NO_MERGE 힌트를 준다.