데이터베이스 질문과 답변
안녕하세요 이제 1년이 안된 개발자초보입니다. 경험이 미천하여 해결못하는 쿼리가있습니다 .ㅠ
SELECT T.CUST_PATH_CD AS CUST_ORG_PATH_CD
FROM (SELECT MICUST_ID,
SUBSTR(SYS_CONNECT_BY_PATH(Z.MICUST_ID,':'),2) CUST_PATH_CD
FROM MCUTG1160 Z
WHERE TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') BETWEEN Z.VALI_FR_DT AND Z.VALI_TO_DT
START WITH Z.MICUST_ID = '10000918' --회사코드
CONNECT BY PRIOR Z.MICUST_ID = Z.MACUST_ID
AND Z.CUST_REL_CD IN ('B02','B03','B04','B06')
) T
WHERE T.MICUST_ID = '70259937'
위와같은 쿼리로 아래와 같은 결과가 나옵니다. 결과는 문제가 없습니다. 하지만 속도가 문제라 속도를 해결하 고 싶습니다.
10000918:10021478:70056100:70259937
아래와 같이 XMLAGG 를 사용하여 속도문제를 해결하려고하는데
SELECT RTRIM(XMLAGG(XMLELEMENT("MICUST_ID", t.micust_id,':')).EXTRACT('//text()').getStringval(),',') || '70259937' CUST_PATH_CD
FROM
(
SELECT Z.MACUST_ID, Z.MICUST_ID, LEVEL LVL, Z.CUST_REL_CD, Z.MICUST_ID CUST_PATH_CD
-- SUBSTR(SYS_CONNECT_BY_PATH(Z.MICUST_ID,':'),2) CUST_PATH_CD
FROM MCUTG1160 Z
WHERE TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') BETWEEN Z.VALI_FR_DT AND Z.VALI_TO_DT
START WITH Z.MICUST_ID = '10000918' --회사코드
CONNECT BY PRIOR Z.MICUST_ID = Z.MACUST_ID
AND Z.CUST_REL_CD IN ('B02','B03','B04','B06')
and rownum < 4
) T
문제는 rownum 이 LEVEL 의 MAX 값인 4보다 작아야한다는 것인대 속도에 영향을 미치지않고
4를 어떻게 구해오냐 하는것입니다.
결론적으로 XMLAGG 을 사용하기위해
아래 처럼 나오게 하는법을 알고 싶습니다.
제가 초보라 질문하는대 부족한 점이 있을지도 모르겠습니다. 더 필요한 정보같은게 있다면 알려주시면 감사하겠습니다.
10000918
10021478
70056100
70259937
SELECT T.CUST_PATH_CD AS CUST_ORG_PATH_CD
FROM (SELECT MICUST_ID,
SUBSTR(SYS_CONNECT_BY_PATH(Z.MICUST_ID,':'),2) CUST_PATH_CD
FROM MCUTG1160 Z
WHERE TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') BETWEEN Z.VALI_FR_DT AND Z.VALI_TO_DT
START WITH Z.MICUST_ID = '10000918' --회사코드
CONNECT BY PRIOR Z.MICUST_ID = Z.MACUST_ID
AND Z.CUST_REL_CD IN ('B02','B03','B04','B06')
) T
WHERE T.MICUST_ID = '70259937'
위와같은 쿼리로 아래와 같은 결과가 나옵니다. 결과는 문제가 없습니다. 하지만 속도가 문제라 속도를 해결하 고 싶습니다.
10000918:10021478:70056100:70259937
아래와 같이 XMLAGG 를 사용하여 속도문제를 해결하려고하는데
SELECT RTRIM(XMLAGG(XMLELEMENT("MICUST_ID", t.micust_id,':')).EXTRACT('//text()').getStringval(),',') || '70259937' CUST_PATH_CD
FROM
(
SELECT Z.MACUST_ID, Z.MICUST_ID, LEVEL LVL, Z.CUST_REL_CD, Z.MICUST_ID CUST_PATH_CD
-- SUBSTR(SYS_CONNECT_BY_PATH(Z.MICUST_ID,':'),2) CUST_PATH_CD
FROM MCUTG1160 Z
WHERE TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') BETWEEN Z.VALI_FR_DT AND Z.VALI_TO_DT
START WITH Z.MICUST_ID = '10000918' --회사코드
CONNECT BY PRIOR Z.MICUST_ID = Z.MACUST_ID
AND Z.CUST_REL_CD IN ('B02','B03','B04','B06')
and rownum < 4
) T
문제는 rownum 이 LEVEL 의 MAX 값인 4보다 작아야한다는 것인대 속도에 영향을 미치지않고
4를 어떻게 구해오냐 하는것입니다.
결론적으로 XMLAGG 을 사용하기위해
아래 처럼 나오게 하는법을 알고 싶습니다.
제가 초보라 질문하는대 부족한 점이 있을지도 모르겠습니다. 더 필요한 정보같은게 있다면 알려주시면 감사하겠습니다.
10000918
10021478
70056100
70259937
by 마농
[2012.04.21 03:22:47]
계층 쿼리를 상당히 잘못 작성하신 듯 합니다.
계층 쿼리에는 조건이 3개가 있는데.
where
start with
connect by
각 조건은 각각의 역할이 있어 그 역할대로 잘 쓰시면 문제 없지만
잘못 사용하시면 엉뚱한 결과나, 엄청난 성능저하를 초래하게 됩니다.
XMLAGG 로 성능향상을 꽤하신다고 하시면서 결국 그 안에는 똑같은 계층 쿼리를 사용하네요.
XMLAGG 문제가 아니라 계층쿼리만 잘 손보시면 될것 같습니다.
작성하신 쿼리가 어떤 의도로 작성된 쿼리인지 설명해 주시면 도움 드리겠습니다.
원본데이터에 대한 설명과 원하는 데이터에 대한 설명
조건이 어떻게 주어져야 하는지에 대한 설명
계층 쿼리에는 조건이 3개가 있는데.
where
start with
connect by
각 조건은 각각의 역할이 있어 그 역할대로 잘 쓰시면 문제 없지만
잘못 사용하시면 엉뚱한 결과나, 엄청난 성능저하를 초래하게 됩니다.
XMLAGG 로 성능향상을 꽤하신다고 하시면서 결국 그 안에는 똑같은 계층 쿼리를 사용하네요.
XMLAGG 문제가 아니라 계층쿼리만 잘 손보시면 될것 같습니다.
작성하신 쿼리가 어떤 의도로 작성된 쿼리인지 설명해 주시면 도움 드리겠습니다.
원본데이터에 대한 설명과 원하는 데이터에 대한 설명
조건이 어떻게 주어져야 하는지에 대한 설명
by 손님
[2012.04.21 13:37:49]
by 손님
[2012.04.21 13:38:13]
by 손님
[2012.04.21 13:38:43]
아는게 부족하여 질문하기도 힘드네요 ㅠ
by 손님
[2012.04.21 13:39:20]
SELECT Z.MACUST_ID, Z.MICUST_ID, level lvl
FROM MCUTG1160 Z
WHERE TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') BETWEEN Z.VALI_FR_DT AND Z.VALI_TO_DT
START WITH Z.MICUST_ID = '10000918' --회사코드
CONNECT BY PRIOR Z.MICUST_ID = Z.MACUST_ID
AND Z.CUST_REL_CD IN ('B02','B03','B04','B06') 아는게 부족하여 질문하기도 힘드네요 ㅠ
by 손님
[2012.04.21 15:39:38]
맨 상위부터 하위로 전개한다면 원하는 코드 뿐 아니라 전체 자료를 모두 가져오게 되지요.
원하는 코드가 딱 정해져 있다면
70259937 이란 코드값이 원하시는 값이라고 한다면?
계층 전개를 역으로 해보세요.
70259937 에서 시작해서 역으로 타고 올라간다면 단 4개의 행만 읽어오게 됩니다.
상위에서 하위로 전개할때 조건이 다음과 같다면
START WITH z.micust_id = '10000918'
CONNECT BY PRIOR z.micust_id = z.macust_id
PRIOR 의 위치만 바꿔 주시면 전개방향이 반대가 됩니다.
START WITH z.micust_id = '70259937'
CONNECT BY z.micust_id = PRIOR z.macust_id
기타 조건들이 계층쿼리의 선행 조건이 되는지? 후행 조건이 되는지 잘 판단하셔야 합니다.
선행이란 계층구조 전개전에 조건을 걸러내는 것이고
후행이란 계층구조 전개후에 조건을 걸러내는 것인데
Where 절의 조건은 후행조건이 됩니다.
선행조건이 되어야 한다면? 조건을 준 집합을 인라인뷰로 한번 감싸셔야 합니다.
원하는 코드가 딱 정해져 있다면
70259937 이란 코드값이 원하시는 값이라고 한다면?
계층 전개를 역으로 해보세요.
70259937 에서 시작해서 역으로 타고 올라간다면 단 4개의 행만 읽어오게 됩니다.
상위에서 하위로 전개할때 조건이 다음과 같다면
START WITH z.micust_id = '10000918'
CONNECT BY PRIOR z.micust_id = z.macust_id
PRIOR 의 위치만 바꿔 주시면 전개방향이 반대가 됩니다.
START WITH z.micust_id = '70259937'
CONNECT BY z.micust_id = PRIOR z.macust_id
기타 조건들이 계층쿼리의 선행 조건이 되는지? 후행 조건이 되는지 잘 판단하셔야 합니다.
선행이란 계층구조 전개전에 조건을 걸러내는 것이고
후행이란 계층구조 전개후에 조건을 걸러내는 것인데
Where 절의 조건은 후행조건이 됩니다.
선행조건이 되어야 한다면? 조건을 준 집합을 인라인뷰로 한번 감싸셔야 합니다.
by 마농
[2012.04.21 15:45:44]
아차! 로그인을 안하고 글을 썼네요 ^^
선행조건을 줄때와 후행조건을 줄때의 결과가 다를 수 있습니다.
또한 계층 전개 조건(Connect by 절의 조건 :
다음 두가지 조건 이 정확하게 무었을 원하는 조건인지?
잘 판단하셔야 합니다. (선행조건, 계층조건, 후행조건)
1. TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') BETWEEN Z.VALI_FR_DT AND Z.VALI_TO_DT
2. Z.CUST_REL_CD IN ('B02','B03','B04','B06')
선행조건을 줄때와 후행조건을 줄때의 결과가 다를 수 있습니다.
또한 계층 전개 조건(Connect by 절의 조건 :
AND Z.CUST_REL_CD IN ('B02','B03','B04','B06'))부분도 영향이 있습니다.다음 두가지 조건 이 정확하게 무었을 원하는 조건인지?
잘 판단하셔야 합니다. (선행조건, 계층조건, 후행조건)
1. TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') BETWEEN Z.VALI_FR_DT AND Z.VALI_TO_DT
by 손님
[2012.04.24 12:27:07]
역으로 전개하여 해결하였습니다.
감사합니다.~
감사합니다.~
) 버튼을 클릭하여 작성 하시면 됩니다.