메뉴 건너뛰기

bysql.net

8. 고급 조인 테크닉-1

2011.04.04 03:56

darkbeom 조회 수:13259

2.8 고급조인테크닉

2.8.1 누적매출구하기

- 전제 : 각 지점별로 판매월과 함께 증가하는 누적매출 구하는 방법


 -- 1. 월별지점매출 테이블을 생성.

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



-- 2. 월별지점매출 테이블을 확인.

SELECT * FROM 월별지점매출;


-- 3. 오라클 8i에서부터 제공 된 분석함수 사용예

SELECT   지점, 판매월, 매출
         , SUM(매출) OVER (PARTITION BY 지점 ORDER BY 판매월
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 누적매출
FROM     월별지점매출;

-----------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |    14 |   546 |     4  (25)| 00:00:01 |
|   1 |  WINDOW SORT       |        |    14 |   546 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| 월별지 |    14 |   546 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

-- 4. 오라클 8i이전 분석함수가 지원 되지않은 사용예


SELECT T1.지점, T1.판매월, MIN(T1.매출) 매출, SUM(T2.매출) 누적매출
FROM   월별지점매출 T1, 월별지점매출? T2
WHERE  T2.지점 = T1.지점
AND    T2.판매월 <= T1.판매월
GROUP  BY T1.지점, T1.판매월
ORDER  BY T1.지점, T1.판매월;


------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |     3 |   234 |     8  (25)| 00:00:01 |
|   1 |  SORT GROUP BY      |        |     3 |   234 |     8  (25)| 00:00:01 |
|*  2 |   HASH JOIN         |        |     3 |   234 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| 월별지 |    14 |   546 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| 월별지 |    14 |   546 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

-- 5. 결과

지점    판매월    매출            지점    판매월    매출    누적매출
10      1         630             10      1         630    630    
10      2         992             10      2         992    1622   
10      3         911             10      3         911    2533   
20      1         583             20      1         583    583    
20      2         993             20      2         993    1576   
20      3         874             20      3         874    2450   
20      4         786   ------->  20      4         786    3236   
20      5         738             20      5         738    3974   
30      1         644             30      1         644    644    
30      2         828             30      2         828    1472   
30      3         778             30      3         778    2250   
30      4         559             30      4         559    2809   
30      5         799             30      5         799    3608   
30      6         945             30      6         945    4553  


- 분석함수는 8i 버전에서 PERSNAL EDITION, ENTERPRISE EDITION에서 지원하고,
- 9i버전에서는 모든 버전에 지원된다.

-- 6. 참고사항 : 분석함수문법은 다음과 같다.

SELECT ANALYTIC_FUNCTION ( ARGUMENTS )
       OVER( [ PARTITION BY 칼럼 ] [ ORDER BY 절 ] [ WINDOWING 절] )
FROM 테이블 명;

- PARTITION BY : 전체 집합을 기준에 의해 소그룹 분할    
- ORDER BY     : PARTITION BY에 나열된 그룹을 정렬
- WINDOWING    : 펑션의 대상이 되는 행 기준으로 범위를 세밀하게 조정



2.8.2 선분이력끊기

- 전제
1. 선분이력 레코드를 가공해야 할때, 월말 기준으로 선분을 끊는 경우
2. 두 선분이 겹치는 구간에 대한 시작일자 및 종료일자 선택 규칙


3.8.1.jpg


 3.8.2.jpg


3.8.3.jpg




 -- 1. 월도 및 선분이력 테이블 생성
-- 월도 데이블 생성
CREATE TABLE 월도 (기준월, 시작일자, 종료일자)
AS
SELECT '2009/06', '2009/06/01', '2009/06/30' FROM DUAL UNION ALL
SELECT '2009/07', '2009/07/01', '2009/07/31' FROM DUAL UNION ALL
SELECT '2009/08', '2009/08/01', '2009/08/31' FROM DUAL UNION ALL
SELECT '2009/09', '2009/09/01', '2009/09/30' FROM DUAL UNION ALL
SELECT '2009/10', '2009/10/01', '2009/10/31' FROM DUAL;

SELECT * FROM 월도;

기준월    시작일자    종료일자
2009/06    2009/06/01    2009/06/30
2009/07    2009/07/01    2009/07/31
2009/08    2009/08/01    2009/08/31
2009/09    2009/09/01    2009/09/30
2009/10    2009/10/01    2009/10/31


-- 선분이력 데이블 생성
CREATE TABLE 선분이력(상품번호, 시작일자, 종료일자, 데이터)
AS
SELECT 'A', '2009/07/13', '2009/08/08', 'A1' FROM DUAL UNION ALL
SELECT 'A', '2009/08/09', '2009/08/20', 'A2' FROM DUAL UNION ALL
SELECT 'A', '2009/08/21', '2009/10/07', 'A3' FROM DUAL;

SELECT * FROM 선분이력;

상품번호 시작일자      종료일자      데이터
A        2009/07/13    2009/08/08    A1
A        2009/08/09    2009/08/20    A2
A        2009/08/21    2009/10/07    A3

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

기준월     시작일자      종료일자     상품번호   데이터    스타일
2009/07    2009/07/13    2009/08/08    A         A1        5----->8
2009/08    2009/07/13    2009/08/08    A         A1        5----->9
2009/08    2009/08/09    2009/08/20    A         A2        6----->10
2009/08    2009/08/21    2009/10/07    A         A3        7----->11
2009/09    2009/08/21    2009/10/07    A         A3        7----->12
2009/10    2009/08/21    2009/10/07    A         A3        7----->13

-- 변환된 선분이력 결과집합
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 시작일자1  -- 스타일 D
        END 시작일자
      , CASE WHEN LST = 시작일자1 AND GST = 종료일자2 THEN 종료일자1  -- 스타일 A
        WHEN LST = 시작일자2 AND GST = 종료일자1 THEN 종료일자2  -- 스타일 B
        WHEN LST = 시작일자1 AND GST = 종료일자1 THEN 종료일자2  -- 스타일 C
        WHEN LST = 시작일자2 AND GST = 종료일자2 THEN 종료일자1  -- 스타일 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.시작일자
) ;

기준월     시작일자      종료일자      상품번호    데이터   스타일
2009/07    2009/07/13    2009/07/31    A           A1       스타일 A
2009/08    2009/08/01    2009/08/08    A           A1       스타일 B
2009/08    2009/08/09    2009/08/20    A           A2       스타일 C
2009/08    2009/08/21    2009/08/31    A           A3       스타일 A
2009/09    2009/09/01    2009/09/30    A           A3       스타일 D
2009/10    2009/10/01    2009/10/07    A           A3       스타일 B

- 결과
1. 겹치는 구간의 시작일자는 두시작일자 중 큽값을 취하고
2. 종료일자는 두 종료일자 중 작은 값을 취하면 된다.

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

기준월     시작일자      종료일자      상품번호    데이터
2009/07    2009/07/01    2009/08/08    A           A1
2009/08    2009/07/13    2009/08/31    A           A1
2009/08    2009/08/01    2009/08/31    A           A2
2009/08    2009/08/01    2009/10/07    A           A3
2009/09    2009/08/21    2009/10/07    A           A3
2009/10    2009/08/21    2009/10/31    A           A3




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

- 전제
1. 쿼리 작성시 데이터 복제 기법을 활용해야 할때


 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;
--SELECT ROWNUM, LPAD(ROWNUM, 2, '0') FROM DUAL CONNECT BY LEVEL <= 31;


ALTER TABLE COPY_T ADD CONSTRAINT COPY_T_PK PRIMARY KEY(NO);


CREATE UNIQUE INDEX COPY_T_NO2_IDX ON COPY_T(NO2);


- 방법
1. 부등호 조인을 이용한 데이터 복제


 -- 총42건 : EMP 총 14 x (B.NO <= 3)
SELECT COUNT(*)
FROM   EMP A, COPY_T B
WHERE  B.NO <= 3;



2. 카티션 곱 발생 시켜 복제 (검색하고자 했던 데이터뿐 아니라 조인에 사용된 테이블의 모든 데이터가 RETRUN되는 현상)
-- 조인 조건이 잘못된 경우
-- 조인 조건을 정의하지 않았을경우


 -- 9i 버그로 인해 인라인뷰로 구현
SELECT * FROM (SELECT ROWNUM, LPAD(ROWNUM, 2, '0') FROM DUAL CONNECT BY LEVEL <= 2);
-- 그 외 버전
SELECT ROWNUM, LPAD(ROWNUM, 2, '0') FROM DUAL CONNECT BY LEVEL <= 2;


-- 총42건 : EMP 총 14 x (LEVEL <= 2)
SELECT COUNT(*)
FROM   EMP A, (SELECT ROWNUM NO FROM DUAL CONNECT BY LEVEL <= 2) B;


 
3. 데이터 복제 기법을 활용하여 부서별 소계 구하기


 
BREAK ON 부서번호
COLUMN 부서번호 FORMAT 9999
COLUMN 사원번호 FORMAT A10
SELECT DEPTNO 부서번호
     , DECODE(NO, 1, TO_CHAR(EMPNO), 2, '부서계') 사원번호
     , SUM(SAL) 급여합, ROUND(AVG(SAL)) 급여평균
FROM   EMP A, (SELECT ROWNUM NO FROM DUAL CONNECT BY LEVEL <= 2)
GROUP BY DEPTNO, NO, DECODE(NO, 1, TO_CHAR(EMPNO), 2, '부서계')
ORDER BY 1, 2;

부서번호 사원번호       급여합   급여평균
-------- ---------- ---------- ----------
      10 7782             2450       2450
         7839             5000       5000
         7934             1300       1300
         부서계           8750       2917
      20 7369              800        800
         7566             2975       2975
         7788             3000       3000
         7876             1100       1100
         7902             3000       3000
         부서계          10875       2175
      30 7499             1600       1600
         7521             1250       1250
         7654             1250       1250
         7698             2850       2850
         7844             1500       1500
         7900              950        950
         부서계           9400       1567

17 rows selected.



4. GROUP BY를 활용하여 부서별 소계 구하기

COLUMN 부서번호 FORMAT A10
SELECT DECODE(NO, 3, NULL, TO_CHAR(DEPTNO)) 부서번호
       , DECODE(NO, 1, TO_CHAR(EMPNO), 2, '부서계', 3, '총계') 사원번호
       , SUM(SAL) 급여합, ROUND(AVG(SAL)) 급여평균
FROM   EMP A, (SELECT ROWNUM NO FROM DUAL CONNECT BY LEVEL <= 3)
GROUP BY DECODE(NO, 3, NULL, TO_CHAR(DEPTNO))
      , NO, DECODE(NO, 1, TO_CHAR(EMPNO), 2, '부서계', 3, '총계')
ORDER  BY 1, 2;


부서번호   사원번호       급여합   급여평균
---------- ---------- ---------- ----------
10         7782             2450       2450
           7839             5000       5000
           7934             1300       1300
           부서계           8750       2917
20         7369              800        800
           7566             2975       2975
           7788             3000       3000
           7876             1100       1100
           7902             3000       3000
           부서계          10875       2175
30         7499             1600       1600
           7521             1250       1250
           7654             1250       1250
           7698             2850       2850
           7844             1500       1500
           7900              950        950
           부서계           9400       1567
           총계            29025       2073

18 rows selected.


 
5. 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;

부서번호 사원번호       급여합   급여평균
-------- ---------- ---------- ----------
      10 7782             2450       2450
         7839             5000       5000
         7934             1300       1300
         부서계           8750       2917
      20 7369              800        800
         7566             2975       2975
         7788             3000       3000
         7876             1100       1100
         7902             3000       3000
         부서계          10875       2175
      30 7499             1600       1600
         7521             1250       1250
         7654             1250       1250
         7698             2850       2850
         7844             1500       1500
         7900              950        950
         부서계           9400       1567
         총계            29025       2073

18 rows selected.




2.8.4 상호배타적 관계의 조인

- 상호배타적 관계(Exclusive OR) : 어떤 엔티티가 두 개 이상의 다른 엔티티의 합집합과 관계를 갖는것
- 상품권결제와 온라인권 및 실권 테이블과의 관계
- 관계선에 표시기된 아크(Arc) 관계 확인


3.8.4.jpg


1. 결제일자


 SELECT  /*+ ORDERED USE_NL(B) USE_NL(C) 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.발행번호



2. 상품권구분 + 결제일자


SELECT  X.주문번호 , X.결제일자, X.결제금액, Y.온라인권번호 상품권번호, X.발행일시 발행일시
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.발행번호



3. [결제일자 + 상품권구분] 변경시 [상품권구분 + 결제일자] 인덱스의 스캔범위 중복,

   [결제일자]인덱스만 사용시 랜덤액세스 불가피


 SELECT  /*+ ORDERED USE_NL(B) USE_NL(C) USE_NL(D) */
        A.주문번호 , A.결제일자, A.결제금액
        , NVL(B.온라인권번호, C.실권번호) 상품권번호
        , NVL(B.발행일시, D.발행일시) 발행일시
FROM    상품권결제 A, 온라인권 B, 실권 C, 실권발행 D
WHERE   결제일자 BETWEEN :DT1 AND :DT2
AND     B.온라인권번호(+) = DECODE(A.상품권구분, '1', A.상품권번호)
AND     C.실권번호(+) = DECODE(A.상품권구분, '2', A.상품권번호)
AND     D.발행번호(+) = C.발행번호



2.8.5 최종 출력 건에 대해서만 조인하기

- 전제
1. 흔히 개발할때 게시판 관련해서 복사하듯이 쓰는 페이징 쿼리
2. 게시판 테이타는 수백만건 게시판유형에 따라 평균 10만건
3. 회원, 게시판유형, 질문유형 3개 테이블과의 조인 수행 하므로 성능 부하


SELECT * FROM (
    SELECT ROWNUM NO, 등록일자, 번호, 제목, 회원명
               , 게시판유형명, 질문유형명, COUNT(*) OVER () CNT
    FROM (
        SELECT A.등록일자, A.번호, A.제목, B.회원명, C.게시판유형명, D.질문유형명
        FROM    게시판 A, 회원 B, 게시판유형 C, 질문유형 D
        WHERE   A.게시판유형 = :TYPE
        AND    B.회원번호 = A.작성자번호
        AND    B.게시판유형 = A.게시판유형
        AND    C.질문유형 = A.질문유형
        ORDER   BY A.등록일자 DESC, A.질문유형, A.번호
    ) WHERE ROWNUM <= 31
)WHERE NO BETWEEN 21 AND 30



4. 인덱스 구성은 아래와 같이 소트 오퍼레이션이 불가피


 게시판_X01 : 게시판유형 + 등록일자 DESC + 번호



5. 게시판_X01 + 질문유형 (인덱스 컬럼 순서를 바꾸는 결정은 하기 쉽지 않으나 추가는 그것보다 낫다.)


게시판_X01 : 게시판유형 + 등록일자 DESC + 번호 + 질문유형


SELECT     /*+ ORDERED USE_NL(A) USE_NL(B) USE_NL(C) USE_NL(D) ROWID(A) */
    A.등록일자, A.번호, A.제목, B.회원명, C.게시판유형명, D.질문유형명, X.CNT
FROM (
    SELECT RID, ROWNUM NO, COUNT(*) OVER () CNT
        FROM    (
                   SELECT ROWID RID
                   FROM   게시판
                   WHERE 게시판유형 = :TYPE
                   ORDER  BY 등록일자 DESC, 질문유형, 번호
                )
        WHERE  ROWNUM <= 31
     ) X, 게시판 A, 회원 B, 게시판유형 C, 질문유형 D
WHERE   A.ROWID = X.RID
AND     B.회원번호 = A.작성자번호
AND     B.게시판유형 = A.게시판유형
AND     C.질문유형 = A.질문유형


1. 게시판 테이블 두번 읽도록 작성했지만 인라인뷰는 인덱스만 읽고
2. 게시판 A 테이블 액세스할때에는 인라인뷰 X ROWID로 직접액세스
3. 실행계획에서 TABLE ACCESS BY INDEX ROWID > TABLE ACCESS BY USER ROWID

- 전제 : 위와 달리 NULL 허용컬럼이 있을경우, 업무적으로 NOT NULL값에 제약이 없어 NULL이 허용될때
         매우 흔하다는 표현....
- 수행 : Outer(+) 기호만 붙여주면 된다.


WHERE   A.ROWID = X.RID
AND     B.회원번호(+) = A.작성자번호
AND     B.게시판유형(+) = A.게시판유형
AND     C.질문유형(+) = A.질문유형



- 전제 : 업무적으로 NULL이 허용하는 조인컬럼들에 대한 제외하고자 할때
- 수행 : 인라인뷰에 IS NOT NULL 추가


WHERE   게시판유형 = :TYPE
AND     작성자번호 IS NOT NULL
AND     게시판유형 IS NOT NULL
AND     A.질문유형 IS NOT NULL



SELECT  /*+ ORDERED USE_NL(B) USE_NL(C) USE_NL(D) */
        A.등록일자, A.번호, A.제목, B.회원명, C.게시판유형명, D.질문유형명, A.CNT
FROM (
    SELECT A.*, ROWNUM NO, COUNT(*) OVER () CNT
    FROM (
        SELECT 등록일자, 번호, 제목, 작성자번호, 게시판유형, 질문유형
        FROM    게시판
        WHERE   게시판유형 = :TYPE
        AND   작성자번호 IS NOT NULL
        AND   게시판유형 IS NOT NULL
        AND   질문유형 IS NOT NULL
        ORDER   BY 등록일자 DESC, 질문유형, 번호
        ) A WHERE ROWNUM <= 31
    ) A, 회원 B, 게시판유형 C, 질문유형 D
WHERE  NO BETWEEN 21 AND 30
AND    B.회원번호 = A.작성자번호
AND    B.게시판유형 = A.게시판유형
AND    C.질문유형 = A.질문유형



반정규화는 성능을 위한 최후의 수단
- 전제
1. 정규화된 모델로 성능내기 어려울때, 반정규화를 답처럼 쓰지마라..
2. 발송메시지건수, 수신인수 동일 추출 속성들이 설계되어있다.
3. 수신확인자수와 수신대상자수를 새고 새글여부를 확인하는 스칼라 서브쿼리 성능 문제


3.8.5.jpg



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;



- 문제점
1. 메세지를 수신할 때마다 메시지 테이블의 수신인수를 갱신해주는 DML 프로그램도 같이 작성해야한다.
2. 일상적이지 않은 업무 때문에 데이터 정합성이 훼손될 수 있다.(예, 사용자 탈퇴 후 메시지인수 갱신)

- 해결
1. 업무 규칙 누락이 생기지 않도록 않도록 꼼꼼히 점검.(반정규화 없이 성능 문제를 해결하는 것이 좋다.)

- 수행
1. 최종 출력되는 10건에 대해서만 수신정보와 새글여부확인 하기 때문에
2. 데이터베이스 설계자에게 DB성능 원리에 대한 깊은 연구가 필요한 이유가 여기에 있다.


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



2.8.6 징검다리 테이블 조인을 이용한 튜닝

- 전제
1. 앞서 성능저하를 두려워해 논리 데이터 모델링 단계에서 습관적인 반정규화실시


3.8.6.jpg


2. FROM 절에서 테이블 개수를 늘려 성능 향상시키는 방법
3. 아래의 쿼리는 고객의 요금 할인 혜택을 조회하고자 한다.


SELECT  /*+ ORDERED USE_NL(S R)*/
        C.고객번호, S.서비스번호, S.서비스구분
        , S.서비스상태코드, S.서비스상태변경코드, R.할인시작일자, R.할인종료일자
FROM    고객 C, 서비스 S, 서비스요금할인 R
WHERE   C.주민법인등록번호 = :CTZ_BIZ_NUM
AND     S.명의고객번호 = C.고객번호
AND     R.서비스번호 =  S.서비스번호
AND     R.서비스상품그룹 = '3001'
AND     R.할인기간코드 = '15'
ORDER   BY R.할인종료일자 DESC, S.서비스번호


--인덱스


고객_N1              : 주민법인등록번호
서비스_N2            : 명의고객번호 + 서비스번호
서비스요금할인_PK    : 서비스요금할인_PK + 서비스상품그룹
서비스요금할인_N1    : 서비스상품그룹 + 할인기간코드



- 쿼리 수행시(319p~320p) : 서버구간에서만 28초, 블록I/O는 226,672번 발생

- Row Source Operation의 처리과정에서 각 오퍼레이션 단계에서의 출력 건수와 블록I/O 발생량


3.8.7.jpg


1. 최종건수 183건
2. 고객테이블 먼저 드리이빙 후 서비스테이블과 NL조인과정에서 66,617개의 블록I/O 발생
3. 서비스요금할인테이블과 NL조인과정에서 160,055개의 블록I/O 추가발생
4. 총 226,672개의 블록I/O 추가발생

3.8.8.jpg


1. 서비스요금할인 테이블 먼저 드라이빙 되도록 바꾼다면

2. [할인기간코드 = '15' AND 서비스상품그룹코드 = '3001'] == 36,557레코드건
2. 테이블 액세스 하는 단계에서 24,826개의 블록I/O 발생
3. 조인순서를 바꾸더라도 조인액세스량 줄이기는 어렵다.
4. 해시조인유도시 91,443(= 66,617 + 24,826)개의 블록I/O 발생 예상.
5. 비록 I/O가 절반이상 줄었다 하더라도 속도는 만족할 수 없다.
6. 최종결과는 얼마되지않으나 필터조건만으로 각부분을 따로 읽으면 결과 건수가 아주 많을때는 튜닝이 어렵다.
7. 즉 NL, 해시 조인도 부하를 줄일 수 없다.
8. 서비스요금할인_N1 인덱스에 서비스번호컬럼을 추가


 서비스요금할인_N1    : 서비스상품그룹 + 할인기간코드 + 서비스번호


SELECT /*+ ORDERED USE_HASH(R_BRDG) ROWID(S) ROWID(R) */    ------------------- 1
       C.고객번호, S.서비스번호, S.서비스구분
       , S.서비스상태코드, S.서비스상태변경코드, R.할인시작일자, R.할인종료일자
FROM   고객 C
       , 서비스 S_BRDG, 서비스요금할인 R_BRDG        -------------------- 2
       , 서비스 S, 서비스요금할인 R
WHERE  C.주민법인등록번호 = :CTZ_BIZ_NUM
AND    S_BRDG.명의고객번호 = C.고객번호
AND    R_BRDG.서비스번호 =  S_BRDG.서비스번호
AND    R_BRDG.서비스상품그룹 = '3001'
AND    R_BRDG.할인기간코드 = '15'
AND   S.ROWID = S_BRDG.ROWID                -------------------- 3
AND   R.ROWID = R_BRDG.ROWID                -------------------- 4
ORDER  BY R.할인종료일자 DESC, S.서비스번호


- 수행
1. 서비스번호를 추가함으로써 양쪽테이블에서 인덱스만 읽은 결과끼리 먼저 조인하고서

   최종결과집합 183건에 대해서만 테이블 액세스
2. 테이블 액세스량을 줄이기 위해 징거다리 조인 추가(ALIAS '_BRDG' == BRIDGE)
3. 쿼리1번 인덱스에서 얻어진 집합끼리 조인할때 대량 데이터 조인이므로 해시조인
4. 쿼리3~4번 추가적인 인덱스 탐색없이 인덱스에서 읽은 ROWID값을 가지고 직접액세스(TABLE ACCESS BY USER ROWID)
5. 두번씩 액세스를 하도록 했지만 실제 처리일량은 한번만 액세스(323p)


3.8.9.jpg


- 결과
1. 고객테이블과 서비스_N2 인덱스를 조인할 때는 블록I/O가 총 363번 발생
2. 서비스요금할인_N1만 읽을때도 블록I/O는 128개뿐

인조식별자사용에 의한 조인 성능 이슈

- 전제
1. 액세스경로에 대한 고려없이 인조 식별자를 설계하면 위와같은 조인이슈가 자주발생
*인조 식별자(Artificial Identifier OR Surrogate Identifier)

2. 주문일자 와 주문순번을 주문상세에 상속


3.8.10.jpg



3. 특정주문일자에 발생한 특정상품 주문금액 집계(주문일자평균 10만건, 상품번호 1000건, 상품번호별 하루평균 600건)


SELECT  SUM(주문상세.가격 * 주문상세.주문수량) 주문금액
FROM    주문, 주문상세
WHERE   주문.주문일자 = 주문상세.주문일자
AND     주문.주문순번 = 주문상세.주문순번
AND     주문.주문일자 = '20090315'
AND     주문상세.상품번호 = 'AC001'


- 결과
주문상세 인덱스 : [상품번호 + 주문일자] OR [주문일자 + 상품번호] 구성으로 효과적 대응
-- 4장 5절 조건절 이행 참조


- 전제
1. 주문번호을 주문상세에 상속
2. 특정주문일자에 발생한 특정상품 주문금액 집계


 3.8.11.jpg



주문_PK        : 주문번호
주문_X01       : 주문일자
주문상세_PK    : 주문번호 + 주문일자
주문상세_X01   : 상품번호


SELECT  SUM(주문상세.가격 * 주문상세.주문수량) 주문금액
FROM    주문, 주문상세
WHERE   주문.주문번호 = 주문상세.주문번호
AND     주문.주문일자 = '20090315'
AND     주문상세.상품번호 = 'AC001'


- 결과
1. 주문_PK 인덱스를 거쳐 주문상세 테이블 10만건 액세스 하고 상품번호로 필터 600건
2. 주문상세_PK 인덱스로 랜덤액세스를 줄이지만 조인 시도 횟수는 줄지 않음
3. 주문상세를 먼저 읽을시 상품번호로 인한 평균카디널리티 증가로 인한 본테이블 및 주문테이블의 랜덤액세스 비효율발생
4. 주문번호로 반정규화 하기보다 주문일자로 반정규화 하는게 더 효과적
5. 되도록이면, 반정규화를 정규화하든지 방법을 통해서 반정규화를 최대한 피하는게 좋다.

인조식별자를 둘 때 주의사항


- 장점
1. PK, FK가 단일 컬럼으로 구성되므로 테이블 간 연결 구조가 단순해지고 인덱스 저장공간이 최소화된다.
2. 다중컬럼으로 조인할 때보다 조인연산을 위한 CPU 사용량이 조금 감소


- 단점
1. 조인연산시 CPU 사용량이 감소가 미미
2. 조인연산횟수와 블록I/O 증가로 더 많은 시스템 리소스 낭비가 쉽다.

- 결과
1. 업무적으로 이미 통용되는 식별자이거나 유연성/확장성을 고려해 인조 식별자를 설계하는 경우를 제외하면

   가급적 피하는것이 좋다.


2.8.7 점이력 조회

1. 점이력 : 데이터 변경이 발생할때마다 변경일자와 함께 새로운 이력레코드를 쌓는 방식
2. 점이력 레코드 빠르게 조회하는 방법
- 전제 : 찾고자 하는 시점보다 앞선 변경일자 중 마지막 레코드


3.8.12.jpg


CREATE TABLE 고객
AS
SELECT  EMPNO 고객번호, ENAME 고객명, 'C70' 가입회사
         , '서울' 거주지역, '...' 주소, '123-' || EMPNO 연락처
         , TO_CHAR(TO_DATE('20050101','YYYYMMDD')+ROWNUM*20000,'YYYYMMDD') 서비스만료일
FROM    EMP
WHERE   ROWNUM <= 10;

CREATE INDEX 고객_IDX01 ON 고객(가입회사);

CREATE TABLE 고객별연체이력
AS
SELECT A.고객번호, B.변경일자, B.연체개월수, B.연체금액
FROM  고객 A
      ,(SELECT TO_CHAR(TO_DATE('20050101', 'YYYYMMDD')+ROWNUM*2, 'YYYYMMDD') 변경일자
               , ROUND(DBMS_RANDOM.VALUE(1, 12)) 연체개월수
               , ROUND(DBMS_RANDOM.VALUE(100, 1000)) * 100 연체금액
       FROM    DUAL
       CONNECT BY LEVEL <= 100000) B;

CREATE INDEX 고객별연체이력_IDX01 ON 고객별연체이력(고객번호, 변경일자);

SELECT A.고객명, A.거주지역, A.주소, A.연락처, B.연체금액
FROM   고객 A, 고객별연체이력 B
WHERE  A.가입회사 = 'C70'
AND    B.고객번호 = A.고객번호
AND    B.변경일자 = (SELECT MAX(변경일자)
                     FROM   고객별연체이력
                     WHERE  고객번호 = A.고객번호
                     AND    변경일자 <= A.서비스만료일);
                    
-------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |    60M|  5690M|    62   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID    | 고객별연체이력|     4 |   128 |     3   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                  |               |    39 |  3822 |    32   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID  | 고객          |    10 |   660 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN            | 고객_IDX01    |    10 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN             | 고객별연체이력|  1115K|       |     2   (0)| 00:00:01 |
|   6 |     SORT AGGREGATE              |               |     1 |    19 |            |          |
|   7 |      FIRST ROW                  |               |   558 | 10602 |     3   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN (MIN/MAX)| 고객별연체이력|   558 | 10602 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.01          0          6          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         59          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.01          0         65          0          10
      
-- ID7~8 : 서브쿼리 내에서 서비스만료일보다 작은 레코드를 모두 스캔하지 않고 오라클이

           인덱스를 꺼꾸로 스캔하면서 가장 큰값 하나만 찾는 방식

SELECT A.고객명, A.거주지역, A.주소, A.연락처, B.연체금액
FROM   고객 A, 고객별연체이력 B
WHERE  A.가입회사 = 'C70'
AND    B.고객번호 = A.고객번호
AND    B.변경일자 = (SELECT  /*+ INDEX_DESC(B 고객별연체이력_IDX01) */ 변경일자
                     FROM   고객별연체이력
                     WHERE  고객번호 = A.고객번호
                     AND    변경일자 <= A.서비스만료일
                     AND    ROWNUM <= 1);
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |               |     0 |     4 |    62   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID  | 고객별연체이력|     4 |   128 |     3   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                |               |    39 |  3822 |    32   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| 고객          |    10 |   660 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | 고객_IDX01    |    10 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN           | 고객별연체이력|  1115K|       |     2   (0)| 00:00:01 |
|*  6 |     COUNT STOPKEY             |               |       |       |            |          |
|*  7 |      INDEX RANGE SCAN         | 고객별연체이력|   558 | 10602 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          6          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.08         11         59          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.08         11         65          0          10

-- 서브쿼리 내에서 액세스되는 인덱스 루트블록에 대한 버퍼 Pinning 효과가 사라져 블록 I/O가 더 발생
-- 두 쿼리 모두 고객별연체이력_idx01 두번 액세스하는 비효율 발생

-- index_desc 힌트와 rownum <= 1 조건 사용시 주의사항
1. 인덱스 구성이 변경되면 쿼리결과가 틀리게 될수있다. 따라서 first row(min/max) 알고리즘이 작동할때는
   반드시 min/max 함수를 사용하는 것이 올바르다.
  
SELECT A.고객명, A.거주지역, A.주소, A.연락처
      ,(SELECT /*+ INDEX_DESC(B 고객별연체이력_IDX01) */ 연체금액
        FROM   고객별연체이력 B
        WHERE  B.고객번호 = A.고객번호
        AND    B.변경일자 <= A.서비스만료일
        AND    ROWNUM <= 1) 연체금액
FROM   고객 A
WHERE  가입회사 = 'C70'

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        3      0.00       0.01          0          3          0           0
Execute      3      0.00       0.00          0          0          0           0
Fetch        6      0.00       0.00          0        132          0          30
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       12      0.00       0.02          0        135          0          30

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  COUNT STOPKEY (cr=40 pr=0 pw=0 time=387 us)
     10   TABLE ACCESS BY INDEX ROWID 고객별연체이력 (cr=40 pr=0 pw=0 time=286 us)
     10    INDEX RANGE SCAN DESCENDING 고객별연체이력_IDX01 (cr=30 pr=0 pw=0 time=191 us)(object id 52904)
     10  TABLE ACCESS BY INDEX ROWID 고객 (cr=4 pr=0 pw=0 time=35 us)
     10   INDEX RANGE SCAN 고객_IDX01 (cr=2 pr=0 pw=0 time=108 us)(object id 52902)
    
-- 스칼라 서브쿼리로 변환하면 인덱스를 두번 액세스하지 않아도 되기때문에 I/O를 그만큼 줄일 수 있다.
-- 인덱스 루트 블록에 대한 버퍼 Pinning 효과는 없다.
-- 연체금액 하나만 읽기 때문에 스칼라 서브쿼리로 변경하기가 쉬웠다.

-- 만약 두개 이상 컬럼을 읽어야 한다면, 필요한 컬럼연결(||)잘라쓰는(substr) 방법을 사용한다.

SELECT 고객명, 거주지역, 주소, 연락처
       , TO_NUMBER(SUBSTR(연체, 3)) 연체금액
       , TO_NUMBER(SUBSTR(연체, 1, 2)) 연체개월수
FROM   (SELECT A.고객명, A.거주지역, A.주소, A.연락처
            ,(SELECT /*+ INDEX_DESC(B 고객별연체이력_IDX01) */
                     LPAD(연체개월수, 2) || 연체금액
              FROM   고객별연체이력
              WHERE  고객번호 = A.고객번호
              AND    변경일자 <= A.서비스만료일
              AND    ROWNUM <= 1) 연체
       FROM   고객 A
       WHERE  가입회사 = 'C70'
)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.01       0.00          0          3          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.03       0.07         11         88          0          20
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.04       0.08         11         91          0          20

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  TABLE ACCESS BY INDEX ROWID 고객 (cr=4 pr=0 pw=0 time=35 us)
     10   INDEX RANGE SCAN 고객_IDX01 (cr=2 pr=0 pw=0 time=181 us)(object id 52902)
    
-- 이력테이블에서 읽어야 할 컬럼 개수가 많다면 일일이 문자열 연결작업이 번거롭다.
-- 이때, 스칼라 서브쿼리에서 rowid 값만 취하고 고객 연체이력을 한번더 조인해보자.

SELECT /*+ ORDERED USE_NL(B) ROWID(B) */ A.*, B.연체금액, B.연체개월수
FROM  (SELECT A.고객명, A.거주지역, A.주소, A.연락처
            ,(SELECT /*+ INDEX_DESC(B 고객별연체이력_IDX01) */ ROWID RID
              FROM   고객별연체이력 B
              WHERE  B.고객번호 = A.고객번호
              AND    B.변경일자 <= A.서비스만료일
              AND    ROWNUM <= 1) RID
       FROM   고객 A
       WHERE  가입회사 = 'C70') A, 고객별연체이력 B
WHERE  B.ROWID = A.RID

 
-------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |   111K|    10M|    15   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                  |               |       |       |            |          |
|*  2 |   INDEX RANGE SCAN DESCENDING   | 고객별연체이력|   558 | 14508 |     3   (0)| 00:00:01 |
|   3 |   NESTED LOOPS                  |               |    11M|  1053M|    12   (0)| 00:00:01 |
|   4 |    TABLE ACCESS BY INDEX ROWID  | 고객          |    10 |   660 |     2   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN            | 고객_IDX01    |    10 |       |     1   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY USER ROWID   | 고객별연체이력|  1115K|    35M|     1   (0)| 00:00:01 |
|*  7 |     COUNT STOPKEY               |               |       |       |            |          |
|*  8 |      INDEX RANGE SCAN DESCENDING| 고객별연체이력|   558 | 14508 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          5          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch        8      0.00       0.00          0        176          0          40
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       16      0.00       0.01          0        181          0          40

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  COUNT STOPKEY (cr=30 pr=0 pw=0 time=301 us)
     10   INDEX RANGE SCAN DESCENDING 고객별연체이력_IDX01 (cr=30 pr=0 pw=0 time=201 us)(object id 52904)
     10  NESTED LOOPS  (cr=44 pr=0 pw=0 time=111 us)
     10   TABLE ACCESS BY INDEX ROWID 고객 (cr=4 pr=0 pw=0 time=255 us)
     10    INDEX RANGE SCAN 고객_IDX01 (cr=2 pr=0 pw=0 time=137 us)(object id 52902)
     10   TABLE ACCESS BY USER ROWID 고객별연체이력 (cr=40 pr=0 pw=0 time=445 us)

-- 인덱스 이외의 컬럼을 참조하지 않았으므로 인덱스만 스캔 예상(TABLE ACCESS BY USER ROWID)
-- 일반적인 NL조인과 같은 Outer 인덱스 > Outer 테이블 > Inner 인덱스 > Inner 테이블 로 진행

-- 스칼라 서브쿼리를 이용하지않고


SELECT /*+ ORDERED USE_NL(B) ROWID(B) */
           A.고객명, A.거주지역, A.주소, A.연락처, B.연체금액, B.연체개월수
FROM   고객 A, 고객별연체이력 B
WHERE  A.가입회사 = 'C70'
AND      B.ROWID = (SELECT /*+ INDEX(C 고객별연체이력_IDX01) */ ROWID
                           FROM    고객별연체이력 C
                           WHERE  C.고객번호 = A.고객번호
                           AND      C.변경일자 <= A.서비스만료일
                           AND      ROWNUM <= 1);
 
----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |   111K|    10M|    15   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |               |    11M|  1053M|    12   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| 고객          |    10 |   660 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | 고객_IDX01    |    10 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY USER ROWID | 고객별연체이력|  1115K|    35M|     1   (0)| 00:00:01 |
|*  5 |    COUNT STOPKEY             |               |       |       |            |          |
|*  6 |     INDEX RANGE SCAN         | 고객별연체이력|   558 | 14508 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

SELECT   /*+ ORDERED USE_NL(B) ROWID(B) */
         A.고객명, A.거주지역, A.주소, A.연락처, B.연체금액, B.연체개월수
FROM     고객 A, 고객별연체이력 B
WHERE    A.가입회사 = 'C70'
AND      B.ROWID = (SELECT /*+ INDEX(C 고객별연체이력_IDX01) */ ROWID
                           FROM    고객별연체이력 C
                           WHERE   C.고객번호 = A.고객번호
                           AND      C.변경일자 <= A.서비스만료일
                           AND      ROWNUM <= 1)


Rows     Row Source Operation
-------  ---------------------------------------------------
     10  NESTED LOOPS  (cr=44 pr=0 pw=0 time=107 us)
     10   TABLE ACCESS BY INDEX ROWID 고객 (cr=4 pr=0 pw=0 time=226 us)
     10    INDEX RANGE SCAN 고객_IDX01 (cr=2 pr=0 pw=0 time=132 us)(object id 52902)
     10   TABLE ACCESS BY USER ROWID 고객별연체이력 (cr=40 pr=0 pw=0 time=413 us)
    
- 정해진 시점 기준으로 조회



- 전제 : 가입회사 = 'C70' 은 고객 10명 이였는데 가입회사별 고객수가 많을 경우 서브 쿼리 수행 횟수가 늘어나

         랜덤 I/O 부하도 심해질 것이다.
         가입회사 조건이 없고 정해진 시점을 기준으로 조회 한다면


SELECT /*+ FULL(A) FULL(B) FULL(C) USE_HASH(A B C) NO_MERGE(B) */
       A.고객명, A.거주지역, A.주소, A.연락처, C.연체금액, C.연체개월수
FROM   고객 A
       ,(SELECT 고객번호, MAX(변경일자) 변경일자
       FROM   고객별연체이력
       WHERE  변경일자 <= TO_CHAR(SYSDATE, 'YYYYMMDD')
       GROUP BY 고객번호) B, 고객별연체이력 C
WHERE  B.고객번호 = A.고객번호
AND    C.고객번호 = B.고객번호
AND    C.변경일자 = B.변경일자;


--------------------------------------------------------------------------------------------------
| Id  | Operation                | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |               |   400K|    45M|       |  5080   (1)| 00:01:01 |
|*  1 |  HASH JOIN               |               |   400K|    45M|       |  5080   (1)| 00:01:01 |
|   2 |   TABLE ACCESS FULL      | 고객          |    10 |   550 |       |     3   (0)| 00:00:01 |
|*  3 |   HASH JOIN              |               |   400K|    24M|   984K|  5072   (1)| 00:01:01 |
|   4 |    VIEW                  |               | 32331 |   599K|       |   754   (1)| 00:00:10 |
|   5 |     SORT GROUP BY        |               | 32331 |   599K|       |   754   (1)| 00:00:10 |
|*  6 |      INDEX FAST FULL SCAN| 고객별연체이력| 32331 |   599K|       |   751   (1)| 00:00:10 |
|   7 |    TABLE ACCESS FULL     | 고객별연체이력|  1115K|    47M|       |   789   (3)| 00:00:10 |
--------------------------------------------------------------------------------------------------

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  HASH JOIN  (cr=6838 pr=6774 pw=0 time=2081110 us)
     10   TABLE ACCESS FULL 고객 (cr=3 pr=0 pw=0 time=92 us)
     10   HASH JOIN  (cr=6835 pr=6774 pw=0 time=2075882 us)
     10    VIEW  (cr=3367 pr=3325 pw=0 time=2050235 us)
     10     SORT GROUP BY (cr=3367 pr=3325 pw=0 time=2050210 us)
  11410      INDEX FAST FULL SCAN 고객별연체이력_IDX01 (cr=3367 pr=3325 pw=0 time=22883 us)(object id 52904)
1000000    TABLE ACCESS FULL 고객별연체이력 (cr=3468 pr=3449 pw=0 time=1000057 us)


-- 위의 쿼리는 고객별연체이력 테이블을 두번 Full Scan하는 비효율


SELECT A.고객명, A.거주지역, A.주소, A.연락처
       , TO_NUMBER(SUBSTR(연체, 11)) 연체금액
       , TO_NUMBER(SUBSTR(연체, 9, 2)) 연체개월수
FROM   고객 A
       ,(SELECT 고객번호, MAX(변경일자 || LPAD(연체개월수, 2) || 연체금액 ) 연체
       FROM   고객별연체이력
       WHERE  변경일자 <= TO_CHAR(SYSDATE, 'YYYYMMDD')
       GROUP BY 고객번호) B
WHERE  B.고객번호 = A.고객번호;

 
--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         | 32331 |  3031K|   831   (7)| 00:00:10 |
|*  1 |  HASH JOIN           |         | 32331 |  3031K|   831   (7)| 00:00:10 |
|   2 |   TABLE ACCESS FULL  | 고객    |    10 |   550 |     3   (0)| 00:00:01 |
|   3 |   VIEW               |         | 32331 |  1294K|   827   (7)| 00:00:10 |
|   4 |    SORT GROUP BY     |         | 32331 |  1420K|   827   (7)| 00:00:10 |
|*  5 |     TABLE ACCESS FULL| 고객별연| 32331 |  1420K|   824   (7)| 00:00:10 |
--------------------------------------------------------------------------------

Rows     Row Source Operation
-------  ---------------------------------------------------
     10  HASH JOIN  (cr=3470 pr=2979 pw=0 time=2130794 us)
     10   TABLE ACCESS FULL 고객 (cr=3 pr=0 pw=0 time=102 us)
     10   VIEW  (cr=3467 pr=2979 pw=0 time=2129922 us)
     10    SORT GROUP BY (cr=3467 pr=2979 pw=0 time=2129916 us)
  11410     TABLE ACCESS FULL 고객별연체이력 (cr=3467 pr=2979 pw=0 time=34273 us)



-- 분석함수 사용시 편리하고 수행속도에도 전혀 불리 하지 않다.(5장 6절 3항에서 다시)


SELECT A.고객명, A.거주지역, A.주소, A.연락처, B.연체금액, B.연체개월수
FROM   고객 A
     ,(SELECT 고객번호, 연체금액, 연체개월수, 변경일자
            , ROW_NUMBER() OVER (PARTITION BY 고객번호 ORDER BY 변경일자 DESC) NO
       FROM   고객별연체이력
       WHERE  변경일자 <= TO_CHAR(SYSDATE, 'YYYYMMDD')) B
WHERE  B.고객번호 = A.고객번호
AND    B.NO = 1;


-- MAX 함수를 이용 헐수도 있지만 위와같이 row_number를 이용하는 것이 더 효과적이다.(5장 6절에서 다시)


SELECT A.고객명, A.거주지역, A.주소, A.연락처, B.연체금액, B.연체개월수
FROM   고객 A
     ,(SELECT 고객번호, 연체금액, 연체개월수, 변경일자
            , MAX(변경일자) OVER (PARTITION BY 고객번호) MAX_DT
       FROM   고객별연체이력
       WHERE  변경일자 <= TO_CHAR(SYSDATE, 'YYYYMMDD')) B
WHERE  B.고객번호 = A.고객번호
AND    B.변경일자 = B.MAX_DT;



 

오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
작성자: 김범석 (darkbeom)
최초작성일: 2011년 4월 3일
본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^


번호 제목 글쓴이 날짜 조회 수
60 Front Page file 운영자 2011.02.16 149417
59 3. 인덱스 파티셔닝 darkbeom 2011.06.20 53806
58 3. 다양한 인덱스 스캔 방식 file 멋진넘 2011.02.19 33798
57 8. 통계정보 Ⅱ [1] 멋진넘 2011.04.30 31051
56 2. 파티션 Pruning 실천하자 2011.06.22 26011
55 3. 뷰 Merging 실천하자 2011.05.15 23376
54 3. 해시 조인 file darkbeom 2011.03.21 21526
53 2. 서브쿼리 Unnesting darkbeom 2011.05.16 19691
52 4. 통계정보 Ⅰ darkbeom 2011.04.26 18083
51 7. 인덱스 스캔 효율 [1] 휘휘 2011.03.09 16878
50 7. Sort Area 크기 조정 실천하자 2011.06.14 15059
49 4. 테이블 Random 액세스 부하 [1] file darkbeom 2011.02.24 14672
48 4. 조인 순서의 중요성 운영자 2011.03.28 14232
47 1. 인덱스 구조 [1] file 실천하자 2011.02.16 14183
46 1. 기본 개념 멋진넘 2011.06.28 13385
» 8. 고급 조인 테크닉-1 [1] file darkbeom 2011.04.04 13259
44 9. 비트맵 인덱스 file 실천하자 2011.03.06 12335
43 1. 옵티마이저 file 실천하자 2011.04.18 11203
42 6. 히스토그램 실천하자 2011.04.25 10910
41 6. Sort Area를 적게 사용하도록 SQL 작성 file 실천하자 2011.06.14 8850