[Oracle] WITH문 사용방법

• 보통 쿼리를 작성할 때 특정 데이터를 뽑아내려 하면 서브쿼리로 작성하는 경우가 많습니다.
  
  
• 하지만 서브쿼리를 사용하게 되면 가독성이 떨어지고 유지보수가 어려워질 수 있으며 중복된 서브쿼리를 계속해서 작성할 경우 성능 또한 떨어지게 됩니다.
  
  
• 이때 사용하기 좋은 with문에 대해 알아보도록 하겠습니다.

---

#. WITH 문이란?

 

• CTE(Common Table Expression, 공용 테이블 표현식)라고도 하며, WITH 문은 단일 SQL 범위 내에서 참조할 수 있는 임시 결과 집합을 정의할 수 있는 SQL 기능입니다.
  
  

1. 사용 범위

- WITH문의 범위는 WITH 뒤에 나오는 SQL 문으로 제한되기에 WITH문을 다시 정의하지 않는 한 후속 쿼리에서 WITH문을 참조할 수 없으므로 가장 먼저 정의한 뒤 쿼리를 실행시키는 것이 좋습니다.

 

2. 가독성 및 유지 관리 가능성

- WITH문은 SQL 쿼리를 더 작고 이해하기 쉬운 부분으로 나누어 쿼리의 가독성과 유지 관리 가능성을 향상시킬 수 있습니다. 특히 여러 개의 중첩된 부분이나 반복되는 서브 쿼리가 있는 복잡한 쿼리를 사용할 때 유용합니다.

 

3. 재사용성

- 상단에 한 번 정의하면 동일 쿼리 내에서 이를 여러 번 참조할 수 있으므로 중복이 줄어들고 쿼리를 더 쉽게 관리할 수 있습니다.

4. 성능

- 쿼리의 가독성과 유지 관리성을 향상시킬 수 있지만 기본 데이터베이스 시스템이 쿼리를 최적화하는 방식에 따라 성능에 영향을 미칠 수 있습니다. 경우에 따라 WITH문을 사용하면 성능이 향상될 수 있습니다.

---

 

#. 사용방법

• 서브쿼리 작성방법과 동일하나 하나의 가장 위에서 with문 내에 작성해 주면 됩니다.
  
  
• 한 번에 여러 개 작성이 가능하며 콤마(,)로 구분하여 줍니다.
  
  
• 서브쿼리처럼 사용도 가능하며 아래와 같이 WITH문을 특정 테이블처럼 사용 가능합니다.

-- 서브쿼리 사용 시
SELECT * FROM MAINTABLE A
INNER JOIN (SELECT col1, col2 
			FROM FIRSTTABLE
			WHERE col1 = 'TEST') B ON A.COL1 = B.COL1
INNER JOIN (SELECT col5, col6
			FROM SECONDTABLE
			WHERE col5 = 'BOBBY') C ON A.COL5 = C.COL5
WHERE A.COL2 = 'HELLO'

-- WITH문 사용 시
WITH TESTWITHFIRST AS (
	SELECT col1, col2
	FROM FIRSTTABLE
	WHERE col1 = 'TEST'
), TESTWITHSECOND AS (
	SELECT col5, col6
	FROM SECONDTABLE
	WHERE col5 = 'BOBBY'
)

SELECT * FROM MAINTABLE A
INNER JOIN TESTWITHFIRST B ON A.COL1 = B.COL1
INNER JOIN TESTWITHSECOND C ON A.COL5 = C.COL5
WHERE A.COL2 = 'HELLO'

 

---

#. WITH문 동작방식

 

• 오라클 with문에서 힌트(동작방식)는 materialize와 inline으로 구분할 수 있습니다.

---

•  MATERIALIZE는 WITH문의 결과 집합을 저장하기 위해 임시 테이블을 생성하고, 이 임시 테이블의 결과 값을 메인 쿼리에서 호출하여 사용합니다.
  
  
• MATERIALIZE 힌트를 사용하는 경우는 쿼리에서 여러 번 사용하고 결과 집합이 크거나 복잡한 경우 유용할 수 있습니다.

---

• INLINE은 WITH문에 정의된 실제 하위 쿼리로 대체한다는 의미로 INLINE 힌트를 사용하는 것은 WITH문의 결과집합이 작고 단순한 경우, 한 번만 호출하여 사용하는 경우에 유용하고 인라인으로 처리하면 잠재적으로 쿼리 성능을 향상시킬 수 있습니다.

WITH TESTWITHFIRST AS (
	SELECT /*+ materialize */ col1, col2
	FROM FIRSTTABLE
	WHERE col1 = 'TEST'
), TESTWITHSECOND AS (
	SELECT /*+ inline */ col5, col6
	FROM SECONDTABLE
	WHERE col5 = 'BOBBY'
)

SELECT * FROM MAINTABLE A
INNER JOIN TESTWITHFIRST B ON A.COL1 = B.COL1
INNER JOIN TESTWITHSECOND C ON A.COL5 = C.COL5
WHERE A.COL2 = 'HELLO'

---

• 서브쿼리와 WITH문 모두 늘 그렇듯 각각의 장단점이 있습니다.
  
  
• WITH 문의 경우 DBMS마다 사용방법이 조금씩 다르기에 호환성을 생각한다면 서브쿼리로 작성하는 것이 좋을 수 있습니다.
  
  
• 또한 간단한 서브쿼리의 경우 WITH문을 사용하는 것보다 가독성이 더 좋을 수 있고, 쿼리 구조 및 실행계획을 잘 처리할 수 있는 경우 사용하기에 적합할 수 있습니다.
  
  

 

===========================================================
틀린 내용이 있거나 이견 있으시면 언제든 가감 없이 말씀 부탁드립니다!
===========================================================