데이터베이스 질문과 답변
주문-제품관계 테이블이 1개의 주문에 복수개의 제품종류를 관리하고 있는데 이를 제품-주문 개별로 표시하고자 합니다.
유의 사항은 하나의 주문에 대해 제품의 종류는 1개부터 10개까지 발생할 수 있습니다.
(즉 주문에 따라 제품 종류가 1개부터 최대 10개가 있을 수 있습니다.)
LEVEL CONNECT문장을 사용하니 처리시간이 너무 많이 걸리는데 개선 방안이 있을까요
WITH WH_ORD_PROD AS
( select ORD_NO, PRD_CD1, PRD_CD2, PRD_CD3, PRD_CD4, PRD_CD5, PRD_CD6, PRD_CD7, PRD_CD8, PRD_CD9, PRD_CD10
from TBL_ORD_PROD )
SELECT ORD_NO, LV, PRD_CD
FROM ( SELECT ORD_NO, LV
, CASE LV WHEN 1 THEN PRD_CD
WHEN 2 THEN PRD_CD2
WHEN 3 THEN PRD_CD3
WHEN 4 THEN PRD_CD4
WHEN 5 THEN PRD_CD5
WHEN 6 THEN PRD_CD6
WHEN 7 THEN PRD_CD7
WHEN 8 THEN PRD_CD8
WHEN 9 THEN PRD_CD9
WHEN 10 THEN PRD_CD10
END AS PRD_CD
FROM WH_ORD_PROD
, (SELECT LEVEL LV FROM dual CONNECT BY LEVEL <=10)
)
WHERE PRD_CD IS NOT NULL
-----------------------------------------------------------------------------------------
CREATE TABLE TBL_ORD_PROD -- 기존 주문-제품관계 테이블
( ORD_NO VARCHAR(10) PK -- 주문번호
, PROD_CD1 VARCHAR(5) --- 제품종류1
, PROD_CD2 VARCHAR(5) --- 제품종류2
, PROD_CD3 VARCHAR(5) --- 제품종류3
, PROD_CD4 VARCHAR(5) --- 제품종류4
, PROD_CD5 VARCHAR(5) --- 제품종류5
, PROD_CD6 VARCHAR(5) --- 제품종류6
, PROD_CD7 VARCHAR(5) --- 제품종류7
, PROD_CD8 VARCHAR(5) --- 제품종류8
, PROD_CD9 VARCHAR(5) --- 제품종류9
, PROD_CD10 VARCHAR(5) --- 제품종류10
)
<기존형태>
ORD_NO PROD_CD1 PROD_CD2 PROD_CD3 PROD_CD4 PROD_CD5 PROD_CD6 PROD_CD7 PROD_CD8 PROD_CD9 PROD_CD10
100 AA100 AA110 AA130 AA129
120 BC130
210 AS100 AS120 AS190 AS270 AS276
<신규 원하는형태>
ORD_NO LV PROD_CD
100 1 AA100
100 2 AA110
100 3 AA130
100 4 AA129
120 1 BC130
210 1 AS100
210 2 AS120
210 3 AS190
210 4 AS270
210 5 AS276
1. 테이블에 직접 Connect by 를 걸어 비효율이 발생한 것도 아니고
2. With 문을 굳이 쓸 이유가 없는데, 그렇다고 이것 때문에 느리다고 할 수도 없는 상황인데요?
3. 인라인뷰 밖에서 IS NOT NULL 을 주고 있는데, 이것 때문에 느릴지는 확인이 필요합니다.
아닐 것 같긴 하지만 인라인뷰 안쪽에서 조인 조건을 주는 방법으로 확인해 보세요.
데이터가 워낙 많아서 느린건 아닌가요?
아니면 실제 쿼리를 보여주신게 아닐 수도 있구요.
다음은 행복제하는 다양한 방법을 소개하고 있으니 여러가지 방법으로 비교해 보세요.
http://gurubee.net/article/55635
비교 결과 공유 부탁드려요.
SELECT ord_no
, lv
, DECODE(lv, 1, prd_cd1
, 2, prd_cd2
, 3, prd_cd3
, 4, prd_cd4
, 5, prd_cd5
, 6, prd_cd6
, 7, prd_cd7
, 8, prd_cd8
, 9, prd_cd9
, 10, prd_cd10
) prd_cd
FROM tbl_ord_prod
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 10)
WHERE lv <= DECODE(null, prd_cd1, 0
, prd_cd2, 1
, prd_cd3, 2
, prd_cd4, 3
, prd_cd5, 4
, prd_cd6, 5
, prd_cd7, 6
, prd_cd8, 7
, prd_cd9, 8
, prd_cd10, 9
, 10)
;
SELECT * FROM (SELECT ord_no , prd_cd1 , prd_cd2 , prd_cd3 , prd_cd4 , prd_cd5 , prd_cd6 , prd_cd7 , prd_cd8 , prd_cd9 , prd_cd10 FROM tbl_ord_prod ) UNPIVOT ( prd_cd FOR lv IN ( prd_cd1 AS 1 , prd_cd2 AS 2 , prd_cd3 AS 3 , prd_cd4 AS 4 , prd_cd5 AS 5 , prd_cd6 AS 6 , prd_cd7 AS 7 , prd_cd8 AS 8 , prd_cd9 AS 9 , prd_cd10 AS 10 ) ) ;
마농님께 감사드립니다.
마농님께서 가이드해주신 2가지 방법으로 테스트를 실시했습니다.
1. LEVEL을 사용했을때 처리시간이 700 sec을 넘어도 안끝나서 job을 중단 시켰습니다.
2. UNPIVOT을 사용했을때 처리시간이 138 sec
그래서 적용은 UNPIVOT으로 하였습니다.
참고로 전체 레코드 건수는 111,456건, UNPIVOT개별 건수는 111,478건이었습니다.
마농님께 다시한번 감사의 말씀을 드립니다.
) 버튼을 클릭하여 작성 하시면 됩니다.