고급 조인 테크닉

누적 매출 구하기

우선 아래와 같이 월별지점매출 테이블을 만들어 보자.

CREATE TABLE 월별지점매출
AS
SELECT DEPTNO "지점"
      ,ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY EMPNO) AS "판매월"
      ,ROUND(DBMS_RANDOM.VALUE(500,1000)) AS "매출"
  FROM SCOTT.EMP
 ORDER BY DEPTNO;

지점별 누적매출 구하기

-- 분석함수사용
SELECT 지점,판매월,매출,
       SUM(매출) OVER(PARTITION BY 지점 ORDER BY 판매월
                      RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 누적매출
FROM 월별지점매출

-- 분석함수사용 못하는 경우
SELECT T1.지점, T1.판매월
       ,MIN(T1.매출) AS "매출", SUM(T2.매출) AS "누적매출"
  FROM 월별지점매출 T1, 월별지점매출 T2
 WHERE T1.지점 = T2.지점
   AND T1.판매월 >= T2.판매월
 GROUP BY T1.지점, T1.판매월
 ORDER BY T1.지점, T1.판매월

선분이력 끊기

선분이력 레코드를 가공해야 할 때가 있는데, 월말 기준으로 선분을 끊는 경우를 살펴보자.
본론으로 들어가기에 앞서, 두 선분이 겹치는 구간에 대한 시작일자 및 종료일자 선택 규칙에 대해 살펴보자.
시간을 나타내는 두 개의 선분이 서로 겹치는 모습을 표현하면, 아래의 그림 (a),(b),©,(d)처럼 네 가지 패턴이 있다.

아래 그림은 '월도'와 '선분이력' 두 개의 테이블이 있을 때, '선분이력'을 '월도'와 조인해서 맨 아래쪽 '변화된 선분이력'과 같은 형태로 만들려고 한다. 즉 두 개 이상의 월도에 걸친 선분(⑤,⑦)을 매 월말 기준으로 끊으려는 것이다.

'월도'와 '선분이력' 테이블을 만들어 보자.

CREATE TABLE 월도(기준월, 시작일자, 종료일자)
AS
SELECT '200906', '20090601', '20090630' FROM DUAL UNION ALL
SELECT '200907', '20090701', '20090731' FROM DUAL UNION ALL
SELECT '200908', '20090801', '20090831' FROM DUAL UNION ALL
SELECT '200909', '20090901', '20090930' FROM DUAL UNION ALL
SELECT '200910', '20091001', '20091030' FROM DUAL ;

CREATE TABLE 선분이력 (상품번호, 시작일자, 종료일자, 데이터)
AS
SELECT 'A', '20090713', '20090808', 'A1' FROM DUAL UNION ALL
SELECT 'A', '20090809', '20090820', 'A2' FROM DUAL UNION ALL
SELECT 'A', '20090821', '20091007', 'A3' FROM DUAL;

SELECT a.기준월, b.시작일자, b.종료일자, b.상품번호, b.데이터
  FROM 월도 a, 선분이력 b
 WHERE b.시작일자 <= a.종료일자
   AND b.종료일자 >= a.시작일자
 ORDER BY  a.기준월, b.시작일자;

아래 표의 하얀색 값이 결과값이며, 분홍색 부분은 위의 그림을 참고로 시작선분을 기준으로 복제된 데이터들을 표현한 것이다.

좀 더 자세히 설명하자면, ⑦번 선분 기준으로 설명한 그림이다.

위 그림을 보아, 해당 범위인 11,12,13이 복제되었다.
이제 여섯 개 선분의 시작일과 종료일을 구해보자. 이를 위해 ⑧~⑬번 선분을 위의 그림에서 예시한 스타일로 분류해 보자

이제 위에서 a,b,c,d 스타일별로 정리했던 표를 이용하면 조금 전에 얻은 여섯 개 선분의 시작일자와 종료일자를 쉽게 구할 수 있다.

SELECT 상품번호
      ,case when lst = 시작일자1 and gst = 종료일자2 then 시작일자2   -- 스타일a
            when lst = 시작일자2 and gst = 종료일자1 then 시작일자1   -- 스타일b
            when lst = 시작일자1 and gst = 종료일자1 then 시작일자2   -- 스타일c
            when lst = 시작일자2 and gst = 종료일자2 then 시작일자2   -- 스타일d
       end 시작일자
      ,case when lst = 시작일자1 and gst = 종료일자2 then 종료일자2   -- 스타일a
            when lst = 시작일자2 and gst = 종료일자1 then 종료일자1   -- 스타일b
            when lst = 시작일자1 and gst = 종료일자1 then 종료일자2   -- 스타일c
            when lst = 시작일자2 and gst = 종료일자2 then 종료일자2   -- 스타일d
       end 종료일자
      ,데이터
  FROM (
          SELECT b.상품번호,b.데이터,a.기준월
                 ,a.시작일자 시작일자1,b.시작일자 시작일자2
                 ,a.종료일자 종료일자1,b.종료일자 종료일자2
                 ,least(a.시작일자,a.종료일자,b.시작일자,b.종료일자) lst
                 ,greatest(a.시작일자,a.종료일자,b.시작일자,b.종료일자) gst
            FROM 월도 a, 선분이력 b
           WHERE b.시작일자 <= a.종료일자
             AND b.종료일자 >= a.시작일자
        ) ;

결과값은 아래와 같다.

데이터 복제를 통한 소계 구하기

쿼리를 작성하다 보면 데이터 복제 기법을 활용해야 할 때가 많다. 데이터복제를 위해 일부러 카티션 곱(Cartesian Product)을 발생시켜 복제하기도 한다.
전통적으로 많이 쓰던 방식은 복제용 테이블(copy_t)을 미리 만들어두고 이를 활용하는 것이다.

-- 테이블 생성
CREATE TABLE COPY_T (NO NUMBER, NO2 VARCHAR2(2));
-- 데이터 적재
insert into copy_t
select rownum, lpad(rownum,2,'0') from all_tables where rownum<=31;

commit;
-- pk,index 생성
alter table copy_t add constraint copy_t_pk primary key(no);
create unique index copy_t_no2_idx on copy_t(no2);

아래 쿼리를 실행하면 emp 테이블에 있는 14개의 레코드가 3개씩 총 42개로 복제된다.

select *
  from scott.emp a, copy_t b
 where b.no<=3

오라클 9i부터는 dual테이블에 start with절 없이 connect by 구문을 사용하면 두 개의 집합이 자동으로 만들어진다.

Select rownum  from dual  connect by level <= 2;

select * 
  from scott.emp a,
       (Select rownum  from dual  connect by level <= 2) b;

데이터 복제 기법을 활용

부서별 소계

총계

rollup 구문 사용

break on 부서번호
column 부서번호 format 9999
column 사원번호 format a10
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;

|
h1. 4. 상호배타적 관계의 조인
{color:blue}상호배타적 관계 : 어떤 엔터티가 두 개 이상의 다른 엔터티의 합집합과 관계를 갖는 것{color}
{color:blue}ERD에 아래처럼 아크(Arc) 관계로 표시{color}
!t8.jpg!

{color:blue}실제 데이터베이스로 구현할때, 상품권결제 테이블은 아래 두가지 방법으로 구축한다.{color}

{color:green}1. 온라인권번호, 실권번호 두 컬럼을 따로 두고, 레코드별로 둘 중 하나의 컬럼에만 값을 입력한다. Outer 조인 이용{color}
|

SELECT /*+ ordered use_nl(b) use_nl© use_nl© use_nl(d) */
a.주문번호, a.결제일자, a.결제금액
, NVL(b.온라인권번호, c.실권번호) 상품권번호
, NVL(b.발행일시, d.발행일시) 발행일시
FROM 상품권결제 a, 온라인권 b, 실권 c, 실권발행 d
WHERE a.결제일자 BETWEEN :dt1 AND :dt2
AND b.온라인권번호(+) = a.온라인권번호
AND c.실권번호(+) = a.실권번호
AND d.발행번호(+) = c.발행번호;

|

{color:green}2. 상품권구분과 상품권번호 컬럼을 두고, 상품권구분이 1일때는 온라인권번호를 입력하고 2일 때는 실퀀번호를 입력한다. Union all 이용{color}
|

SELECT x.주문번호, x.결제일자, x.결제금액, y.온라인권번호 상품권번호, y.발행일시, ...
FROM 상품권결제 x, 온라인권 y
WHERE x.상품권구분 = '1'
AND x.결제일자 BETWEEN :dt1 AND :dt2
AND y.온라인권번호(+) = x.상품권번호
UNION ALL
SELECT x.주문번호, x.결제일자, x.결제금액, y.실권번호 상품권번호, z.발행일시, ...
FROM 상품권결제 x, 실권 y, 실권발행 z
WHERE x.상품권구분 = '2'
AND x.결제일자 BETWEEN :dt1 AND :dt2
AND y.실권번호(+) = x.상품권번호
AND z.발행번호(+) = y.발행번호;

• 쿼리를 위아래 두번 수행하지만, 인덱스구성에 따라 처리 범위는 달라진다.
  1. (상품권구분 + 결제일자) : 읽는 범위 중복 없음
  2. (결제일자 + 상품권구분) : 인덱스 스캔범위에 중복 발생
  3. (결제일자) : 상품권구분을 필터링하기 위한 테이블 Random 액세스까지 중복 발생

|

{color:green}3. 중복 액세스에 의한 비효울 제거{color}
|

SELECT /*+ ordered use_nl(b) use_nl© use_nl© use_nl(d) */
a.주문번호, a.결제일자, a.결제금액
, NVL(b.온라인권번호, c.실권번호) 상품권번호
, NVL(b.발행일시, d.발행일시) 발행일시
FROM 상품권결제 a, 온라인권 b, 실권 c, 실권발행 d
WHERE a.결제일자 BETWEEN :dt1 AND :dt2
AND b.온라인권번호(+) = DECODE(a.상품권구분, '1', a.상품권번호)
AND c.실권번호(+) = DECODE(a.상품권구분, '2', a.상품권번호)
AND d.발행번호(+) = c.발행번호;

|

h1. 5. 최종 출력 건에 대해서만 조인하기
{color:blue}회면 페이지 처리시 흔히 사용되는 방식이다.{color}
{color:blue}조건과 정렬 컬럼을 결합인덱스로 구성하면 인덱스 블록만 읽어 테이블 액세스 범위와 조인 범위를 줄일 수 있다. ( 교재 참고 page. 312~ ) {color} 
{color:blue}조건과 정렬 컬럼이 많아져 인덱스 조정이 어려울 때는, 해당 테이블까지 액세스하여 전체를 읽어 정렬한 후 불필요한 조인 횟수를 줄일 수 있다. ( 교재 참고 page. 316~ ){color}

*반정규화는 성능을 위한 최후의 수단*
  정규화된 모델로는 제대로된 성능을 내기 어려울 때만 반정규화를 단행해야 하는것이 정석이다.  그러나, 미리 반정규화를 실시하는 경우가 자주 있다.
!t9.jpg!
page. 317의 그림2-27과 같은 업무를 아래와 같은 SQL로 개발했다면 수신확인자수와 수신대상자수를 세고 새글여부를 확인하는 스칼라 서브쿼리 때문에 성능 문제를 겪었을 것이고, 이를 해결하지 못하면 위의 그림과 같이 설계하기 마련이다.

SELECT ..
FROM (
SELECT ..
FROM (
SELECT a.발신인ID, a,발송일시, a.제목, b.사용자이름 AS 보낸이
, ( SELECT COUNT(수신일시) FROM 메시지수신인 ..) 수신확인자수
, ( SELECT COUNT(*) FROM 메시지수신인 ..) 수신대상자수
, ( CASE WHEN EXISTS ( SELECT 'x' FROM 메시지수신인
WHERE 발신자ID = a.발신자ID
AND 발송일시 = a.발송일시
AND 수신자ID = :로그인사용자ID
AND 수신일시 IS NULL ) THEN 'Y' END ) 새글여부
FROM 메시지 a, 사용자 b
ORDER BY a.발송일시 DESC
) a
WHERE rownum <= 10
)
WHERE no between 1 and 10;

 위와 같은 추출 속성을 도입하면 메시지를 수신할 때마다 메시지 테이블의 수신인수를 갱신해주는 DML도 같이 작성해야 한다.
 문제는 일상적이지 않은 업무로 데이터 정합성이 훼손될 수 있다는데 있다.
*반정규화를 실시했으면 업무 규칙 누락이 생기지 않도록 꼼꼼히 점검해야 한다.*

=> 최종 출력되는 10건에 대해서만 수신정보와 새글 여부를 확인하는 방식으로 쿼리 변경하여 성능 문제 해결
(스칼라 서브쿼리를 맨 바깥의 SELECT LIST에서 처리함)

SELECT a.발신인ID, a,발송일시, a.제목, b.사용자이름 AS 보낸이
, ( SELECT COUNT(수신일시) || '/' || COUNT(*) FROM 메시지수신인 ..) 수신확인
, ( CASE WHEN EXISTS ( .. ) THEN 'Y' END ) 새글여부
FROM (
SELECT ROWNUM NO, ...
FROM ( SELECT 발신인ID, a,발송일시, a.제목 FROM 메시지 ORDER BY a.발송일시 DESC )
WHERE ROWNUM <= 30
) a, 사용자 b
WHERE NO BETWEEN 21 AND 30;

h1. 6. 징검다리 테이블 조인을 이용한 튜닝
h5. from절에 조인되는 테이블 개수를 늘려 성능을 향상시키는 사례( 교재 참고 page. 319~ )
1. 최종 결과 건수는 얼마 되지 않으면서, 필터 조건만으로 각 부분을 따로 읽으면 결과 건수가 아주 많을 때 튜닝하기가 어렵다.
2. 조인 테이블을 추가하여 인덱스만 읽도록 하고, 인덱스만 읽은 결과끼리 먼저 조인하고 최종 결과집합에 대해서만 테이블을 액세스하도록 한다.
3. 테이블을 액세스할 때는 추가적인 인덱스 탐색 없이 인덱스에서 읽은 rowid 값을 가지고 직접 액세스한다. ( 실행계획에 Table Access By User ROWID 라고 표시)

*예제*
실행계획
!t13.jpg!
실행계획의 각 오퍼레이션 단계에서의 출력건수와 블록I/O발생량을 표시한 그림이다.
!t12.jpg!
최종건수는 183건에 불과하지만, 고객 테이블을 먼저 드라이빙해서 NL 조인하는 과정에서 이미 66,617개의 블록I/O가 발생했고, 이어서 서비스요금할인 테이블과 NL 조인하는 과정에서 160,055개의 블록I/O가 발생했다.
이와 같은 최종건수는 얼마 되지 않으면서, 필터 조건만으로 각 부분을 따로 읽으면 결과 건수가 아주 많을 때 튜닝하기가 가장 어렵다. 

튜닝을 하기 위해 서비스요금할인_N1 인덱스에 '서비스번호'컬럼 추가
서비스요금할인_N1 : 서비스상품그룹 + 할인기간코드 + 서비스번호

교재 쿼리 및 실행계획 참고

튜닝 전 쿼리 성능이 느린 이유가 조인 부하 때문이었는데, '서비스'와 '서비스요금할인'테이블을 한 번씩 더 조인 함으로써 I/O가 줄면서 성공적으로 튜닝이 되었다.
*양쪽테이블에서 인덱스만 읽은 결과끼리 먼저 조인하고, 최종 결과집합에 대해서만 테이블을 엑세스*
그렇게 하려고 앞의 서비스요금할인_N1인덱스에 '서비스번호'를 추가했던 것이다.


여기서 부터 완젼하게... 코어쪽이요 ㅜ
[여기클릭 | http://wiki.gurubee.net/pages/viewpage.action?pageId=4948223]


h2. 문서에 대하여

* 최초작성자 : 한남주
* 최초작성일 : 2010년 09월 02일
* {color:blue}{*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*{color}