[Tuning] perfect_study_04

Part 5. Cartesian Product

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

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

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

 

[사례연구 #12] Cartesian Product의 응용Ⅰ

  * Cartesian Product를 응용한 대표적인 사례

☞ ① FROM 절과 WHERE 조건이 같으면서 UNION ALL을 사용한 SQL
   ② 데이터베이스에 없는 데이터를 있는 것처럼 할 때
   ③ 데이터 모델링이 잘못 되었을 때

  → Cartesian Product는 "데이터 복제"라는 개념을 활용하기 위해서
                         ============
     사용함. 데이터 복제에 대한 작업으로 인해 발생하는 Disk I/O는
     발생하지 않음.

     하지만 이를 잘못 사용하는 경우 오히려 데이터를 부풀리는 원인이
     됨으로써 퍼포먼스 향상을 달성하지 못할 수도 있음.

  * Cartesian Product 적용 대상 중에는 ROLLUP, CUBE, GROUPING SETS(v9i)
    를 사용할 수 있는 것이 있음.

    → 함수를 사용할 수 있는 대상은 일부라 할 수 있지만, 그러나 함수를
       사용할 수 있는 상황이라면 함수를 사용해서 SQL을 구현하는 것이
       cartesian product를 잘못하게 됨으로써 있을 수 있는 문제를 근본적
       으로 해결할 수 있는 방법이 될 수 있다.

  * GROUPING SETS 사용

    1) ROLLUP(COURSE_CODE, YEAR)

    SELECT COURSE_CODE, YEAR, NVL(SUM(DEPOSIT_AMOUNT),0) AS S_T_AMT
      FROM EC_APPLY
     WHERE COURSE_CODE < 30
     GROUP BY GROUPING SETS((COURSE_CODE,YEAR), (COURSE_CODE), ());

    2) CUBE(COURSE_CODE, YEAR)

    SELECT COURSE_CODE, YEAR, NVL(SUM(DEPOSIT_AMOUNT),0) AS S_T_AMT
      FROM EC_APPLY
     WHERE COURSE_CODE < 30
     GROUP BY GROUPING SETS((COURSE_CODE,YEAR), (COURSE_CODE), (YEAR), ())
     ORDER BY 1, 2;

  * 참고 - 사례연구 #12에서 다음과 같이 출력하기 위해서는 ...

 GUBUN       CD_YR    S_T_AMT
 ------ ---------- ----------
 과정별          1          0
                 2          0
                10          0
                14     270000
                28   20560000
                29   20969800
 년도별       1998    2250000
              1999          0
              2000   37299800
              2001    2250000
 총합                41799800

 

 SELECT DECODE(RN, 1, GUBUN) AS GUBUN,
        CD_YR, S_T_AMT
   FROM (SELECT ROW_NUMBER() OVER(PARTITION BY GUBUN ORDER BY GUBUN) AS RN,
                GUBUN, CD_YR, S_T_AMT
          FROM (SELECT CASE WHEN G1 = 0 AND G2 = 1 THEN '과정별'
                            WHEN G1 = 0 AND G2 = 0 THEN '년도별'
                            ELSE '총합' END  AS GUBUN,
                       CASE WHEN G1 = 0 AND G2 = 1 THEN COURSE_CODE
                            WHEN G1 = 0 AND G2 = 0 THEN TO_NUMBER(YEAR)
                            ELSE NULL END  AS CD_YR,
                       NVL(SUM(T_AMT),0) AS S_T_AMT
                  FROM (SELECT COURSE_CODE,
                               YEAR,
                               SUM(DEPOSIT_AMOUNT) AS T_AMT,
                               GROUPING(COURSE_CODE) AS G1,
                               GROUPING(YEAR) AS G2
                          FROM EC_APPLY
                         WHERE COURSE_CODE < 30
                         GROUP BY ROLLUP(COURSE_CODE, YEAR))
                 GROUP BY CASE WHEN G1 = 0 AND G2 = 1 THEN '과정별'
                               WHEN G1 = 0 AND G2 = 0 THEN '년도별'
                               ELSE '총합' END,
                          CASE WHEN G1 = 0 AND G2 = 1 THEN COURSE_CODE
                               WHEN G1 = 0 AND G2 = 0 THEN TO_NUMBER(YEAR)
                               ELSE NULL END));

[사례연구 #13] Cartesian Product의 응용Ⅱ

  * Cartesian Product를 응용한 대표적인 사례

   ① FROM 절과 WHERE 조건이 같으면서 UNION ALL을 사용한 SQL
☞ ② 데이터베이스에 없는 데이터를 있는 것 처럼 할 때
   ③ 데이터 모델링이 잘못 되었을 때

  참고) 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;

 

[사례연구 #14] Cartesian Product의 응용Ⅲ

  * Cartesian Product를 응용한 대표적인 사례

   ① FROM 절과 WHERE 조건이 같으면서 UNION ALL을 사용한 SQL
   ② 데이터베이스에 없는 데이터를 있는 것 처럼 할 때
☞ ③ 데이터 모델링이 잘못 되었을 때

   ★ 테이블의 설계 변경에 따른 데이터 변환 방법으로써 사용하는 경우임

      ~  반정규화(나열식(형)) →  정규화

   참고) 정규화 → 반정규화(나열식(형))

       SQL> SELECT ID, MAX(DECODE(GUBUN,1,TEL)) AS TEL1,
                       MAX(DECODE(GUBUN,2,TEL)) AS TEL2,
                       MAX(DECODE(GUBUN,3,TEL)) AS TEL3
              FROM PERSONAL1
             GROUP BY ID;

 

Part 6. Tuning 활용

집계 테이블(Summary table)에 대한 데이터 생성과  관련하여
전반적으로 생각해 보는 것과 Dynamic SQL 구현 및 실행으로
인해 발생할 수 있는 문제를 해결할 수 있게 하는 Binding Vari-
able활용 방법, 그리고 함수를 지나치게 많이 사용하게 됨으로써
발생하는 문제를 해결할 수 있는 방법을 알아 보고자 한다.

[사례연구 #15] 집계 작업의 효율성 제고

 * 집계 테이블로 인한 문제

   ① 데이터 생성을 위한 프로그램 개발 및 관리
   ② 적절한 Job 스케쥴 수립 및 관리
   ③ 집계 작업이 실행된 이후 생기는 데이터 수정에 따른 재집계
   ④ 실시간 데이터가 아님.

 * 집계테이블에 대한 데이터 생성은 주로 Batch 작업에 의해서 이루어
   지며, 이때 일괄적으로 데이터를 모두 처리하는 식으로 작업을 한다.
   이로 인해 처리하려는 데이터가 작을 때는 정해진 시간 내에 작업이
   완료될 수 있으나 데이터가 많은 경우에는 정해진 시간 내에 작업을
   완료할 수 없게 된다.

 
 * 집계테이블 상에 설계되어 있는 "누계"와 같은 항목을 처리할 때
   효율적으로 처리할 수 있도록 해야 한다.

  → "누계"를 SQL로 구현하는 경우 analytic function을 사용을 고려

      SUM(S_AMT) OVER([PARTITION BY][ORDER BY][WINDOWING절])

 * 컬럼 제거

   SQL> ALTER TABLE SUM_OF_AMOUNT SET UNUSED COLUMN T_S_AMT;
        → 거의 시간이 소요되지 않는다.

   SQL> ALTER TABLE SUM_OF_AMOUNT DROP UNUSED COLUMNS;
        → 데이터가 많은 테이블일수록 시간이 많이 소요된다.

  ★ 대용량 데이터에 대한 DML 작업시 아래의 에러가 발생할 수 있음
     → ORA-1555: snapshot too old: rollback segment %s too small

    - 해결책
      ① 롤백 세그먼트의 크기를 늘려줌으로써 필요한 롤백 정보가 다른
         트랜잭션에 의해 지워질 가능성을 줄이도록 한다. 
      ② 아주 큰 작업 전용의 롤백 세그먼트를 추가로 생성한다.
      ③ 처리하고자 하는 데이터가 클(많은) 경우, 데이터를 나누어서
         작업을 여러번 수행할 수 있도록 한다.

  ★ 실시간적 또는 과거형으로 "집계현황"을 제공할 수 있는 집계테이블
     ========     ======

     → "Materialized View" 또는 "Mview"
       
       * Mview는 마치 view 처럼 SQL을 갖고 있으면서, 데이터와 인덱스
         를 갖는 테이블이다.

        ~ Refresh fast on commit
        ~ Refresh fast on demand

        │실시간│       │ Log   │     │집 계 │
        │데이터│  -->  │       │ --> │      │
        │테이블│       │(I,U,D)│     │테이블│
 

          □ Materialized View와 관련된 시스템 dictionary
 
            1) DBA_MVIEWS
               - Materialized View에 대한 전체적인 정보를 보여준다.
 
            2) DBA_MVIEW_LOGS
               - Materialized View Log에 대한 전체적인 정보를 보여준다.
 
            3) DBA_REGISTERED_SNAPSHOTS
               - Materialized View에 대한 시스템 정보를 보여준다.
                 예를 들면, current_snapshots Column에는 마지막으로
                 Refresh 된 시간을 보여준다.
 
            4) DBA_SNAPSHOT_LOGS
               - Materialized View Log에 대한 시스템 정보를 보여준다.
 
 
   Example
   =======
 
  ※ MASTER Table EMP에 대한 Materialized View의 정보를 조회해본다.
  
    SQL> select log_owner, master, log_table
           from dba_snapshot_logs
          where master = 'EMP';
    
    LOG_OWNER             MASTER          LOG_TABLE
    --------------------  -------------   ---------------
    ECAMPUS               EMP             MLOG$_EMP
 
 
  ※ MASTER Table EMP에 대한 Materialized View Location의 정보를 조회해본다.
 
    SQL> select l.log_owner, r.name, r.snapshot_site 
           from dba_registered_snapshots r,
                dba_snapshot_logs        l
          where r.snapshot_id = l.snapshot_id 
            and l.master='EMP';
    
    OWNER       NAME          SNAPSHOT_SITE
    ----------  ------------  ---------------
    ECAMPUS     MV_EMP        DBA00

   
  ※ MASTER Table EMP에 대한 가장 최근 Refresh Time을 조회해 본다.
 
    SQL> select r.name, r.snapshot_site
               ,to_char(l.current_snapshots,'YYYY-MM-DD, HH24:MI:SS') as refresh_date
           from  dba_registered_snapshots  r,
                 dba_snapshot_logs         l 
          where r.snapshot_id = l.snapshot_id
            and l.master='EMP';

    NAME            SNAPSHOT_SITE       REFRESH_DATE
    --------------- ------------------- ---------------------
    MV_EMP          DBA00               2010-08-19, 16:53:03