서브쿼리중급
CTE (Common Table Expression)
WITH 절로 정의하는 임시 명명 결과 집합
정의
WITH 키워드로 쿼리 앞부분에 임시 이름을 가진 결과 집합을 정의하고, 본 쿼리에서 그 이름을 테이블처럼 참조한다. 복잡한 서브쿼리를 분리해 가독성을 높이고, 같은 결과를 여러 번 참조할 때 유용하다. 오라클 11g 이상은 WITH RECURSIVE 없이 CONNECT BY로 계층 처리가 더 자연스럽다.
왜 중요한가?
서브쿼리가 두 번 이상 참조되거나, 단계별 가공이 필요할 때 가독성이 극적으로 좋아진다. 옵티마이저는 CTE를 인라인하거나(머지) materialize하거나 결정하므로, 재사용 시 자동으로 한 번만 계산되리라 가정하면 안 된다.
틀리기 쉬운 포인트
- !오라클은 CTE를 기본 인라인 처리합니다. 한 번만 계산된다고 가정하면 안 됩니다.
- !CTE는 가독성을 위한 도구. 옵티마이저가 단순 서브쿼리로 변환할 수 있습니다.
- !재귀 CTE는 종료 조건(앵커)을 명확히 하지 않으면 무한 루프가 됩니다.
예시
WITH dept_avg AS ( SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id ) SELECT e.name, e.salary, d.avg_sal FROM employees e JOIN dept_avg d ON e.dept_id = d.dept_id WHERE e.salary > d.avg_sal;
성능 포인트
!오라클은 CTE를 기본 인라인. /*+ MATERIALIZE */ 힌트로 임시 결과를 강제 캐시할 수 있다. 무거운 CTE를 여러 번 참조한다면 MATERIALIZE를 검토하라.