서브쿼리 조인
1. 인라인 뷰 : FROM 절에 사용한 서브쿼리
2. 중첩된 서브쿼리 : WHERE 절에 사용한 서브 쿼리
3. 스칼라 서브쿼리 : 한 레코드당 정확히 하나의 값을 반환하는 서브쿼리다. 주로 SELECT-LIST에서 사용하지만 몇 가지 예외사항을 제외하면 컬럼이 올 수 있는 대부분 위치에 사용할 수 있다.
필터 오퍼레이션
서브쿼리를 필터 방식으로 처리하기 위한 힌트 no_unnest.
필터 오퍼레이션은 기본적으로 NL 조인과 처리 루틴이 같다.
NL조인과 차이점은 필터는 캐싱기능을 갖는다는 점이다. 서브쿼리를 수행하기 전에 항상 캐시부터 확인한다.
필터 서브쿼리는 메인쿼리에 종속되므로 조인 순서가 고정된다. 항상 메인쿼리가 드라이빙 집합이다.
서브쿼리 Unnesting
힌트는 unnest이다. 메인쿼리와 서브쿼리가 같은 레벨 구조로 만들어 조인한다.
필터방식은 항상 메인쿼리가 드라이빙이 되지만, Unnesting된 서브쿼리는 메인 쿼리 집합보다 먼저 처리될 수 있다.
조인 방식도 다양하게 사용할 수 있다. nl_sj, hash_sj 등
서브쿼리에 rownum을 사용하면 Unnesting을 사용하지 못한다.
예) mainquery 힌트: /*+leading(subquery_table@subq) use_nl(main_table) */
subquery 힌트: /*+ qb_name(subq) unnest */
예2) mainquery 힌트: /*+no_merge(subquery_alias) leading(subquery_alias) use_nl(main_table)*/
subquery 힌트 없음
예3) mainquery 힌트 없음
subquery 힌트: /*+ unnest hash_sj */
서브쿼리 Pushing
Pushing 서브쿼리는 서브쿼리 필터링을 가능한 한 앞 단계에서 처리하도록 강제하는 기능이며, push_subq/no_push_subq 힌트로 제어한다. push_subq 힌트는 항상 no_unnest 힌트와 같이 기술하는 것이 올바른 사용법이다.
서브쿼리 FILTER를 먼저: no_unnest push_subq
서브쿼리 FILTER를 나중에: unnest no_push_subq
예) mainquery 힌트: /*+leading(maintable1) use_nl(maintable2) */
subquery 힌트: /*+ NO_UNNEST PUSH_SUBQ */
뷰와 조인
최적화 단위가 쿼리 블록이므로 옵티마이저가 뷰 쿼리를 변환하지 않으면 뷰 쿼리 블록을 독립적으로 최적화한다. 인라인 뷰와 메인쿼리와 머징하려면 merge 힌트를 이용해 뷰를 메인 쿼리와 머징한다. 뷰 머징을 방지하고자 할 땐 no_merge 힌트를 사용한다.
select t.coulumn1, t.column2
from table t,
(
select /*+merge*/ s.col1, s.col2
from subquery s
where 일시 > sysdate-3
)
where t.id = s.main_id조인조건 Pushdown
11g 이후로 조인 조건 Pushdown 이라는 쿼리 변환 기능이 작동한다. 메인 쿼리를 실행하면서 조인 조건절 값을 건건이 뷰 안으로 밀어 넣는 기능이다. /*+ no_merge push_pred */ 힌트를 사용한다. 이 방식을 사용하면 뷰를 독립적으로 실행할 때처럼 부분범위 처리가 가능하다.
select t.column1, t.column2, ss.col1, ss.col2
from table t
,(
select /*+no_merge push_pred*/ s.col1, s.col2
from subquery s
where ...
)ss
where t.id = ss.col1Lateral 인라인 뷰
인라인 뷰 안에서 메인쿼리 테이블 컬럼을 참조하면 에러가 발생한다. 오라클 12c부터 인라인 뷰를
Lateral로 선언하면, 인라인 뷰 안에서 메인쿼리 테이블 컬럼을 참조할 수 있다.
select * from 학교 s,
Lateral(select * from 학년 g where g.학교코드 = s.학교코드)
Outer 조인
select * from 학교 s,
Lateral(select * from 학년 g where g.학교코드 = s.학교코드)(+)
Outer Apply 조인
select * from 학교 s,
OUTER APPLY(select * from 학년 g where g.학교코드 = s.학교코드)
Cross Apply 조인 : Lateral과 동일
select * from 학교 s,
CROSS APPLY(select * from 학년 g where g.학교코드 = s.학교코드)
기존에 익숙한 구문으로도 원하는 실행계획을 자유롭게 만들어 낼수 있기 때문에 튜닝 과정에서
알 수 없는 이유로 조인 조건 Pushdown 기능이 잘 작동하지 않을 때만 활용하자.
스칼라 서브쿼리 조인
함수를 사용하는 쿼리를 실행하면, 함수 안에 있는 SELECT 쿼리를 메인쿼리 건수만큼 재귀적으로 반복 실행된다. 스칼라뷰로 실행하는 경우는 함수와 비슷해 보이지만 함수처럼 재귀적으로 실행하는 구조는 아니다. 컨텍스트 스위칭 없이 메인쿼리와 서브쿼리를 한 몸체처럼 실행한다. Outer 조인문처럼 NL 조인 방식으로 실행된다.
스칼라 서브쿼리 캐싱효과
스칼라 서브쿼리로 조인하면 오라클은 조인 횟수를 최소화하려고 입력 값과 출력 값을 내부캐시에 저장해 둔다. 조인할 때 마다 일단 캐시에서 입력 값을 찾아보고, 찾으면 저장된 출력 값을 반환한다. 캐시에서 찾지 못할 때만 조인을 수행하며, 결과는 버리지 않고 캐시에 저장해 둔다.
함수의 경우도 스칼라 서브쿼리를 덧쒸우면 (select 함수 froim dual) 캐싱 효과를 낼 수 있다.
스칼라 서브쿼리 캐싱 부작용
스칼라 서브쿼리 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 적을 때 효과가 있다. 캐싱된 데이터가 매번 없다면 불필요한 캐싱 탐색 때문에 일반 조인문보다 느리고 불필요하게 자원만 낭비하는 셈이다.
메인 쿼리 집합이 매우 작은 경우도 캐싱 성능에 도움을 주지 못한다. 메인쿼리 집합이 적으면 캐시 재사용성도 낮다.
두 개 이상의 값 반환
1)문자열로 결합하고 바깥쪽 액세스 쿼리에서 SUBSTR 함수로 분리
2)오브젝트 TYPE을 사용, TYPE을 미리 선언해 두어야 하는 불편함 때문에 잘 쓰이지 않는다.
3)인라인 뷰 사용. 11g 이후 조인 조건 Pushdown 기능이 있어 인라인 뷰를 마음 편히 사용할 수 있게 되었다.
스칼라 서브쿼리 Unnesting
12c부터 스칼라 서브쿼리도 Unnesting이 가능해졌다.