오라클 서브쿼리 설명 조인 튜닝
DB CAFE
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 서브 쿼리 조인[편집]
- 인라인 뷰 : FROM 절에 사용한 서브쿼리
- 중첩된 서브쿼리 : WHERE 절에 사용한 서브 쿼리
- 스칼라 서브쿼리 : 한 레코드당 정확히 하나의 값을 반환하는 서브쿼리다. 주로 SELECT-LIST에서 사용하지만 몇 가지 예외사항을 제외하면 컬럼이 올 수 있는 대부분 위치에 사용할 수 있다.
1.1 필터 오퍼레이션[편집]
- 서브쿼리를 필터 방식으로 처리하기 위한 힌트 no_unnest.
- 필터 오퍼레이션은 기본적으로 NL 조인과 처리 루틴이 같다.
- NL조인과 차이점은 필터는 캐싱기능을 갖는다
- 서브쿼리를 수행하기 전에 항상 캐시부터 확인
- 필터 서브쿼리는 메인쿼리에 종속되므로 조인 순서가 고정된다. 항상 메인쿼리가 드라이빙 집합이다.
1.2 서브쿼리 Unnesting[편집]
- unnest는 풀어서 헤친다는 뜻. 힌트는 /*+ unnest */
- 메인쿼리와 서브쿼리가 같은 레벨 구조로 만들어 조인한다.
- 필터방식은 항상 메인쿼리가 드라이빙이 되지만, Unnesting된 서브쿼리는 메인 쿼리 집합보다 먼저 처리될 수 있다.
- /*+ push_subq */
- 조인 방식도 다양하게 사용할 수 있다.
- nl_sj, hash_sj 등
- 서브쿼리에 rownum을 사용하면 Unnesting을 사용하지 못한다.
1.3 서브쿼리 Pushing[편집]
- Pushing 서브쿼리는 서브쿼리 필터링을 가능한 한 앞 단계에서 처리하도록 강제하는 기능
- push_subq/no_push_subq 힌트로 제어
- push_subq 힌트는 항상 no_unnest 힌트와 같이 기술
- 서브쿼리 FILTER를 먼저: no_unnest push_subq
- 서브쿼리 FILTER를 나중에: unnest no_push_subq
1.4 뷰와 조인[편집]
- 최적화 단위가 쿼리 블록이므로 옵티마이저가 뷰 쿼리를 변환하지 않으면 뷰 쿼리 블록을 독립적으로 최적화한다.
- 인라인 뷰와 메인쿼리와 머징하려면 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_id1.5 조인조건 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.col1- PUSH_PRED 는 조인 조건 컬럼을 뷰안으로 병합하라고 조언해 주는 힌트, 즉 인라인 뷰 v 의 조건절로 100 = v.manager_id(+) 조건이 들어가도록 변경한 다음에 실행계획을 수립하는 것
- NO_MERGE는 메인쿼리와 인라인뷰가 합쳐지는 것 , 즉 병합을 하지말고 인라인 뷰 먼저 실행 하라.
SELECT /*+ NO_MERGE(v) PUSH_PRED(v) */
*
FROM employees e,
(SELECT manager_id
FROM employees
) v
WHERE e.manager_id = v.manager_id(+)
AND e.employee_id = 100;- NO_MERGE는 아래와 같이 인라인 뷰가 먼저 실행되게 하고 싶을 때도 사용하고, 아래와 같을 때 함수를 한번만 호출하도록
유도할때에도 사용되어 집니다.
/* FN_GetGbn() 호출이 DECODE 갯수만큼 더 실행 됨 */
SELECT SUM(DECODE(GBN, 1, amt, null)) gbn1_amt
,SUM(DECODE(GBN, 2, amt, null)) gbn2_amt
,SUM(DECODE(GBN, 3, amt, null)) gbn3_amt
FROM
(
SELECT AMT, FN_GetGbn(code)
FROM 테이블
)==> 튜닝 조차
/* FN_GetGbn() 호출이 한번만 이루어 짐 */
SELECT SUM(DECODE(GBN, 1, amt, null)) gbn1_amt
,SUM(DECODE(GBN, 2, amt, null)) gbn2_amt
,SUM(DECODE(GBN, 3, amt, null)) gbn3_amt
FROM
(
SELECT /*+ NO_MERGE */ AMT, FN_GetGbn(code)
FROM 테이블
)1.5.1 Lateral 인라인 뷰[편집]
인라인 뷰 안에서 메인쿼리 테이블 컬럼을 참조하면 에러가 발생한다. 오라클 12c부터 인라인 뷰를 Lateral로 선언하면, 인라인 뷰 안에서 메인쿼리 테이블 컬럼을 참조할 수 있다.
select * from 학교 s,
Lateral(select *
from 학년 g
where g.학교코드 = s.학교코드)1.5.2 Outer 조인[편집]
select * from 학교 s,
Lateral(select *
from 학년 g
where g.학교코드 = s.학교코드)(+)1.5.3 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이 가능해졌다.