-
변경이력에서 여러 건을 조회 할 때의 해결방법

-변경이력 조회시 rownum의 활용

 

들어가기 전에
이 글을 읽기 위해서 지난번 글(변경이력 테이블에 종료일자가 필요한가?)을 먼저 읽고 오기 바란다. 그렇지 않으면 이해하기 힘들며 이 글과 어떻게 연결되는지 알 수 없다.

이전 글이 다분히 이론적이었다면 이번에는 실전적인 문제를 다룬다. 이 글에서 언급된 기법을 알지 못하면 실무에서 종료일자가 없이 시작일자만을 사용했을때 성능이 떨어질 것이다.

이 글은 종료일자의 장단점에 대해 충분한 고민후에 사용했던 사람들에 대한 공격이 아님을 일러둔다. 역정규화에 대한 위험성을 알리고, 정규형 또한 빠른 성능을 가졌다는 것을 증명함으로써 종료일자를 사용할 때는 많은 고민이 있어야 한다는 것을 주장하는 것이다.  

글의시작
지난 글에서 뜨거운 토론이 있었다. 무려 댓글이 28개나 달렸으며 의견도 크게 두 가지로 갈렸다. 즉 데이터 정합성을 위해 종료일자를 자제해야 한다는 측과 성능관점에서는 장점이 워낙 크므로 종료일자를 사용해야 한다는 측의 두 가지이다. 여러 반론들도 쏟아졌다. 현실 세계의 다양하고 복잡한 SQL이 있으므로 지난 시간에 언급했던 세가지 유형만으로는 충분하지 않다는 것. 일리가 있다. 그래서 이번 시간에는 현실에서 많이 나오는 패턴에 대하여 알아보기로 하자. MAX 값 한 건이나 특정시점의 한 건을 구하는 것이 아니라 여러 건이 조회되는 패턴에 대해 알아보겠다지난 글에서 여러 사람들이 이야기 하는 현실적인 예제란 아래와 같았다.

  

SELECT a.svc_no, a.cust_no, a.acct_no, a.svc_date, a.txt,

       b.start_dt, b.txt, c.start_dt, c.txt, d.start_dt, d.txt

  FROM svc a, svc_hist b, acct_hist c, cust_hist d

 WHERE a.svc_date BETWEEN SYSDATE - 201 AND SYSDATE - 1

   AND b.svc_no = a.svc_no

   AND d.cust_no = a.cust_no

   AND c.acct_no = a.acct_no

   AND a.svc_date BETWEEN b.start_dt AND b.end_dt -- 서비스 변경이력

   AND a.svc_date BETWEEN c.start_dt AND c.end_dt -- 계정 변경이력

   AND a.svc_date BETWEEN d.start_dt AND d.end_dt -- 고객 변경이력

   AND b.txt > ' '

   AND c.txt > ' '

   AND d.txt > ' ' ;

 

시작일자만으로 이 SQL을 빠르게 조회할 수 있니?
조인조건을 보면 특정시점이 아닌 a.svc_date로 변경이력과 between 조인을 하고 있다. 이런 스타일의 SQL에서는 시작일자 컬럼만으로는 between을 사용할 수 없으므로 느리다는 것. 그리고 SQL의 결과는 한 건이 아니라 여러 건이기 때문에 rownum = 1 조건을 사용할 수 없으므로 역정규화를 하여 종료일자를 추가하는 것이 성능상 유리하다는 의견이 있었다. 이것이 사실일까?

 

환경 Oracle11g R2

테스트를 위하여 object를 생성하고 통계정보를 수집한다.   

 

CREATE TABLE CUST_HIST AS    --고객이력

SELECT A.CUST_NO, B.*

  FROM ( SELECT LEVEL AS  CUST_NO

          FROM DUAL

          CONNECT BY LEVEL <= 50) A,

       ( SELECT SYSDATE - level AS start_dt, --시작일시

                SYSDATE - level + 1 - 1/24/60/60 AS end_dt, --종료일시

                '종료일자의 필요성 테스트' as txt

           FROM DUAL

        CONNECT BY LEVEL <= 2000) B;

       

ALTER TABLE CUST_HIST ADD CONSTRAINT PK_CUST_HIST PRIMARY KEY (CUST_NO, START_DT) USING INDEX;

CREATE INDEX IX_CUST_HIST_01 ON TLO.CUST_HIST (CUST_NO, END_DT, START_DT) ;

       

CREATE TABLE ACCT_HIST AS     -- 계정이력

SELECT A.*, B.*

  FROM ( SELECT LEVEL AS  ACCT_NO

          FROM DUAL

          CONNECT BY LEVEL <= 50) A,

       ( SELECT SYSDATE - level AS start_dt, --시작일시

                SYSDATE - level + 1 - 1/24/60/60 AS end_dt, --종료일시

                '종료일자의 필요성 테스트' as txt

           FROM DUAL

        CONNECT BY LEVEL <= 2000) B;

      

ALTER TABLE ACCT_HIST ADD CONSTRAINT PK_ACCT_HIST PRIMARY KEY (ACCT_NO, START_DT) USING INDEX;

CREATE INDEX IX_ACCT_HIST_01 ON ACCT_HIST (ACCT_NO, END_DT, START_DT) ;

       

CREATE TABLE SVC AS         --서비스

SELECT *

  FROM ( SELECT LEVEL AS svc_no,

                TRUNC(dbms_random.value(1,51)) AS CUST_NO,

                TRUNC(dbms_random.value(1,51))  AS ACCT_NO,

                SYSDATE - level + 1 - 0.5 AS SVC_DATE, --서비스 가입일시

                '종료일자의 필요성 테스트' as txt

           FROM DUAL CONNECT BY LEVEL <= 2000) A ;

          

ALTER TABLE SVC ADD CONSTRAINT PK_SVC PRIMARY KEY (SVC_NO) USING INDEX;

CREATE INDEX IX_SVC_01 ON SVC (SVC_DATE) ;

 

CREATE TABLE SVC_HIST AS   --서비스이력

SELECT A.svc_no, B.*

  FROM ( SELECT svc_no

           FROM SVC) A,

       ( SELECT SYSDATE - level AS start_dt, --시작일시

                SYSDATE - level + 1 - 1/24/60/60 AS end_dt, --종료일시

                '종료일자의 필요성 테스트' as txt

           FROM DUAL

        CONNECT BY LEVEL <= 2000) B;

       

ALTER TABLE SVC_HIST ADD CONSTRAINT PK_SVC_HIST PRIMARY KEY (SVC_NO, START_DT) USING INDEX NOLOGGING; 

CREATE INDEX IX_SVC_HIST_01 ON SVC_HIST (SVC_NO, END_DT, START_DT) NOLOGGING;      

 

begin

    dbms_stats.gather_table_stats(user, 'SVC_HIST', cascade => true);

    dbms_stats.gather_table_stats(user, 'CUST_HIST', cascade => true);

    dbms_stats.gather_table_stats(user, 'ACCT_HIST', cascade => true);

    dbms_stats.gather_table_stats(user, 'SVC', cascade => true);

end;

 

이제 테스트를 수행해보자. 종료일자 + 시작일자 인덱스의 장점은 최근 데이터를 구할 때 효율적이다. 따라서 종료일자 + 시작일자 인덱스를 이용하여 비교적 최근 데이터 200 건을 조회해보자. 종료일자 + 시작일자 인덱스를 강제로 사용하기 위해 힌트를 사용하였다.

 

SELECT /*+ USE_NL(A B C D) INDEX(A IX_SVC_01) INDEX(B IX_SVC_HIST_01)
           INDEX(C IX_ACCT_HIST_01) INDEX(D IX_CUST_HIST_01) */

       a.svc_no, a.cust_no, a.acct_no, a.svc_date, a.txt,

       b.start_dt, b.txt, c.start_dt, c.txt, d.start_dt, d.txt

  FROM svc a, svc_hist b, acct_hist c, cust_hist d

 WHERE a.svc_date BETWEEN SYSDATE - 201 AND SYSDATE - 1

   AND b.svc_no = a.svc_no

   AND d.cust_no = a.cust_no

   AND c.acct_no = a.acct_no

   AND a.svc_date BETWEEN b.start_dt AND b.end_dt

   AND a.svc_date BETWEEN c.start_dt AND c.end_dt

   AND a.svc_date BETWEEN d.start_dt AND d.end_dt

   AND b.txt > ' '

   AND c.txt > ' '

   AND d.txt > ' ' ;

 

--------------------------------------------------------------------------------------------------------------

| Id  | Operation                        | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

--------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                 |                 |      1 |        |    200 |00:00:00.04 |    1937 |

|*  1 |  FILTER                          |                 |      1 |        |    200 |00:00:00.04 |    1937 |

|   2 |   NESTED LOOPS                   |                 |      1 |        |    200 |00:00:00.04 |    1937 |

|   3 |    NESTED LOOPS                  |                 |      1 |    180M|    200 |00:00:00.09 |    1738 |

|   4 |     NESTED LOOPS                 |                 |      1 |   1868K|    200 |00:00:00.04 |    1352 |

|   5 |      NESTED LOOPS                |                 |      1 |  19399 |    200 |00:00:00.02 |     783 |

|   6 |       TABLE ACCESS BY INDEX ROWID| SVC             |      1 |    201 |    200 |00:00:00.01 |       7 |

|*  7 |        INDEX RANGE SCAN          | IX_SVC_01       |      1 |    201 |    200 |00:00:00.01 |       4 |

|*  8 |       TABLE ACCESS BY INDEX ROWID| SVC_HIST        |    200 |     96 |    200 |00:00:00.02 |     776 |

|*  9 |        INDEX RANGE SCAN          | IX_SVC_HIST_01  |    200 |     96 |    200 |00:00:00.01 |     576 |

|* 10 |      TABLE ACCESS BY INDEX ROWID | ACCT_HIST       |    200 |     96 |    200 |00:00:00.02 |     569 |

|* 11 |       INDEX RANGE SCAN           | IX_ACCT_HIST_01 |    200 |     96 |    200 |00:00:00.01 |     371 |

|* 12 |     INDEX RANGE SCAN             | IX_CUST_HIST_01 |    200 |     96 |    200 |00:00:00.01 |     386 |

|* 13 |    TABLE ACCESS BY INDEX ROWID   | CUST_HIST       |    200 |     96 |    200 |00:00:00.01 |     199 |

--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(SYSDATE@!-201<=SYSDATE@!-1)

   7 - access("A"."SVC_DATE">=SYSDATE@!-201 AND "A"."SVC_DATE"<=SYSDATE@!-1)

   8 - filter("B"."TXT">' ')

   9 - access("B"."SVC_NO"="A"."SVC_NO" AND "B"."END_DT">=SYSDATE@!-201 AND "B"."END_DT" IS NOT NULL)

       filter(("B"."START_DT"<=SYSDATE@!-1 AND "A"."SVC_DATE">="B"."START_DT" AND

              "A"."SVC_DATE"<="B"."END_DT"))

  10 - filter("C"."TXT">' ')

  11 - access("C"."ACCT_NO"="A"."ACCT_NO" AND "C"."END_DT">=SYSDATE@!-201 AND "C"."END_DT" IS NOT

              NULL)

       filter(("C"."START_DT"<=SYSDATE@!-1 AND "A"."SVC_DATE">="C"."START_DT" AND

              "A"."SVC_DATE"<="C"."END_DT"))

  12 - access("D"."CUST_NO"="A"."CUST_NO" AND "D"."END_DT">=SYSDATE@!-201 AND "D"."END_DT" IS NOT

              NULL)

       filter(("D"."START_DT"<=SYSDATE@!-1 AND "A"."SVC_DATE">="D"."START_DT" AND

              "A"."SVC_DATE"<="D"."END_DT"))

  13 - filter("D"."TXT">' ')

 

1937 블럭을 Scan하였다. 수행시간도 1초미만으로 최적이다. 그럼 이제 최근 데이터가 아닌 오래된 데이터를 구해보자. 종료일자 + 시작일자 인덱스의 단점은 오래된 데이터를 구할 때 드러난다.

 

SELECT /*+ USE_NL(A B C D) INDEX(A IX_SVC_01) INDEX(B IX_SVC_HIST_01)
           INDEX(C IX_ACCT_HIST_01) INDEX(D IX_CUST_HIST_01) */

       a.svc_no, a.cust_no, a.acct_no, a.svc_date, a.txt,

       b.start_dt, b.txt, c.start_dt, c.txt, d.start_dt, d.txt

  FROM svc a, svc_hist b, acct_hist c, cust_hist d

 WHERE a.svc_date BETWEEN SYSDATE - 1999 AND SYSDATE - 1799 -- 오래된 데이터 조회

   AND b.svc_no = a.svc_no

   AND d.cust_no = a.cust_no

   AND c.acct_no = a.acct_no

   AND a.svc_date BETWEEN b.start_dt AND b.end_dt

   AND a.svc_date BETWEEN c.start_dt AND c.end_dt

   AND a.svc_date BETWEEN d.start_dt AND d.end_dt

   AND b.txt > ' '

   AND c.txt > ' '

   AND d.txt > ' ' ;

 

--------------------------------------------------------------------------------------------------------------

| Id  | Operation                        | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

--------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                 |                 |      1 |        |    200 |00:00:02.15 |    6498 |

|*  1 |  FILTER                          |                 |      1 |        |    200 |00:00:02.15 |    6498 |

|   2 |   NESTED LOOPS                   |                 |      1 |        |    200 |00:00:02.15 |    6498 |

|   3 |    NESTED LOOPS                  |                 |      1 |    182M|    200 |00:00:01.31 |    6301 |

|   4 |     NESTED LOOPS                 |                 |      1 |   1889K|    200 |00:00:01.21 |    4425 |

|   5 |      NESTED LOOPS                |                 |      1 |  19528 |    200 |00:00:01.11 |    2344 |

|   6 |       TABLE ACCESS BY INDEX ROWID| SVC             |      1 |    202 |    200 |00:00:00.01 |       7 |

|*  7 |        INDEX RANGE SCAN          | IX_SVC_01       |      1 |    202 |    200 |00:00:00.01 |       3 |

|*  8 |       TABLE ACCESS BY INDEX ROWID| SVC_HIST        |    200 |     97 |    200 |00:00:00.77 |    2337 |

|*  9 |        INDEX RANGE SCAN          | IX_SVC_HIST_01  |    200 |     97 |    200 |00:00:00.65 |    2137 |

|* 10 |      TABLE ACCESS BY INDEX ROWID | CUST_HIST       |    200 |     97 |    200 |00:00:00.09 |    2081 |

|* 11 |       INDEX RANGE SCAN           | IX_CUST_HIST_01 |    200 |     97 |    200 |00:00:00.09 |    1887 |

|* 12 |     INDEX RANGE SCAN             | IX_ACCT_HIST_01 |    200 |     97 |    200 |00:00:00.09 |    1876 |

|* 13 |    TABLE ACCESS BY INDEX ROWID   | ACCT_HIST       |    200 |     97 |    200 |00:00:00.01 |     197 |

--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(SYSDATE@!-1999<=SYSDATE@!-1799)

   7 - access("A"."SVC_DATE">=SYSDATE@!-1999 AND "A"."SVC_DATE"<=SYSDATE@!-1799)

   8 - filter("B"."TXT">' ')

   9 - access("B"."SVC_NO"="A"."SVC_NO" AND "B"."END_DT">=SYSDATE@!-1999 AND "B"."END_DT" IS NOT NULL)

       filter(("B"."START_DT"<=SYSDATE@!-1799 AND "A"."SVC_DATE">="B"."START_DT" AND

              "A"."SVC_DATE"<="B"."END_DT"))

  10 - filter("D"."TXT">' ')

  11 - access("D"."CUST_NO"="A"."CUST_NO" AND "D"."END_DT">=SYSDATE@!-1999 AND "D"."END_DT" IS NOT NULL)

       filter(("D"."START_DT"<=SYSDATE@!-1799 AND "A"."SVC_DATE">="D"."START_DT" AND

              "A"."SVC_DATE"<="D"."END_DT"))

  12 - access("C"."ACCT_NO"="A"."ACCT_NO" AND "C"."END_DT">=SYSDATE@!-1999 AND "C"."END_DT" IS NOT NULL)

       filter(("C"."START_DT"<=SYSDATE@!-1799 AND "A"."SVC_DATE">="C"."START_DT" AND

              "A"."SVC_DATE"<="C"."END_DT"))

  13 - filter("C"."TXT">' ')

 

무려 6498 블럭을 Scan 하였다. 3배 이상 느려져서 수행시간도 2초가 넘어버렸다. 물론 오래된 데이터를 자주 사용하지 않는다면 피해가 줄어들 것이다. 하지만 가끔이라도 오래된 데이터를 조회한다면 Timeout이 발생할 수 있다. 즉 성능을 위해 종료일자 컬럼을 추가하였지만 시점이 언제인가에 따라 성능이 더 느려질 수 있다는 것이다. 이 예제에서는 오래된 데이터를 2005년도로 가정하고 조회하였다. 현실에서는 2005년이면 그리 오래된 것이 아닐 수도 있다. 만약 테스트 데이터를 더 많이 만들어 2000년도 이전 데이터를 조회한다면 더 느려질 것이다.

 

그럼 이 문제를 어떻게 해결할 거니?

먼저 type을 하나 만들자. 이제부터는 종료일자 컬럼은 없다고 가정한다. 그리고 인덱스도 시작일자 인덱스만 사용한다.

 

CREATE OR REPLACE TYPE hist_type AS OBJECT

(b_st_dt date,

 b_txt      char(24),

 c_st_dt date,

 c_txt      char(24),

 d_st_dt date,

 d_txt      char(24) );

/        

        

SELECT a.svc_no, a.cust_no, a.acct_no, a.svc_date, a.txt,

       a.h.b_st_dt, a.h.b_txt, a.h.c_st_dt, a.h.c_txt, a.h.d_st_dt, a.h.d_txt

  FROM (SELECT a.*,

               (SELECT hist_type(b.start_dt, b.txt, c.start_dt, c.txt, d.start_dt, d.txt)

                  FROM ( SELECT * FROM svc_hist  b ORDER BY start_dt DESC) b, --변경이력1

                       ( SELECT * FROM cust_hist c ORDER BY start_dt DESC) c, --변경이력2

                       ( SELECT * FROM acct_hist d ORDER BY start_dt DESC) d  --변경이력3 

                 WHERE b.svc_no = a.svc_no

                   AND b.start_dt <= a.svc_date

                   AND b.txt > ' '

                   AND c.cust_no = a.cust_no

                   AND c.start_dt <= a.svc_date

                   AND c.txt > ' '

                   AND d.acct_no = a.acct_no

                   AND d.start_dt <= a.svc_date

                   AND d.txt > ' '

                   AND ROWNUM = 1) AS h                                      -- ROWNUM 사용

          FROM svc a

         WHERE a.svc_date BETWEEN SYSDATE - 201 AND SYSDATE - 1) a ;

 

위의 SQL이 복잡해 보이지만 스칼라 서브쿼리에 집중해 주기 바란다. 스칼라 서브쿼리 내부의 인라인뷰는 변경이력 테이블들이다. 여러 개의 서로 다른 변경이력 테이블을 조회한다고 해도 from 절에 인라인뷰를 계속 추가하면 된다. 위의 예제에서는 변경이력을 3개만 사용하였다. 그리고 여러 건을 조회할 때에도 rownum을 사용할 수 있음을 알 수 있다. 여기서 인라인뷰에 order by를 사용한 것은 Index_desc 힌트와 rownum = 1 조합은 안전한가? 에서 언급된 order by가 적용된 인라인뷰와 rownum의 원리를 이용한 것이다. 그리고 FPD(Filter Push Down : 조건이 뷰 내부로 파고듦)을 활용한 것이다.

 

-----------------------------------------------------------------------------------------------------------

| Id  | Operation                        | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                 |              |      1 |        |    200 |00:00:00.01 |       7 |

|*  1 |  COUNT STOPKEY                   |              |    200 |        |    200 |00:00:00.02 |    2006 |

|   2 |   NESTED LOOPS                   |              |    200 |      1 |    200 |00:00:00.01 |    2006 |

|   3 |    NESTED LOOPS                  |              |    200 |      1 |    200 |00:00:00.01 |    1202 |

|   4 |     VIEW                         |              |    200 |      1 |    200 |00:00:00.01 |     600 |

|*  5 |      TABLE ACCESS BY INDEX ROWID | CUST_HIST    |    200 |    100 |    200 |00:00:00.01 |     600 |

|*  6 |       INDEX RANGE SCAN DESCENDING| PK_CUST_HIST |    200 |     18 |    200 |00:00:00.01 |     400 |

|   7 |     VIEW                         |              |    200 |      1 |    200 |00:00:00.01 |     602 |

|*  8 |      TABLE ACCESS BY INDEX ROWID | ACCT_HIST    |    200 |    100 |    200 |00:00:00.01 |     602 |

|*  9 |       INDEX RANGE SCAN DESCENDING| PK_ACCT_HIST |    200 |     18 |    200 |00:00:00.01 |     402 |

|  10 |    VIEW                          |              |    200 |      1 |    200 |00:00:00.01 |     804 |

|* 11 |     TABLE ACCESS BY INDEX ROWID  | SVC_HIST     |    200 |    100 |    200 |00:00:00.01 |     804 |

|* 12 |      INDEX RANGE SCAN DESCENDING | PK_SVC_HIST  |    200 |     18 |    200 |00:00:00.01 |     604 |

|* 13 |  FILTER                          |              |      1 |        |    200 |00:00:00.01 |       7 |

|  14 |   TABLE ACCESS BY INDEX ROWID    | SVC          |      1 |    201 |    200 |00:00:00.01 |       7 |

|* 15 |    INDEX RANGE SCAN              | IX_SVC_01    |      1 |    201 |    200 |00:00:00.05 |       4 |

-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM=1)

   5 - filter("C"."TXT">' ')

   6 - access("C"."CUST_NO"=:B1 AND "C"."START_DT"<=:B2)

   8 - filter("D"."TXT">' ')

   9 - access("D"."ACCT_NO"=:B1 AND "D"."START_DT"<=:B2)

  11 - filter("B"."TXT">' ')

  12 - access("B"."SVC_NO"=:B1 AND "B"."START_DT"<=:B2)

  13 - filter(SYSDATE@!-201<=SYSDATE@!-1)

  15 - access("A"."SVC_DATE">=SYSDATE@!-201 AND "A"."SVC_DATE"<=SYSDATE@!-1)

 

성능 또한 최적이다. 물론 종료일자 + 시작일자 인덱스에 비해 조금 많은 블럭을 Scan 하였지만 무시할 수 있는 정도이다. 이 정도면 종료일자 + 시작일자 인덱스 보다 우월하다. 그 이유는 오래된 데이터를 조회 할 때에도 성능이 동일 하다는 것이다. 아래의 SQL을 보자. 

 

SELECT a.svc_no, a.cust_no, a.acct_no, a.svc_date, a.txt,

       a.h.b_st_dt, a.h.b_txt, a.h.c_st_dt, a.h.c_txt, a.h.d_st_dt, a.h.d_txt

  FROM (SELECT a.*,

               (SELECT hist_type(b.start_dt, b.txt, c.start_dt, c.txt, d.start_dt, d.txt)

                  FROM ( SELECT * FROM svc_hist  b ORDER BY start_dt DESC) b,

                       ( SELECT * FROM cust_hist c ORDER BY start_dt DESC) c,

                       ( SELECT * FROM acct_hist d ORDER BY start_dt DESC) d  

                 WHERE b.svc_no = a.svc_no

                   AND b.start_dt <= a.svc_date

                   AND b.txt > ' '

                   AND c.cust_no = a.cust_no

                   AND c.start_dt <= a.svc_date

                   AND c.txt > ' '

                   AND d.acct_no = a.acct_no

                   AND d.start_dt <= a.svc_date

                   AND d.txt > ' '

                   AND ROWNUM = 1) AS h

          FROM svc a

         WHERE a.svc_date BETWEEN SYSDATE - 1999 AND SYSDATE - 1799) a ;

 

-----------------------------------------------------------------------------------------------------------

| Id  | Operation                        | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                 |              |      1 |        |    200 |00:00:00.01 |       7 |

|*  1 |  COUNT STOPKEY                   |              |    200 |        |    200 |00:00:00.02 |    2004 |

|   2 |   NESTED LOOPS                   |              |    200 |      1 |    200 |00:00:00.01 |    2004 |

|   3 |    NESTED LOOPS                  |              |    200 |      1 |    200 |00:00:00.01 |    1202 |

|   4 |     VIEW                         |              |    200 |      1 |    200 |00:00:00.01 |     602 |

|*  5 |      TABLE ACCESS BY INDEX ROWID | CUST_HIST    |    200 |    100 |    200 |00:00:00.01 |     602 |

|*  6 |       INDEX RANGE SCAN DESCENDING| PK_CUST_HIST |    200 |     18 |    200 |00:00:00.01 |     402 |

|   7 |     VIEW                         |              |    200 |      1 |    200 |00:00:00.01 |     600 |

|*  8 |      TABLE ACCESS BY INDEX ROWID | ACCT_HIST    |    200 |    100 |    200 |00:00:00.01 |     600 |

|*  9 |       INDEX RANGE SCAN DESCENDING| PK_ACCT_HIST |    200 |     18 |    200 |00:00:00.01 |     400 |

|  10 |    VIEW                          |              |    200 |      1 |    200 |00:00:00.01 |     802 |

|* 11 |     TABLE ACCESS BY INDEX ROWID  | SVC_HIST     |    200 |    100 |    200 |00:00:00.01 |     802 |

|* 12 |      INDEX RANGE SCAN DESCENDING | PK_SVC_HIST  |    200 |     18 |    200 |00:00:00.01 |     602 |

|* 13 |  FILTER                          |              |      1 |        |    200 |00:00:00.01 |       7 |

|  14 |   TABLE ACCESS BY INDEX ROWID    | SVC          |      1 |    202 |    200 |00:00:00.01 |       7 |

|* 15 |    INDEX RANGE SCAN              | IX_SVC_01    |      1 |    202 |    200 |00:00:00.01 |       3 |

-----------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM=1)

   5 - filter("C"."TXT">' ')

   6 - access("C"."CUST_NO"=:B1 AND "C"."START_DT"<=:B2)

   8 - filter("D"."TXT">' ')

   9 - access("D"."ACCT_NO"=:B1 AND "D"."START_DT"<=:B2)

  11 - filter("B"."TXT">' ')

  12 - access("B"."SVC_NO"=:B1 AND "B"."START_DT"<=:B2)

  13 - filter(SYSDATE@!-1999<=SYSDATE@!-1799)

  15 - access("A"."SVC_DATE">=SYSDATE@!-1999 AND "A"."SVC_DATE"<=SYSDATE@!-1799)

 

예전 데이터를 조회할 때에도 성능이 동일함을 알 수 있다. 이것은 매우 중요한 관점이다. 같은 SQL을 실행함에도 매우 느릴때가 있고, 매우 빠를 때가 있다면 그 SQL의 성능은 불안정하다고 할 수 있다. 이로써 이전 글에서 이슈가 되었던 여러 건을 조회할 때에도 종료일자는 필요 없음을 알 수 있다. 물론 SQL이 몇 줄 길어지긴 했지만 그 이유 때문에 역정규화의 단점인 데이터의 정합성을 해칠 수는 없는 일이다. SQL이 몇 줄 늘어나더라도 이렇게 사용해야 하는 이유는 또 있다.


역정규화할때 필요한 정합성을 체크하는 프로그램의 길이를 생각하면 몇 줄의 손해는 아무것도 아니다. 여기에 더하여 정합성을 보정하는 프로그램도 필요하므로 위의 몇 줄이 추가된 SQL이 손해라고 생각할 수는 없다. 또한 위의 SQL이 어려운 전문가용 SQL이라고 생각 하지는 않는다. 

 

우리는 Type을 쓸 수 없다

만약 type을 이용할 수 없는 환경이라면 아래의 SQL을 이용해야 한다. 비슷한 유형이지만 이 경우는 변경이력 테이블을 두 번 Scan하는 비효율을 감안해야 한다. 물론 이때에도 rownum = 1은 사용할 수 있다. 먼저 최근의 데이터를 조회한다.

 

SELECT a.svc_no, a.cust_no, a.acct_no, a.svc_date, a.txt,

       b.start_dt, b.txt, c.start_dt, c.txt, d.start_dt, d.txt

  FROM (SELECT a.*,

               (SELECT b.rowid||c.rowid||d.rowid as rid

                  FROM ( SELECT * FROM svc_hist  b ORDER BY start_dt DESC) b,

                       ( SELECT * FROM cust_hist c ORDER BY start_dt DESC) c,

                       ( SELECT * FROM acct_hist d ORDER BY start_dt DESC) d   

                 WHERE b.svc_no = a.svc_no

                   AND b.start_dt <= a.svc_date

                   AND b.txt > ' '

                   AND c.cust_no = a.cust_no

                   AND c.start_dt <= a.svc_date

                   AND c.txt > ' '

                   AND d.acct_no = a.acct_no

                   AND d.start_dt <= a.svc_date

                   AND d.txt > ' '

                   AND ROWNUM = 1) AS rid

          FROM svc a

         WHERE a.svc_date BETWEEN SYSDATE - 201 AND SYSDATE - 1 ) a,

       svc_hist b, cust_hist C, acct_hist D 

 WHERE SUBSTR(A.RID, 1,                     LENGTH(A.RID)/3) = B.ROWID

   AND SUBSTR(A.RID, LENGTH(A.RID)/3 + 1,   LENGTH(A.RID)/3) = C.ROWID

   AND SUBSTR(A.RID, 2*LENGTH(A.RID)/3 + 1, LENGTH(A.RID)/3) = D.ROWID ;

  

------------------------------------------------------------------------------------------------------------

| Id  | Operation                         | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                  |              |      1 |        |    200 |00:00:00.02 |    2610 |

|   1 |  NESTED LOOPS                     |              |      1 |   8040G|    200 |00:00:00.02 |    2610 |

|*  2 |   COUNT STOPKEY                   |              |    200 |        |    200 |00:00:00.01 |    2006 |

|   3 |    NESTED LOOPS                   |              |    200 |      1 |    200 |00:00:00.01 |    2006 |

|   4 |     NESTED LOOPS                  |              |    200 |      1 |    200 |00:00:00.01 |    1202 |

|   5 |      VIEW                         |              |    200 |      1 |    200 |00:00:00.01 |     600 |

|*  6 |       TABLE ACCESS BY INDEX ROWID | CUST_HIST    |    200 |    100 |    200 |00:00:00.01 |     600 |

|*  7 |        INDEX RANGE SCAN DESCENDING| PK_CUST_HIST |    200 |     18 |    200 |00:00:00.01 |     400 |

|   8 |      VIEW                         |              |    200 |      1 |    200 |00:00:00.01 |     602 |

|*  9 |       TABLE ACCESS BY INDEX ROWID | ACCT_HIST    |    200 |    100 |    200 |00:00:00.01 |     602 |

|* 10 |        INDEX RANGE SCAN DESCENDING| PK_ACCT_HIST |    200 |     18 |    200 |00:00:00.01 |     402 |

|  11 |     VIEW                          |              |    200 |      1 |    200 |00:00:00.01 |     804 |

|* 12 |      TABLE ACCESS BY INDEX ROWID  | SVC_HIST     |    200 |    100 |    200 |00:00:00.01 |     804 |

|* 13 |       INDEX RANGE SCAN DESCENDING | PK_SVC_HIST  |    200 |     18 |    200 |00:00:00.01 |     604 |

|  14 |   NESTED LOOPS                    |              |      1 |    201M|    200 |00:00:00.02 |    2410 |

|  15 |    NESTED LOOPS                   |              |      1 |    201K|    200 |00:00:00.02 |    2212 |

|  16 |     VIEW                          |              |      1 |    201 |    200 |00:00:00.02 |    2013 |

|* 17 |      FILTER                       |              |      1 |        |    200 |00:00:00.01 |       7 |

|  18 |       TABLE ACCESS BY INDEX ROWID | SVC          |      1 |    201 |    200 |00:00:00.01 |       7 |

|* 19 |        INDEX RANGE SCAN           | IX_SVC_01    |      1 |    201 |    200 |00:00:00.01 |       4 |

|  20 |     TABLE ACCESS BY USER ROWID    | CUST_HIST    |    200 |   1000 |    200 |00:00:00.01 |     199 |

|  21 |    TABLE ACCESS BY USER ROWID     | ACCT_HIST    |    200 |   1000 |    200 |00:00:00.01 |     198 |

|  22 |   TABLE ACCESS BY USER ROWID      | SVC_HIST     |    200 |  40000 |    200 |00:00:00.01 |     200 |

------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter(ROWNUM=1)

   6 - filter("C"."TXT">' ')

   7 - access("C"."CUST_NO"=:B1 AND "C"."START_DT"<=:B2)

   9 - filter("D"."TXT">' ')

  10 - access("D"."ACCT_NO"=:B1 AND "D"."START_DT"<=:B2)

  12 - filter("B"."TXT">' ')

  13 - access("B"."SVC_NO"=:B1 AND "B"."START_DT"<=:B2)

  17 - filter(SYSDATE@!-201<=SYSDATE@!-1)

  19 - access("A"."SVC_DATE">=SYSDATE@!-201 AND "A"."SVC_DATE"<=SYSDATE@!-1)

 

변경이력에서 단 하나의 컬럼만 조회할 때는 위의 방법을 사용할 필요가 없다. rowid 대신에 값을 select 하면 된다. 따라서 비효율도 없다. 하지만 변경이력에서 여러 컬럼을 조회해야 한다면 위의 방법처럼 rowid를 사용해야 한다. 그렇게 되면 변경이력 테이블은 두 번씩 액세스 하는 셈이다. 하지만 이때에도 인덱스는 두 번 Scan 하지 않는다. 이제 성능의 안정성을 보기 위해 오래된 데이터를 조회해 보자.   

  

SELECT a.svc_no, a.cust_no, a.acct_no, a.svc_date, a.txt,

       b.start_dt, b.txt, c.start_dt, c.txt, d.start_dt, d.txt

  FROM (SELECT a.*,

               (SELECT b.rowid||c.rowid||d.rowid as rid

                  FROM ( SELECT * FROM svc_hist  b ORDER BY start_dt DESC) b,

                       ( SELECT * FROM cust_hist c ORDER BY start_dt DESC) c,

                       ( SELECT * FROM acct_hist d ORDER BY start_dt DESC) d   

                 WHERE b.svc_no = a.svc_no

                   AND b.start_dt <= a.svc_date

                   AND b.txt > ' '

                   AND c.cust_no = a.cust_no

                   AND c.start_dt <= a.svc_date

                   AND c.txt > ' '

                   AND d.acct_no = a.acct_no

                   AND d.start_dt <= a.svc_date

                   AND d.txt > ' '

                   AND ROWNUM = 1) AS rid

          FROM svc a

         WHERE a.svc_date BETWEEN SYSDATE - 1999 AND SYSDATE - 1799 ) a,

       svc_hist b, cust_hist C, acct_hist D 

 WHERE SUBSTR(A.RID, 1,                     LENGTH(A.RID)/3) = B.ROWID

   AND SUBSTR(A.RID, LENGTH(A.RID)/3 + 1,   LENGTH(A.RID)/3) = C.ROWID

   AND SUBSTR(A.RID, 2*LENGTH(A.RID)/3 + 1, LENGTH(A.RID)/3) = D.ROWID;

 

------------------------------------------------------------------------------------------------------------

| Id  | Operation                         | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                  |              |      1 |        |    200 |00:00:00.02 |    2602 |

|   1 |  NESTED LOOPS                     |              |      1 |   8080G|    200 |00:00:00.02 |    2602 |

|*  2 |   COUNT STOPKEY                   |              |    200 |        |    200 |00:00:00.02 |    2004 |

|   3 |    NESTED LOOPS                   |              |    200 |      1 |    200 |00:00:00.01 |    2004 |

|   4 |     NESTED LOOPS                  |              |    200 |      1 |    200 |00:00:00.01 |    1202 |

|   5 |      VIEW                         |              |    200 |      1 |    200 |00:00:00.01 |     602 |

|*  6 |       TABLE ACCESS BY INDEX ROWID | CUST_HIST    |    200 |    100 |    200 |00:00:00.01 |     602 |

|*  7 |        INDEX RANGE SCAN DESCENDING| PK_CUST_HIST |    200 |     18 |    200 |00:00:00.01 |     402 |

|   8 |      VIEW                         |              |    200 |      1 |    200 |00:00:00.01 |     600 |

|*  9 |       TABLE ACCESS BY INDEX ROWID | ACCT_HIST    |    200 |    100 |    200 |00:00:00.01 |     600 |

|* 10 |        INDEX RANGE SCAN DESCENDING| PK_ACCT_HIST |    200 |     18 |    200 |00:00:00.01 |     400 |

|  11 |     VIEW                          |              |    200 |      1 |    200 |00:00:00.01 |     802 |

|* 12 |      TABLE ACCESS BY INDEX ROWID  | SVC_HIST     |    200 |    100 |    200 |00:00:00.01 |     802 |

|* 13 |       INDEX RANGE SCAN DESCENDING | PK_SVC_HIST  |    200 |     18 |    200 |00:00:00.01 |     602 |

|  14 |   NESTED LOOPS                    |              |      1 |    202M|    200 |00:00:00.03 |    2402 |

|  15 |    NESTED LOOPS                   |              |      1 |    202K|    200 |00:00:00.02 |    2205 |

|  16 |     VIEW                          |              |      1 |    202 |    200 |00:00:00.02 |    2011 |

|* 17 |      FILTER                       |              |      1 |        |    200 |00:00:00.01 |       7 |

|  18 |       TABLE ACCESS BY INDEX ROWID | SVC          |      1 |    202 |    200 |00:00:00.01 |       7 |

|* 19 |        INDEX RANGE SCAN           | IX_SVC_01    |      1 |    202 |    200 |00:00:00.01 |       3 |

|  20 |     TABLE ACCESS BY USER ROWID    | CUST_HIST    |    200 |   1000 |    200 |00:00:00.01 |     194 |

|  21 |    TABLE ACCESS BY USER ROWID     | ACCT_HIST    |    200 |   1000 |    200 |00:00:00.01 |     197 |

|  22 |   TABLE ACCESS BY USER ROWID      | SVC_HIST     |    200 |  40000 |    200 |00:00:00.01 |     200 |

------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter(ROWNUM=1)

   6 - filter("C"."TXT">' ')

   7 - access("C"."CUST_NO"=:B1 AND "C"."START_DT"<=:B2)

   9 - filter("D"."TXT">' ')

  10 - access("D"."ACCT_NO"=:B1 AND "D"."START_DT"<=:B2)

  12 - filter("B"."TXT">' ')

  13 - access("B"."SVC_NO"=:B1 AND "B"."START_DT"<=:B2)

  17 - filter(SYSDATE@!-1999<=SYSDATE@!-1799)

  19 - access("A"."SVC_DATE">=SYSDATE@!-1999 AND "A"."SVC_DATE"<=SYSDATE@!-1799)

 

이번에는 같은 방법으로 오래된 데이터를 조회 해보았지만 성능은 역시 안정적이다. 최근 데이터를 조회할 때는 종료일자 + 시작일자 인덱스가 1937 블럭을 Scan하였고 위의 SQL 에서는 2602블럭을 Scan 하였으므로 좋지 않다고 생각할 수 있다. 하지만 오래된 데이터를 구할 때 종료일자 + 시작일자 인덱스가 6498 블럭이나 Scan 하였다는 점을 생각하면 같은 테이블들을 두번씩이나 사용했음에도 성능에서 우열을 가리기 힘들다.

 

결론

변경이력을 Between 으로 조인해서 여러 건을 조회할 때에도 종료일자의 장점은 찾기 힘들므로 변경일자(시작일자)만 사용하면 된다. 어찌되었건 현재와 같은 무조건적인 종료일자의 사용은 자제해야 한다. 역정규화를 하거나 하지 않는 것은 당신의 자유다. 그리고 변경이력에 종료일자를 추가하여 역정규화 하는 것은 정합성 측면에서는 손실이지만 장점도 있다. 하지만 그것은 성능이 아니라 SQL이 간단해진다는 관점이 아닐까? 몇 배나 단순해질지가 문제이긴 하지만.... 

 

PS

이 글을 보고 성급하게 운영중인 테이블을 건드리면 안 된다. 제발 운영중인 테이블과 인덱스는 그냥 두기 바란다. 만약 역정규화를 제거하려면 다음 번 모델링 시에 반영하기 바란다. 그것이 아니라면 철저히 준비하고 실행하기 바란다.

 

현실세계의 모든 문제를 해결할 수 있는 글이란 없다. 그러므로 시작일자 컬럼만으로 해결하기 어려운 또 다른 패턴의 SQL이 있다면 나에게 알려주기 바란다. 그래야 더 많은 문제가 해결 될 터이니. 시작일자만으로 구현하기 어려운 SQL들도 결국은 몇 개의 패턴으로 정리되지 않을까 생각해본다.  


Posted by extremedb
,


테스트에 사용할 이력테이블을 현실에 가깝에 만들어서 테스트 해달라는 요청이 있었습니다. 그래서 첨부파일을 따로 올립니다. 고객번호가 테이블과 인덱스에 추가된것을 제외하고 내용은 대동소이합니다. 2010.11.18



-점이력과 선분이력의 차이는 무엇인가?
-선분이력은 언제 필요한가?
 


주문상품 변경이력 테이블에 종료일시가 보인다. 설계자에게 종료일시가 왜 필요한지 물어보았다. “선분이력을 만들기 위함입니다.” 왠 선분이력? 주문상품 변경이력에 선분이력이 왜 필요한지 다시 물어 보았다. “최근에 변경된 주문 데이터를 가져오려면 주문번호 + 상품번호 + 종료일시 + 시작일시로 PK 인덱스를 구성해야 빠르게 가져올 수 있기 때문입니다. 알만 하신 분이 이런것을 왜 물어봅니까?” 어이쿠 한방 맞았다. 이럴때는 물러나야 한다. 설계자가 필자를 감사나 감리로 느낄 수 있기 때문이다.

각종 변경이력 테이블에 인덱스(종료일자 + 시작일자) 열풍이 지난 몇 년간 불고 있는 듯하다. 인덱스를 떠나서 종료일자를 아예 PK 컬럼으로 설정해 놓고 있다. 하지만 유용한 물건도 용도를 모르고 사용하면 다칠 수 있다. 변경이력에 성능을 위한 종료일자는 필요 없다. 종료일자는 성능관점에서 고려할 것이 아니라 업무적으로 필요할 때만 사용해야 한다.

 

환경: Oracle 11g R2

 

(시작일시 + 종료일시) 인덱스와 (종료일시 + 시작일시) 인덱스의 성능비교를 위해 100만 건을 생성한다. 

CREATE TABLE TEST1 AS  

SELECT SYSDATE - level AS start_dt,                  --시작일시

       SYSDATE - level + 1 - 1/24/60/60  AS end_dt,  --종료일시

       '종료일자의 필요성 테스트' as txt

  FROM DUAL

CONNECT BY LEVEL <= 1000000;

 

ALTER TABLE TEST1 MODIFY(START_DT  NOT NULL, END_DT  NOT NULL);

 

CREATE INDEX idx_test1_01 ON TEST1 (START_DT, END_DT) ;

CREATE INDEX idx_test1_02 ON TEST1 (END_DT, START_DT) ;

 

begin

    dbms_stats.gather_table_stats(user, 'TEST1', cascade => true);

end;

 

시작일시, 종료일시의 min, max 값을 구해보자.

select TO_CHAR(min(START_DT), 'SYYYYMMDD HH24MISS') min_st_dt,

       TO_CHAR(max(START_DT), 'SYYYYMMDD HH24MISS') max_st_dt,

       TO_CHAR(min(END_DT),   'SYYYYMMDD HH24MISS') min_ed_dt,

       TO_CHAR(max(END_DT),   'SYYYYMMDD HH24MISS') max_ed_dt

  from test1;

 

MIN_ST_DT        MAX_ST_DT        MIN_ED_DT        MAX_ED_DT      

---------------- ---------------- ---------------- ----------------

-07281227 105223  20101115 105223 -07281228 105222  20101116 105222

1 row selected.

 

BC 728년부터 시작하여 가장 최근의 시작일자는 2010 11 15 10 52 23초이다. 참고로 BC를 나타내려면 ‘SYYYYMMDD’를 포맷으로 사용해야 한다. 이제 테스트를 시작해보자.

 

최근 정보를 구할 때 시작일자 + 종료일자 인덱스는 느리다?

최근 데이터를 구할 때는 시작일시 + 종료일시 인덱스를 사용하면 비효율적이라고 하였다. 정말 그런지 (시작일시 + 종료일시) 인덱스를 이용하여 가장 최근의 데이터를 구해보자.

 

SELECT /*+ gather_plan_statistics INDEX(T idx_test1_01) */ *

  FROM TEST1 T

 WHERE TO_DATE('20101116', 'SYYYYMMDD') BETWEEN start_dt AND end_dt

   AND ROWNUM = 1;

 

----------------------------------------------------------------------------------------------

| Id  | Operation                    | Name         | Starts | A-Rows |   A-Time   | Buffers |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |              |      1 |      1 |00:00:00.21 |    3773 |

|*  1 |  COUNT STOPKEY               |              |      1 |      1 |00:00:00.21 |    3773 |

|   2 |   TABLE ACCESS BY INDEX ROWID| TEST1        |      1 |      1 |00:00:00.21 |    3773 |

|*  3 |    INDEX RANGE SCAN          | IDX_TEST1_01 |      1 |      1 |00:00:00.21 |    3772 |

----------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM=1)

   3 - access("END_DT">=TO_DATE(' 2010-11-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "START_DT"<=TO_DATE(' 2010-11-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

       filter("END_DT">=TO_DATE(' 2010-11-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

 

시작일자 + 종료일자 인덱스는 최근의 데이터를 구할 때 불리하다. 한 건을 구하기 위해 3773 블럭이나 Scan 하였기 때문이다. 시작일자 + 종료일자 인덱스는 최근 데이터를 구할 때 비효율적임을 알 수 있다.

 

최근 정보를 구할 때 종료일자 + 시작일자 인덱스는 빠르다?
최근 정보(2010년 11월 16일 데이터)를 구하기 위해 종료일자 + 시작일자 인덱스를 이용한다.
 

SELECT /*+ gather_plan_statistics INDEX(T idx_test1_02) */ *

  FROM TEST1 T

 WHERE TO_DATE('20101116', 'SYYYYMMDD') BETWEEN start_dt AND end_dt

   AND ROWNUM = 1;

 

----------------------------------------------------------------------------------------------

| Id  | Operation                    | Name         | Starts | A-Rows |   A-Time   | Buffers |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |              |      1 |      1 |00:00:00.01 |       4 |

|*  1 |  COUNT STOPKEY               |              |      1 |      1 |00:00:00.01 |       4 |

|   2 |   TABLE ACCESS BY INDEX ROWID| TEST1        |      1 |      1 |00:00:00.01 |       4 |

|*  3 |    INDEX RANGE SCAN          | IDX_TEST1_02 |      1 |      1 |00:00:00.01 |       3 |

----------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM=1)

   3 - access("END_DT">=TO_DATE(' 2010-11-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "START_DT"<=TO_DATE(' 2010-11-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

       filter("START_DT"<=TO_DATE(' 2010-11-16 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

 

종료일자 + 시작일자 인덱스는 불과 4블록만 Scan 하였으므로 매우 효율적이다. 설계자들이 종료일자를 선호하는 이유는 이것뿐이 아니다. 최종 변경건의 종료일자에 ‘99991231’을 입력하면 쉽고 빠르게 max 값을 찾을 수 있다는 사실이 모델러를 기쁘게 한다. 아래의 SQL을 보자.

 

Max 일자를 구하기 위해 최종 종료일자를 4000 12 31일로 update 한다.

 

UPDATE TEST1

   SET END_DT = TO_DATE('40001231','YYYYMMDD')

 WHERE start_dt = TO_DATE('20101115 105223', 'SYYYYMMDD HH24MISS') ;

1 row updated.

 

COMMIT;

Commit complete.

 

이제 max 값을 구해보자.

SELECT /*+ gather_plan_statistics INDEX(T idx_test1_02) */ *

  FROM TEST1 T

 WHERE end_dt = TO_DATE('40001231','YYYYMMDD')

   AND ROWNUM = 1;

 

----------------------------------------------------------------------------------------------

| Id  | Operation                    | Name         | Starts | A-Rows |   A-Time   | Buffers |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |              |      1 |      1 |00:00:00.01 |       4 |

|*  1 |  COUNT STOPKEY               |              |      1 |      1 |00:00:00.01 |       4 |

|   2 |   TABLE ACCESS BY INDEX ROWID| TEST1        |      1 |      1 |00:00:00.01 |       4 |

|*  3 |    INDEX RANGE SCAN          | IDX_TEST1_02 |      1 |      1 |00:00:00.01 |       3 |

----------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM=1)

   3 - access("END_DT"=TO_DATE(' 4000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

 

좋은 것이 아니라 선입견이다. 틀을 깨라

종료일자에 4000 12 31일 조건만 주면 쉽고 빠르게 max 값을 구할 수 있다. 정리하면, 종료일시 + 시작일시 인덱스는 빠르고, between을 사용할 수 있고, 종료일자에 = 조건을 주면 max값을 찾을 수 있다.  이 세 가지 사실만으로 종료일자는 충분히 매력적이라고 생각 할 수 있다.

하지만 역설적이게도 이런 사실들은 여러 사람을 함정에 빠트린다. 종료일시라는 컬럼을 사용하는 것이 왜 함정인지 지금부터 논의해보자.

 

인덱스를 동적으로 바꿔서 사용할 것인가?

위의 예제들을 보면 인덱스(종료일자 + 시작일자)를 사용해야 하는 것처럼 보인다. 하지만 최근 데이터가 아닌 오래된 데이터를 보려면 어떻게 할 것인가? 최근 데이터를 구할 때는 종료일자 + 시작일자 인덱스를 사용하고 예전 데이터를 구할 때는 시작일자 + 종료일자 인덱스를 사용해야 하는가?  그렇게 하려면 기준이 필요하다. 언제부터가 오래된 데이터 인가? 1달 전 데이터? 아니면 1년 전 데이터? 이력테이블마다 기준을 만든다는 것은 불가능한 일이므로 그렇게 할 수도 없고, 그렇게 할 필요도 없다.

 

PK의 정합성을 어떻게 확보할 것인가?
서론에서 언급했던 PK에 대해서 논의해보자. 성능관점에서 주문번호 + 상품번호 + 종료일시 + 시작일시를 PK로 지정했다고 가정하고, 이때까지 PK의 정합성에 별 문제가 없다고 좋아해서는 안 된다. 주문상품 변경이력 테이블에서 본래의 식별자는 주문번호 + 상품번호 + 변경일시(시작일시)이다. 3개의 컬럼으로 Unique를 만족해야 한다. 하지만 종료일시가 PK에 끼어듦으로 해서 정합성이 깨질 수 있다. 예를 들면 종료일시를 PK에 추가하는 순간 같은 주문번호로 같은 상품을 같은 변경일시에 2건이상 insert 하는 것(Dup)을 허용하는 꼴이 된다. 종료일시를 PK에 추가하였으므로 세개의 값이 같아도 종료일시만 다르다면 insert가 가능하다. 변경이력 테이블의 데이터에 Dup이 많은 이유는 대부분 종료일시 때문이다.  

 

인덱스에서 종료일자를 빼면 성능이 저하된다?

왜 인덱스(종료일시 + 시작일시) VS 인덱스(시작일시 + 종료일시)만 고려해야 하는가? 틀을 깨보자. 그냥 시작일시만 인덱스로 잡아보자. 기존 인덱스를 모조리 삭제하고 시작일시만으로 구성된 인덱스 만들어 본다. 그리고 이제부터 종료일시 컬럼도 없다고 가정한다.

DROP INDEX idx_test1_01;

DROP INDEX idx_test1_02;

CREATE INDEX idx_test1_03 ON TEST1 (START_DT) ; 

                                                                                                  

이제 최종 데이터를 구해보자.

SELECT /*+ gather_plan_statistics  */ *

  FROM test1 t

 WHERE start_dt = (SELECT MAX (start_dt) FROM test1);

 

----------------------------------------------------------------------------------------------

| Id  | Operation                    | Name         | Starts | A-Rows |   A-Time   | Buffers |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |              |      1 |      1 |00:00:00.01 |       7 |

|   1 |  TABLE ACCESS BY INDEX ROWID | TEST1        |      1 |      1 |00:00:00.01 |       7 |

|*  2 |   INDEX RANGE SCAN           | IDX_TEST1_03 |      1 |      1 |00:00:00.01 |       6 |

|   3 |    SORT AGGREGATE            |              |      1 |      1 |00:00:00.01 |       3 |

|   4 |     INDEX FULL SCAN (MIN/MAX)| IDX_TEST1_03 |      1 |      1 |00:00:00.01 |       3 |

----------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("START_DT"=)

 

시작일시가 인덱스의 선두컬럼이므로 성능이 좋지 않을것으로 판단하면 오산이다. 3773 블록이 아니라 고작 7블록을 scan 했다. 이 정도면 충분한 성능이다. 서브쿼리를 사용했으므로 SQL이 복잡해진다는 의견은 받아 들일 수 없다. 위의 쿼리가 그렇게 복잡하다면 SQL을 다시 공부해야 한다.

또 다른 반박의견으로는 종료일시 + 시작일시 인덱스를 사용하면 4블록만 Scan하지만 시작일시만으로 구성된 인덱스는 7블록을 Scan한다는 의견이 있다. 맞는 말이다. 하지만 초당 수백 번 혹은 수천 번 사용되는 것이 아니라면 이대로 사용해도 무리가 없다. 만약 자주 사용되어 부하가 심한 SQL이라면 다음처럼 사용하면 된다.

 

SELECT /*+ gather_plan_statistics */ *

  FROM (SELECT *

          FROM test1 a

         ORDER BY a.start_dt DESC)

 WHERE ROWNUM = 1 ;  

 

-----------------------------------------------------------------------------------------------

| Id  | Operation                     | Name         | Starts | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |              |      1 |      1 |00:00:00.01 |       4 |

|*  1 |  COUNT STOPKEY                |              |      1 |      1 |00:00:00.01 |       4 |

|   2 |   VIEW                        |              |      1 |      1 |00:00:00.01 |       4 |

|   3 |    TABLE ACCESS BY INDEX ROWID| TEST1        |      1 |      1 |00:00:00.01 |       4 |

|   4 |     INDEX FULL SCAN DESCENDING| IDX_TEST1_03 |      1 |      1 |00:00:00.01 |       3 |

-----------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM=1)

 

정확히 4블록만 Scan 하였으며, 위의 SQL도 복잡하지 않음을 알 수 있다. max값을 구하기 위해 종료일자 컬럼은 필요치 않음을 알 수 있다.

종료일시 + 시작일시로 구성된 인덱스의 장점은 특정 시점의 테이터를 between으로 구할 수 있다는 것이다. 종료일시 컬럼이 없고 시작일시만으로 구성된 인덱스는 특정시점의 데이터를 조회해야 할때 between을 사용할 수는 없다. 과연 SQL은 얼마나 복잡해질까? 또한 최근 데이터를 구할 때 얼마나 느려질까?

 

SELECT /*+ gather_plan_statistics */ *                   

  FROM (SELECT *

          FROM test1

         WHERE start_dt <= TO_DATE('20101115 105223', 'SYYYYMMDD HH24MISS')

         ORDER BY start_dt DESC )

  WHERE ROWNUM = 1;    

 

------------------------------------------------------------------------------------------------

| Id  | Operation                      | Name         | Starts | A-Rows |   A-Time   | Buffers |

------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |              |      1 |      1 |00:00:00.01 |       4 |

|*  1 |  COUNT STOPKEY                 |              |      1 |      1 |00:00:00.01 |       4 |

|   2 |   VIEW                         |              |      1 |      1 |00:00:00.01 |       4 |

|   3 |    TABLE ACCESS BY INDEX ROWID | TEST1        |      1 |      1 |00:00:00.01 |       4 |

|*  4 |     INDEX RANGE SCAN DESCENDING| IDX_TEST1_03 |      1 |      1 |00:00:00.01 |       3 |

------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM=1)

   4 - access("START_DT"<=TO_DATE(' 2010-11-15 10:52:23', 'syyyy-mm-dd hh24:mi:ss'))

 

장점이 없는데 자리만 차지한다

특정 시점의 데이터를 구하려면 시작일시 <= 특정시점을 만족하는 데이터 중에 max(시작일시)에 해당하는 값을 취하면 된다. 위에서 보듯이 SQL이 어렵지 않으며 성능 또한 최적이다. 이로써 특정시점의 데이터를 구하는 것도 종료일시가 필요치 않으며, 시작일시 인덱스만으로도 충분한 성능을 낸다는 것을 알 수 있다. 또한 최근 데이터(2010 11 15)를 구할 때에도 시작일시 인덱스를 사용하면 최적이다. 이제 종료일시 + 시작일시 인덱스는 성능이 우월 하지 않음을 알게 되었다. 또한 종료일시 컬럼은 어떠한 장점도 없으면서 테이블과 인덱스의 덩치(Disk Size)만 크게한다는 것도 알게 되었다.

 

변경이력에서 종료일자는 추출속성이며 성능을 위해 역정규화 한 것

사실은 ~변경이력에서 시작일자 혹은 종료일자란 없는 것이다. 시작일자는 변경일자로 사용해야 하며 종료일자는 존재하지 않는 것이다. 종료일자 혹은 종료일시는 추출속성이다. 이 추출 속성을 유지하기 위해 원본 테이블의 데이터가 변경될 때마다 트리거성으로 이력테이블의 종료일자에 update가 발생한다. update가 없다면 변경이력 테이블의 모든 종료일자의 값은 ‘99991231’일 것이다.

 

만약 종료일자 없이 시작일자만 관리했다면 이러한 update는 발생하지 않는다. 원본 테이블에 변경이 자주 발생할 수록 update의 부하는 심해질 것이다. 또한 가능성은 많지 않지만 이런 역정규화에 의해 데이터의 정합성이 깨질 수도 있다.

 

그럼 종료일자는 언제 사용해야 하나?

가장 중요한 부분이다. 종료일자는 성능관점이 아니라 업무(Business)적으로 필요할 때만 써야 한다. 예를 들어보자. 당신이 프로젝트에 투입되는 계약을 했다고 가정해보자. 그 계약서에는 '며칠부터 며칠까지 프로젝트에 투입된다'고 명시되어 있어야 한다. 이럴 때는 시작일자 종료일자를 사용해야 한다. 또한 자동차를 빌릴 때(Rent)에도 언제부터 언제까지 사용할 것인지 명시되어야 한다. ~변경이력과 변경이력이 아닌 것과의 차이점을 알았다면 종료일자를 언제 써야 하는지도 이해한 것이다. 바로 이것이 점이력과 선분이력의 차이이다. 원래 점이력이었던 것을 선분이력으로 바꾸어선 안된다.

 

결론

1 ~변경이력 테이블에서 종료일자는 성능관점이나 개발생산성 관점에서 장점이 없다.

2 ~변경이력 테이블의 PK에 종료일자를 추가하면 정합성을 해칠 수 있다. 이렇게 되면 DB 차원에서 정합성을 보장하지 못한다. 따라서 종료일자가 추가된 PK가 존재함에도 불구하고 주문번호 + 상품번호 + 시작일자로 Unique 인덱스를 추가로 생성하거나 아니면 프로그램에서 정합성 체크를 해야 한다.
3 ~변경이력 테이블에서 종료일자는 테이블과 인덱스의 사이즈를 각각 크게한다. Disk 공간이 더 필요할 것이다.

4 인덱스가 A + B 일 때 insert 하는 속도는 인덱스가 B 컬럼으로만 되어있을 때 insert 하는 속도보다 느리다. 이 원리는 종료일자 + 시작일자 인덱스와 시작일자만으로 구성된 인덱스에도 적용된다.

5 ~변경이력 테이블에서 종료일자는 추출속성이며 역정규화가 필요하다. 역정규화는 원본데이터가 변경될 때마다 update가 추가로 발생된다.
6 역정규화는 데이터가 틀어질 위험성을 가지고 있다.

 

1, 2, 3, 4, 5, 6에 의해서 종료일자 혹은 종료일시는 변경이력에서 성능관점으로 사용해서는 안되며 업무적으로 필요할 때에만 사용해야 한다. 이렇게 된다면 성능이 보장되는 것은 물론이고, 인덱스와 테이블의 사이즈도 줄어들 뿐만 아니라 역정규화의 단점인 update의 부하와 데이터의 정합성이 틀어지는 것을 막을 수 있다.

필자는 선입견이 많아서 자주 되뇌이는 말이 있다.
"진실이라고 생각되는 것들은 때때로 변한다. 하지만 선입견은 좀처럼 변하지 않는다." 만약 이말을 따른다면 우리가 이미 알고 있다고 생각하는 것들을 의도적으로 의심해 보아야 한다. 나는 이런 사람들을 린치핀이라 부르고 싶다.

Posted by extremedb
,


부제 : min/max값을 안전하고 빠르게 구하는 방법


최종일자, 최종순번을 구하기 위한 전통적인 방법은 index desc 힌트와 rownum = 1 조합이었다. 하지만 이것은 대단히 위험한 방법이다. 왜냐하면 튜닝을 하기 전에 값이 맞아야 하며, 성능튜닝은 그 이후의 문제이기 때문이다. 위의 방법은 인덱스의 구성컬럼이 변경 혹은 삭제되거나, 인덱스명이 바뀌면 max 값을 구하지 못한다. 즉 성능을 향상시키기 위해 값이 틀릴 수 있는 가능성을 열어놓은 것이다. 이런 방법은 어떤 이유로도 받아들여져서는 안 된다. 나 또한 예전에 이런 방법을 사용했지만 이는 필자의 명백한 잘못이었다.

 

올바른 값을 얻어야 하고 성능도 충족해야 하므로 오라클은 first_row(min/max) operation을 내놓았다. 따라서 우리는 index_desc + rownum 대신에 first_row(min/max)을 사용해야 한다. 그런데 항상 first_row(min/max)를 사용해야 할까? first_row(min/max)가 비효율적인 경우는 index_desc + rownum 조합을 생각해 볼 수 있다. 하지만 인덱스가 변경 및 삭제될 때 성능이 느려질지언정 답이 틀리면 안 된다. 만약 max가 아닌 잘못된 값으로 update 되었다고 상상해보라. 큰일이다. 원복시키기도 어렵다. update 가 여러번 되었을 수 있기 때문이다.

 

환경 :Oracle11g R2

 

CREATE INDEX ix_cust_channel_time ON SALES (CUST_ID, CHANNEL_ID, TIME_ID)  ;

CREATE INDEX ix_cust_time_channel ON SALES (CUST_ID, TIME_ID, CHANNEL_ID)  ;
CREATE INDEX ix_time_cust_channel ON SALES (TIME_ID, CUST_ID, CHANNEL_ID)  ;

 

인덱스와 where 조건이 완벽할 때

 

SELECT /*+ gather_plan_statistics INDEX(s ix_cust_channel_time) */

       MAX (time_id)

  FROM sales s

 WHERE cust_id = :v_cust   --30777 대입

   AND channel_id = 2;

 

------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                 | Starts | A-Rows |   A-Time   | Buffers |

------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                      |      1 |      1 |00:00:00.01 |       3 |

|   1 |  SORT AGGREGATE              |                      |      1 |      1 |00:00:00.01 |       3 |

|   2 |   FIRST ROW                  |                      |      1 |      1 |00:00:00.01 |       3 |

|*  3 |    INDEX RANGE SCAN (MIN/MAX)| IX_CUST_CHANNEL_TIME |      1 |      1 |00:00:00.01 |       3 |

------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2)

 

인덱스가 CUST_ID + CHANNEL_ID + TIME_ID로 되어 있는 경우는 문제가 없다. first_row(min/max) operation을 사용할 수 있고 비효율이 없기 때문에 값이 틀려질 수 있는 index_desc + rownum을 사용해선 안 된다.

 

where 조건에 인덱스의 중간 컬럼이 빠졌을 때  

 

SELECT /*+ gather_plan_statistics INDEX(S IX_CUST_TIME_CHANNEL) */

       MAX (time_id)

  FROM sales s

 WHERE cust_id = :v_cust   --30777 대입

   AND channel_id = 2;

 

------------------------------------------------------------------------------------------------------

| Id  | Operation                    | Name                 | Starts | A-Rows |   A-Time   | Buffers |

------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |                      |      1 |      1 |00:00:00.01 |       3 |

|   1 |  SORT AGGREGATE              |                      |      1 |      1 |00:00:00.01 |       3 |

|   2 |   FIRST ROW                  |                      |      1 |      1 |00:00:00.01 |       3 |

|*  3 |    INDEX RANGE SCAN (MIN/MAX)| IX_CUST_TIME_CHANNEL |      1 |      1 |00:00:00.01 |       3 |

------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("CUST_ID"=:V_CUST)

       filter("CHANNEL_ID"=2)

 

인덱스가 CUST_ID + TIME_ID + CHANNEL_ID 로 되어 있는 경우를 보자. 인덱스의 중간컬럼이 where절에 빠져있지만  CUST_ID의 선택도가 워낙 좋으므로 문제가 되지 않는다. first_row(min/max) operation을 그대로 사용하면 된다.

 

인덱스의 선두 컬럼이 where 조건에서 빠지는 경우

 

SELECT /*+ gather_plan_statistics INDEX(S IX_TIME_CUST_CHANNEL) */

       MAX (time_id)

  FROM sales s

 WHERE cust_id = :v_cust  --30777 대입

   AND channel_id = 2;

 

-----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                 | Starts | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                      |      1 |      1 |00:00:00.02 |     755 |

|   1 |  SORT AGGREGATE             |                      |      1 |      1 |00:00:00.02 |     755 |

|   2 |   FIRST ROW                 |                      |      1 |      1 |00:00:00.02 |     755 |

|*  3 |    INDEX FULL SCAN (MIN/MAX)| IX_TIME_CUST_CHANNEL |      1 |      1 |00:00:00.02 |     755 |

-----------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter(("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2))

 

인덱스가 TIME_ID + CUST_ID + CHANNEL_ID로 구성되어 있을때 인덱스의 선두 컬럼이 where 조건에서 빠졌다. 그로 인해 Index full scan이 발생하여 쓸모 없는 752블록을 Scan하였다. 즉 인덱스를 끝부분부터 계속 scan하다가 운 좋게 755 블록을 scan해보니 cust_id = 30777 channel_id  = 2을 만족하는 값을 모두 처리한 것이다. 운이 나쁘면 인덱스를 모조리 읽어야 할 수도 있다.

 

서브쿼리나 인라인뷰를 이용하여 집합을 추가하자는 의견에 대해

인덱스의 선두 컬럼이 where 조건에서 빠지는 경우는 강제로 집합을 추가하자는 의견이 있다. 아래의 SQL이 그것이다.

 

SELECT  TIME_ID

  FROM  ( SELECT /*+ LEADING(C) INDEX_DESC(S IX_TIME_CUST_CHANNEL)  */  S.time_id

            FROM sales S,

                 (SELECT TRUNC(SYSDATE) - LEVEL + 1 AS time_id

                   FROM DUAL

                CONNECT BY LEVEL <= 7300 ) C

           WHERE S.cust_id = :v_cust   --30777

             AND S.channel_id = 2

             AND S.time_id = C.time_id )

 WHERE ROWNUM = 1;

 

---------------------------------------------------------------------------------------------------------

| Id  | Operation                       | Name                 | Starts | A-Rows |   A-Time   | Buffers |

---------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT                |                      |      1 |      1 |00:00:00.02 |     512 |

|*  1 |  COUNT STOPKEY                  |                      |      1 |      1 |00:00:00.02 |     512 |

|   2 |   NESTED LOOPS                  |                      |      1 |      1 |00:00:00.02 |     512 |

|   3 |    VIEW                         |                      |      1 |   3484 |00:00:00.02 |       0 |

|   4 |     CONNECT BY WITHOUT FILTERING|                      |      1 |   3484 |00:00:00.01 |       0 |

|   5 |      FAST DUAL                  |                      |      1 |      1 |00:00:00.01 |       0 |

|*  6 |    INDEX RANGE SCAN DESCENDING  | IX_TIME_CUST_CHANNEL |   3484 |      1 |00:00:00.01 |     512 |

---------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM=1)

   6 - access("S"."TIME_ID"=INTERNAL_FUNCTION("C"."TIME_ID") AND "S"."CUST_ID"=:V_CUST AND

              "S"."CHANNEL_ID"=2)

 

이렇게 하니 Scan한 블럭수가 1/3 정도 줄어들었다. 하지만 불필요한 조인이 3484번이나 발생하였다. 이것이 최적은 아니다. 또한 명시적으로 max값을 보장하게 작성된 SQL도 아니다.

 

Index_ss 힌트를 사용했다. 하지만……

위의 예에서 보듯이 인덱스의 선두 컬럼이 조건절에 없을때 이빨이 빠진 집합을 추가하는 것과 first_row(min/max)를 사용하는 것은 둘다 비효율적이다. 그러므로 index_desc + rownum을 사용하되 값이 바뀌지 않도록 해야 한다. 그런데 인덱스의 첫 번째 컬럼이 조건 절에서 빠졌으므로 index_ss_desc + rownum을 사용해야 한다. 이것이 가능할까? SQL을 바꾸지 않으면 불가능하다. 아래의 SQL을 보자.

 

SELECT /*+ gather_plan_statistics INDEX_SS(s ix_time_cust_channel) */

       MAX (time_id)

  FROM sales s

 WHERE cust_id = :v_cust     --30777

   AND channel_id = 2;

 

-----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                 | Starts | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                      |      1 |      1 |00:00:00.02 |     755 |

|   1 |  SORT AGGREGATE             |                      |      1 |      1 |00:00:00.02 |     755 |

|   2 |   FIRST ROW                 |                      |      1 |      1 |00:00:00.02 |     755 |

|*  3 |    INDEX FULL SCAN (MIN/MAX)| IX_TIME_CUST_CHANNEL |      1 |      1 |00:00:00.02 |     755 |

-----------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - filter(("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2))

 

Min/Max Index Skip Scan을 동시에 사용할 수 없다

min 혹은 max 함수를 사용했을 때 Oracle9i 버전과는 달리 10g 11g에서는 index skip scan을 사용할 수 없다. 힌트를 추가해도 마찬가지이다. 아래의 10053 trace를 보자.

 

***************************************

SINGLE TABLE ACCESS PATH

  Single Table Cardinality Estimation for SALES[S]

  ColGroup (#1, Index) IX_TIME_CUST_CHANNEL

    Col#: 2 3 4    CorStregth: 185.95

  ColGroup Usage:: PredCnt: 2  Matches Full:  Partial:

  Table: SALES  Alias: S

    Card: Original: 918843.000000  Rounded: 33  Computed: 32.54  Non Adjusted: 32.54

kkofmx: index filter:"S"."CUST_ID"=:B1

 

kkofmx: index filter:"S"."CHANNEL_ID"=2

 

  Access Path: index (Min/Max)

    Index: IX_TIME_CUST_CHANNEL

    resc_io: 3.00  resc_cpu: 21564

    ix_sel: 1.000000  ix_sel_with_filters: 0.000035

 ***** Logdef predicate Adjustment ******

 Final IO cst 0.00 , CPU cst 50.00

 ***** End Logdef Adjustment ******

 ***** Logdef predicate Adjustment ******

 Final IO cst 0.00 , CPU cst 50.01

 ***** End Logdef Adjustment ******

    Cost: 5.28  Resp: 5.28  Degree: 1

  Best:: AccessPath: IndexRange

  Index: IX_TIME_CUST_CHANNEL

         Cost: 5.28  Degree: 1  Resp: 5.28  Card: 1.00  Bytes: 0

***************************************

 

힌트를 사용했지만 Index Skip Scan은 고려조차 되지 않는다. 위의 Trace를 보면 "first row(Min/Max)가 가능하다면 Index Skip Scan을 고려하지 않는 로직이 10g 11g의 옵티마이져에 존재한다라고 추론할 수 있다. 인덱스와 where 절이 일치하지 않는 상태라 하더라도 비효율적인 index full scan (Min/Max)에 만족할 수는 없다. 바로 이럴 때 index_desc rownum 조합을 답이 틀려질 수 없도록 사용하면 된다.

 

아래처럼 max 함수를 제거하면 Index Skip Scan을 사용할 수는 있다.

 

SELECT /*+ INDEX_SS_DESC(S IX_TIME_CUST_CHANNEL) */

       time_id

  FROM sales s

 WHERE cust_id = :v_cust  --30777

   AND channel_id = 2

   AND ROWNUM = 1;

 

-----------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name                 | Starts | A-Rows |   A-Time   | Buffers |

-----------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                      |      1 |      1 |00:00:00.01 |     264 |

|*  1 |  COUNT STOPKEY              |                      |      1 |      1 |00:00:00.01 |     264 |

|*  2 |   INDEX SKIP SCAN DESCENDING| IX_TIME_CUST_CHANNEL |      1 |      1 |00:00:00.01 |     264 |

-----------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM=1)

   2 - access("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2)

       filter(("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2))

 

Max를 없애면 index skip scan을 사용할 수 있다. 하지만.....
블록수가 755에서 264 1/3으로 줄어들었다. 하지만 인덱스가 수정 및 삭제되면 답이 틀릴 수 있으므로 위험하긴 마찬가지 이다. 따라서 다음의 SQL처럼 사용해야 한다.

 

안정적이고 성능을 고려한 SQL  

 

SELECT MAX(time_id)

  FROM  ( SELECT /*+ INDEX_SS_DESC(S IX_TIME_CUST_CHANNEL) */ time_id

            FROM sales S

           WHERE cust_id = :v_cust                                             --30777

             AND channel_id = 2

           ORDER BY time_id DESC)

 WHERE ROWNUM = 1; 

 

-------------------------------------------------------------------------------------------------------

| Id  | Operation                     | Name                 | Starts | A-Rows |   A-Time   | Buffers |

-------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |                      |      1 |      1 |00:00:00.01 |     264 |

|   1 |  SORT AGGREGATE               |                      |      1 |      1 |00:00:00.01 |     264 |

|*  2 |   COUNT STOPKEY               |                      |      1 |      1 |00:00:00.01 |     264 |

|   3 |    VIEW                       |                      |      1 |      1 |00:00:00.01 |     264 |

|*  4 |     INDEX SKIP SCAN DESCENDING| IX_TIME_CUST_CHANNEL |      1 |      1 |00:00:00.01 |     264 |

-------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter(ROWNUM=1)

   4 - access("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2)

       filter(("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2))

 

ORDER BY를 사용했지만 인덱스의 영향으로 SORT를 하지 않으므로 성능저하도 없다. 또한 인라인뷰 내에서 ORDER BY를 사용하고 외부에서 ROWNUM을 사용했기 때문에 인덱스가 수정 및 삭제되더라도 성능이 느려질 뿐 값이 틀려질 수는 없다. 위의 SQL에서 마지막에 max 함수를 사용한 이유는 where조건에 만족하는 건수가 없더라도 null을 출력해야하기 때문이다. 앞으로 index_desc + rownum 조합을 사용할 것이라면 위의 방법을 사용하길 바란다.
 

결론

1. Index_desc + rownum을 사용하지 말고 first_row(min/max)를 사용하라

2. 1번이 비효율적인 경우에만 index_desc(혹은 index_ss_desc) + order by를 사용하고 뷰로 감싸라.
    그리고 뷰 외부에서 rownum을 사용하라.

3. first_row(min/max)를 사용할 수 있는 환경에서는 index skip scan을 사용할 수 없다. 꼭 사용하려면 집계 함수를 제거하라.

이렇게 하면 성능과 안정성을 동시에 고려할 수 있다.

 

글의 배경
이런 이야기를 하는 이유는 비판적 사고의 필요성 때문이다. 개발자에게 인라인뷰와 Order by가 없는 Index_desc + Rownum의 위험성을 설명해주었더니 나에게 책을 가져온다. 나는 본적이 없지만 아주 좋은 SQL 튜닝 책이라고 한다. 그것도 어려운 영문 책이다. 열심히 공부하는 사람임에 틀림없다. 하지만 개발자의 한마디 때문에 그사람의 인상이 바뀌어 버렸다. 그 한마디는 이 책에 Index_desc + Rownum을 사용하라고 되어있습니다.” 였다. 그것이 얼마나 위험한 것인지 여러번 증명하고 설득해 보았으나 맘을 바꾸기는 불가능 하였다. 이래서는 곤란하다. 책을 성경이나 불경처럼 여기고, 저자를 종교의 교주로 여겨서는 안 된다. 이론은 반론이 증명되면 폐기될 수 있다. 하지만 가치관이 개입된 믿음이나 신념은 좀처럼 바뀌지 않는다. 어떠한 증거를 내놓아도 그렇다. 신념은 종교생활에 사용했으면 한다.


책의 내용 중에 잘못된 것이 있으니 나쁜 책이라고 말하는게 아니다. 필자의 서적을 포함해서 모든 책의 내용은 틀릴 수 있다. 정작 나쁜 것은 책이나 저자가 종교화될 때이다. 그런 무 비판적 종교는 이공계 사람에게 치명적이다. 비판적 사고는 엔지니어와 과학자의 버팀목이자 과학기술을 발전시키는 핵심이기 때문이다. 널리 알려진 과학 논쟁인 쿤과 포퍼의 대결에서도 이러한 언급은 드러난다. 두명 모두 비판적 사고는 반드시 필요하다고 하였다. 다만 시기의 문제일 뿐이다.

답답한 마음에 글을 올려보았다. 앞으로 이런 글을 다시 쓰고 싶지 않다.

"신앙은 믿음으로 이루져야한다. 하지만 과학은 비판과 증명으로 이루어져야 한다." 


Posted by extremedb
,


부제: 긴 글을 요약해야 하는 이유

 

우리는 매일 많은 시간을 투자하며 글을 쓴다. 기획서, 제안서, 보고서, 업무요청서, 분석서, 문제해결서처럼 Business와 관련된 것으로부터 블로그, 이메일, 메신저, 일기처럼 개인적인 것까지 글의 종류는 많다. 그런데 Business와 관련된 글은 대부분 딱딱하고, 복잡하고, 분량이 많아서 보기 힘들다. 업무자체가 딱딱하고, 복잡한 것은 어쩔 수 없지만 글의 분량은 조절 할 수 있다. 아니, 조절해야 한다. 분량을 조절하지 않으면 당신의 글은 읽히지도 않고 휴지통으로 갈 것이다. 왜냐하면 몇 십 페이지 혹은 몇 백 페이지에 달하는 글을 모두 읽은 후 의사를 결정하는 사람은 많지 않기 때문이다. 만약 그 글을 모두 읽었다고 해도 다음과 같은 말을 들을 것이다. “보고서가 35페이지나 되니 열심히 작성했군. 그런데 도대체 뭘 말하고 싶은 거야? 다시 작성해 주게. 이번에는 핵심사항만 보고하길 바라네”

 

One Page Proposal은 무엇인가?

글을 읽는 이유는 내용 중 핵심을 파악하고 작성자의 의도를 간파하는 것이다. 그런데 분량이 많은 글은 의도를 파악하기 힘들다. 그래서 글은 짧고, 쉽고, 명확하게 써야 한다. One Page Proposal의 목적은 작성자의 의도를 작은 분량(1 page)으로 간결하고 읽기 쉽게 요약하는 것이다. 이 책에서 제시하는 작성지침에 따른다면 목적을 달성할 수 있다. 이 글에서는 작성지침과 그 지침을 따르는 예제를 보여줄 것이다. 물론 이것들은 책의 내용을 요약한 것이다.

 

One Page Proposal 구조(작성지침 1번부터 9번까지) 

1. 제목(신문의 헤드라인에 해당함)
제목은 이야기의 헤드라인이며 주제를 알려주는 상표 기능을 한다.


2.
부제(신문의 소제목에 해당함)
제목을 보강해라. 기획서의 주제를 더욱 명확히 밝히고, 묘사적인 단어와 구를 써서 좀 더 표현력 있게 만들어 읽는 사람의 호기심을 자아낼 수 있어야 한다

 
3.
목표(이 글을 읽어야 하는 이유
)
"
무슨 일을 해보겠다는 겁니까?" 혹은 더 상세하게 "이 기획서가 통과되면 어떤 일을 성취하시겠다는 겁니까?"에 대한 대답이다. 짧게 1~2 줄로 표현한다.


4. 2
차 목표(장점을 부각시킴)
목적을 상세히 밝힌다. 목표 아래에 대여섯 개 수준으로 작성한다. (필자는 생각이 다르다. 2~3개정도로 작성하는 것이 적절하다) 2차 목표는 1차 목표를 보완하고 장점을 부각시켜 읽는 사람의 동의를 얻어내야 한다. 읽는 사람이 첫 번째 목표에 어느 정도 수긍이 갔다면 두 번째 목표에서는 수익을 강조함으로써 더욱 강한 이미지를 줄 수 있다. 2차 목표는 .
부호로 시작한다.


5.
논리적 근거(목표를 실행해야 하는 이유와 근거)

본문의 시작이며 글의 서두에 해당한다. 이 제안을 받아들여야 하는 이유와 근거를 제시한다. 논리적 근거는 목표가 불러일으킨 의문점들을 미리 예상하고 답변함으로써 반대세력을 설득하고 목표를 지원하는 역할을 한다. 논리적 근거는 다음의 세 가지를 포함해야 한다


-
설정(근거, 상황, 그리고 실행자 혹은 실행조직의 장점) 

. 읽는 사람의 관심을 잡는다.
.
당신이 누구이며 어떤 지식을 갖추고 있는 지 알게 한다.

. 기획서의 내용을 뒷받침 할 수 있는 적절한 근거와 상황을 요약한다.


-
매력포인트(이 방법이 최고의 해결책인 이유)
주장하는 바의 클라이맥스이다. 기획서를 실행시키면 목표와 2차 목표가 완수될 것이라는 주장을 보강하라. 현상들을 반박할 여지가 없고, 시기 적절한 것들이어야 하며, 현 상황에서 당신의 프로젝트가 최고의 해결책인 이유를 설명할 수 있어야 한다.


-
설득(그로 인해 얻는 이익)
글의 제안이 수락된다면 어떻게 되나? 그로 인해 얻는 이익 즉 목표와 2차 목표가 실현되는 이유와 방법을 설명하라.


6.
재정(돈은 얼마나 드나?)
일을 실행하는데 드는 비용은 구체적으로 얼마인가? 또 어떻게 자금을 마련할 수 있나? 재정은 돈과 관련된 것이 아니라면 생략이 가능하다.


7.
현재 상태(이 제안을 실행하기 위한 상황과 고려사항)
이 제안을 실행하기 위한 환경은 어떤가? 여기서도 돈이 관련이 없다면 그 부분은 생략해도 된다.

- 이 제안을 위해 미리 준비한 것은 무엇인가?
-
자금을 얼마 동안 조달했는가? 그 자금의 성격은? 액수는? 조건이 따르는 자금인가?(생략가능
)
-
비재정적 성격의 지원이 있는가? 있다면 누구로부터
?
-
사업이 진행되는 과정에 걸림돌이 있는가? 사람인가, 회사인가, 정부기관인가
?
-
형식적인 절차나 법률적인 검토가 필요한 것이 있는가?


8.
실행(글의 결론에 해당함)
읽는 사람이 무엇을 해야 하는가? 읽는 사람이 실행할 수 있는 수준에서 어떻게 해야 당신을 도울 수 있는지 말하라. 최소한 자세한 제안서를 보기원한다면 기획자에게 연락을 하라고 부탁해야 한다
.

9. 날짜와 이름, 서명(서명은 생략가능)

 

One Page Proposal 예제


 

쿠푸의 대() 피라미드
영원불멸의 기하학적 설계로 창조된, 파라오를 기리기 위한 대 기념물

목표 : 이집트의 위대함을 기리고 파라오에게 영원한 생명을 보장하는 기념물을 건축하기 위한 것.
 .
이집트의 최신 기술을 보여주기 위한 것

 .
왕비에게 영원한 휴식 장소를 제공하기 위한 것


 
위대한 파라오 쿠푸는 자신과 이집트의 위대함을 반영하는 기념물을 건설한다는 계획을 공표하였다. 나라를 최초로 통일한 나르메르(Narmer) 왕이 아비도스의 수혈식 분묘에 묻힌 이후, 이집트의 파라오는 신과 인간 모두에게 유일한 존재로 인정받았다. 그럼에도 불구하고 이전의 무덤들은 태양신 라(Ra)와 파라오의 근복적인 관계를 제대로 반영하지 못했다. 수혈식 분묘에 얹힌 구조물 역시 파라오가 내세를 여행할 수 있을 만큼 옥체와 보물을 충분히 보호하지 못하고 있다. 선왕인 스네프루(Snefru)의 무덤이 도굴된 것만 봐도 그 사실을 알 수 있다.

 
왕실에서 가장 현식적인 건축가인 헤몬은 기념물의 새롭고 완벽한 디자인을 구상해 대 피라미드의 모양으로 발전시켰다. 고안된 디자인은 독특하면서도 기존 건축물과 이집트 전통을 헤치지 않는 연계성을 지닌다. 헤몬은 파라오의 궁전에서 대규모 프로젝트를 수행한 경험이 있다. 파라오의 일가 중 한 명이며, 관리 능력 또한 뛰어나다
.

 
제안한 건축의 장점 중에는 다음과 같은 것들이 포함된다. 1)기자(Giza) 평야의 가장 단단한 암석 위에 세워질, 피라미드를 위한 최고의 자리. 2) 세계 최대의 기념 건축물 3) 남북 방향의 별과 피라미드를 일직선상에 놓아 파라오가 항상 떠오르는 태양을 향할 수 있도록 한 완벽한 설계. 4) 도굴 방지 기술을 사용하여 파라오와 왕비가 내세를 안전하게 여행할 수 있도록 함. 5) 전체 구조물과 최대 편차 8인치 이내의 기술. 6) 세계 최고의 품질을 자랑하는 투라(Tura)의 석회암 외벽
.

재정 : 세계 최대의 건축물인 대 피라미드를 완성하려면 이집트 국고의 1/4이 필요하다. 하지만 셈나(Semna) 서쪽으로부터 델타에 이르는 지역의 추수를 고려해 세금을 증가시키면, 그 액수는 선왕이 지출한 액수보다 5% 초과할 뿐이다. 파라오의 기술자들이 예비 설계도에 따라 측정한 바에 따르면 482피트의 피라미드를 건축하는 데 2.6톤 무게의 돌이 대략 230만 개 필요할 것이다. 건축 비용은 공사 시산인 23년에 걸쳐 점차적으로 지출될 것이다
.

현재 상태 : 예비 설계도는 완성되었음. 기자 근처의 채석장에서 석회암과 화강암을 공급받기로 약속되었음. 램프에 쓰일 금과 삼나무는 이집트의 새 영토인 비블로스의 누비아 저지대에서 공급 받을 수 있음. 인력관리국에서 범람 시기에 10만 명의 장인과 노동자를 공급해 줄 수 있으며, 그렇게 되면 2528년까지 공사가 완료될 수 있음
.

실행 : 위대한 파라오 쿠푸는 헤몬을 건설부 총신으로 임명하고, 그에게 대 피라미드 공사의 권한을 줄 것.

 

                                                                                                                    2010-04-01 홍길동

 

활용방안

필자는 One Page proposal의 방법을 분석서, 보고서, 기획서의 첫 페이지로 이용하고 있다. 첫 페이지 이후에는 분석서, 보고서, 기획서의 본문을 첨부하면 된다. 또한 발표(프리젠테이션)자료를 만들 때는 마지막 부분의 요약페이지로 One Page proposal을 이용한다. 이것의 활용방안은 무궁무진하다. 잘 생각 해보면 글을 요약하는 원리는 말을 할 때에도 사용할 수 있다.
 

결론

긴 글을 1 page로 요약한다면 그 글은 선택될 확률이 높다. 또한 짧은 글은 읽는 사람에게 의사를 결정할 자유를 준다. 그렇지만 아직도 많은 수의 글이 무지막지한 분량 때문에 의사결정의 단계까지 가지도 못하고 폐기되고 있다. 오늘도 우리는 정보의 홍수 속에 살고 있고, 그 중에는 수없이 많은 글들이 있다. 그것들을 다 읽을 수는 없는 노릇이 아닌가?

THE ONE PAGE PROPOSAL(강력하고 간결한 한 장의 기획서) 상세보기
패트릭 G. 라일리 지음 | 을유문화사 펴냄
세상에거 가장 강력한 기획서는 한 장으로 압축된 간결하고 인상적인 기획서다. 이 책은 어떻게 그런 기획서를 작성할 것인지 구체적인 방법을 일러준다. 책이 다루는 주제 만큼 책 전체의 분량도 상당히 간결하다.



 


Posted by extremedb
,