1. 개요
대용량 통계 쿼리 작성 시 보통 데이터를 추출할 대상(기준) 및 그 대상에 대해 데이터를 추출할 합계(산출) 기준 등을 분류하고 이를 어떻게 다루어야 할 지 고민이 많이 된다.
가독성과 성능적인 부분을 많이 고민해야 하는데, 일단 고민의 시작이 보통 WITH절과 서브쿼리를 사용할 지 부터 이루어지므로 어떤 것을 사용해야 할 지 한 번 고민해보았다.
2. WITH 절과 서브쿼리
-
WITH
WITH절은 기준이 되는 테이블, 데이터를 산출할 영역을 WITH절로 한꺼번에 묶는 것이다. -
서브쿼리
기준이 되는 테이블, 데이터를 산출할 영역을 서브쿼리로 묶는 것이다.
3. 나의 상황
결론 : 성능개선도 상황에 맞게.
보통 서브쿼리로 하다가 반복되는 부분이 많으니 WITH절로 묶는 경우가 대부분인 것 같은데, 나의 경우 기준이 다른 데이터가 워낙 많아 WITH절로 묶는 것 부터 시작하였다. 그런데 이게 말 그대로 보통의 경우이고.. 서브쿼리로 시작하는 것을 일반적인 상황이라는 글들이 많았는데 좀 알아보니까 그냥 자신의 상황에 따라 맞게 시작하는 것이 좋은 것 같다.
WITH절의 경우 full scan이 일어나는데, 인덱스 및 조인조건을 잘 건다면 성능적인 부분을 크게 개선할 수 있다.
데이터 산출 조건을 통해 서브쿼리의 데이터양을 줄인다면, 서브쿼리가 WITH절보다 성능이 좋아질 수 있다. 또한 인덱스, 힌트 등의 조치를 통해 성능적으로 더 우세하게 만들 수 있다. 보통 서브쿼리가 인덱스의 영향을 받지 않아 성능이 안좋아 질 수 있다는 말이 있었는데, 오히려 서브쿼리로 튜닝한 후에 WITH절보다 더 조회성능이 개선되었다.
WITH절도 대용량 조건이면 사용하지 않는게 좋다고 하는데, 산출 기준이 너무 다르다면 어쩔 수 없이 WITH절을 사용해야하는 경우가 생긴다.
4. 현업에서 느낀 WITH/서브쿼리 유의사항
- 기준이 다른 통계를 다른 임시 테이블(WITH절) 사용할 경우, 최대한 데이터 갯수를 최소화(조회조건)하여 사용하는 것이 바람직하다.
- WITH 절을 여러 개 사용할 때, 다른 WITH 절을 하나의 테이블로 활용하여 통계 산출성 테이블을 활용하는 것이 좋다.
- 기준 테이블에서 최대한 모든 데이터를 조회하고, 다른 통계 산출성 테이블을 활용한다.
- 서브쿼리를 사용할 경우, 조회를 할 때 마다(row 마다) select 조회를 실행하므로 속도가 매우 느려진다. WITH 절에서 통계 산출 쿼리를 활용한 후, JOIN을 통해 구하는 것이 훨씬 성능적으로 효율적이다.
- 대용량 통계 조회 시 WITH절을 사용하거나, 서브쿼리를 사용할 수 있는데 DBMS 차원의 조치(인덱스 혹은 힌트 추가)가 아니면 성능적인 차이는 WITH절이 오히려 나을 수 있다(WITH절 자체가 통계 조회가 적은 경우에만 사용하기 때문에 그런듯하다).
- 분명 WITH절은 확장성, 가독성 측면에서 서브쿼리보다 우세하다. 또한 서브쿼리는 각 행마다 SELECT 동작이 발생하게 되며, 서브쿼리에는 인덱스 정보가 담겨져 있지 않으므로 성능적인 조치의 영향(복잡한 연산 혹은 크기가 클 경우)을 받지 않을 수 있다.
- 다만 현재 MIS 통계 조회를 진행하면서 서브쿼리와 인덱스 도움을 받아 WITH절 보다 훨씬 더 성능이 우세하게 되었는데, 무조건적으로 성능이 나쁘다는 것은 아닌것 같다. 상황에 맞게 사용할 필요가 있어 보인다.
- GROUP BY가 힘들다면 PARTITON BY 사용하자.
5. 참고자료
성능관점에서 WITH절/서브쿼리의 차이점
https://project-notwork.tistory.com/111
https://schatz37.tistory.com/3
