Oracle (10) - WITH 절 사용방법

SQL을 작성하다 보면 복잡한 서브쿼리를 여러 번 사용해야 하는 경우가 있습니다. 이 경우 가독성이 떨어지고, 쿼리에 불필요한 연산이 많아져 쿼리 성능도 저하될 수 있습니다. 이를 개선할 수 있는 하나가 WITH 절입니다.

오라클에서는 9i 버전부터 지원하며 12c 버전부터는 프로시저에서도 사용할 수 있게 되었습니다.

 

WITH 사용법

해당 예제는 학생들의 점수를 합산하여 조회하는 예시입니다.

WITH SCORE_ALL_TB AS
( 
  SELECT STUDENT_NO		AS	STUDENT_NO
       , SUM(SCORE)		AS	SCORE_ALL
    FROM STUDENT_SCORE
   GROUP BY STUDENT_NO
)

SELECT A.STUDENT_NO
	 , B.NAME
     , A.SCORE_ALL
  FROM SCORE_ALL_TB A
  LEFT JOIN STUDENT_INFO B
 WHERE A.STUDENT_NO = B.STUDENT_NO

위 쿼리에서 SCORE_ALL_TB를 임시 테이블처럼 정의된 서브쿼리입니다. 메인 쿼리에서는 이를 테이블처럼 조인하여 사용할 수 있게 되었습니다. 동일한 계산식을 여러 번 사용할 필요 없이 재사용이 가능하며 쿼리가 더 깔끔해집니다.

 

WITH 절 동작 방식

오라클의 WITH 절은 Materialize와 Inline 2가지의 동작합니다. (옵티마이저 힌트로 제어 가능)

• Materialize 방식 : WITH 절 결과를 임시 테이블에 저장합니다. 동일한 서브쿼리를 여러 번 호출해도 쿼리를 한번만 실행하고, 저장된 데이터를 재사용합니다.
• Inline 방식 : 참조 시 Inline View 형태로 실행합니다. 참조된 횟수만큼 쿼리를 실행합니다. (서브쿼리와 작동방식 동일)

데이터 크기와 호출 횟수에 따라 Materalize/Inline 방식이 유리할 수 있습니다. 옵티마이저가 자동으로 선택하나 주석을 통해 강제할 수 있습니다.

• /*+ materialize */
• /*+ inline */ 

WITH SCORE_ALL_TB AS
( 
  SELECT /*+ materialize */
         STUDENT_NO		AS	STUDENT_NO
       , SUM(SCORE)		AS	SCORE_ALL
    FROM STUDENT_SCORE
   GROUP BY STUDENT_NO
)

SELECT A.STUDENT_NO
	 , B.NAME
     , A.SCORE_ALL
  FROM SCORE_ALL_TB A
  LEFT JOIN STUDENT_INFO B
 WHERE A.STUDENT_NO = B.STUDENT_NO

 

마지막으로

WITH 절은 쿼리의 가독성을 올리는 것뿐만 아니라 성능 최적화와 유지 보수에 도움이 됩니다.

• 복잡한 SQL을 정리할 때
• 반복되는 서브쿼리를 효율적으로 관리할 때
• 실행 성능을 개선할 때 

 

참고 게시글

https://gent.tistory.com/419

[Oracle] 오라클 WITH 절 사용법 및 동작방식 (WITH 여러개)