행위

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

DB CAFE

(서브쿼리 조인에서 넘어옴)
notifications_active 데이터베이스 전문기업 안내

1 서브 쿼리 조인[편집]

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

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


1.2 스칼라 서브쿼리 조인의 장단점[편집]

1.2.1 장점[편집]

  1. 함수를 사용하는 쿼리를 실행하면, 함수 안에 있는 SELECT 쿼리를 메인쿼리 건수만큼 재귀적으로 반복 실행된다.
  2. 스칼라뷰로 실행하는 경우는 함수와 비슷해 보이지만 함수처럼 재귀적으로 실행하는 구조는 아니다.
  3. 컨텍스트 스위칭 없이 메인쿼리와 서브쿼리를 한 몸체처럼 실행한다.
  4. Outer 조인문처럼 NL 조인 방식으로 실행된다.
  5. 스칼라 서브쿼리 캐싱효과
    1. 스칼라 서브쿼리로 조인하면 오라클은 조인 횟수를 최소화하려고 입력 값과 출력 값을 내부캐시에 저장해 둔다.
    2. 조인할 때 마다 일단 캐시에서 입력 값을 찾아보고, 찾으면 저장된 출력 값을 반환한다.
    3. 캐시에서 찾지 못할 때만 조인을 수행하며, 결과는 버리지 않고 캐시에 저장해 둔다.
    4. (사용자)함수의 경우도 스칼라 서브쿼리로(select 함수 from dual) 캐싱 효과 를 낼 수 있다. 중복 처리건이 많을수록 캐싱효과가 뛰어남.

1.2.2 단점[편집]

  1. 스칼라 서브쿼리 캐싱 부작용
    1. 스칼라 서브쿼리 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 적을 때 효과가 있다.
    2. 캐싱된 데이터가 매번 없다면 불필요한 캐싱 탐색 때문에 일반 조인문보다 느리고 불필요하게 자원만 낭비하는 셈이다.
    3. 메인 쿼리 집합이 매우 작은 경우도 캐싱 성능에 도움을 주지 못한다. 메인쿼리 집합이 적으면 캐시 재사용성도 낮다.
  2. 두 개 이상의 값 반환
    1. 문자열로 결합하고 바깥쪽 액세스 쿼리에서 SUBSTR 함수로 분리
    2. 오브젝트 TYPE을 사용, TYPE을 미리 선언해 두어야 하는 불편함 때문에 잘 쓰이지 않는다.

1.3 스칼라 서브쿼리 성능 개선 사항[편집]

  1. 서브쿼리 안에서 rownum 가상컬럼 사용시 캐싱 효과가 사라짐

1.4 필터 오퍼레이션 no_unnest[편집]

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

1.5 서브쿼리 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.6 서브쿼리 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.7 상관 관계 서브쿼리[편집]

  1. N : 1 조인시 1인 테이블과 조인시 distinct된 값의 종류가 적을때 서브쿼리 캐싱효과, 버퍼피닝 효과 극대화
  2. N : 1 조인시 N인 테이블이 서브쿼리가 되면 유리한 경우
    1. JPPD(Join Push Predicate) 조인컬럼 조건이 N쪽 서브쿼리 안으로 미리 들어가 조인의 범위를 줄여주어 성능을 개선하는 기능

1.8 비 상관 관계 서브쿼리[편집]

  1. 서브쿼리가 먼저수행 되고 메인쿼리가 후 수행
  2. 서브쿼리 결과가 메인쿼리에 연결되는 컬럼에 인덱스가 존재해야 성능개선에 효과적임.

1.9 뷰와 조인[편집]

  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.10 조인조건 Pushdown (JPPD)[편집]

  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.10.1 Lateral 인라인 뷰[편집]

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

1.10.2 Lateral Outer 조인[편집]

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

1.10.3 Outer Apply 조인[편집]

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

1.10.4 Cross Apply 조인[편집]

Lateral과 동일
select * from 학교 s,
   CROSS APPLY(select * from 학년 g where g.학교코드 = s.학교코드)
  • 기존에 익숙한 구문으로도 원하는 실행계획을 자유롭게 만들어 낼수 있기 때문에 튜닝 과정에서 알 수 없는 이유로 조인 조건 Pushdown 기능이 잘 작동하지 않을 때만 활용하자.
  • (+) 를 같이 사용해야 하는 지 꼭 확인해야 한다. 정합성이 맞지 않을수 있다. 스칼라 서브쿼리는 lateral로 변경하고자 한다면 반드시 (+)를 추가해야 한다.
원본 주소 "http://dbcafe.co.kr/wiki/index.php?title=오라클_서브쿼리_설명_조인_튜닝&oldid=10004"