다잇소


[IT/트랜드] 김태공의 2018 알아두면 편리한 SQL TIP

2018.02.07
김태공

 

지난 글에 이어서 날짜 및 시간 데이타 검증 SQL을 알아 보겠습니다.

시간 컬럼에 입력된 데이타가 정당한지 검증하는 SQL을 알아봤고…이번에는 날짜 컬럼에 데이타가 정당한지 알아 보겠습니다.

날짜 및 시간 컬럼에 대한 검증은 실전에서도 자주 사용하는 SQL이므로 숙지해 두면 개발이나 운영하는데 많은 도움이 될 것입니다.

아래같이 날자형 컬럼을 문자타입으로 설정시 (시작년월일,시작시간) 형태로 일자와 시간을 분리한 테이블입니다.

 

CREATE TABLE TAB10
(
로그인ID VARCHAR2(20) NOT NULL,
시작년월일 CHAR(8) NOT NULL,
시작시간 CHAR(4) NOT NULL,
………
……..
);
범위
. 시작년월일 : YYYYMMDD (시작일자 ~ 마지막일자)
. 시작시간 : HHMM

먼저 <시작년월일>의 시작일자와 마지막일자를 구해야 합니다.
아래같이 모두 아시는 방법으로 구하면 됩니다.

SELECT MIN(시작년월일) 시작일자, MAX(시작년월일) 마지막일자
FROM TAB10;

시작일자 마지막일자
20170518 20200717
그림1

 

그다음 시작일자가 ‘20170518’ 부터 마지막일자가 ‘20200717’까지 정당한 일자를 구합니다.
SELECT TO_CHAR(TO_DATE (‘20170518’, ‘YYYYMMDD’) + LEVEL – 1,’YYYYMMDD’) CHK_DATE
FROM DUAL
CONNECT BY (TO_DATE (‘20170518’, ‘YYYYMMDD’) + LEVEL – 1) <=
TO_DATE (‘20200717’, ‘YYYYMMDD’) ;
그림2

 

위의 그림같이 정당한 일자가 구해지면 해당 테이블과 조인하여 범위를 벗어나는 일자를 찾습니다.

 

WITH TMP AS
(
SELECT TO_CHAR(TO_DATE (‘20170518’, ‘YYYYMMDD’) + LEVEL – 1,’YYYYMMDD’) CHK_DATE
FROM DUAL
CONNECT BY (TO_DATE (‘20170518’, ‘YYYYMMDD’) + LEVEL – 1) <= TO_DATE (‘20200717’, ‘YYYYMMDD’)
)
SELECT *
FROM TAB10 A
WHERE 시작년월일 NOT IN (SELECT CHK_DATE FROM TMP WHERE CHK_DATE IS NOT NULL) ;

 

 

그림3

위의 그림을 보시면 ‘20170733’ 일이 나오죠?
7월33일은 달력에 없는 날자이므로 쓰레기 데이타가 입력된 경우 입니다.

만약 TAB10 테이블의 데이타 양이 적으면 문제가 없겠지만… 대량의 데이타 이면 시간이 오래 걸리겟죠?

예리한 분은 위의 SQL을 보고 의문을 가졌을 것입니다.

왜 SQL 마지막에 “WHERE CHK_DATE IS NOT NULL” 이 부분을 추가 했을까?

성능 때문입니다.

“WHERE CHK_DATE IS NOT NULL” 이 부분을 빼고 실행계획을 보겠습니다.

 

 

그림4

 

다시 “WHERE CHK_DATE IS NOT NULL” 이 부분을 다시 추가해서 실행계획을 보겠습니다.

 

 

그림5

 

위의 2개 그림의 실행계획이 많이 틀리죠?

성능에 대한 부분은 추후 다른 지면을 통해 자세히 다루어 보도록 하겠습니다.

 

 
김 석기의 프로필 사진
| Wise리더
관심분야 데이타베이스 Sql튜닝

카테고리 레이어 닫기