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

CONNECT BY LEVEL

by 미친꼬부기 [Oracle Tuning] OVER PARTITION BY [2020.08.10 14:27:07]

안녕하세요

고수님들의 도움이 필요합니다

추출하려는 값들 아래와 같습니다. 그리고 이에 따른 쿼리는 바로 밑에 있습니다.

이런식으로 데이터 조회시 IN 절에 데이터 값이 많아지면 수행속도가 엄청 느려집니다. 몇십분에서 몇시간정도....

아래 쿼리를 효과적으로 튜닝 또는 어떤식으로 변경해야 할지 고수분들의 솔류션이 필요합니다.

데이터값					쿼리 결과값
	A	B	C	D		A	B	C	TO_KEY
1	A	A	A	2	1	A	A	A	20200810_1
2	B	B	B	2	2	A	A	A	20200810_2
3	C	C	C	2	3	B	B	B	20200810_1
4	D	D	D	2	4	B	B	B	20200810_2
5	E	E	E	3	5	C	C	C	20200810_1
6	F	F	F	4	6	C	C	C	20200810_2
7	G	G	G	5	7	D	D	D	20200810_1
8	H	H	H	1	8	D	D	D	20200810_2
9	I	I	I	2	9	E	E	E	20200810_1
					10	E	E	E	20200810_2
					11	E	E	E	20200810_3
					12	F	F	F	20200810_1
					13	F	F	F	20200810_2
					14	F	F	F	20200810_3
					15	F	F	F	20200810_4
					16	G	G	G	20200810_1
					17	G	G	G	20200810_2
					18	G	G	G	20200810_3
					19	G	G	G	20200810_4
					20	G	G	G	20200810_5
					21	H	H	H	20200810_1
					22	I	I	I	20200810_1
					23	I	I	I	20200810_2

WITH DATA_T AS
(
     SELECT   A
            , B
            , C
            , D AS ROW_CNT            
      FROM TB_20200810_IMSI WHERE A IN ('A','B','C','D','E','F','G','H','I')
)
SELECT A, B, C, TO_CHAR(SYSDATE, 'YYYYMMDD') || '_' || ROW_NUMBER() OVER(PARTITION BY A, B, C ORDER BY A, B, C) AS TO_KEY 
FROM DATA_T
CONNECT BY LEVEL <= ROW_CNT
GROUP BY A, B, C, LEVEL
ORDER BY A, LEVEL

by 우리집아찌 [2020.08.11 09:38:13]

-- 행복제를 한뒤  A, B, C, LEVEL 하는것이면 많은데이터가 필요없어보입니다.
-- 다중행의 행복제는 엄청난 양의 데이터를 발생시킵니다. 행복제까지만 해보시고 COUNT 해보세요.

CONNECT BY LEVEL <= ROW_CNT
GROUP BY A, B, C, LEVEL

by 우리집아찌 [2020.08.11 10:01:23]

TB_20200810_IMSI 데이터 샘플 좀 올려주세요.

by 미친꼬부기 [2020.08.11 13:34:42]

내용에 데이터 샘플 추가했습니다.

by 우리집아찌 [2020.08.11 14:01:21]

WITH TB_20200810_IMSI AS
(
SELECT 'A' A , 'A' B ,'A'  C ,2 D FROM DUAL UNION ALL
SELECT 'B' , 'B' ,'B' ,2 D FROM DUAL UNION ALL
SELECT 'C' , 'C' ,'C' ,2 D FROM DUAL UNION ALL
SELECT 'D' , 'D' ,'D' ,2 D FROM DUAL UNION ALL
SELECT 'E' , 'E' ,'E' ,3 D FROM DUAL UNION ALL
SELECT 'F' , 'F' ,'F' ,4 D FROM DUAL UNION ALL
SELECT 'G' , 'G' ,'G' ,5 D FROM DUAL UNION ALL
SELECT 'H' , 'H' ,'H' ,1 D FROM DUAL UNION ALL
SELECT 'I' , 'I' ,'J' ,2 D FROM DUAL 
)
SELECT A.A , A.B , A.C , TO_CHAR(SYSDATE, 'YYYYMMDD') || '_' || LV TO_KEY 
     , A.D , B.LV 
  FROM TB_20200810_IMSI A
     , ( SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= (SELECT MAX(D) FROM TB_20200810_IMSI )  ) B
   WHERE A.D >= B.LV   
 ORDER BY A.A , B.LV

by 미친꼬부기 [2020.08.12 16:47:37]

보내주신 쿼리를 이용해서 적용하려고 했는데 회사 DBA 이거 카타시안 곱이라고 잘못 실수하면 데이터가 엄청많아진다고 하던데요?

by 우리집아찌 [2020.08.12 16:58:46]

네 카타시안곱 또는 CROSS JOIN 을 이용한 SQL 입니다.

실수? 가 먼지는 모르지만 기존 SQL보다는 훨신 더 좋은 성능이 나올겁니다.

by pajama [2020.08.12 17:54:11]

recursive 쿼리로 한번 해봤습니다. 컬럼 뒤에 1을 추가했습니다.

WITH TB_20200810_IMSI (A, B, C, D, E) AS
(
SELECT * FROM (
SELECT 'A' A, 'A' B, 'A' C, 2 D, 1 E FROM DUAL UNION ALL
SELECT 'B' , 'B' ,'B' ,2, 1 FROM DUAL UNION ALL
SELECT 'C' , 'C' ,'C' ,2, 1 FROM DUAL UNION ALL
SELECT 'D' , 'D' ,'D' ,2, 1 FROM DUAL UNION ALL
SELECT 'E' , 'E' ,'E' ,3, 1 FROM DUAL UNION ALL
SELECT 'F' , 'F' ,'F' ,4, 1 FROM DUAL UNION ALL
SELECT 'G' , 'G' ,'G' ,5, 1 FROM DUAL UNION ALL
SELECT 'H' , 'H' ,'H' ,1, 1 FROM DUAL UNION ALL
SELECT 'I' , 'I' ,'I' ,2, 1 FROM DUAL ) t
UNION ALL
SELECT  A, B, C, D, E + 1 FROM TB_20200810_IMSI WHERE E < D
)
SELECT A, B, C, TO_CHAR(SYSDATE, 'YYYYMMDD') || '_' || E TO_KEY 
FROM TB_20200810_IMSI
ORDER BY A, B, C, E

by 마농 [2020.08.14 13:13:27]

작성하신 쿼리가 카테시안곱이 발생하는 잘못 작성된 쿼리이고
우리집아찌님께서 답변주신 쿼리가 카테시안곱을 방지하는 정상 쿼리입니다.
Connect By LEVEL <= n 를 이용한 행복제 방식은 dual 과 같은 1건의 자료에만 적용하는 방법입니다.
여러건의 집합(테이블)에 직접 적용하면 안됩니다.
http://gurubee.net/article/55635

댓글등록

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

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

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

데이터베이스 질문과 답변