[Oracle] DATE 타입의 컬럼 조회 조건 쿼리문 TO_DATE 사용? TO_CHAR 사용?

DATE 타입 컬럼 조회 (TO_CHAR 사용)

DATE 타입의 컬럼 조회 일자 조회 조건을 줄 때, 습관적으로 TO_CHAR를 사용해왔다.

보통 Javascript 에서 datetimepicker 등의 라이브러리를 이용해서 조회일자의 기간을 조건으로 조회하고, 시작일자와 종료일자의 문자열을 파라메터로 BETWEEN AND 쿼리로 테이블을 조회한다.

 

큰 고민 없이 주로 TO_CHAR를 사용해왔던 큰 이유는 TO_DATE 처리 시에 DATE FORMAT 에 따라서모두 초기값 처리 되는 문제 때문이었다.

 

SELECT TO_DATE('2022' , 'YYYY') AS YYYY
     , TO_DATE('2022-01' , 'YYYY-MM') AS YYYYMM
     , TO_DATE('2022-01-02' , 'YYYY-MM-DD') AS YYYYMMDD
     , TO_DATE('2022-01-02 13' , 'YYYY-MM-DD HH24') AS YYYYMMDDHH24
     , TO_DATE('2022-01-02 13:35' , 'YYYY-MM-DD HH24:MI') AS YYYYMMDDHH24MI
  FROM DUAL

이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.

추천인 코드 : AF8800551

 

위와 같이 연도로 TO_DATE를 할 경우 현재 월의 01일 00시 00분 00초,

연도와 월로 TO_DATE를 할 경우는 01일의 00시 00분 00초,

연도와 월, 일로 TO_DATE 할 경우 00시 00분 00초,

연도와 월, 일시로 TO_DATE 할 경우 00분 00초,

연도와 월, 일시분으로 TO_DATE 할 경우 00초로 처리되어 조회되는 것을 볼 수 있다.

 

 

만약 TEST 테이블 에 데이터가 위와 같이 있을 경우에 2022년 03월 14일의 데이터를 조회하는 조건식을 사용한다고 가정하자.

 

SELECT OCRN_DTM
  FROM TEST_TABLE
 WHERE OCRN_DTM = TO_DATE('2022-03-14', 'YYYY-MM-DD')

 

이렇게 조회하는 경우 OCRN_DTM이 '2022-03-14 00:00:00' 가 아닌 이상 그 어떤 데이터도 조회되지 않는다.

 

SELECT OCRN_DTM
  FROM TEST_TABLE
 WHERE OCRN_DTM BETWEEN TO_DATE('2022-03-14', 'YYYY-MM-DD') AND TO_DATE('2022-03-14', 'YYYY-MM-DD')

 

마찬가지로 해당 조건은  '2022-03-14 00:00:00' 와  '2022-03-14 00:00:00' 사이의 정보를 조회하는 것이기 때문에  '2022-03-14 00:00:00' 데이터가 아니면 조회되지 않는다.

 

 

하지만 TO_CHAR 를 사용하면 DATE TYPE으로 변형하지 않고 문자열을 비교하게 되는 것이기 때문에

어떤 방식으로 해도 성립되어 2022년 03월 14일에 해당하는 모든 데이터가 조회된다.

 

SELECT OCRN_DTM
  FROM TEST_TABLE
 WHERE TO_CHAR(OCRN_DTM, 'YYYY-MM-DD') = '2022-03-14'

 

SELECT OCRN_DTM
  FROM TEST_TABLE
 WHERE TO_CHAR(OCRN_DTM, 'YYYY-MM-DD') BETWEEN '2022-03-14' AND '2022-03-14'

 

 

TO_CHAR 문제 직면..

그렇게 단순히 데이터 처리에 대해 깊게 생각하지 않고 (CTRL C + V의 결과..) 반복적으로 사용하다보니, 큰 문제를 놓쳐왔다.   

LOG TABLE (이력성 테이블) 데이터 조회 시에 딜레이가 걸릴 수 밖에 없는 조회 조건이었던 것이다.

 

기본적으로 이력성 테이블은 생성일시가 테이블 키 컬럼이거나 멀티키에 포함되는 경우가 아주 많다.

 

그런데 이 키 컬럼을 형변환해서 조회를 하게되면, INDEX를 활용할 수 없어 전체 테이블을 FULL SCAN 처리하게 된다.

1초도 안되서 처리될 수 있는 것을  그 많은 데이터를 FULL SCAN하느라 5초가 걸리기도 하는 것이다.

(이력성이다 보니 데이터가 쌓이면 쌓일수록 그 딜레이는 더 커져만 갔다..)

 

 

 

DATE TYPE의 키 컬럼인 경우 BETWEEN 사용 해결

일단 EQUALS(=)을 사용하는 경우의 조건은 문자열 데이터가 'YYYYMMDDHH24MISS' (연월일시분초) 가 전부 오지 않는 이상은 형변환 없이 조건을 충족시킬 수 있는 방법이 없는 것으로 판단된다. 

 

만약 Javascript 에서 넘기는 문자열 파라메터를 수정할 수 있다면, 기간이 아닌 하나의 일자만 선택한다해도 BETWEEN을 사용하는 것도 하나의 방법이다. 

 

물론 비교하려는 컬럼이 키 컬럼인 경우, 그 중에서도 이력성 테이블이어서 데이터의 양이 많고 딜레이가 큰 경우라면 말이다.

 

TO_DATE BETWEEN 조회 시에 DATE FORMAT에 맞게 종료일자에 더해준다.

 

-- YYYY-MM-DD : 2022년 03월 14일 BETWEEN 의 경우 
-- 종료일자 + 1
SELECT * FROM TEST_TABLE
WHERE OCRN_DTM BETWEEN TO_DATE('2022-03-14', 'YYYY-MM-DD') AND TO_DATE('2022-03-14', 'YYYY-MM-DD') + 1

-- YYYY-MM-DD HH24 : 2022년 03월 14일 13시 BETWEEN 의 경우 
-- 종료일자 + 1/24 (1일/24 = 1시간)
SELECT * FROM TEST_TABLE
WHERE OCRN_DTM BETWEEN TO_DATE('2022-03-14 13', 'YYYY-MM-DD HH24') AND TO_DATE('2022-03-14 13', 'YYYY-MM-DD HH24') + 1/24

-- YYYY-MM-DD HH24 : 2022년 03월 14일 13시 30분 BETWEEN 의 경우 
-- 종료일자 + 1/24 (1일/24/60 = 1분)
SELECT * FROM TEST_TABLE
WHERE OCRN_DTM BETWEEN TO_DATE('2022-03-14 13:30', 'YYYY-MM-DD HH24:MI') AND TO_DATE('2022-03-14 13:30', 'YYYY-MM-DD HH24:MI') + 1/24/60

 

 

본인의 데이터 형태와 상황에 따라 경우는 다르지, 경우에 따라 생각은 하면서 개발하자 ㅎㅎ)