[Tuning] perfect_study_03

 
[사례연구 #08] Sub-query로 Join해결

 * Join과 Sub-query를 성능상 비교 대상이 될 수 있다.

   기본적으로 테이블에 대한 데이터 처리를 join에서 할 때와 sub-query
   에서 할 때를 서로 비교함으로써 상대적으로 적게 데이터를 처리할 수
   있도록 하는 방법을 선택할 수 있어야 한다.
 
   - Correlated Sub-query와 Nested Sub-query 사용을 통해서 조인에 
     대한 튜닝을 하고자 함.

     ① Correlated sub-query의 실행원리
        항상 main-query가 먼저 실행되며, 이때 데이터가 추출되는 가운데
        sub-query가 반복해서 실행된다.
    
     ② Nested sub-query의 실행원리
        일반적으로 sub-query가 먼저 실행되며, 그 후에 main-query가
        실행된다.

        ㈜ Sub-query의 수행결과에 비교되는 Main-query컬럼에
           인덱스가 없으면 먼저 수행되지 않는다.

           또한 Correlated Sub_query와 함께 OR조건에 의해서
           사용되고 있다면 먼저 수행되지 않는다.

          →  WHERE (Nested Subquery) OR (Correlated Subquery)

              * 모든 Subquery가 메인쿼리보다 나중에 행된다!!!

 
[사례연구 #09] Scalar 서브쿼리 활용

 * Join을 튜닝할 때 사용할 수 있는 방법으로써, User Defined Function과
   Scalar subquery를 서로 비교함.

   이 때 테이블에 대한 데이터 처리를 join에서 할 때와 두 방법에서
   할 때를 서로 비교함으로써 상대적으로 데이터를 적게 처리할 수
   있도록 하는 방법을 선택할 수 있어야 한다. 

 * UDF와 Scalar subquery의 특성

   ① 이들은 두 건 이상의 데이터를 한 번에 return할 수 없다.
   ② 찾고자 하는 데이터가 없는 경우엔 NULL을 return한다.
   ③ 이들은 마치 loop를 수행하는 것과 같이 SQL을 실행하게 된다.
      특히, 이 부분을 주의해야 함.

  → UDF 사용 보다는 Scalar subquery에 의한 퍼포먼스가 더 좋다.

 

Part 4. Analytic Functions 활용

분석함수(Analytic Functions)는 복잡한 business logic을 구현하고,
대용량 데이터를 효율적으로 처리할 수 있게 하는 함수이다.

이러한 함수는  대부분의 Self join 과 Self subquery 형태의 SQL을
튜닝하는 차원에서 활용할 수 있는  수단이 될 수 있다.

따라서 Part 4 에서는 주어진 사례를 통해서 분석함수 활용 방안
에 대해서 알아보고자 한다.

 

[사례연구 #10] Self-조인 해결Ⅰ

 * Analytic functions 사용을 통해서 기존의 self-join을 제거할 수
   있도록 한다. 이러한 함수는 self-subquery를 제거하는 차원에서도
   사용할 수 있다. (Analytic functions은 v8i 부터 사용 가능)

 * GROUP함수 KEEP( )

  SELECT BRNCOD,CMPID,
         MAX(STSDAT) KEEP(DENSE_RANK FIRST ORDER BY STSDAT DESC, STS DESC),
         MAX(STS)    KEEP(DENSE_RANK FIRST ORDER BY STSDAT DESC, STS DESC),
         MAX(AMT)    KEEP(DENSE_RANK FIRST ORDER BY STSDAT DESC, STS DESC)   
    FROM CUSTOMER
   WHERE STSDAT <= '20031130'
  GROUP BY BRNCOD, CMPID;

  SELECT BRNCOD,CMPID,
         MAX(STSDAT) KEEP(DENSE_RANK LAST ORDER BY STSDAT ASC, STS ASC),
         MAX(STS)    KEEP(DENSE_RANK LAST ORDER BY STSDAT ASC, STS ASC),
         MAX(AMT)    KEEP(DENSE_RANK LAST ORDER BY STSDAT ASC, STS ASC)   
    FROM CUSTOMER
   WHERE STSDAT <= '20031130'
  GROUP BY BRNCOD, CMPID;

  본 예제의 경우 다음과 같은 analytic functions를 사용하였다.

  1) FIRST_VALUE(expr) OVER([PARTITION BY][ORDER BY][WINDOWING절])
  2) LAST_VALUE(expr)  OVER([PARTITION BY][ORDER BY][WINDOWING절])
  3) MAX(expr)         OVER([PARTITION BY][ORDER BY][WINDOWING절])

 
  * MAX(...) OVER(...) 함수 사용에 의한 SQL - 추가 작성

 SELECT C.BRNCOD, C.CMPID, C.STSDAT, C.STS, C.AMT
   FROM (SELECT BRNCOD, CMPID, STSDAT, STS,
                MAX(STSDAT||STS)
                OVER(PARTITION BY BRNCOD, CMPID
                     ORDER BY STSDAT DESC, STS DESC
                     ROWS UNBOUNDED PRECEDING) MAX_DTSTS,
               AMT
          FROM CUSTOMER
         WHERE STSDAT <= '20031130') C
 WHERE C.STSDAT = SUBSTR(C.MAX_DTSTS,1,8)
   AND C.STS    = SUBSTR(C.MAX_DTSTS,9,1)
   AND C.AMT > 0;

 SELECT C.BRNCOD, C.CMPID, C.STSDAT, C.STS, C.AMT
   FROM (SELECT BRNCOD, CMPID, STSDAT, STS,
                MAX(STSDAT||STS)
                OVER(PARTITION BY BRNCOD, CMPID
                ORDER BY STSDAT ASC, STS ASC
                ROWS BETWEEN CURRENT ROW
                         AND UNBOUNDED FOLLOWING) MAX_DTSTS,
               AMT
          FROM CUSTOMER
         WHERE STSDAT <= '20031130') C
 WHERE C.STSDAT = SUBSTR(C.MAX_DTSTS,1,8)
   AND C.STS    = SUBSTR(C.MAX_DTSTS,9,1)
   AND C.AMT > 0;

 

[사례연구 #11] Self-조인 해결Ⅱ
 
 * [사례연구 #10]과 마찬가지로 self-join 제거를 위해 사용할 수 있는
   Analytic Function에 대해서 학습

※ 본 예제의 경우 아래의 analytic function를 사용하였다.

 1) LEAD(expr) OVER([PARTITION BY][ORDER BY])

  ~ 각 데이터를 기준으로 바로 그 다음 데이터를 처리토록 함
    ㈜ Windowing절을 갖지 않는다.
 
 2) LAG(expr) OVER([PARTITION BY][ORDER BY])
 
 ~ 각 데이터를 기준으로 바로 앞의 데이터를 처리토록 함
    ㈜ Windowing절을 갖지 않는다.
  
    eg) 직전 가격처리 예제

    SELECT CODE, SEQ_NO, PRICE AS CUR_PRI,
           LAG(PRICE,1,0) OVER(PARTITION BY CODE
                          ORDER BY SEQ_NO ASC) AS BEF_PRI
      FROM DANGA_HIST;

 * Outer 조인과 같은 결과를 낳는 U.D.F(User Defined Function) 사용
                                =============================
  → 종료일을 처리하기 위한 차원에서 User Defined Function 사용

CREATE OR REPLACE FUNCTION FN_ENDYMD(A_CD IN VARCHAR2, A_SQ IN VARCHAR2)
RETURN VARCHAR2
IS
   H_END_YMD   DANGA_HIST.BEGIN_YMD%TYPE := NULL;
BEGIN

   SELECT BEGIN_YMD INTO H_END_YMD
     FROM DANGA_HIST
    WHERE CODE = A_CD
      AND SEQ_NO = A_SQ + 1;

  RETURN H_END_YMD;
END;
/

     ----------------------------------------------------------
→   SELECT CODE, SEQ_NO, PRICE, BEGIN_YMD,
            NVL(FN_ENDYMD(CODE, SEQ_NO),'99991231') AS END_YMD
       FROM DANGA_HIST
      ORDER BY CODE ASC, SEQ_NO ASC;
     ----------------------------------------------------------

Part 5. Cartesian Product

Cartesian Product는 다음과 같은 조인을 수행할 때 발생한다.
   ① WHERE절 없는 조인 수행
   ② 조인을 위한 조건 없이 조인 수행

Cartesian Product는 "데이터 복제" 라는 개념을 활용하기 위해 사용하게
된다. 그 러나 잘못 사용하는 경우에는 오히려 데이터를 부풀리는 원인이  
되기 때문에 퍼포먼스를 오히려 나쁘게 할 수도 있다.

Part 5 에서는 Cartesian Product를 적용해야 할 대상을 사례별로 다루어
봄으로써, Cartesian Product를 정확하게 사용할 수 있도록 하고자 한다.

  참고) Cartesian Product에 의한 '2011'년 날짜 생성

      SELECT YMD
        FROM (SELECT '2011'||B.DAY||A.DAY AS YMD,
                     TO_CHAR(LAST_DAY(TO_DATE('2011'||B.DAY,'YYYYMM'))
                             ,'YYYYMMDD') AS L_DAY, ROWNUM
                FROM EC_DAY A, EC_DAY B
               WHERE B.DAY <= '12')
        WHERE YMD <= L_DAY;