[Oracle] WITH문과 재귀쿼리

WITH 절이란?

쉽게 생각하면 이름이 부여된 서브쿼리, 임시 테이블을 만든다.

WITH와 VIEW와 차이점

• WITH절은 한번 실행할 쿼리문내에 정의되어 있을 경우, 그 쿼리문 안에서만 실행된다.
• VIEW는 한번 만들면 DROP할 때 까지 없어지지 않는다.

WITH절을 사용하는 이유

• 동일 블록에 대해 반복적으로 SQL을 사용하는 경우 그 블록에 이름을 부여해 재사용 할 수 있기 떄문에 쿼리 성능을 높일 수 있음
  자주 실행되는 경우 한번만 Parsing되고 Plan 계획이 수립되므로 쿼리 성능향상에 도움

• 가독성이나 재사용성 때문에 사용되기도 하지만, 계층형 쿼리를 만들기 위해서도 필요하다.
  오라클11g부터는 CONNECT BY절로 계층형 쿼리를 구현할 수 있지만, 그 이전 버전이나 다른 DBMS에서는 WITH문으로 구현한다.

  
  
  

사용법

	WITH TEST AS
	(
		SELECT 'WITH절' AS TEST1
		FROM DUAL
	)

	SELECT *
	FROM TEST;

-- 다중 WITH문
	WITH TEST1 AS			--첫 번째 WITH문
	(
		SELECT 'EX1' AS A
		FROM DUAL
		UNION ALL
		SELECT 'EX2'
		FROM DUAL
	),
	TEST2 AS				--두 번째 WITH문
	(
		SELECT 'EX3' AS A
		FROM DUAL
		UNION ALL
		SELECT A
		FROM TEST1		--첫 번째 WITH문 참조
	)

	SELECT *
	FROM TEST2;

오류

오라클에서는 한번만 사용되면 Inline View,
두번이상 사용되면 Materialize View로 처리한다.
- 한번도 사용하지 않으면 [ORA-01762] 오류 발생

---

WITH를 사용한 재귀 쿼리 (계층형 쿼리)

계층형 쿼리란?

계층형 구조(Hierarchical Structure)는 부모와 자식 간의 관계를 깊이로 구분하여 표현하는 것으로
트리구조, 카테고리 분류, 조직도에 많이 사용한다.

• Oracle 10g버전 부터 CONNECT BY문 사용 가능
• Oracle 11g R2 버전부터 WITH문 사용 가능

WITH절로 재귀를 사용하는 조건

1. 조회에 사용되는 컬럼명들을 WITH문을 정의할 때 반드시 명시.
2. 시작 조건이 되는 테이블과 서브쿼리를 DRIVING으로 사용하는 테이블을 UNION ALL로 결합
3. 종료조건이 무한루프에 빠지면 안됨.

• 드라이빙 테이블 (DRIVING TABLE / OUTER TABLE) : JOIN시 먼저 엑세스 돼서 ACCESS PATH를 주도하는 테이블
• 드리븐 테이블 (DIVEN TABLE / INNER TABLE) : 나중에 엑세스 되는 테이블

예제) 1부터 10까지 출력하는 재귀쿼리

WITH NUMBERS AS(
SELECT 1 AS NUM
FROM DUAL
UNION ALL
SELECT A.NUM + 1 AS NUM
FROM NUMBERS A
WHERE A.NUM < 10
)
SELECT B.NUM
FROM NUMBERS B

---

CONNECT BY 문법

START WITH ~ CONNECT BY 사용

• START WITH 조건에 계층형 구조의 최상위 계층의 로우를 식별하는 조건을 넣는다
• CONNECT BY 조건에는 계층형 구조가 어떻게 연결되는지 표현한다.

  SELECT [컬럼]...
  FROM [테이블]
  WHERE [조건]
  START WITH [최상위 조건]
  CONNECT BY [NOCYCLE][PRIOR 계층형 구조 조건];

---

• 참조 블로그

  https://coding-factory.tistory.com/445 -- WITH절
  https://heodolf.tistory.com/71 -- WITH절 오류부분
  https://coding-factory.tistory.com/461 -- 오라클 START WITH
  https://devuna.tistory.com/36 -- 드라이빙 테이블