야바바
65
2019-03-15 09:20:58
17
571

쿼리문 질문있습니다


안녕하세요 개발시작한지 얼마 안된 초보개발자입니다 ㅠ 기간검색에 대한 쿼리문을 만들고있는중입니다

현재 테이블에 일자 관련 컬럼이 DATE형으로 들어오는게 아니고 년,월,일이 분리되어 number형으로

들어가는 형태로 되어있습니다.

그래서 해당 컬럼들을가지고 TO_DATE함수로 년,월,일 Date형으로 변환한 다음 between 조건절을

이용해서 기간을 넣으려고하는데 문제는 어떤 컬럼을가지고 between조건절을 걸어야 할지 잘 모르겠습니다

이런경우에는 어떤 방법으로 쿼리문을 만들어야될까요??


0
0
  • 답변 17

  • 도각도각
    3k
    2019-03-15 09:22:55
    WHERE 등록일시 BETWEEN 검색시작일시 AND 검색종료일시
    1
  • 야바바
    65
    2019-03-15 09:30:45

    아 넵 감사합니다 도각도각님 그런데 등록일시 컬럼이 DATE형이아니고 년월일 컬럼으로 분리되어있는

    상황인데 where to_date(년||월||일,'yyyymmdd') between 검색시작일시 and 검색종료일시 

    이런식으로 만들라는 말씀이신가요??

    0
  • 도각도각
    3k
    2019-03-15 10:27:32 작성 2019-03-15 10:27:40 수정됨

    조건값과 비교값 모두 같은 자료형으로 만들어야죠.

    1
  • 야바바
    65
    2019-03-15 11:28:27 작성 2019-03-21 15:17:40 수정됨

    넵 정말 감사합니다!!! :)

    0
  • 배우고싶은이
    580
    2019-03-15 11:34:42

    where to_date(년||월||일,'yyyymmdd') between 검색시작일시 and 검색종료일시 


    --> 이렇게 하면 모든 레코드에 대해서 to_date 함수가 작동하지 않나요?

    1
  • 르매
    382
    2019-03-15 12:56:04

    @배우고싶은이 

    네, 그렇죠.. 테이블 Full Scan이 일어나고 모든 레코드에 대해서 to_date() 함수가 사용됩니다. 당연히 성능은 극악이고 이런 설계에서는 인덱스도 도움이 안되는...

    날짜를 string으로 저장하거나 년, 월, 일로 분리해서 저장하는 경우가 있는데.. 비교적 흔하게 보게 되는 잘못된 설계의 사례죠.

    1
  • 야바바
    65
    2019-03-15 13:17:02 작성 2019-03-15 13:17:38 수정됨

    배우고싶은이,르매님 답변 감사합니다!!  두분 말씀은 to_date()함수에 년,월,일을 넣으면 3컬럼이 가지고있는 모든 데이터를 조회하게되서 퍼포먼스적으로 안좋다는 말씀이신거죠?

    0
  • venetianGM
    58
    2019-03-15 16:06:52

    일시(일자+시각)류는 YYYYMMDD HHMMSS 문자열 컬럼으로 설계하는 것이 가장 좋습니다.

    일자 : 8자리문자열

    시각: 6자리문자열

    이것은 LIKE검색이나 범위검색시 인덱스부분범위탐색(Index range scan)을 유도하기 위해서이죠.


    년월일컬럼이 분리되어있는데 인덱스를 태우고 싶다면 FBI(Function based index)를 만들어야 됩니다.

    1
  • 배우고싶은이
    580
    2019-03-15 16:39:51

    //venetianGM

    저는 일시 컬럼은 DATETIME 이나 TIMESTAMP 형으로 하는것이 좋다고 배웠는데요..

    DATETIME이나 TIMESTAMP로 하면 검색시에 불리한가요?

    1
  • 르매
    382
    2019-03-15 16:49:17

    날짜 컬럼은 datetime 으로 설계하는 것이 좋습니다.

    string은 I/O면에서 불리합니다. 당장 YYYYMMDD HHMMMM 로 표시하면 15byte를 사용합니다.
    이에 반해 datetime(0)를 사용하면 5byte면 됩니다.

    datetime은 내부적으로 정수를 사용하기 때문에 정렬, 포인트 검색, 범위 검색 모두에서 유리합니다.

    최소한 string이어서 더 유리한 부분은 없다고 봐야죠.

    1
  • venetianGM
    58
    2019-03-15 16:55:18

    DATETIME, TIMESTAMP 형을 추천하는건 아주 오래전, 자료저장공간절약(Poorman's algorithm)때 얘기입니다.

    문자열자료형이 최소 14byte 공간이 필요한 반면에 일시자료형은 4~8byte로 저장할 수 있는 장점이 있었기 때문이죠.

    현재에도 일시자료형은 DBMS저장공간은 절약할 수 있겠지만, 웹환경에서 클라이언트로 전달될때 오히려 반대가 됩니다.

    2019-01-13 16:53:21 로 표준출력이 문자열로 전달되기 때문이죠, 이는 20190113165321 14byte보다 5byte초과된 값입니다.

    문자열자료형은 DBMS에서 LIKE검색이 되어 비교적 코드간결성면에서도 유리하고 BETWEEN검색에 유리하여 일자와 시간으로 해당 레코드를 찾아내는데 일시자료형보다 훨씬 유리합니다.

    1
  • venetianGM
    58
    2019-03-15 16:58:24

    덧붙혀서 잘못된 설계로 인해, 불필요하게 일시자료형을 TO_CHAR로 형변환해서 사용하는 경우가 허다합니다.

    1
  • 르매
    382
    2019-03-15 17:10:05 작성 2019-03-15 17:16:22 수정됨

    DBMS에서 데이터를 DB 연결 드라이버를 거쳐 웹서버에 전달했을 때 19자가 된다고 말씀하시고 계신데, 그건 DBMS의 설계나 성능과는 무관합니다. 설령 그 19자를 DBMS가 변환해서 보내준다하더라도.. CPU와 네트워크 I/O 보다는 스토리지 I/O 쪽이 병목이기 때문에 이쪽을 절약해 주는 것이 좋습니다.

    그리고 TO_CHAR로 형 변환해서 사용하는 것은 SARGs 형태의 올바른 쿼리 작성에 익숙하지 않으신 분들의 실수이지, datetime 유형의 문제가 아닙니다. ^^;

    그보다는 datetime 유형을 제대로 다루려면 약간의 시행 착오와 경험이 필요한데 비해.. 문자열은 상대적으로 많이 다루다 보니 익숙하고 편해서 생긴 편견이라고 봅니다.

    실제로는 일자를 다루는 다양한 문제에서.. datetime이 아니라서 문제가 생기는 경우는 있어도 datetime이어서 문제가 생기는 경우는 없으니까요.

    1
  • venetianGM
    58
    2019-03-15 17:22:32

    네, 르매님 말씀하신데로, datetime을 잘 사용하면 좋겠지만 현실은 그렇지 않습니다.

    모든 대규모 차세대 시스템 전사DA팀에서 모든 날짜관련 컬럼들을 문자형으로 데이터표준화하는 이유도 바로 그것입니다.

    참조 : https://okky.kr/article/230029 

    1
  • 르매
    382
    2019-03-15 17:39:47 작성 2019-03-15 17:41:06 수정됨

    네, 말씀하신 "현실"이 SI 현장이고 레거시와 조율해야하는 현장이라면 일부 동의합니다~

    저도 십수년전에 날짜 컬럼에 문자열을 쓰는게 맞다는 개발자분과 논쟁을 벌인적이 있었는데, 그 분도 대규모 전산 시스템을 다루던 곳에서 오셨었죠.

    생각해보니 DB2나 오라클을 오래 하신 분들 중에 이런 경향을 보게 되는 듯한데, 다 그런 것은 아닙니다. 예를 들면 MS-SQL 쪽에서는 이건 논쟁의 여지조차 없습니다. (string 사용하면 초보 취급)

    저도 DB 설계만 15년 넘게 해왔고, 지금까지 현업에서 손을 떼지 않고 있기 때문에 현실은 알만큼은 안다고 생각합니다만, 언급하신 현장에서는 실제 성능과 쿼리 효용성과 별개로 불가항력적인 부분이 있다는 것에 대해 이해하게 되었습니다. 감사합니다.

    다만, 서비스 개발 - 특히 모델 설계 - 에 주도권을 가지고 있고, 레거시의 제약이 없는 환경이라면 datetime 유형을 사용하시도록 권해 드립니다.


    1
  • venetianGM
    58
    2019-03-15 18:27:03 작성 2019-03-15 18:55:48 수정됨

    번외로 수많은 케이스들이 있겠지만, 오라클DB 기준으로 "기준일자가 오늘인 데이터"를 예로 들자면


    1. 문자열자료형, VARCAHR2(8)인 경우

    select bas_dt...from ... where bas_dt = '20190315' 

    => index range scan

    1,000 유저가 동시에 저 기준일자를 조회한다고 했을때 클라이언트 전달자료는 8byte x 1,000 = 8Mbyte


    2. 일시자료형인 경우

    1日 = 1 x 24 x 60 x 60 = 86400초

    23시간59분59초 = 86399초

    86399 / (1 * 24 * 60 * 60) = 0.999988425925926日

    select bas_dt...from ... where bas_dt between to_date('20190315','YYYYMMDD') and to_date('20190315','YYYYMMDD') + 0.999988425925926

    => index range scan

    또는

    select bas_dt..from ... where bas_dt >= to_date('20190315','YYYYMMDD') and bas_dt < to_date('20190315','YYYYMMDD') + 1

    => index range scan

    또는

    select bas_dt...from ... where to_char(bas_dt,'YYYYMMDD') = '20190315'

    => table full scan

    1,000 유저가 동시에 저 기준일자를 조회한다고 했을때 클라이언트 전달자료는 19byte x 1,000 = 19Mbyte
    Concurrent user가 100만이 넘어가고 저런필드가 수많이 존재한다면 불필요한 네트워크 트래픽이 급증하게 됩니다.
    Disk I/O입장에서는 VARCHAR2(8) vs DATE = 8byte vs 8byte로 동일하겠죠.
    (물론 일자+시간을 담기위해 VARCHAR2(14)로 일시를 담았다면 14byte vs 8byte로 Disk I/O가 불리하긴 합니다만, 일자와 시간을 분리해서 VARCHAR2(8), VARCHAR2(6)으로 담게되면 동일합니다.)


    결국, 이것은 근현대사에서 20년 넘짓 분쟁을 일으킬 정도로 국내에서 주로 발생한 잘 알려진 이슈입니다.
    한국인들이 YYYYMMDD 날짜표기법을 선호하기 때문이죠.
    이것은 마치 서양인들이 1byte(8bit)도 안되는 7bit에 모든 alphanum과 특수기호를 섞은 경량 캐릭터셋인 us7ascii 또는 latin1, latin2 를 사용하면서 고민도 하지 않는 부분인 한글깨짐현상과 비슷한 존재입니다.
    한국에서는 3Byte라는 무거운 al32utf8 캐릭터셋을 사용하면서도 고질적인 한글깨짐 현상의 운명을 피할수 없죠.
    (아..  나도 서양인으로 태어날껄)
    0
  • 르매
    382
    2019-03-15 20:12:24 작성 2019-03-15 20:16:15 수정됨

    제 개인적인 생각을 말씀드려봅니다.

    date, time, datetime, timestamp 는 날짜와 시간 형식의 데이터를 저장하도록 최적화되어 있는 데이터 유형입니다.

    RDB의 미덕 중 정말 훌륭한 것 하나가 데이터의 무결성을 보장해 주는 부분입니다. 이 점을 기억해 본다면, 실제 저장되는 값에 최적화된 유형을 사용하는 것의 잇점을 이해하시리라 믿습니다.

    예를 들어 음수가 저장되지 않아야 할 때 UNSIGNED로 선언하거나, UNSIGNED를 지원하지 않는 MS-SQL은 CHECK Constraint를 사용하죠.

    날짜, 시간 형식도 그렇습니다.
    date, time, datetime, timestamp 모두 맞는 형식일 때만 저장됩니다.

    문자열로 저장한다면?

    문자열로 날짜를 저장하도록 설계했는데, 버그 때문에 날짜 형식에 어긋나는 데이터가 들어가는 일이 비일비재합니다. 또는 날짜 형식은 맞는데 YYYYMMDD와 DDMMYYYY가 섞일 수도 있겠죠.

    잘못된 데이터는 쌓여만 가는데.. 언제 눈치 채나요?
    당연히 날짜 형식이라고 생각해서 쿼리를 작성했는데 오류가 발생하면.. 그제서야 알게됩니다.

    성능 문제도 마찬가지입니다.

    물론 요즘 하드웨어 스펙이 워낙 좋기 때문에..  디스크 I/O나 네트워크 트래픽을 고려해서데이터 유형의 사이즈까지 고민해야하나? 라고 생각해보면.. 맞습니다. 이걸로 유의미한 성능 차이가 나기는 어렵습니다.

    그래도 이 문제가 언급되었으니 계속 얘기를 해본다면 ^^

    먼저 Oracle, MySQL, MS-SQL 모두 date, time, datetime을 지원합니다.
    각각 3byte, 3byte, 8byte 인데, MySQL과 MS-SQL에서는 초까지만 저장하는 경우 5byte 만 사용합니다.

    여기서의 사이즈는 디스크를 읽고 쓸때 디스크 I/O에 영향을 미치는 부분이죠.
    그리고 DBMS는 거의 디스크 I/O에서 병목이 발생하기 때문에 늘 조심하는 부분이기도 합니다.

    그런데 사실 venetianGM 님께서 네트워크 트래픽을 언급하실 때까지, 그런  문제가 있을 수 있다고 생각해보지 않았습니다. 왜냐하면 클라이언트에 찍히는 표준 일자 형식으로 변환하는 작업이 DBMS에서 일어나는 작업 일 것이라고 생각해 본 일이 없기 때문입니다. 저는 이 역할이 OLE DB, ODBC, JDBC 와 같은 드라이버 수준에서 일어나는 매핑 및 변환이라고 이해하고 있었습니다.

    그래서 조금 찾아봤지만 명확히 어느 쪽인지 알려주는 자료는 찾지 못했습니다. (아시는 분 알려주시면 감사하겠습니다~)

    하지만 처음에 기술한 것처럼.. 이렇든 저렇든 지금의 하드웨어 환경에서 유의미한 성능 차이는 찾기 어렵다는게 제 생각입니다.

    제 나름의 결론은...

    여전히 문자열을 지양해야한다는 것입니다.

    즉, datetime 류의 유형이 기본 선택지이고, 문자열을 사용해야하는 명백한 이유가 있을 때만 문자열을 사용해야 한다고 생각합니다. ^^

    0
  • 로그인을 하시면 답변을 등록할 수 있습니다.