행위

오라클 서브쿼리 설명 조인 튜닝

DB CAFE

Dbcafe (토론 | 기여)님의 2023년 2월 10일 (금) 00:30 판 (→‎스칼라 서브쿼리 조인의 장단점)
thumb_up 추천메뉴 바로가기

1 서브 쿼리 조인[편집]

1.1 서브 쿼리의 종류[편집]

  1. 인라인 뷰 : FROM 절에 사용한 서브쿼리
  2. (중첩된) 서브쿼리 : WHERE 절에 사용한 서브 쿼리
  3. 스칼라 서브쿼리 : 한 레코드당 정확히 하나의 값을 반환하는 서브쿼리
    1. 주로 SELECT-LIST에서 사용하지만 몇 가지 예외사항을 제외하면 컬럼이 올 수 있는 대부분 위치에 사용할 수 있다.

1.2 필터 오퍼레이션[편집]

  1. 서브쿼리를 필터 방식으로 처리하기 위한 힌트 no_unnest.
  2. 필터 오퍼레이션은 기본적으로 NL 조인과 처리 루틴이 같다.
  3. NL조인과 차이점은 필터는 캐싱기능을 갖는다
    1. 서브쿼리를 수행하기 전에 항상 캐시부터 확인
    2. 필터 서브쿼리는 메인쿼리에 종속되므로 조인 순서가 고정된다. 항상 메인쿼리가 드라이빙 집합이다.

1.3 서브쿼리 Unnesting[편집]

  1. unnest는 풀어서 헤친다는 뜻. 힌트는 /*+ unnest */
  2. 메인쿼리와 서브쿼리가 같은 레벨 구조로 만들어 조인한다.
  3. 필터방식은 항상 메인쿼리가 드라이빙이 되지만, Unnesting된 서브쿼리는 메인 쿼리 집합보다 먼저 처리될 수 있다.
  4. 조인 방식도 다양하게 사용할 수 있다.
    1. nl_sj, nl_aj, hash_sj, hash_aj 등
    2. 서브쿼리에 rownum을 사용하면 Unnesting을 사용하지 못한다.
  5. 스칼라 서브쿼리 Unnesting
    1. 12c부터 스칼라 서브쿼리도 Unnesting이 가능해졌다.


1.4 서브쿼리 Pushing[편집]

  1. Pushing 서브쿼리는 서브쿼리 필터링을 가능한 한 앞 단계에서 처리하도록 강제하는 기능
  2. push_subq/no_push_subq 힌트로 제어
  3. push_subq 힌트는 항상 no_unnest 힌트와 같이 기술
    1. 서브쿼리 FILTER를 먼저: /*+ no_unnest push_subq */
    2. 서브쿼리 FILTER를 나중에: /*+ unnest no_push_subq */

1.5 뷰와 조인[편집]

  1. 최적화 단위가 쿼리 블록이므로 옵티마이저가 뷰 쿼리를 변환하지 않으면 뷰 쿼리 블록을 독립적으로 최적화한다.
  2. 인라인 뷰와 메인쿼리와 머징하려면 merge 힌트를 이용해 뷰를 메인 쿼리와 머징한다.
  3. 뷰 머징을 방지하고자 할 땐 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

1.6 조인조건 Pushdown[편집]

  1. 11g 이후로 조인 조건 Pushdown 이라는 쿼리 변환 기능이 작동한다.
  2. 메인 쿼리를 실행하면서 조인 조건절 값을 건건이 뷰 안으로 밀어 넣는 기능이다.
  3. /*+ no_merge push_pred */ 힌트를 사용한다.
  4. 이 방식을 사용하면 뷰를 독립적으로 실행할 때처럼 부분범위 처리가 가능하다.
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
  1. PUSH_PRED 는 조인 조건 컬럼을 뷰안으로 병합하라고 조언해 주는 힌트, 즉 인라인 뷰 v 의 조건절로 100 = v.manager_id(+) 조건이 들어가도록 변경한 다음에 실행계획을 수립하는 것
  2. 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;
  1. 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.6.1 Lateral 인라인 뷰[편집]

  1. 인라인 뷰 안에서 메인쿼리 테이블 컬럼을 참조하면 에러가 발생한다.
  2. 오라클 12c부터 인라인 뷰를 Lateral로 선언하면, 인라인 뷰 안에서 메인쿼리 테이블 컬럼을 참조할 수 있다.
select * from 학교 s,
   Lateral(select * 
             from 학년 g 
            where g.학교코드 = s.학교코드)

1.6.2 Lateral Outer 조인[편집]

select * from 학교 s,
   Lateral(select *
             from 학년 g 
            where g.학교코드 = s.학교코드)(+)

1.6.3 Outer Apply 조인[편집]

Lateral (+) 와 동일
select * from 학교 s,
   OUTER APPLY(select *
                 from 학년 g where g.학교코드 = s.학교코드)

1.6.4 Cross Apply 조인[편집]

Lateral과 동일
select * from 학교 s,
   CROSS APPLY(select * from 학년 g where g.학교코드 = s.학교코드)

기존에 익숙한 구문으로도 원하는 실행계획을 자유롭게 만들어 낼수 있기 때문에 튜닝 과정에서 알 수 없는 이유로 조인 조건 Pushdown 기능이 잘 작동하지 않을 때만 활용하자.

원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=오라클_서브쿼리_설명_조인_튜닝&oldid=7337"