데이터베이스 질문과 답변
by sun [SQL Query] CONNECT BY LEVEL [2016.09.25 00:34:06]
질문요지를 다시 수정하여 올림니다.
CONNECT BY LEVEL을 사용하여 모든 구간의 날짜를 가져오는 쿼리인데요...
아래 파란색 부분은 0.02초 나오지만 전체쿼리를 하면 DATA 가 점점 증가하면서 속도가 느려지는거 같아여..
SELECT distinct CAMP_NO AS CD,
GUBUN_NM AS CD_NM,
TO_CHAR(TO_DATE(START_DATE, 'YYYYMMDD') + LEVEL -1, 'YYYYMMDD') AS YMD
FROM ( SELECT CAMP_NO ,SEQ,GUBUN_NM,START_DATE,END_DATE ,
TO_DATE( END_DATE,'YYYYMMDD') - TO_DATE(START_DATE, 'YYYYMMDD') AS DIFF
FROM CAMP_DET
WHERE OFFICE = :AS_OFFICE_CD
AND CAMP_NO between :AS_MONTH||'00001' AND :AS_MONTH||'0099'
AND ( (START_DATE BETWEEN :AS_MONTH||'01' AND :AS_MONTH||'31') OR
(END_DATE BETWEEN :AS_MONTH||'01' AND :AS_MONTH||'31') )
)
CONNECT BY LEVEL <= DIFF +1 ;
[파란색 부분의 sql 결과] - 전체 2건, 0.02.초걸림.
CAMP_NO SEQ GUBUN_NM 시작일 종료일
--------------------------------------------------------------
2016090005 1 CAMP1 캠페인 20160905 20160911 => 05일~11일까지 7건
2016090014 1 CAMP2 캠페인 20160901 20160930 => 01~30일까지 31 건
총 38건 나와야함.
.
[전체결과] - 3198 건
2016090014 CAMP2 캠페인 20160901
2016090014 CAMP2 캠페인 20160902 => 2건
2016090014 CAMP2 캠페인 20160902
2016090014 CAMP2 캠페인 20160903 => 4건
2016090014 CAMP2 캠페인 20160903
2016090014 CAMP2 캠페인 20160903
2016090014 CAMP2 캠페인 20160903
2016090014 CAMP2 캠페인 20160904 => 8건
2016090014 CAMP2 캠페인 20160904
2016090014 CAMP2 캠페인 20160904
2016090014 CAMP2 캠페인 20160904
2016090014 CAMP2 캠페인 20160904
2016090014 CAMP2 캠페인 20160904
2016090014 CAMP2 캠페인 20160904
2016090014 CAMP2 캠페인 20160904
2016090014 CAMP2 캠페인 20160905 =>16건
2016090014 CAMP2 캠페인 20160905
2016090005 CAMP1 캠페인 20160905
........
이렇게 예상했던 38건이 아니라 각 날짜별로 *2건씩 점점 늘어나는군요..
이전 질문이 속도가 느리다는 질문을 드렸다가 이것저것 보다보니 위와같이 DATA가 증가하는 문제가 있었기
때문에 속도가 느렸던 모양입니다.
하나의 camp_no 만 해봤을땐 건수 제대로 나오는데 2개이상의 camp_no가 조회될땐 점점 증가해서 나오는데..
쿼리에 어떤 문제점이 있는지 좀 알 수 있을까요.. 부탁드립니다.
제 질문에 제가 해결을 했습니다. 혹시나 저같이 해매시는 분들 있으시면 도움되시길 바랍니다.
아래 붉은색 글자가 위 질문의 쿼리에 추가된 내용입니다.
SELECT distinct CAMP_NO AS CD,
GUBUN_NM AS CD_NM,
TO_CHAR(TO_DATE(START_DATE, 'YYYYMMDD') + LEVEL -1, 'YYYYMMDD') AS YMD
FROM ( SELECT rownum rn, CAMP_NO ,SEQ,GUBUN_NM,START_DATE,END_DATE ,
TO_DATE( END_DATE,'YYYYMMDD') - TO_DATE(START_DATE, 'YYYYMMDD') AS DIFF
FROM CAMP_DET
WHERE OFFICE = :AS_OFFICE_CD
AND CAMP_NO between :AS_MONTH||'00001' AND :AS_MONTH||'0099'
AND ( (START_DATE BETWEEN :AS_MONTH||'01' AND :AS_MONTH||'31') OR
(END_DATE BETWEEN :AS_MONTH||'01' AND :AS_MONTH||'31') )
)
CONNECT BY LEVEL <= DIFF +1
and prior rn = rn
and prior dbms_random.value <> 1 ;
그런데.. and prior dbms_random.value <> 1 ; => 요부분이 잘 이해가 안되는군요..
저두 구글링을 해서 찾기 했지만.. 이전 랜덤값이 1이 아닌것을 가져온다? 혹시 이에 대해 하시는 분 댓글 부탁드립니다.
1. Connect By Level 을 이용한 행복제 방법은 오직 1건일 때만 사용합니다.
- 복수의 건에 직접 적용하면 카티션 곱에 의한 성능 저하가 생깁니다.
- http://www.gurubee.net/article/55635
2. 기간 검색 조건이 틀렸네요.
- 기간 검색은 시작일과 종료일을 교차 비교하면 됩니다.
3. 기간이 월을 벗어날 경우 월에 해당하는 일자만 보여줘야 한다면?
- 약간의 응용이 필요하겠네요.
SELECT cd , cd_nm , TO_CHAR(sdt + lv - 1, 'yyyymmdd') AS ymd FROM (SELECT camp_no AS cd , gubun_nm AS cd_nm , GREATEST(TO_DATE(:as_month, 'yyyymm'), TO_DATE(start_date, 'yyyymmdd')) sdt , LEAST(LAST_DAY(TO_DATE(:as_month, 'yyyymm')), TO_DATE(end_date, 'yyyymmdd')) edt FROM camp_det WHERE office = :as_office_cd AND camp_no BETWEEN :as_month||'00001' AND :as_month||'0099' AND start_date <= :as_month||'31' AND end_date >= :as_month||'01' ) a , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 999) b WHERE lv <= edt - sdt + 1 ORDER BY cd, ymd ;
) 버튼을 클릭하여 작성 하시면 됩니다.