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만 정도라서.. 최대한 최적화를 하고 싶은데
따로 시도할만한 방법이나 도움 될 만한 정보가 있을까요?
실제 테이블엔 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에 해당하는 데이터는 다음 레벨 조회시 안나오게 하는게 가능하다면
속도를 더 개선할 수 있을것 같아서 시도해보고 있는데 어렵네요 ㅜㅜ
1번 쿼리의 순환 참조 방지 조건이 저렇게 들어간 이유가
본인이 다시 나오는 데이터는 필요가 없어서 넣었던거라
지금 쿼리상으로는 이상하네요...
탐색 레벨 한도는 정할예정이구요..
2번 쿼리는 레벨 한도 조건을 CONNECT BY 절에 주니까 속도차이가 좀 많이나긴 하는데
1번 쿼리가 여전히 더 빠르네요..
CYCLE bc SET iscycle TO "1" DEFAULT "0" 이 구문은 없지만..
CONNECT_BY_ISCYCLE 로 사이클에 대한 부분을 알 수는 있는데
다른 레벨에서 같은 데이터를 보고있으면 그건 못잡더라구요...
2LEVEL에서 조회됬던 ROW를 3LEVEL에서 안나오게 하고 싶은데 방법을 모르겠네요 후으...
감사합니다.
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 ;
적용해서 실행해보니
직렬화해서 중복을 제거하니까
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 ;
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) ;