꿈꾸는 개발자, DBA 커뮤니티 구루비

쿼리 수정 조언좀 부탁드려요.

by yozm0213 [2014.01.24 09:11:06]

SELECT TOTAL_CHOGWA, CNT, USER_ID, USER_NAME, GRADE_NAME, DEFAULT_UNIT_PRICE, EXCEED_UNIT_PRICE, 
 NVL(EXCEED_TIME,0) EXCEED_TIME, 10 DEFAULT_TIME, 
 CASE WHEN NVL(TOTAL_TIME,0) = 0 THEN 10 ELSE NVL(TOTAL_TIME,0) END TOTAL_TIME, 
 NVL(ACTUAL_EXCEED_TIME, 0) ACTUAL_EXCEED_TIME, 
 (10*DEFAULT_UNIT_PRICE) DEFAULT_PRICE, NVL(TOTAL_TIME - 10,0)*EXCEED_UNIT_PRICE EXCEED_PRICE, (10*DEFAULT_UNIT_PRICE)+NVL(TOTAL_TIME - 10,0)*EXCEED_UNIT_PRICE PRICE 
 FROM ( 
 SELECT 
 CHOGWA_HOUR_1 + TRUNC( CHOGWA_HOUR_2 / 60 ) || ';' || ROUND(MOD(CHOGWA_HOUR_2 / 60 , 1) * 60) TOTAL_CHOGWA , 
 K.DEFAULT_UNIT_PRICE, 
 K.EXCEED_UNIT_PRICE, 
 K.OVER_TIME ACTUAL_EXCEED_TIME, 
 CNT, 
 USR_SID, 
 K.USER_NAME, 
 K.USER_ID, 
 K.GRADE_NAME, 
 (CHOGWA_HOUR_1 + TRUNC( CHOGWA_HOUR_2 / 60 )) EXCEED_TIME, 
 CASE WHEN (10+CHOGWA_HOUR_1 + TRUNC( CHOGWA_HOUR_2 / 60 )) > OVER_TIME 
 THEN OVER_TIME ELSE 10 + CHOGWA_HOUR_1 + TRUNC( CHOGWA_HOUR_2 / 60 ) END TOTAL_TIME 
 FROM 
 ( 
 SELECT 
 SUM( SUBSTR(TO_CHAR(CHOGWA , 'HH24MI') , 1 , 2) ) CHOGWA_HOUR_1 , 
 SUM( SUBSTR(TO_CHAR(CHOGWA , 'HH24MI') , 3 , 4) ) CHOGWA_HOUR_2 , 
 COUNT(*) CNT, 
 USR_SID 
 FROM 
 ( 
 SELECT 
 CASE 
 WHEN TO_DATE('040000' , 'HH24MISS') < COMPARE_TIME 
 THEN TO_DATE('040000' , 'HH24MISS') 
 ELSE 
 CASE 
 WHEN TO_DATE('000000' , 'HH24MISS') < COMPARE_TIME 
 THEN COMPARE_TIME 
 ELSE TO_DATE('000000' , 'HH24MISS') 
 END 
 END CHOGWA, 
 DEP_CODE, 
 USR_SID, 
 CLSS_NM 
 FROM 
 ( 
 SELECT 
 EXCE_WORK_YMD , 
 WORK_ST_TM , 
 WORK_END_TM , 
 DEP_CODE, 
 USR_SID, 
 CLSS_NM , 
 TO_DATE 
 ( 
 CASE 
 WHEN ROUND( MOD(COMPARE_TIME - THIRD_TIME , 1) * 24 * 60 * 60 ) < 0 
 THEN 0 
 ELSE ROUND( MOD(COMPARE_TIME - THIRD_TIME , 1) * 24 * 60 * 60 ) 
 END 
 , 
 'SSSSS' 
 ) COMPARE_TIME 
 FROM 
 ( 
 SELECT 
 EXCE_WORK_YMD , 
 WORK_ST_TM , 
 WORK_END_TM , 
 THIRD_TIME , 
 DEP_CODE, 
 USR_SID, 
 CLSS_NM , 
 TO_DATE( ROUND( MOD(SECOND_TIME - FIRST_TIME , 1) * 24 * 60 * 60 ) , 'SSSSS') COMPARE_TIME 
 FROM 
 ( 
 SELECT 
 ( 
 TO_CHAR(TO_DATE(EXCE_WORK_YMD , 'XYYYY-MM-DD') , 'YYYY-MM-DD') || 
 '(' || TO_CHAR(TO_DATE(EXCE_WORK_YMD , 'YYYY-MM-DD') , 'DY' , 'NLS_DATE_LANGUAGE=KOREAN') || ')' 
 ) EXCE_WORK_YMD , 
 TO_CHAR(TO_DATE(WORK_ST_TM , 'HH24MISS') , 'HH24MI') WORK_ST_TM , --출근시간 
 CASE 
 WHEN TO_NUMBER(WORK_END_TM) > 2359 
 THEN TO_CHAR(TO_DATE('2359' , 'HH24MISS') , 'HH24MI') 
 ELSE TO_CHAR(TO_DATE(WORK_END_TM , 'HH24MISS') , 'HH24MI') 
 END WORK_END_TM , --퇴근시간 
 CASE 
 WHEN HOLIDAY_SE = 0 AND TO_NUMBER(WORK_ST_TM) > 0800 
 THEN TO_DATE('0900' , 'HH24MISS') 
 ELSE TO_DATE(WORK_ST_TM , 'HH24MISS') 
 END FIRST_TIME , --계산용 출근시간 
 CASE 
 WHEN TO_NUMBER(WORK_END_TM) > 2359 
 THEN TO_DATE('2359' , 'HH24MISS') 
 ELSE TO_DATE(WORK_END_TM , 'HH24MISS') 
 END SECOND_TIME , --계산용 퇴근시간 
 DECODE( HOLIDAY_SE , 1 , TO_DATE('000000' , 'HH24MISS') , TO_DATE('100000' , 'HH24MISS') ) THIRD_TIME , 
 DEP_CODE, 
 USR_SID, 
 CLSS_NM 
 FROM SLFTNGOVWRK 
 WHERE BSN_SE = '002' 
 AND SUBSTR (EXCE_WORK_YMD, 1, 6) = '201312' 
 ) M, (SELECT USER_SN FROM SSOV_USER WHERE DEPT_ID='30301230000') N WHERE M.USR_SID=N.USER_SN 
 ) 
 ) 
 ) 
 WHERE 1=1 
 GROUP BY USR_SID 
 ) A, 
 ( SELECT 
 A.USER_NAME, 
 A.DEPT_NAME, 
 A.USER_SN, 
 A.USER_ID, 
 B.DEFAULT_UNIT_PRICE, 
 B.EXCEED_UNIT_PRICE, 
 A.GRADE_NAME, 
 A.GRADE_ID, 
 A.POSITION_ID, 
 CASE WHEN A.USER_ID = C.USER_ID THEN C.OVER_TIME 
 ELSE DECODE((SELECT 'Y' FROM DUAL WHERE EXISTS(SELECT 'Y' FROM U103_USER_OVER_TIME_DEFAULT)), 'Y', (SELECT OVER_TIME_DEFAULT FROM U103_USER_OVER_TIME_DEFAULT), 0) 
 END OVER_TIME 
 FROM SSOV_USER A, 
 U103_UNIT_PRICE B, 
 U103_USER_OVER_TIME C 
 WHERE DEPT_ID='30301230000' 
 AND USER_STAT IN ('AAA', 'AAB', 'BAB', 'BAF', 'BAN', 'BAP', 'DAC', 'BAR') 
 AND USE_YN='1' 
 AND A.GRADE_ID = B.UNIT_CLASS 
 AND A.USER_ID = C.USER_ID(+) 
 AND USER_SN > '3900000000000' 
 AND A.USER_ID NOT IN (SELECT CONTRACT_WORKERS_ID FROM U103_UNIT_PRICE WHERE CONTRACT_WORKERS_DEPT_CODE = '30301230000' AND DEL_YN = 'N') 
 AND B.REGULARITY = 'Y' 
 UNION 
 SELECT 
 A.USER_NAME, 
 A.DEPT_NAME, 
 A.USER_SN, 
 A.USER_ID, 
 B.DEFAULT_UNIT_PRICE, 
 B.EXCEED_UNIT_PRICE, 
 A.GRADE_NAME, 
 A.GRADE_ID, 
 A.POSITION_ID, 
 CASE WHEN A.USER_ID = C.USER_ID THEN C.OVER_TIME 
 ELSE DECODE((SELECT 'Y' FROM DUAL WHERE EXISTS(SELECT 'Y' FROM U103_USER_OVER_TIME_DEFAULT)), 'Y', (SELECT OVER_TIME_DEFAULT FROM U103_USER_OVER_TIME_DEFAULT), 0) 
 END OVER_TIME 
 FROM SSOV_USER A, 
 U103_UNIT_PRICE B, 
 U103_USER_OVER_TIME C 
 WHERE DEPT_ID='30301230000' 
 AND USER_STAT IN ('AAA', 'AAB', 'BAB', 'BAF', 'BAN', 'BAP', 'DAC', 'BAR') 
 AND USE_YN='1' 
 AND A.USER_ID = B.CONTRACT_WORKERS_ID(+) 
 AND A.USER_ID = C.USER_ID(+) 
 AND USER_SN > '3900000000000' 
 AND A.USER_ID IN (SELECT CONTRACT_WORKERS_ID FROM U103_UNIT_PRICE WHERE CONTRACT_WORKERS_DEPT_CODE = '30301230000' AND DEL_YN = 'N') 
 AND B.REGULARITY = 'N' 
 ) K 
 WHERE A.USR_SID(+)=K.USER_SN 
 ORDER BY GRADE_ID, POSITION_ID 
 )

by yozm0213 [2014.01.24 09:19:13]

사용자 2천명 정도의 하루 2천건의 데이터가 한달동안 쌓이는걸 셀렉트해옵니다.
그런데 속도가 너무느리네요. 혹시 not in이나 union때문에 많이 느려지는 걸까요?
조언좀 부탁드려요..

by 마농 [2014.01.24 10:43:44]

맨 안쪽의 년월 조건 변경하세요.
-- AND SUBSTR (exce_work_ymd, 1, 6) = '201312'
AND exce_work_ymd >= TO_DATE('201312', 'yyyymm')
AND exce_work_ymd < ADD_MONTHS(TO_DATE('201312', 'yyyymm'), 1)
조건 주실 때 컬럼을 변형하지 마시고 조건값을 변형하세요.

그리고 맨 안쪽 쿼리의 Case 문은 쓸데 없는 복잡한 구문인 듯 합니다.
안써도 될 TO_NUMBER, TO_DATE, TO_CHAR 를 너무 그냥 막 가져다가 쓴 느낌?
work_st_tm, work_end_tm 이 4자리 문자열이라면?
TO_NUMBER, TO_DATE, TO_CHAR 등은 모두 다 필요 없어 보입니다.

M 과 N 의 조인도
N 을 M 안으로 밀어넣어 조인하셔도 될것 같구요.

K 안쪽 쿼리의 Exists 구문은 완전 잘못 사용하신 구문이구요.
Exists 를 이런식으로 사용하시면 아무 소용 없습니다.
Exists 구문은 메인쿼리의 컬럼을 서브쿼리에 조건으로 투입시켜야 합니다.
아무 조건 없이 사용한 Exists 구문은 항상 참일 수밖에 없습니다.

K 안쪽 쿼리의 UNION 은 UNION ALL 로 써야 하구요.
NOT IN 은 아우터 조인 후 널체크 하는 방식으로 바꿀 수 있습니다.
더 나아가서는 IN, NOT IN 두개로 나누지 말고
IN 절의 서브쿼리를 FROM 절로 올려 아우터 조인 한뒤
조인 결과가 있는지 없는지에 따라 다르게 표현하는 방식으로 하면
UNION 없이 하나로 가능 할듯도 합니다.

by yozm0213 [2014.01.24 14:12:36]

답변 감사합니다. 조언해주신대로 모두 수정하였는데도 속도가 느리네요. union all이 문제인듯 싶은데 하나로 만든들기 어렵네요. 혹시 union대신 쓸 수 있는 방법이 있을지..
지금 union 위에 쿼리는 정규직 정보를 모두 셀렉트 해오면서 비정규직 정보를 제외한 쿼리이고 아래 쿼리는 비정규직 정보만을 조회해와서 union으로 합한 쿼리입니다. 그런데 이걸 하나로 만드는게 어려운 이유가 정규직은 아이디로 구분짓지 않고 직급으로 구분짓기때문에 정보를 조회해올떄 정규직, 비정규직을 가리지 않고 정보를 조회해오거든요. 그래서 하나로 만드려니 정규직 정보가 나와도 비정규직에 등록되어 있으면 비정규직을 우선으로 나오게해야하는데 이걸 하나로 만들수있을까요?

by 마농 [2014.01.24 14:43:20]

SELECT a.user_name
     , a.dept_name
     , a.user_sn
     , a.user_id
     , NVL(b1.default_unit_price, b.default_unit_price) default_unit_price
     , NVL(b1.exceed_unit_price , b.exceed_unit_price ) exceed_unit_price 
     , a.grade_name
     , a.grade_id
     , a.position_id
     , COALESCE( c.over_time
               , (SELECT over_time_default FROM u103_user_over_time_default)
               , 0
               ) over_time
  FROM ssov_user a
     , u103_unit_price b
     , u103_unit_price b1
     , u103_user_over_time c
 WHERE dept_id = '30301230000'
   AND user_stat IN ('AAA', 'AAB', 'BAB', 'BAF', 'BAN', 'BAP', 'DAC', 'BAR')
   AND use_yn = '1'
   AND a.user_id = c.user_id(+)
   AND user_sn > '3900000000000'
   AND b.regularity = 'Y'
   AND a.grade_id = b.unit_class(+)
   AND a.user_id = b1.contract_workers_id(+)
   AND b1.contract_workers_dept_code(+) = '30301230000'
   AND b1.del_yn(+) = 'N'
;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변