1. WITH
Oracle에서 임시적으로 데이터를 추출하기 위한 특정 범위 및 table을 구성하기위한 문법이다.
WITH를 사용하는 가장 주된 목적은 가독성이다.
SELECT 데이터를 추출해낼때 보통은 하나의 테이블에서 바로 추출할 수 있는 것이 아닌, 수많은 join과 정렬 및 다수의 SELECT를 거쳐 데이터를 추출하는 것이 일반적이다.
이럴 경우 SELECT FROM (SELECT FROM (SELECT ... 이런 식으로 데이터를 추출하는 기준인 FROM절을 한번에 진행하는 것이 아닌, 여러번 진행하여 한 부분에서 코드가 비대해지고 가독성이 그만큼 떨어질 수 밖에 없다.
따라서 WITH를 사용하여, 추출하고자 하는 대상을 WITH문에 우선 구성해놓은 후 이후 SELECT FROM문에 활용한다면 코드를 깔끔하고 보기좋게 구성할 수 있다.
2. 성능지연문제
하지만 안타깝게도 (사이트에 따라 다를 수 있지만) WITH 사용을 금지하는 곳이 있을 정도로, (대용량 데이터를 추출하는 쿼리에서) 성능 문제를 수반한다.
2-1. TEMP SPACE
WITH를 사용한다면 필연적으로 해당 WITH문의 결과를 임시적으로 보관하는 buffer, 혹은 공간이 필요하다.
사용자가 늘어나게 된다면 해당 쿼리를 처리하기 위해 필요한 temp space 공간이 그만큼 늘어나게 되며, application은 이러한 temp space 관련 처리를 하기위한 별도의 성능과 비용을 소모하게 된다.
따라서 소규모에서는 그 차이가 안느껴질 수도 있으나, 규모가 커질수록 temp space가 감당하는 부담과 비용 등이 with문을 사용하는 것을 원인으로 증가할 수 있다.
2-2. 대용량 데이터 처리시 index 생략 및 성능저하 현상
WITH절에서 connect by, join 등 table full scan하는 요인을 많이 사용하게 된다면 기본적으로 해당 쿼리는 full scan하는 쿼리로 변질한다.
상황에 따라 DB index를 생략하는 현상까지 발생하기도 하는데(index scan이 안됨, 이에 대해선 더 공부해야함), 그만큼 성능을 저하시킬 수 있는 요인이라 할 수 있겠다.
3. 유의사항
WITH문을 사용한다면 성능적으로 무조건 안좋다, 사용하지 않는 것이 좋다는 아니고 역시 상황에 따라 잘 판단하여 써야한다.
- WITH절이 다수의 table을 join하는 형태가 아닌, dual을 통해 날짜 등 단순히 특정 데이터를 사용하는 등 선별범위가 크지 않은 경우
- 특정 범위를 반복적으로 사용하고, 그 범위가 많은 비용 및 시간이 소요될 경우
- 응답 시간이 중요하지 않은 경우
- 데이터 처리 및 사용자 요청이 크지 않아 메모리 공간에 대한 필요성이 그리 크지 않은 경우
실무적으로 경험을 더 해봐야 하겠지만, 위와 같은 상황에서는 WITH문 사용을 고려할 수도 있을 것이다.
4. 참고자료
WITH문과 성능지연 문제 - http://agritsa.blogspot.com/2019/12/oracle-with-2-connect-by.html
WITH문을 사용하면 좋은 경우(쿼리블록) - https://0x0000.tistory.com/3
