재귀 & 계층 쿼리에서 다른 레벨간 데이터 중복을 막을 방법이 있을까요? 0 14 252

by 아리시마 [SQL Query] SQL SQL 튜닝 [2021.11.24 13:32:19]


> 질문~

재귀 & 계층 쿼리에서 다른 레벨 간 데이터 중복을 막을 방법이 있을까요?

2level에서 추출된 데이터가 3level에서는 안나오게 하고 싶습니다.

데이터가 늘어날수록 시간이 기하급수적으로 늘어나서요.

 

> 하려는 일 

특정 검색어로 검색된 연관 데이터들 전체를 순서 상관없이 뽑는 게 목적입니다..

그래서 중복을 전부 제거하면서 뽑으려고 합니다.

접근 방법이 잘못됐다면 다른 방법은 어떤 게 있을까요?

 

일단 CUBRID를 사용했습니다.

 

> 사용해본 방법

01. 재귀 쿼리로 뽑는 방법. (CTE 재귀)

데이터 100열정도 실행시간 0.14초

WITH TT AS (
    SELECT 'A'  AS AC , 'B' BC, '1' AS RN FROM db_root
    UNION ALL
    SELECT 'B' , 'C', '2' FROM db_root
    UNION ALL
    SELECT 'B' , 'D', '3' FROM db_root
    UNION ALL
    SELECT 'C' , 'D', '4' FROM db_root
    UNION ALL
    SELECT 'D' , 'C', '5' FROM db_root
    UNION ALL
    SELECT 'D' , 'B', '6' FROM db_root
    UNION ALL
    SELECT 'D' , 'E', '7' FROM db_root
    UNION ALL
    SELECT 'E' , 'B', '8' FROM db_root
    UNION ALL
    SELECT 'E' , 'F', '9' FROM db_root
    UNION ALL
    SELECT 'F' , 'B', '10' FROM db_root
    UNION ALL
    SELECT 'F' , 'A', '11' FROM db_root
), 
TM AS (
        SELECT *, 0  AS REQ_CNT FROM TT WHERE AC = 'A'
        UNION ALL
        
        SELECT TT.AC, TT.BC, TT.RN,  TSUB.REQ_CNT + 1   
        FROM TM TSUB INNER JOIN TT  ON TSUB.BC = TT.AC
        
        WHERE TSUB.REQ_CNT <= 10
        -- 순환 참조 방지
        AND TSUB.BC != 'A'
        GROUP BY TT.RN
)
SELECT * FROM TM GROUP BY AC, BC

 

02. START WITH  Connect by Prior NO CYCLE을 이용한 계층 쿼리

데이터 100열정도 실행시간 지정된 레벨에 / 데이터에 따라서 기하급수적으로 증가.

WITH TT AS  (
    SELECT 'A'  AS AC , 'B' BC, '1' AS RN FROM db_root
    UNION ALL
    SELECT 'B' , 'C', '2' FROM db_root
    UNION ALL
    SELECT 'B' , 'D', '3' FROM db_root
    UNION ALL
    SELECT 'C' , 'D', '4' FROM db_root
    UNION ALL
    SELECT 'D' , 'C', '5' FROM db_root
    UNION ALL
    SELECT 'D' , 'B', '6' FROM db_root
    UNION ALL
    SELECT 'D' , 'E', '7' FROM db_root
    UNION ALL
    SELECT 'E' , 'B', '8' FROM db_root
    UNION ALL
    SELECT 'E' , 'F', '9' FROM db_root
    UNION ALL
    SELECT 'F' , 'B', '10' FROM db_root
    UNION ALL
    SELECT 'F' , 'A', '11' FROM db_root
)
SELECT LEVEL, t.* FROM TT AS t
WHERE LEVEL < 5
START WITH AC = 'A' CONNECT BY NOCYCLE PRIOR BC = AC
GROUP BY AC, BC 

 

 

03. 외부를 GROUP BY로 감싸서 계속 중복 제거하면서 GROUP BY를 여러개 사용하는 방법..

데이터 100열정도 실행시간 0.1초


WITH TM AS  (
	SELECT 'A'  AS AC , 'B' BC, '1' AS RN FROM db_root
	UNION ALL 
	SELECT 'B' , 'C', '2' FROM db_root
	UNION ALL 
	SELECT 'B' , 'D', '3' FROM db_root
	UNION ALL 
	SELECT 'C' , 'D', '4' FROM db_root
	UNION ALL 
	SELECT 'D' , 'C', '5' FROM db_root
	UNION ALL 
	SELECT 'D' , 'B', '6' FROM db_root
	UNION ALL 
	SELECT 'D' , 'E', '7' FROM db_root
	UNION ALL 
	SELECT 'E' , 'B', '8' FROM db_root
	UNION ALL 
	SELECT 'E' , 'F', '9' FROM db_root
	UNION ALL 
	SELECT 'F' , 'B', '10' FROM db_root
	UNION ALL 
	SELECT 'F' , 'A', '11' FROM db_root
)

SELECT * FROM TM WHERE AC IN (
SELECT BC FROM TM WHERE AC IN (
SELECT BC FROM TM WHERE AC IN (
SELECT BC FROM tm START WITH AC = 'A' CONNECT BY NOCYCLE PRIOR BC = AC AND LEVEL <= 2
	UNION ALL  SELECT  'A' FROM DB_ROOT
) GROUP BY AC, BC
) GROUP BY AC, BC
) GROUP BY AC, BC

 

일단 3가지 방법을 해봤는데

속도 3 > 1 >>> 2 

1번

2번보다 속도는 많이 빠르고 조회도 정상적으로 됩니다.

다른 레벨에서 똑같은 데이터가 나오지만 2번보다는 나오는 데이터 양이 적습니다.

데이터가 많아지면 결국 부하가 생기더군요

 

실제 조회 데이터가 2번보다는 GROUP BY를 계속 하면서 재귀가 돌아서 

데이터가 조금씩 늘어나서 부하가 적은편 입니다.

 

2번

조회 자체가 너무 오래 걸리고 다른 레벨에서 똑같은 데이터가 제곱으로 늘어나는 느낌이고...

 

3번

중복 제거는 잘 되지만 좀 없어 보이고 레벨 조절을

쿼리문이 계속 늘어나야 한다는 단점이 있습니다.

 

실 데이터는 10만~100만 정도라서.. 최대한 최적화를 하고 싶은데

따로 시도할만한 방법이나 도움 될 만한 정보가 있을까요?

by 모래가흙흙 [2021.11.24 14:12:06]

일단 (2) 은 이게 맞는거 아닌가요?

NOCYCLE BC != AC    =>   NOCYCLE prior BC = AC


by 아리시마 [2021.11.24 14:25:46]

아 그러네요 실수했네요. 다른 쿼리 만들고 질문하려고 예시용으로 만들다가 실수했네요.. 

애초에 실행 시간도 잘못됐군요. 수정 해둘깨요 

 


by 마농 [2021.11.24 15:04:03]

쿼리가 다른데요? 이상하고.
2번 쿼리는 A 로 시작하는데?
1번 쿼리는 시작조건이 없네요?
3번 쿼리는 PRIOR 가 없네요?
2번 쿼리가 가장 깔끔하고 빠를 것 같은데...


by 몸에좋은SG [2021.11.24 15:48:14]

에고 죄송합니다.  잘못 작성한 부분이 있었네요... 다시 수정할깨요.

 

저도 처음 작성한 쿼리가 2번쿼리인데요.. 이게 데이터가 많아지니까

다른레벨에서 중복된 로우가 계속나와서 하위 레벨로 내려갈수록 조회시간이 엄청 오래걸리더라구요..

 

제가 잘못 작성한 부분이 있는걸까요?


by 모래가흙흙 [2021.11.24 16:29:36]

AC컬럼을 선두로하는 인덱스가 있나요?

인덱스 테스트가 가능하다면,  AC  컬럼 인덱스를 추가하여 테스트해보셔용


by 몸에좋은SG [2021.11.24 16:49:15]

실제 테이블엔 AC BC RN까지 3개가 PK로 되어있어서

3개가 인덱스라서 RN이 다른 AC BC가 존재합니다.

그래서 위의 쿼리만큼 시간이 걸리지는 않는데 

데이터가 많아지면 결국 같은 문제가 생기긴 하더라구요..

 

30만행 데이터를 만든다음 AC, BC, RN을 PK로 설정하고 돌려본결과 

1번쿼리는 0.3 ~ 0.5

2번쿼리 0.04초

3번쿼리는 1.7 ~ 2.0로 결과가 나오네요.

 

지금은 A B C D E F 이렇게 6종류의 데이터만 있지만

실제론 데이터를 AC, BC로 GROUP BY 할경우 약 10만건정도라

 

실제 테이블 10만건에서 

1번쿼리 5D 1.7S / 6D 2.2S / 7D 3.5S / 8D = 6.7S / 9D = 13S  / 10D = 13S

2번쿼리 사망

3번쿼리 1번과 거의 동일 

한 결과가 나오더라구요..

 

5~ 6뎁스부턴 조회되는 결과가 중복되는경우가 대부분이라 

다른 레벨에서 이미 나온 ROW에 해당하는 데이터는 다음 레벨 조회시 안나오게 하는게 가능하다면 

속도를 더 개선할 수 있을것 같아서 시도해보고 있는데 어렵네요 ㅜㅜ

 

 


by 마농 [2021.11.24 17:21:21]

1번 쿼리의 순환 참조 방지 조건은 틀린 듯 하네요.
Oracle 에서는 다음과 같이 사용하는데
CUBRID 에서도 동일한 문법인지는 모르겠네요.

CYCLE bc SET iscycle TO "1" DEFAULT "0"
SELECT * FROM tm;

끝까지 탐색한다면 느릴 수 밖에 없을 듯 합니다.
탐색 레벨 한도를 정해야 하지 않을까요?
2번 쿼리는 레벨 한도 조건을 WHERE 절이 아닌 CONNECT BY 절에 줘보세요.


by 몸에좋은SG [2021.11.24 17:51:44]

1번 쿼리의 순환 참조 방지 조건이 저렇게 들어간 이유가

본인이 다시 나오는 데이터는 필요가 없어서 넣었던거라 

지금 쿼리상으로는 이상하네요...

 

탐색 레벨 한도는 정할예정이구요..

 

2번 쿼리는 레벨 한도 조건을 CONNECT BY 절에 주니까 속도차이가 좀 많이나긴 하는데 

1번 쿼리가 여전히 더 빠르네요.. 

 

CYCLE bc SET iscycle TO "1" DEFAULT "0" 이 구문은 없지만..

CONNECT_BY_ISCYCLE 로 사이클에 대한 부분을 알 수는 있는데

다른 레벨에서 같은 데이터를 보고있으면 그건 못잡더라구요...

 

2LEVEL에서 조회됬던 ROW를 3LEVEL에서 안나오게 하고 싶은데 방법을 모르겠네요 후으...

감사합니다. 


by 마농 [2021.11.25 08:06:41]

1번쿼리 조건은
- A 가 아닌 것을 찾는 조건이지 순환 참조 방지 조건이 아니죠.
- A 만 순환 되는 건 아니니까요.
샘플 자료가
- A ~ F 까지 6개만 가지고 30만건의 샘플자료를 만들었다면? 중복자료가 다수 존재하겠네요.
- 실제 중복자료가 존재한다면? 중복 재거 후에 계층 쿼리를 돌려야 할 것 같습니다.


by 몸에좋은SG [2021.11.25 09:17:36]

실제데이터는 

중복자료를 제거후에 계층쿼리를 돌리고 있습니다. 중복제거한 데이터도 몇만건이 나오는게 문제긴해서..

 

1번 쿼리 조건은 A가 아닌것을 찾는조건이 맞네요. 

한번 참조한 데이터를 기록했다가 안찾으면 좋을탠데 어렵네요..

 

 


by 마농 [2021.11.25 13:43:24]

1. A 와 연관된 걸 찾는 거라면 기존 쿼리에도 문제가 있네요.
- ac 에서만 A 를 찾는다면 ac 에는 A 가 없고 bc 에만 A 가 있는 걸 못찾게 되구요.
- 또한 ac -> bc 방향으로만 찾으면 안되고 bc -> ac 방향도 함께 찾아야 맞습니다.
2. 중복 제거 및 직렬화 처리후 게층 전개
- (C > D) (D > C) 이런거는 (C > D) 하나만 남기고
- (E > B) 이런거는 (B > E) 형태로 방향을 바꾸고.
- 전체 자료를 작은 값에서 큰값으로 한방향으로만 흐르도록 직렬화하여 중복을 제거한뒤
- 조건에 따라 순방향 전개와 역방향 전개를 하고 UNION 하여 결과 도출
 

-- Oracle 에서 Test --
WITH tt AS
(
SELECT 'A' ac, 'B' bc, 1 rn   FROM dual
UNION ALL SELECT 'B', 'C',  2 FROM dual
UNION ALL SELECT 'B', 'D',  3 FROM dual
UNION ALL SELECT 'C', 'D',  4 FROM dual
UNION ALL SELECT 'D', 'C',  5 FROM dual
UNION ALL SELECT 'D', 'B',  6 FROM dual
UNION ALL SELECT 'D', 'E',  7 FROM dual
UNION ALL SELECT 'E', 'B',  8 FROM dual
UNION ALL SELECT 'E', 'F',  9 FROM dual
UNION ALL SELECT 'F', 'B', 10 FROM dual
UNION ALL SELECT 'F', 'A', 11 FROM dual
)
, t1 AS
(
SELECT DISTINCT 
       LEAST   (ac, bc) ac
     , GREATEST(ac, bc) bc
  FROM tt
)
SELECT bc
  FROM t1
 START WITH ac = 'C'
 CONNECT BY PRIOR bc = ac
 UNION
SELECT ac
  FROM t1
 START WITH bc = 'C'
 CONNECT BY PRIOR ac = bc
;
-- Oracle 에서 Test --
WITH tt AS
(
SELECT 'A' ac, 'B' bc, 1 rn   FROM dual
UNION ALL SELECT 'B', 'C',  2 FROM dual
UNION ALL SELECT 'B', 'D',  3 FROM dual
UNION ALL SELECT 'C', 'D',  4 FROM dual
UNION ALL SELECT 'D', 'C',  5 FROM dual
UNION ALL SELECT 'D', 'B',  6 FROM dual
UNION ALL SELECT 'D', 'E',  7 FROM dual
UNION ALL SELECT 'E', 'B',  8 FROM dual
UNION ALL SELECT 'E', 'F',  9 FROM dual
UNION ALL SELECT 'F', 'B', 10 FROM dual
UNION ALL SELECT 'F', 'A', 11 FROM dual
)
, t1 AS
(
SELECT DISTINCT
       LEAST   (ac, bc) ac
     , GREATEST(ac, bc) bc
  FROM tt
)
, t2(ac, bc) AS
(
SELECT ac, bc
  FROM t1
 WHERE ac = 'C'
 UNION ALL
SELECT b.ac, b.bc
  FROM t2 a
 INNER JOIN t1 b
    ON a.bc = b.ac
)
, t3(ac, bc) AS
(
SELECT ac, bc
  FROM t1
 WHERE bc = 'C'
 UNION ALL
SELECT b.ac, b.bc
  FROM t2 a
 INNER JOIN t1 b
    ON a.ac = b.bc
)
SELECT bc FROM t2
 UNION
SELECT ac FROM t3
;

 


by 몸에좋은SG [2021.11.25 15:45:15]

 

적용해서 실행해보니

직렬화해서 중복을 제거하니까 

A > B 

B > 0101이 데이터가 0101 B형태로밖에 안생겨서 2뎁스에서 0101을 못찾는 현상이 발생하네요

아니면 제가 쿼리에서 실수를 한걸까요??

원테이블 데이터를보니 이런 문제 때문에 좌우데이터를 크로스해서 한번 더 넣은것 같긴하네요...

 

직렬화해서 탐색하는 방법은 좋은 방법인것 같습니다. 감사합니다 

 

 

 

WITH tt AS
(
SELECT 'A' ac, 'B' bc, 1 rn   FROM DB_ROOT
UNION ALL SELECT 'B', 'C',  2 FROM DB_ROOT
UNION ALL SELECT 'B', '0101',  3 FROM DB_ROOT
UNION ALL SELECT '0000', 'E',  5 FROM DB_ROOT
UNION ALL SELECT '0101', '0000',  5 FROM DB_ROOT
UNION ALL SELECT '0101', 'E',  6 FROM DB_ROOT
UNION ALL SELECT '0101', 'F',  7 FROM DB_ROOT
UNION ALL SELECT 'E', 'B',  8 FROM DB_ROOT
UNION ALL SELECT 'E', 'F',  9 FROM DB_ROOT
UNION ALL SELECT 'F', 'B', 10 FROM DB_ROOT
)
, t1 AS
(
SELECT DISTINCT
       LEAST   (ac, bc) ac
     , GREATEST(ac, bc) bc
     , RN
  FROM tt
)

SELECT AC, bc, LEVEL, RN
  FROM t1
 START WITH ac = 'A'
 CONNECT BY PRIOR bc = ac
 
 UNION
SELECT AC, bc, LEVEL, RN
  FROM t1
 START WITH bc = 'A'
 CONNECT BY PRIOR ac = bc
;

 


by 마농 [2021.11.25 16:07:50]

1. 음. 그렇군요.
- (A > C) (C > B) 에서 (C > B) 를 (B > C) 로 바꾸면 B 를 못찾게 되네요.
- 이 방법은 안되겠네요.
2. 그런데 DISTINCT 부분은 잘못 사용하신 듯 하네요.
- rn 을 넣으려면 DISTINCT 하면 안되고
- DISTINCT 하려면 rn 을 넣으면 안되요.

결국 다음과 같이 해야 할 것 같은데.
성능과는 점점 더 멀어지는 것 같네요.
 

-- Oracle 에서 Test --
WITH tt AS
(
SELECT 'A' ac, 'B' bc, 1 rn   FROM dual
UNION ALL SELECT 'B'   , 'C'   ,  2 FROM dual
UNION ALL SELECT 'B'   , '0101',  3 FROM dual
UNION ALL SELECT '0000', 'E'   ,  5 FROM dual
UNION ALL SELECT '0101', '0000',  5 FROM dual
UNION ALL SELECT '0101', 'E'   ,  6 FROM dual
UNION ALL SELECT '0101', 'F'   ,  7 FROM dual
UNION ALL SELECT 'E'   , 'B'   ,  8 FROM dual
UNION ALL SELECT 'E'   , 'F'   ,  9 FROM dual
UNION ALL SELECT 'F'   , 'B'   , 10 FROM dual
)
, t1 AS
(
SELECT DISTINCT
       LEAST   (ac, bc) ac
     , GREATEST(ac, bc) bc
  FROM tt
)
SELECT DISTINCT *
  FROM t1
 START WITH 'B' IN (ac, bc)
 CONNECT BY NOCYCLE (PRIOR bc = ac OR PRIOR ac = bc)
;

 


by 몸에좋은SG [2021.11.26 11:18:40]

네 ㅜㅜ RN은 그냥 어떤열이 최종적으로 나왔나 보려고 넣었던거라..

성능과는 점점 더 멀어지네요 ㅜㅜ 

 

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입