날짜 조회 안티 패턴 #1

최근에 수행한 프로젝트에서 날짜 조회 안티 패턴을 몇가지 발견했다. 관련 내용을 살펴보자.

테스트를 위해 아래와 같이 테이블을 생성하자. d1, d2 칼럼은 문자 타입에 시분초가 포함된 날짜 값이 저장된다.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (d1 VARCHAR2(14), v1 NUMBER, d2 VARCHAR2(14));

CREATE INDEX t1_x1 ON t1 (d1);
CREATE INDEX t1_x2 ON t1 (d2);

아래 쿼리는 d2가 전일인 d1의 연월에 해당하는 데이터를 조회한다. SUBSTR (d1, 1, 4) 표현식에 의해 인덱스를 사용할 수 없고, TO_CHAR (SYSDATE, 'YYYYMMDD') - 1 표현식에 의해 월초일이 부정확한 데이터가 반환된다.^[각주:1]

-- 2
SELECT *
  FROM t1
 WHERE SUBSTR (d1, 1, 4) IN (SELECT SUBSTR (d1, 1, 4)
                               FROM t1
                              WHERE d2 LIKE TO_CHAR (SYSDATE, 'YYYYMMDD') - 1 || '%');

-----------------------------------
| Id  | Operation          | Name |
-----------------------------------
|   0 | SELECT STATEMENT   |      |
|*  1 |  HASH JOIN SEMI    |      |
|   2 |   TABLE ACCESS FULL| T1   |
|*  3 |   TABLE ACCESS FULL| T1   |
-----------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(SUBSTR("D1",1,4)=SUBSTR("D1",1,4))
   4 - access("D2" LIKE TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE@!,'YYYYMMDD'))-1)||'%')
       filter("D2" LIKE TO_CHAR(TO_NUMBER(TO_CHAR(SYSDATE@!,'YYYYMMDD'))-1)||'%')

성능을 개선하기 위해서는 아래처럼 다소 복잡한 쿼리를 작성해야 한다.

-- 3
SELECT b.*
  FROM (SELECT   TRUNC (TO_DATE (d1, 'YYYYMMDD'), 'MM') AS d1
            FROM t1
           WHERE d2 >= TO_CHAR (TRUNC (SYSDATE) - 1, 'YYYYMMDD')
             AND d2 <  TO_CHAR (TRUNC (SYSDATE), 'YYYYMMDD')
        GROUP BY TRUNC (TO_DATE (d1, 'YYYYMMDD'), 'MM')) a
     , t1 b
 WHERE b.d1 >= TO_CHAR (a.d1, 'YYYYMM')
   AND b.d1 <  TO_CHAR (ADD_MONTHS (a.d1, 1), 'YYYYMM');

--------------------------------------------------
| Id  | Operation                        | Name  |
--------------------------------------------------
|   0 | SELECT STATEMENT                 |       |
|   1 |  NESTED LOOPS                    |       |
|   2 |   NESTED LOOPS                   |       |
|   3 |    VIEW                          |       |
|   4 |     HASH GROUP BY                |       |
|*  5 |      FILTER                      |       |
|   6 |       TABLE ACCESS BY INDEX ROWID| T1    |
|*  7 |        INDEX RANGE SCAN          | T1_X2 |
|*  8 |    INDEX RANGE SCAN              | T1_X1 |
|   9 |   TABLE ACCESS BY INDEX ROWID    | T1    |
--------------------------------------------------

위 쿼리가 복잡한 이유는 날짜 값이 저장되는 칼럼에 문자 타입을 사용했기 때문이다. 아래와 같이 DATE 타입으로 테이블을 다시 생성해보자.

-- 4
DROP TABLE t1 PURGE;
CREATE TABLE t1 (d1 DATE, v1 NUMBER, d2 DATE);

CREATE INDEX t1_x1 ON t1 (d1);
CREATE INDEX t1_x2 ON t1 (d2);

데이터 변환 함수(TO_* 함수)만 제거했는데도 쿼리가 훨씬 간결해진 것을 확인할 수 있다. 개발 편의성은 물론 데이터 무결성 측면에서도 날짜 값은 날짜 타입에 저장하는 편이 바람직하다.

-- 5
SELECT b.*
  FROM (SELECT   TRUNC (d1, 'MM') AS d1
            FROM t1
           WHERE d2 >= TRUNC (SYSDATE) - 1
             AND d2 <  TRUNC (SYSDATE)
        GROUP BY TRUNC (d1, 'MM')) a
     , t1 b
 WHERE b.d1 >= a.d1
   AND b.d1 <  ADD_MONTHS (a.d1, 1);

--------------------------------------------------
| Id  | Operation                        | Name  |
--------------------------------------------------
|   0 | SELECT STATEMENT                 |       |
|   1 |  NESTED LOOPS                    |       |
|   2 |   NESTED LOOPS                   |       |
|   3 |    VIEW                          |       |
|   4 |     HASH GROUP BY                |       |
|*  5 |      FILTER                      |       |
|   6 |       TABLE ACCESS BY INDEX ROWID| T1    |
|*  7 |        INDEX RANGE SCAN          | T1_X2 |
|*  8 |    INDEX RANGE SCAN              | T1_X1 |
|   9 |   TABLE ACCESS BY INDEX ROWID    | T1    |
--------------------------------------------------

1. 2019-01-01인 경우 20190100이 반환됩니다. TO_CHAR (SYSDATE - 1, 'YYYYMMDD') 표현식을 사용해야 의도한 결과를 얻을 수 있습니다. [본문으로]