데이터베이스 질문과 답변
조회하는 쿼리가 ,(컴마)로 구분되어 정규식 표현 함수로 구분을 한 쿼리가 있습니다.
SELECT
T_B.ORG_C AS ORG_C
, T_B.BR_C AS BR_C
, (CASE WHEN T_B.BR_USE_YN = 'N' THEN (
SELECT
T_A.UP_GRP_NM || ' > ' || T1.BR_NM || '(' || T_B.BR_NM || ')'
FROM TCM_DP T1
WHERE T1.BR_C = T_B.MGR_BR_C
)
ELSE (CASE WHEN T_A.UP_GRP_NM = T_B.BR_NM THEN T_B.BR_NM
ELSE T_A.UP_GRP_NM || ' > ' || T_B.BR_NM
END)
END) AS BR_NM
FROM TCM_DP_UP_GRP T_A
, (
SELECT
ORG_C
, BR_C
, BR_NM
, UP_GRP_C
, SOTNG
, BR_USE_YN
, MGR_BR_C
FROM TCM_DP
WHERE REGEXP_LIKE(MGR_BR_C,
(
SELECT LISTAGG(BR_C_LIST, '|') WITHIN GROUP (ORDER BY BR_C_LIST)
FROM (
SELECT
DISTINCT REGEXP_SUBSTR(A.MGR_BR_C, '[^,]+', 1, LEVEL) AS BR_C_LIST
FROM (
SELECT
MGR_BR_C
FROM TCM_DP
WHERE 1=1
<if test="BR_USE_YN == null and BR_USE_YN != 'ALL'">
AND BR_USE_YN = 'Y'
</if>
)A
CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(A.MGR_BR_C, '[^,]', '')) + 1
)
)
)
) T_B
WHERE T_A.UP_GRP_C = T_B.UP_GRP_C
ORDER BY T_A.UP_GRP_SEQ, T_B.SOTNG
위 쿼리는 현재 mybatis에서 사용중인 쿼리입니다.
https://drive.google.com/file/d/1EY5bzgsnacjbI2q679X8CzZ781RF-P0k/view?usp=sharing는 자체적으로 쿼리를 시험해본 쿼리입니다.
https://drive.google.com/file/d/1cxMoaf242k4ea7Mrg-8v1Hw6Nwgbjjkf/view 는 조회할 때 사용하는 데이터입니다.
자체적으로 테스트를 해보았을 때 중복제거를 없애면 빨라지지만 중복제거가 없어지면 안될 것 같습니다.
정규식 표현 함수 자체가 성능적으로 문제가 있다고 들었는데 어떻게해야 개선할 수 있을까요?
기존 쿼리 인수인계 및 물어볼 사람이 없어서 파악하다보니 정규식 표현을 쓰는 함수의 문제가 있는 것 같은데 어떻게 개선을 해야할지 알려주시면 감사하겠습니다..
1. Connect by Level 을 이용한 행 복제 이용 방법이 틀렸습니다.
- 여러건의 테이블에 직접적으로 사용되면 안됩니다. (엄청난 성능 저하)
- dual 처럼 단건의 자료에 사용되는 방식입니다.
- http://gurubee.net/article/55635
2. 같은 테이블을 여러번 사용해야만 하는지 의문이구요.
- 올려주신 링크는 제가 확인이 불가하여 자료를 분석할 수 없네요.
- 샘플 자료를 직접 적어 주실 수 있나요?
- 쿼리를 이렇게 사용한 이유를 설명해 주실 수 있나요?
일단 첫번째로 샘플 자료를 적겠습니다.
DB툴로 테스트용 쿼리를 친 내용입니다.
SELECT DISTINCT REGXP_SUBSTR(A.MGR_BR_C,'[^,]+',1,LEVEL) AS BR_C_LIST
FROM(
SELECT
MGR_BR_C
FROM ECM.TCM_DP
WHERE 1=1 AND BR_USE_YN = 'Y'
)A
CONNECT BY LEVEL <= LENGTH(REGXP_REPLACE(A.MGR_BR_C,'[^,]',''))+1
입니다.
그리고 두번째 자료입니다.
MGR_BR_C에 데이터는 아래와 같이 들어가 있습니다.
078
075,044,059,081
073
070
076,044
077
013
040
039
028
112,044,043,026
043,112
042
064,044
059,044
이하생락 총 52줄의 내용이 있음
그리고 쿼리를 이렇게 사용한 이유같은 경우에는 제가 여기 들어오기 전부터 전사람이 이렇게 만들었고 그분이 퇴사하시고 회사내에서 여쭤볼 사람이 없어서 이유를 잘 모르겠습니다. 솔직히 쿼리 해석도 매끄럽게 잘 안됩니다..
일단 링크 주신 것을 참고하여 쿼리 수정을 시도해보겠습니다. 감사합니다.
인덱스 정보도 주세요.
설계서를 봤을 때 PK는 ORG_C(기관_코드) BR_C(부점_코드) 입니다. 그리고 인덱스라고 써져있는건없는것 같고
색인 ID에 IE1_TCM_DP[BR_NM(부점_명)] 정도만 있습니다.
IE1 이게 뭔지 잘 모르겠네요...
-- 1. 잘못 사용된 Connect By Level 구문 수정 --
SELECT b.org_c
, b.br_c
, CASE WHEN b.br_use_yn = 'N'
THEN a.up_grp_nm || ' > ' || c.br_nm || '(' || b.br_nm || ')'
ELSE DECODE(a.up_grp_nm, b.br_nm, '', a.up_grp_nm || ' > ') || b.br_nm
END br_nm
FROM tcm_dp_up_grp a
, tcm_dp b
, tcm_dp c
, (SELECT LISTAGG(br_c_list, '|') WITHIN GROUP(ORDER BY br_c_list) br_c_list
FROM (SELECT DISTINCT REGEXP_SUBSTR(mgr_br_c, '[^,]+', 1, lv) br_c_list
FROM tcm_dp
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 9)
WHERE 1=1
AND br_use_yn = 'Y'
AND lv <= LENGTH(mgr_br_c) - LENGTH(REPLACE(mgr_br_c, ',')) + 1
)
) d
WHERE a.up_grp_c = b.up_grp_c
AND b.mgr_br_c = c.br_c(+)
AND REGEXP_LIKE(b.mgr_br_c, d.br_c_list)
ORDER BY a.up_grp_seq, b.sotng
;
-- 2. Exists 구문으로 변경 --
SELECT b.org_c
, b.br_c
, CASE WHEN b.br_use_yn = 'N'
THEN a.up_grp_nm || ' > ' || c.br_nm || '(' || b.br_nm || ')'
ELSE DECODE(a.up_grp_nm, b.br_nm, '', a.up_grp_nm || ' > ') || b.br_nm
END br_nm
FROM tcm_dp_up_grp a
, tcm_dp b
, tcm_dp c
WHERE a.up_grp_c = b.up_grp_c
AND b.mgr_br_c = c.br_c(+)
AND EXISTS (SELECT 1
FROM tcm_dp
WHERE 1=1
AND br_use_yn = 'Y'
AND REGEXP_LIKE(b.mgr_br_c, REPLACE(mgr_br_c, ',', '|'))
)
ORDER BY a.up_grp_seq, b.sotng
;
솔직히 아예 쿼리를 짜주실 것 까지는 생각 못했는데 감사합니다. DB툴로 돌려보고 개발계에 적용하니 속도가 확연히 다르네요 알려주신 덕에 빨리 끝낸 만큼 답변내용 공부해서 완벽하게 제껄로 만들겠습니다. 감사합니다
) 버튼을 클릭하여 작성 하시면 됩니다.