[SQL] 고급 조인 테크닉 (부등호/BETWEEN 조인, CROSS 조인 테이블 복제 , DECODE 조인, ROWID 조인)

 

오늘은 고급 조인 테크닉을 활용한 SQL에 대해서 소개한다. 가장 많이 사용되는 등치(`=`) 조건 외에 활용할 수 있는 고급 조인 테크닉 네 가지를 소개한다.

• 부등호/`BETWEEN` 조인
• CROSS 조인을 활용한 테이블 복제
• `DECODE` 조인
• `ROWID` 조인

이러한 테크닉들은 DBMS 버전이나 환경의 제약이 있을 때, 또는 쿼리 성능 최적화가 필요한 상황에서 매우 유용한 대안이 될 수 있으므로 잘 익혀두도록 하자.

---

1. 부등호/`BETWEEN` 조인

부등호(`>`, `>=`, `<`, `<=`)와 `BETWEEN`으로 조인하는 기법이다.

누적매출을 구한다면 일반적으로는 분석함수(또는 윈도우 함수)를 사용할 수 있다.

 

다음과 같이 분석함수를 활용하여 구하던 누적 매출을 분석함수 없이 구해야 한다면 어떻게 해야 할까?

 

분석함수 `SUM() OVER()`

지점별로 판매월 단위로 누적매출 구하는 쿼리를 살펴보자.

지점별로 `partition`한 뒤 파티션이 시작되는 행부터 현재 행(`current row`)까지를 합계 처리하면 된다.

select 지점
	, 판매월
	, 매출
 	, sum(매출) over (partition by 지점 order by 판매월 
			range between unbounded preceding and current row) 누적매출
from 월별지점매출

 

하지만 DBMS나 버전 제약으로 분석함수를 쓸 수 없다면 어떻게 해야 할까?

 

부등호 SELF 조인

부등호 조건으로 동일 테이블을 self 조인하여 다음과 같은 쿼리로 바꿀 수 있다.

select t1.지점, t1.판매월
	, min(t1.매출) 매출 -- 💫
	, sum(t2.매출) 누적매출
from   월별지점매출 t1
	, 월별지점매출 t2
where  t2.지점 = t1.지점
	and    t2.판매월 <= t1.판매월 -- 💫
group by t1.지점, t1.판매월
order by t1.지점, t1.판매월;

---

2. CROSS JOIN 테이블 복제

소계를 구하는 예시를 통해 복제 테이블 활용에 대해서 알아보자.

 

`GROUP BY ROLLUP` 활용

다음은 `GROUP BY ROLLUP`을 활용해서 사원별 급여, 부서별 소계, 그리고 총계를 구하는 SQL이다.

select deptno 부서번호
	, case when grouping(empno) = 1 and grouping(deptno) = 1 then '총계'
		when grouping(empno) = 1 then '부서계'
		else to_char(empno) end  사원번호
	, sum(sal) 급여합, round(avg(sal)) 급여평균
from   emp
group by rollup(deptno, empno)
order by 1, 2;

`GROUP BY ROLLUP(deptno, empno)`구문을 통해서 다음과 같이 세 계층으로 소계를 내준다.

• `GROUP BY (deptno, empno)`
• `GROUP BY (deptno)`
• `GROUP BY (null)`

 

마찬가지로 위 쿼리를 `ROLLUP` 없이 일반 조인문으로 변환할 수 있을까?

 

복제용 테이블 활용

복제용 테이블과 조인해서 동일 테이블을 여러 개로 복제하여 각각의 copied set마다 다른 처리를 할 수 있다.

이번 예제에서는 3개의 grouping set이 필요하므로, 다음과 같이 3행짜리 복제용 테이블을 만들어 사용한다.

select rownum no 
from dual 
connect by level <= 3

 

위 복제용 테이블과 사원(`emp`) 테이블을 CROSS 조인(조건절 없이 cartisian product)하고,  `DECODE` 함수를 활용해 복제용 테이블의 `no` 값에 따라서 `GROUP BY` 처리를 달리 할 수 있다.

select decode(no, 3, null, to_char(deptno)) 부서번호
	, decode(no, 1, to_char(empno), 2, '부서계', 3, '총계') 사원번호
	, sum(sal) 급여합
from   emp a
	, (select rownum no from dual connect by level <= 3)
group by no
	, decode(no, 3, null, to_char(deptno))
	, decode(no, 1, to_char(empno), 2, '부서계', 3, '총계')
order by 1, 2;

---

3. `DECODE` 조인

조인 조건절에 `DECODE`함수 또는 `CASE`문을 활용하여 액세스 효율을 높일 수도 있다.

 

`UNION ALL`로 동일 범위 탐색 비효율

다음은 `a.상품권구분` 코드에 따라서 코드값이 1일 때는 `온라인권` 테이블과 조인한 결과와, 코드값이 2일 때는 `실권` 테이블과 조인한 결과를 만들고 각각의 결과 집합을 `UNION ALL`해서 보여주는 쿼리다.

select a.주문번호
	, b.온라인권번호 상품권번호
	, b.발행일시 발행일시
from 상품권결제 a
	, 온라인권 b
where a.상품권구분 = '1'
	and a.상품권번호 = b.온라인권번호
union all
select a.주문번호
	, c.실권번호 상품권번호
	, c.발행일시 발행일시
from 상품권결제 a
	, 실권 c
where a.상품권구분 = '2'
	and a.상품권번호 = c.온라인권번호;

위 쿼리의 문제점은 `상품권결제` 테이블을 두 번 스캔하는 비효율이 있다.

`상품권결제` 테이블에 인덱스가 없다면 테이블 풀스캔이 두 번 일어날 수 있다.

인덱스가 있더라도 인덱스 설계에 따라서 동일한 범위를 두 번 탐색하는 비효율이 있을 수 있다.

 

`DECODE` 함수 조인조건

조인조건에 `DECODE`를 활용해서 `a.상품권구분` 코드가 1일 때는 `온라인권` 테이블과 조인하고, 코드값이 2일 때는 `실권` 테이블과 조인하도록 하면 `상품권결제` 테이블을 한 번만 스캔하도록 할 수 있다.

select a.주문번호
	, nvl(b.온라인권번호, c.실권번호) 상품권번호
	, nvl(b.발행일시, d.발행일시) 발행일시
from 상품권결제 a
	, 온라인권 b
	, 실권 c
where decode(a.상품권구분, '1', a.상품권번호) = b.온라인권번호(+) --💫OUTER 필수
	and decode(a.상품권구분, '2', a.상품권번호) = c.실권번호(+) --💫OUTER 필수

`OUTER JOIN(+)`이 필요한 이유는 두 조건을 동시에 만족시키는 결괏값은 없기 때문이다.

---

4. `ROWID` 조인

최근 게시글 10건을 보여주기 위한 다음 쿼리의 문제점을 보자.

SELECT *
FROM (
    SELECT b.*, u.user_name  -- 모든 컬럼을 다 읽어야 하므로 랜덤액세스 발생
    FROM board b
	    , users u
    WHERE board_type = 'NOTICE'
	    and b.user_id = u.user_id
    ORDER BY reg_date DESC  -- 인덱스가 없다면 정렬 발생
) x
WHERE rownum <= 10;

--------------------------------------------------------------------
| Id | Operation                   | Name    | Rows  | Cost         |
--------------------------------------------------------------------
|  0 | SELECT STATEMENT            |         |    10 |        2027  |
|  1 |  COUNT STOPKEY              |         |       |              |
|  2 |   VIEW                      |         |   100 |        2027  |
|  3 |    SORT ORDER BY            |         |   100 |        2027  |
|  4 |     HASH JOIN               |         |   100 |        2025  |
|  5 |      TABLE ACCESS FULL      | BOARD   |   100 |        1024  |
|  6 |      TABLE ACCESS FULL      | USERS   |  1000 |        1000  |
--------------------------------------------------------------------

우선, `board` 테이블의 모든 컬럼을 조회(`SELECT b.*`)하고 있기 때문에, 테이블 액세스가 필요하다. `board_type`이 NOTICE에 해당하는 모든 레코드를 찾아간 뒤 조인까지 마치고, `reg_date` 순으로 모든 데이터를 정렬한 뒤에 단 10건의 데이터만 사용하고 있다.

실행계획을 보면 조인 대상 테이블 모두 풀 스캔이 발생하고 있다.

 

`ROWID`로 최소한의 데이터 읽기

이런 경우, 다음과 같이 적절한 인덱스를 사용할 수 있도록 힌트를 기재해주어야 한다. 

다음 쿼리는 pk_board(board_type, reg_date)으로 구성된 인덱스가 존재하는 경우를 예시한다.

SELECT b.*, u.user_name
FROM (
    SELECT /*+ INDEX_DESC(a pk_board) */ ROWID rid
    FROM board a
    WHERE board_type = 'NOTICE'
    AND rownum <= 10
) x,
board b,
users u
WHERE b.rowid = x.rid
AND b.user_id = u.user_id;

----------------------------------------------------------------
| Id | Operation                      | Name      | Rows | Cost |
----------------------------------------------------------------
|  0 | SELECT STATEMENT               |           |   10 |   4  |
|  1 |  NESTED LOOPS                  |           |   10 |   4  |
|  2 |   NESTED LOOPS                 |           |   10 |   3  |
|  3 |    VIEW                        |           |   10 |   2  |
|  4 |     COUNT STOPKEY              |           |      |      |
|  5 |      INDEX FULL SCAN DESCENDING| PK_BOARD  |   10 |   2  |
|  6 |    TABLE ACCESS BY USER ROWID  | BOARD     |    1 |   1  |
|  7 |   TABLE ACCESS BY INDEX ROWID  | USERS     |    1 |   1  |
----------------------------------------------------------------

인덱스를 역순으로 읽으며 `rowid`를 10건만 가져온 뒤 `rowid`를 활용해 `board` 테이블에 접근한 뒤 `user`테이블과 조인하고 있다.

인덱스에서 얻은  `rowid`를 활용하여 테이블에 액세스 하는 방식은, 인덱스를 경유하여 테이블 랜덤액세스를 할 때와 유사한 처리 방식으로 레코드를 가져오므로 이로 인한 비효율은 크지 않다.

 

최적화된 인덱스가 없는 경우에도 다음과 같이 SQL을 작성하면, 최소한의 랜덤액세스만 발생하고, 소트 영역도 최소화로 사용할 수 있다.

SELECT b.*, u.user_name
FROM (
	SELECT a.* 
	FROM (
	    SELECT ROWID rid
	    FROM board a
	    WHERE board_type = 'NOTICE'
		ORDER BY reg_date DESC
	) a
	WHERE rownum <= 10
) x
, board b
, users u
WHERE b.rowid = x.rid
AND b.user_id = u.user_id;

------------------------------------------------------------
| Id | Operation                     | Name         | Cost |
------------------------------------------------------------
|  0 | SELECT STATEMENT              |              |  25  |
|  1 |  NESTED LOOPS                 |              |  25  |
|  2 |   NESTED LOOPS                |              |  24  |
|  3 |    VIEW                       |              |  23  |
|  4 |     COUNT STOPKEY             |              |      |
|  5 |      VIEW                     |              |  23  |
|  6 |       INDEX FULL SCAN         | IDX_BOARD_NO |  22  |
|  7 |    TABLE ACCESS BY USER ROWID | BOARD        |   1  |
|  8 |   TABLE ACCESS BY INDEX ROWID | USERS        |   1  |
-------------------------------------------------------------

---

5. 맺는 말

이번 글을 통해 데이터 쿼리 시 활용할 수 있는 네 가지 고급 조인 테크닉에 대해서 알아 보았다.

 

소개한 네 가지 고급 조인 테크닉은 각각 특정 상황에서 쿼리 성능을 최적화하는데 도움이 될 수 있다.

• 부등호/`BETWEEN` 조인 : 부등호 조인은 분석 함수를 사용할 수 없는 환경에서 누적 계산을 할 수 있도록 한다.
• CROSS 조인을 활용한 테이블 복제 : `ROLLUP`과 같은 고급 집계 기능을 대체할 수 있다.
• `DECODE` 조인 : `DECODE` 조인은 불필요한 테이블 스캔을 줄여 조인 효율을 향상시킬 수 있다.
• `ROWID` 조인 : `ROWID` 조인은 대용량 데이터에서 페이징 처리 시 발생하는 불필요한 정렬과 랜덤 액세스를 최소화하는 데 활용할 수 있다.

고급 조인 테크닉들을 잘 활용하기 위해서는 각 조인 기법의 작동 방식을 잘 이해하고 있어야 대량의 데이터 처리 시 일부 필요한 레코드만 처리할 수 있도록 할 수 있다.

---

출처

조시형, 오라클 성능 고도화 원리와 해법 2, 디비안, https://product.kyobobook.co.kr/detail/S000061696048