날짜 데이터 저장
DBMS마다 날짜와 시간을 저장할 수 있는 데이터 타입을 제공한다. 그러나 날짜와 시간을 저장하는 데 'YYYYMMDD', 'HH24MISS' 형식의 문자열을 사용하는 경우도 많다. 인터넷 문서나 오래된 책을 찾아보면 날짜를 저장할 때 DATE 타입을 사용하지 말고 문자열로 저장하는 것이 좋다고 주장하는 경우도 흔하게 볼 수 있다. 날짜나 시간 데이터를 저장하는 데 문자열 데이터 타입을 사용하면 불필요하게 저장 공간이 늘어날 뿐 아니라 데이터 정합성이 떨어지고 성능에까지 영향을 미칠 수 있다. 이에 대해서 하나씩 살펴보자
저장공간 증가 문제
오라클에서 날짜를 저장하는데 사용되는 DATE 타입은 기본적으로 7 byte를 차지한다. 날짜를 저장할 때 VARCHAR2(8)을 사용한다면 기본적으로 11 byte가 사용된다(데이터 8 byte + 컬럼길이 저장을 위한 3 byte). 4 byte 차이는 그리 크지 않지만 데이터가 1천만건, 1억건 들어간다고 치면 이 차이만으로도 40 MB, 400 MB 차이가 발생하는 것이다. 만약 시간까지 저장하기 위해 'YYYYMMDDHH24MISS' 형식으로 저장한다면 19 byte(데이터 16 byte + 컬럼길이 저장을 위한 3 byte)가 필요하고 이걸 1억건 저장하는 경우 12 * 100,000,000 = 1.2 GB의 공간이 더 필요하게 된다. 이 컬럼이 인덱스에도 포함된다면 불필요하게 낭비되는 공간은 더 늘어난다. 날짜를 문자열로 저장하는 테이블이 수십~수백개에 이르면 낭비되는 공간도 그만큼 더 늘어날 것이다. 요즘은 디스크 가격이 매우 싸기 때문에 이정도 저장 공간은 대세에 영향을 미치지 않는다고 생각할 수도 있다. 날짜를 문자열로 저장하는 것이 좋다면 이 정도 공간이 더 사용되는 것을 용납하지 못할 것도 없겠지만, 날짜를 문자열로 저장하는 경우 데이터 정합성이 떨어지고 성능 문제까지 야기할 수 있다. 그렇다면 얻는 것은 아무 것도 없으면서, 그리고 데이터 정합성과 성능에서 손해를 보면서 저장 공간까지 낭비하는 꼴이 된다.
데이터 정합성 문제
문자열 데이터 타입에 날짜를 저장하면 해당 컬럼에 유효하지 않은 날짜가 들어갈 수 있다. '20070229'는 유효한 날짜가 아니지만 VARCHAR2(8)로 지정된 컬럼에는 아무 문제없이 데이터를 넣을 수 있다. '20081234', 'xxxxxxxx'와 같은 되지도 않는 데이터도 아무런 제한 없이 들어갈 수 있다. 프로그램에서 날짜를 제대로 집어넣기 때문에 데이터베이스에 이런 데이터가 들어갈 가능성은 없다고 우기는 개발자들도 있지만, 데이터베이스의 데이터를 조작하는 것은 그 프로그램뿐이 아니다. 다른 사람이 작성한 프로그램, 또 다른 사람이 작성한 프로그램, DB에 직접 접속해 데이터를 조작하는 경우도 생각해야 한다. 과거 데이터를 신규 시스템으로 전환하다 보면 이런 식의 유효하지 않은 데이터를 많이 볼 수 있다. 적절한 데이터 타입만 사용했더라도 그런 유효하지 않은 데이터가 데이터베이스 들어가기 전에 걸러낼 수 있었을 것이다. 가장 좋은 것은 저장하고자 하는 데이터에 가장 알맞는 데이터 타입을 사용해 데이터를 저장하는 것이다. 날짜를 저장할 때 DATE 타입으로 저장한다면 앞에서 예로 든 유효하지 않는 데이터는 아예 데이터베이스에 들어가지도 못한다.
성능 문제
옵티마이저는 데이터 타입에 따라 가능한 값을 안다. 테이터 타입에 따라 SQL이 어떻게 다르게 실행되는지를 확인해보자. 다음과 같이 테이블과 인덱스를 만들고 통계정보를 생성한다.
create table t as
select
to_char(sysdate-level, 'YYYYMMDD') str_dt,
trunc(sysdate-level) date_dt,
sys.dbms_random.string('U', 100) data
from dual
connect by level <= 1000;
create index t_ix01 on t(str_dt);
create index t_ix02 on t(date_dt);
analyze table t compute statistics;
이제 다음과 같이 테이블을 조회해보자. 우리는 2006년12월31일과 2007년1월1일 사이는 하루밖에 없다는 것을 안다.
SQL> set autotrace traceonly explain statistics
SQL> select * from t
2 where date_dt between to_date('20061231','YYYYMMDD')
3 and to_date('20070101','YYYYMMDD');
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1694393217
---------------------------------------------------------------
|Id|Operation |Name |Rows|Bytes|Cost (%CPU)|
--------------------------------------------------------------
| 0|SELECT STATEMENT | | 3| 345| 3 (0)|
| 1| TABLE ACCESS BY INDEX ROWID|T | 3| 345| 3 (0)|
|*2| INDEX RANGE SCAN |T_IX02| 3| | 2 (0)|
---------------------------------------------------------------
...
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
925 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
날짜가 DATE 타입으로 되어 있을 때는 옵티마이저 또한 2006년12월31일과 2007년1월1일 사이는 하루밖에 없다는 것을 알고 인덱스를 사용해 테이블에 접근한다. 또한 실행 통계를 보면 consistent gets가 5에 불과함을 확인할 수 있다. 날짜를 VARCHAR2에 저장한 경우에는 어떻게 될까?
SQL> select * from t
2 where str_dt between '20061231' and '20070101';
Elapsed: 00:00:00.00
Execution Plan
--------------------------------------------------------
Plan hash value: 1601196873
-------------------------------------------------------
|Id| Operation | Name| Rows| Bytes|Cost (%CPU)|
-----------------------------------------------------
| 0| SELECT STATEMENT | | 301| 34615| 7 (0)|
|*1| TABLE ACCESS FULL| T | 301| 34615| 7 (0)|
-------------------------------------------------------
...
Statistics
--------------------------------------------------------
1 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
925 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
문자열 '20061231'과 '20070101' 사이에는 다음과 같이 수많은 값이 들어갈 수 있다.
'20061232', '20061233', '20061234'...
'2007123A', '2007123B', ...
옵티마이저도 이것을 안다. 따라서 두 값 사이에 많은 값이 들어갈 수 있다면 인덱스를 사용하는 것보다 테이블 전체를 읽는 것이 낫겠다고 판단한 것이다. 그러나 실제 실행 통계를 확인해보면 contsistent gets가 22로 증가한 것을 볼 수 있다. 즉 불합리하게 I/O가 증가한 것이다. 이는 성능에서도 그만큼 손해를 봤음을 뜻한다.
날짜 연산 문제
문자열로 날짜를 저장했을 때는 날짜 연산이 필요한 경우 to_date 함수로 날짜를 DATE 형으로 변환한 다음 날짜 연산을 하고 다시 to_char 함수를 사용해 해당 데이터를 문자열로 바꾸어야 한다. 함수가 중첩되어 코드를 알아보기가 어려워진다. 날짜를 문자열로 저장해 뭔가 얻는 것이 있다면 그렇게 하는 것도 의미가 있을지 모르겠지만, 날짜를 문자열로 저장해 이익이 되는 것이 무엇이 있는지는 모르겠다.
날짜와 시간, 한 컬럼에 저장해야 하나, 두 컬럼으로 나누어야 하나
다음과 같은 식으로 날짜와 시간을 별도 컬럼으로 저장하는 경우도 많은 것 같다.
create table T (
...
create_dt varchar2(8),
create_tm varchar2(6),
...
);
이런 식으로 날짜와 시간을 분리하는 것은 날짜뿐 아니라 시간 자체만도 의미있는 데이터로 보겠다는 관점을 반영한 것이다. 날짜가 없는 시간이 과연 데이터로서 의미가 있을까? 이는 보는 관점에 따라 달라질 것이다. 이렇게 날짜와 시간을 따로 저장하는 것을 좋아하는 사람은 오라클 DATE 타입이 날짜와 시간을 함께 저장하는 것에 불만을 품고 위와 같이 날짜와 시간을 분리해 문자열로 저장하는 것인지도 모르겠다. 그러나 이런 경우를 생각해보자. 데이터 생성 일시가 2008년2월1일 오전 10시~2008년2월5일 오전10인 데이터를 조회하려면 어떻게 해야 할까? 날짜와 시간 컬럼이 분리되어 있다면 답이 깔끔하지 않다.
select * from t
where
create_dt||create_tm between '20080201100000'
and '20080205100000';
이렇게 하면 조회는 되겠지만, create_dt+create_tm으로 인덱스가 걸려 있는 경우 인덱스를 타지 못한다. 인덱스 컬럼이 가공되기 때문이다. 인덱스를 타게 하기 위해서는 아마도 다음과 같이 좀더 복잡한 쿼리를 작성해야 할 것이다.
select * from t
where (create_dt = '20080201' and create_tm >= '100000')
or (create_dt between '20080202' and '20080204')
or (create_dt = '20080203' and create_tm <= '100000')
만약 create_dt를 DATE 타입으로 했고, 여기에 날짜와 시간을 함께 저장한다면 다음과 같이 단순하고 직관적으로 쿼리할 수 있다.
select * from t
where
create_dt between to_date('20080201100000','yyyymmddhh24miss')
and to_date('20080205100000','yyyymmddhh24miss');
꼭 시간을 독립적으로 다루어야 하는 경우가 있을지도 모르겠다. 그러나 나는 아직까지 그런 경우를 보지 못했다.
결론
날짜를 저장할 때는 DATE 타입을 사용하자. 그게 날짜를 저장하는 제일 좋은 방법이다.
참고
- Thomas Kyte, Effective Oracle by Design, p374~ (Use the Correct Datatype)
- 올바른 데이터 타입 사용하기