[SQL튜닝/인덱스와 조인] 조인2 (Outer 조인, 스칼라 서브쿼리 조인, 조인을 내포한 DML 튜닝, 수정 가능 조인 뷰, bypass_ujvc, MERGE)

🖥️ 들어가며

📌 조인을 내포한 DML 튜닝
: Oracle 11g 이후로는 bypass_ujvc 힌트 대신 MERGE문을 사용해 조인 포함 업데이트를 수행하는 것이 권장됩니다.

 

---

 

✏️ 1. Outer 조인

• 기본 매커니즘
  • NL조인은 특성상 Outer 조인할 때 방향이 한 쪽으로 고정됩니다.
  • Outer 기호(+)가 붙지 않는 테이블 = (+)의 반대쪽을 드라이빙 테이블로 선택합니다.
    (D.DEPTNO(+) = E.DEPTNO 이라면 E.DEPTNO가 드라이빙 테이블입니다. Right Outer Join)
  • Leading 힌트를 사용하더라도 순서를 바꿀 수 없습니다. (논리적 모순)
• Outer Hash 조인
  • EMP 테이블과 DEPT 테이블을 Outer Hash Join으로 결합하는 예시입니다.
  • Build 테이블: EMP
  • 실행 순서
    • EMP 테이블을 읽어 Hash 테이블로 만듦 → EMP가 Outer 테이블이므로, 모든 행이 Hash 테이블에 저장
    • DEPT 테이블(Inner 테이블)을 순차적으로 읽음
    • 각 DEPT 행에 대해 EMP Hash 테이블을 검색하여 조인 조건에 만족하는 행을 찾음
    • 조인에 성공한 행 → 결과 집합에 추가, 해당 EMP 행에 조인 성공 표시
    • DEPT 테이블 처리가 끝나면, EMP Hash 테이블에서 조인 실패한 행들을 찾음(= 조인 성공 표시가 없는 행들)
    • 조인에 실패한 EMP 행들 → 결과 집합에 추가, 이때 DEPT 컬럼들은 Null로 채워짐

SELECT /*+ LEADING(E) USE_HASH(D) */ *
FROM   EMP E, DEPT D
WHERE  D.DEPTNO(+) = E.DEPTNO;

 

• Right Outer 조인
  • 일반적으로 Right Outer 조인에서는 오른쪽 테이블이 Outer 집합이 되어야 하지만,
    SWAP_JOIN_INPUTS 힌트를 사용하여 DEPT를 Inner 집합으로 만들고 Hash 테이블로 사용할 수 있습니다.
  • Build 테이블: DEPT
  • 실행 순서
    • SWAP_JOIN_INPUTS(D) → DEPT 테이블을 읽어 Hash 테이블로 만듦(Build Input), Inner 집합
    • 그 다음 EMP 테이블(Outer 집합)을 읽으면서 DEPT의 Hash 테이블 탐색
    • 조인 조건이 만족하지 않더라도 EMP의 모든 행은 결과에 포함

 SELECT /*+ LEADING(D) USE_HASH(E) SWAP_JOIN_INPUTS(D) */ *
 FROM   EMP E, DEPT D
 WHERE  D.DEPTNO(+) = E.DEPTNO;

 

 

✏️ 2. 스칼라 서브쿼리를 이용한 조인

• 스칼라 서브쿼리
  • 정의 
    • 함수처럼 한 레코드당 정확히 하나의 값만 리턴하는 서브 쿼리
    • 주로 Select 절에 많이 나오지만, 대부분의 컬럼이 위치할 수 있는 곳에 작성할 수 있습니다.
    • 스칼라 서브쿼리의 테이블은 NL 조인에서 Inner 테이블 역할을 수행, 드라이빙 테이블로부터 조인 조건을 상수로 제공 받습니다.
  • 특징
    • 수행횟수 최소화를 위한 캐싱 기능이 있습니다.
    • From절 이하가 간단해집니다.
    • 스칼라 서브쿼리를 위한 캐시
      • 10g 이후에는 _query_execution_cache_max_size 만큼 (11g의 경우 65,536개)
        => 따라서 DW 쿼리에는 스칼라 서브쿼리보다 Join이 적합합니다.
      • 입력되는 값(메인쿼리의 결과 값)이 캐시 수보다 적어야 함
      • 동일 값이 자주 나타나야 효과적입니다.
    • 주로 메인 쿼리가 M, 스칼라 서브쿼리가 1측으로 조회할 때 효과적입니다.

select  empno, ename, sal, hiredate
        , (select d.dname           -> 출력값 : d.dname
           from dept d
           where d.deptno = e.empno -> 입력값 : e.empno
           ) dname
from    emp e
where   sal >= 2000

 

 

✏️ 3. 조인을 내포한 DML 튜닝

• 전통적인 방식의 UPDATE
  
  • 기존: 거래 테이블을 Set절에서 3번, Where 절에서 1번 Access => 총 4번
  • 변경: 거래 테이블을 Set절에서 1번, Where 절에서 1번 access => 총 2번 (2번의 비효율을 줄임)

-- 전통적인 방식의 Update
update 고객 C
set 최종거래일시 = (select max(거래일시) from 거래
                    where 고객번호 = c.고객번호
                    and 거래일시 >= trunc(add_months(sysdate, -1)))
  , 최근거래횟수 = (select count(*) from 거래
                    where 고객번호 = c.고객번호
                    and 거래일시 >= trunc(add_months(sysdate, -1)))
  , 최근거래금액 = (select sum(거래금액) from 거래
                    where 고객번호 = c.고객번호
                    and 거래일시 >= trunc(add_months(sysdate, -1)))
where exists (select 'X' from 거래
              where 고객번호 = c.고객번호
              and 거래일시 >= trunc(add_months(sysdate, -1)))
              
-- 수정 후
update 고객 C
set (최종거래일시, 최근거래횟수, 최근거래금액) = 
    (select max(거래일시), count(*), sum(거래금액) 
     from   거래
     where  고객번호 = c.고객번호
     and    거래일시 >= trunc(add_months(sysdate, -1)))
where exists (select 'X' 
               from 거래
               where 고객번호 = c.고객번호
               and 거래일시 >= trunc(add_months(sysdate, -1)))

 

• 수정 가능 조인 뷰(Updatable Join View)
  • 두 개 이상의 테이블을 조인한 뷰에 대해 DML 연산을 수행할 수 있는 기능입니다.
  • Updatable Join View는 1:M 관계에서 M쪽만 변경 가능합니다. (즉, 1쪽 테이블은 변경 불가)
  • bypass_ujvc 힌트
    
    • bypass_ujvc(bypass Updatable Join View Check): 수정 가능 뷰 체크를 생략
    • 이 힌트를 사용하여 DBMS가 일반적으로 수행하는 검사를 생략하고, 대신 개발자나 DBA가 그 책임을 지게 됩니다.
    • 업데이트 대상이 되는 테이블(1:M 관계에서 1에 해당하는 쪽)에 중복 레코드가 없을 때만 사용해야 합니다.
    • 1쪽 집합에 Unique 인덱스가 없다면, DBMS는 어느 테이블이 1쪽 집합인지 알 수 없습니다.
      → Update를 위해 참조하는 테이블이 1쪽 집합임을 확인할 수 없을 때 발생하는 에러 메시지
      ORA-01779: cannot modify a column which maps to a non key-preserved table
  • bypass_ujvc => 1번의 조인으로 Update

update /*+ bypass_ujvc */
(select /*+ ordered use_hash(c) */
         c.최종거래일시, c.최근거래횟수, c.최근거래금액
        ,t.거래일시, t.거래횟수, t.거래금액
 from (select 고객, max(거래일시) 거래일시, count(*) 거래횟수, sum(거래금액) 거래금액
       from 거래
       where 거래일시 >= trunc(add_months(sysdate,-1))
       group by 고객) t
     , 고객 c
    where c.고객번호 = t.고객번호 
) 
set 최종거래일시 = 거래일시
  , 최근거래횟수 = 거래횟수
  , 최근거래금액 = 거래금액

 

• ⭐⭐⭐주의: bypass_ujvc 힌트는 Oracle 11g부터 지원이 중단되었으므로, 최신 버전에서는 사용할 수 없습니다. 대신 MERGE 문을 사용하는 것이 권장됩니다.

MERGE INTO 고객 c
USING (
      SELECT   고객번호, MAX(거래일시) AS 거래일시, COUNT(*) AS 거래횟수, SUM(거래금액) AS 거래금액
      FROM     거래
      WHERE    거래일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1))
      GROUP BY 고객번호
      ) t
ON (c.고객번호 = t.고객번호)
WHEN MATCHED THEN UPDATE SET
c.최종거래일시 = t.거래일시,
c.최근거래횟수 = t.거래횟수,
c.최근거래금액 = t.거래금액;

 

---

📒 정리하면

1. Outer 조인의 방향: NL 조인은 방향이 고정되며, Hash 조인은 SWAP_JOIN_INPUTS 힌트로 방향을 변경할 수 있습니다.
2. 스칼라 서브쿼리: 레코드당 하나의 값 반환, 캐싱 기능을 통해 성능을 향상시킬 수 있스빈다.
3. 조인을 포함한 DML 튜닝: 중복 액세스를 줄이는 것이 중요, 단일 서브쿼리로 여러 줄을 업데이트 하는 방식이 효율적입니다.
4. 수정 가능 조인 뷰(Updatable Join View): 1:M 관계에서 M쪽만 변경 가능, bypass_ujvc 힌트로 제약을 우회할 수 있습니다.
5. Oracle 11g 이후로는 bypass_ujvc 힌트 대신 MERGE문을 사용하여 조인을 포함한 업데이트를 수행하는 것이 권장됩니다.

---

• 참고자료
  • http://www.gurubee.net/lecture/3345 
  • http://www.gurubee.net/lecture/3271 
  • http://www.gurubee.net/lecture/3270