데이터베이스 질문과 답변

계층 쿼리 속도 개선 0 7 9,034

by 감자 [DB 기타] [2025.01.22 14:20:41]

holidayrepeat.xlsx (10,708Bytes)
holiday.xlsx (12,562Bytes)

안녕하세요

붙임파일 근무일수 구하기_1은 휴일 내역이고, 붙임파일 근무일수 구하기_2는 주당 근무일수를 구하는 쿼리로 보여지는데요

붙임파일 1은 금방 나오는데, 이를 이용한 붙임파일2에서도 계층쿼리를 적용하였는데 속도가 엄청 느려요

속도 개선 방법 좀 부탁드립니다.(테이블 내역 및 쿼리 결과 파일 추가 첨부합니다)

감사합니다.

by 마농 [2025.01.22 16:00:27]

Connect By Levle <= n 형태의 행복제 밥법은
dual 과 같이 1건의 자료에 적용하는 방법입니다.
다건의 집합에 직접 적용하면 큰일(?) 납니다.
http://gurubee.net/article/55635

직접 Connect By 조건을 걸지 마시고
(SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 99) 와 조인하는 형태로 개선해 보세요.
별도 달력 테이블을 가지고 있다면?
Connect By 를 이용하지 않고 달력 테이블을 이용하셔도 됩니다.

holiday_repeat 이란 테이블이 이용 되는데
테이블 이름이나 카운트에 이용하는 걸로 봐서는
이 테이블에 연속되는 일자들을 이미 가지고 있을 것 같은 느낌이 드네요.

테이블 자료 예시와 원하는 결과 자료를
원본 대비 결과표 형태로 예를 들어 주시면 좋을 듯 하네요.

by 감자 [2025.01.22 22:01:44]

답변 감사합니다. 많은 도움이 되었습니다. 저 구조라면 마농님 말씀대로 시간이 많이 걸릴 수 밖에 없군요.

해당 테이블(HOLIDAY_REPEAT 테이블은 HOLIDAY 테이블에 매년 공휴일을 입력하는데 단순히 매년 3.1절 처럼 공휴일이 반복되는 경우에 반복 입력을 피하기 위하여 만든 테이블임) 내용 및 쿼리문에 대한 결과 파일을 추가하였습니다. 

다시 한 번 쿼리 개선 부탁드립니다.

감사합니다

by 마농 [2025.01.23 10:39:08]

1. 잘못된 connect by 사용
2. 그로 인한 성능 저하 및 다량의 중복 자료 발생
3. 중복자료는 Distinct 로 제거 <- 잘못된 해결 법(중복 자체가 발생하지 않아야 함)

by 마농 [2025.01.23 10:03:26]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
WITH holiday_repeat AS
(
SELECT '01' hlr_hld_id FROM dual
UNION ALL SELECT '02' FROM dual
UNION ALL SELECT '03' FROM dual
UNION ALL SELECT '04' FROM dual
UNION ALL SELECT '05' FROM dual
UNION ALL SELECT '06' FROM dual
UNION ALL SELECT '07' FROM dual
UNION ALL SELECT '08' FROM dual
)
, holiday AS
(
SELECT '01' hld_id, '신정' hld_name, DATE '2020-01-01' hld_start_date, DATE '2020-01-01' hld_end_date FROM dual
UNION ALL SELECT '02', '삼일절'  , DATE '2020-03-01', DATE '2020-03-01' FROM dual
UNION ALL SELECT '03', '어린이날', DATE '2020-05-05', DATE '2020-05-05' FROM dual
UNION ALL SELECT '04', '현충일'  , DATE '2020-06-06', DATE '2020-06-06' FROM dual
UNION ALL SELECT '05', '광복절'  , DATE '2020-08-15', DATE '2020-08-15' FROM dual
UNION ALL SELECT '06', '한글날'  , DATE '2020-10-09', DATE '2020-10-09' FROM dual
UNION ALL SELECT '07', '개천절'  , DATE '2020-10-03', DATE '2020-10-03' FROM dual
UNION ALL SELECT '08', '성탄절'  , DATE '2020-12-25', DATE '2020-12-25' FROM dual
UNION ALL SELECT '09', '추석'    , DATE '2024-09-16', DATE '2024-09-18' FROM dual
UNION ALL SELECT '10', '추석'    , DATE '2025-10-05', DATE '2025-10-07' FROM dual
UNION ALL SELECT '11', '설날'    , DATE '2024-02-09', DATE '2024-02-11' FROM dual
UNION ALL SELECT '12', '설날'    , DATE '2025-01-28', DATE '2025-01-30' FROM dual
)
SELECT a.hld_id
     , a.hld_name
     , TO_CHAR(ADD_MONTHS(a.hld_start_date, y * 12) + d - 1, 'yyyymmdd') hld_date
     , TO_CHAR(ADD_MONTHS(a.hld_start_date, y * 12) + d - 1, 'd') hld_week
  FROM (SELECT a.hld_id
             , a.hld_name
             , a.hld_start_date
             , a.hld_end_date
             , a.hld_end_date - a.hld_start_date + 1 date_diff
             , NVL2( b.hlr_hld_id
                   , TO_CHAR(sysdate, 'yyyy') - TO_CHAR(a.hld_start_date, 'yyyy')
                   , 0) year_diff
          FROM holiday a
          LEFT OUTER JOIN holiday_repeat b
            ON a.hld_id = b.hlr_hld_id
        ) a
     , (SELECT LEVEL     d FROM dual CONNECT BY LEVEL <= 99) d
     , (SELECT LEVEL - 1 y FROM dual CONNECT BY LEVEL <= 99) y
 WHERE d <= date_diff
   AND y <= year_diff
;

 

by 감자 [2025.01.23 21:23:01]

마농님, 매우 매우 정말로 감사합니다.

(추가질문) 

(SELECT LEVEL     d FROM dual CONNECT BY LEVEL <= 99) d

(SELECT LEVEL - 1 y FROM dual CONNECT BY LEVEL <= 99) y 

상기 부분에서 '99' 는 연도가 늘어나도 문제가 없을까요?

by 마농 [2025.01.24 10:06:53]

1. d 부분은
- 우리나라 휴일은 3일이 최대입니다.
- 9 로 바꿔도 문제 없을 듯 하구요.
2. y 부분은
- 예시자료의 최초 시작인 2020년 기준으로 보면
- 향후 90 년 정도는 문제 없습니다.

by 감자 [2025.02.01 18:45:29]

마농님, 답변 매우 감사합니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입