변경이력 테이블에 종료일자가 필요한가?

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

invalid-file

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

주문상품 변경이력 테이블에 종료일시가 보인다. 설계자에게 종료일시가 왜 필요한지 물어보았다. “선분이력을 만들기 위함입니다.” 왠 선분이력? 주문상품 변경이력에 선분이력이 왜 필요한지 다시 물어 보았다. “최근에 변경된 주문 데이터를 가져오려면 주문번호 + 상품번호 + 종료일시 + 시작일시로 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의 부하와 데이터의 정합성이 틀어지는 것을 막을 수 있다.

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