데이터베이스 질문과 답변
이미지 캡쳐 한걸 어떻게 등록 할지 몰라서 다시 등록 합니다.
WITH TAB_OLD AS (
SELECT /*+ FULL(A) PARALLEL(16)*/
ITEM_DV_NM3 AS A1 ,
REGEXP_SUBSTR(BB1,'[^ ]+',1,2) AS BB1, BB2, BB3, BB4, BB5
FROM AAA_OLD A
WHERE 1=1
AND REGEXP_SUBSTR(REGEXP_SUBSTR(BB1,'[^ ]+',1,1),'[^_]+',1,2) = 'EDM1'
AND REGEXP_SUBSTR(REGEXP_SUBSTR(BB1,'[^ ]+',1,1),'[^_]+',1,3)||'_'||REGEXP_SUBSTR(REGEXP_SUBSTR(BB1,'[^ ]+',1,1),'[^_]+',1,4) = 'R_89'
)
, TAB_NEW AS (
SELECT /*+ FULL(A) PARALLEL(16)*/
ITEM_DV_NM3 AS A1 ,
REGEXP_SUBSTR(BB1,'[^ ]+',1,2) AS BB1, BB2, BB3, BB4, BB5
FROM AAA_NEW A
WHERE 1=1
AND SUBSTR(REGEXP_SUBSTR(REGEXP_SUBSTR(BB1,'[^ ]+',1,1),'[^_]+',1,4),1,4) = 'EDM1'
AND REGEXP_SUBSTR(REGEXP_SUBSTR(BB1,'[^ ]+',1,1),'[^_]+',1,1)||'_'||REGEXP_SUBSTR(REGEXP_SUBSTR(BB1,'[^ ]+',1,1),'[^_]+',1,2) = 'R_89'
)
SELECT PK, GB
, DECODE(GROUPING(GB), 1, CASE WHEN COUNT(DISTINCT BB1) =2 OR COUNT(*) =1 THEN 'FALSE' ELSE 'TRUE' END, BB1) AS BB1
, DECODE(GROUPING(GB), 1, CASE WHEN COUNT(DISTINCT BB2) =2 OR COUNT(*) =1 THEN 'FALSE' ELSE 'TRUE' END, BB2) AS BB2
, DECODE(GROUPING(GB), 1, CASE WHEN COUNT(DISTINCT BB3) =2 OR COUNT(*) =1 THEN 'FALSE' ELSE 'TRUE' END, BB3) AS BB3
, DECODE(GROUPING(GB), 1, CASE WHEN COUNT(DISTINCT BB4) =2 OR COUNT(*) =1 THEN 'FALSE' ELSE 'TRUE' END, BB4) AS BB4
, DECODE(GROUPING(GB), 1, CASE WHEN COUNT(DISTINCT BB5) =2 OR COUNT(*) =1 THEN 'FALSE' ELSE 'TRUE' END, BB5) AS BB5
, DECODE(GROUPING(GB), 1,
CASE WHEN COUNT(DISTINCT BB1) =2 OR COUNT(*) =1 THEN 1 ELSE 0 END
+ CASE WHEN COUNT(DISTINCT BB2) =2 OR COUNT(*) =1 THEN 1 ELSE 0 END
+ CASE WHEN COUNT(DISTINCT BB3) =2 OR COUNT(*) =1 THEN 1 ELSE 0 END
+ CASE WHEN COUNT(DISTINCT BB4) =2 OR COUNT(*) =1 THEN 1 ELSE 0 END
+ CASE WHEN COUNT(DISTINCT BB5) =2 OR COUNT(*) =1 THEN 1 ELSE 0 END
) GAP
FROM (
-------------------------------------------------------------------------------------------------------
SELECT DENSE_RANK() OVER (ORDER BY BB1, BB3, BB4, BB5) PK, GB, BB1, BB2, BB3, BB4, BB5
FROM (
SELECT A1 GB, BB1 *1 AS BB1, BB2, BB3, BB4, BB5 FROM TAB_OLD
UNION ALL
SELECT A1 GB, BB1 *1 AS BB1, BB2, BB3, BB4, BB5 FROM TAB_NEW
)
----------------------------------------------------------------------------------------------------------
)
WHERE 1=1
GROUP BY PK, ROLLUP((GB, BB1, BB2, BB3, BB4, BB5))
ORDER BY PK, GB NULLS LAST
처음 부터 설명을 좀더 상세히 했어야 하는 건데 이제야 상세 설명을 드립니다.
켑쳐 이미지에 있는 것 처럼 OLD T1 ==> NEW T2로 옮길때 CTAS 나 EXP / IMP로 옮기는 게 아니고
OLD에서 T1을 만들기 위해 필요한 AAA, BBB, CCC, DDD가 필요하고
NEW에서 T2을 만들기 위해 필요한 AAA1, BBB2, CCC3, DDD4가 필요 합니다.
제가 요청 드렸던 SQL은 T1과 T2가 값이 다른 ROW 와 COLUMND을 찾고 차이가 나는 이유를 알기 위해
OLD AAA, BBB, CCC, DDD, NEW AAA1, BBB2, CCC3, DDD4 을 확인 하는 것 입니다.
NEW T2는 제가 만들고 AAA1, BBB2, CCC3, DDD4는 타팀에서 만들기 때문에 원인 규명이 필요 합니다.
(NEW T2를 만들기 위한 SQL은 제가 작성 합니다)
그런데 OLD T1 와 NEW T2 차이를 찾는데 그 동안은 최대 250만건 컬럼은 30개 까지는 약 15분 정도가 걸려서 크게(?) 문제가 없이 틀린 걸 찾고 원인을 규명 하였으나
컬럼이 최대 200개 까지 되는 컬럼들을 작업하니 차이만 찾는데 많은 시간이 걸립니다.
그래서 점선 있는 부분을 임시 테이블로 만들어서 (약 4분 소요)도 해 보았으나 역시 오래 걸리는걸 보면 그 보다는 위의 DECODE 들어 가는 계산 부분이 오래 걸리는 것 같습니다.
(SQL 툴이 10분 이상 수행되면 세션이 저절로 끊어 지는 보안 정책이 적용 되어있어 임시 프로그램에 위 SQL을 넣어 오차를 찾아 보니 40분 ~ 50분 정도 걸림.컬럼은 200개 정도 20만건 정도)
오차를 찾으면 그 다음은 원인을 찾아야 하는데 오차를 찾는데만 이렇게 걸리니 원인 규명에 시간이 너무 많이 걸리네요 혹시 좀더 속도를 올릴 방법은 없을 까요 ?
찾고자 하는 결과 FORMAT은 위 SQL이 딱 인데 시간이 넘 오래 걸리네요 ~~~
확인 부탁드립니다.
WITH tab_old AS
(
SELECT 'OLD' a1, 'A001' b1, 'S001' b2, '20231001' b3, 100000 b4, null b5 FROM dual
UNION ALL SELECT 'OLD', 'B001', 'S002', '20231002', 10000, 100 FROM dual
UNION ALL SELECT 'OLD', 'C001', 'S002', '20231001', 45000, 450 FROM dual
UNION ALL SELECT 'OLD', 'D001', 'S002', '20231001', null, 450 FROM dual
UNION ALL SELECT 'OLD', 'E001', 'S002', '20231001', null, 450 FROM dual
)
, tab_new AS
(
SELECT 'NEW' a1, 'A001' b1, 'S002' b2, '20231001' b3, 100000 b4, null b5 FROM dual
UNION ALL SELECT 'NEW', 'B001', 'S003', '20231002', 20000, 200 FROM dual
UNION ALL SELECT 'NEW', 'C001', 'S002', '20231001', 46000, 460 FROM dual
UNION ALL SELECT 'NEW', 'D001', 'S002', '20231001', null, null FROM dual
UNION ALL SELECT 'NEW', 'F001', 'S002', '20231001', null, 450 FROM dual
)
SELECT *
FROM (SELECT pk, c, o, n
, ''||CASE WHEN o = n OR (o IS NULL AND n IS NULL) THEN 0 ELSE 1 END f
, SUM(CASE WHEN o = n OR (o IS NULL AND n IS NULL) THEN 0 ELSE 1 END)
OVER(PARTITION BY pk) gap
FROM (SELECT gb
, DENSE_RANK() OVER(ORDER BY b1, b3) pk
, b1, b2, b3
, b4||'' b4
, b5||'' b5
FROM (SELECT a1 gb, b1, b2, b3, b4, b5 FROM tab_old
UNION ALL
SELECT a1 gb, b1, b2, b3, b4, b5 FROM tab_new
)
)
UNPIVOT INCLUDE NULLS (v FOR c IN (b1, b2, b3, b4, b5))
PIVOT (MIN(v) FOR gb IN ('OLD' o, 'NEW' n))
)
UNPIVOT INCLUDE NULLS (v FOR gb IN (o AS 'OLD', n AS 'NEW', f AS ''))
PIVOT (MIN(v) FOR c IN ('B1' b1, 'B2' b2, 'B3' b3, 'B4' b4, 'B5' b5))
ORDER BY pk, gb DESC NULLS LAST
;
다른 방식으로 풀어 봤습니다.
성능은 장담 못합니다.
일단 DECODE 및 COUNT 사용을 줄이는 형태로 바꿔 봤습니다.
쿼리 개선
CASE 대신 DECODE 를 사용하면 더 간결해 지네요.
DECODE 가 NULL 값 비교도 가능하다는 사실을 잊고 있엇네요.
- 변경전 : CASE WHEN o = n OR (o IS NULL AND n IS NULL) THEN 0 ELSE 1 END
- 변경후 : DECODE(o, n, 0, 1)
WITH tab_old AS
(
SELECT 'OLD' a1, 'A001' b1, 'S001' b2, '20231001' b3, 100000 b4, null b5 FROM dual
UNION ALL SELECT 'OLD', 'B001', 'S002', '20231002', 10000, 100 FROM dual
UNION ALL SELECT 'OLD', 'C001', 'S002', '20231001', 45000, 450 FROM dual
UNION ALL SELECT 'OLD', 'D001', 'S002', '20231001', null, 450 FROM dual
UNION ALL SELECT 'OLD', 'E001', 'S002', '20231001', null, 450 FROM dual
)
, tab_new AS
(
SELECT 'NEW' a1, 'A001' b1, 'S002' b2, '20231001' b3, 100000 b4, null b5 FROM dual
UNION ALL SELECT 'NEW', 'B001', 'S003', '20231002', 20000, 200 FROM dual
UNION ALL SELECT 'NEW', 'C001', 'S002', '20231001', 46000, 460 FROM dual
UNION ALL SELECT 'NEW', 'D001', 'S002', '20231001', null, null FROM dual
UNION ALL SELECT 'NEW', 'F001', 'S002', '20231001', null, 450 FROM dual
)
SELECT *
FROM (SELECT pk, c, o, n
, ''||DECODE(o, n, 0, 1) f
, SUM(DECODE(o, n, 0, 1)) OVER(PARTITION BY pk) gap
FROM (SELECT gb
, DENSE_RANK() OVER(ORDER BY b1, b3) pk
, b1, b2, b3
, b4||'' b4
, b5||'' b5
FROM (SELECT a1 gb, b1, b2, b3, b4, b5 FROM tab_old
UNION ALL
SELECT a1 gb, b1, b2, b3, b4, b5 FROM tab_new
)
)
UNPIVOT INCLUDE NULLS (v FOR c IN (b1, b2, b3, b4, b5))
PIVOT (MIN(v) FOR gb IN ('OLD' o, 'NEW' n))
)
UNPIVOT INCLUDE NULLS (v FOR gb IN (o AS 'OLD', n AS 'NEW', f AS ''))
PIVOT (MIN(v) FOR c IN ('B1' b1, 'B2' b2, 'B3' b3, 'B4' b4, 'B5' b5))
ORDER BY pk, gb DESC NULLS LAST
;
감사하고 흐뭇한 마음으로 가져가서 테스트 하고 후기 올리겠습니다.
다시 한번 감사 합니다. ^^
프로젝트 오픈이 얼마 남지 않아 계속 된 야근속에 오랜만에 사이트를 접속 합니다.
테스트를 해 본 결과 ( 임시 프로그램에 위 SQL을 넣어 오차를 찾아 보니 40분 ~ 50분 )
임시 프로그램에 SQL을 심어서 돌려보니 약 17분정도 걸렸습니다.
매우 유 의미한 정도로 속도가 단축되어 일단 통으로 외워야 할 듯 하네요.
또한, 테스트 중 가로 데이터를 세로로 변경 한 후에 마지막 다시 가로로 변경 하는 듯 한데 걍 세로로만 데이터를 DB에 넣어 활을을 해도 좋은 듯 합니다.
지금은 오픈 마무리로 다양한 테스트를 할 시간이 부족하지만 분명 상세하게 숙지가 필요하다는 생각을 했네요
감사합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.