[Oracle] WITH 문 사용법

WITH 절 이란?

WITH절은 오라클9 버전 부터 지원 되는 구문으로 WITH 절로 임시 테이블을 생성해서 메모리에 올려 놓고 필요시 참조해서 쓸수 있는 장점이 있다. WITH절을 활용하지 않고 서브 쿼리로 작성 할 경우 공통 부분에 대한 공용화가 어렵고 가독성이 떨어지는 단점이 있는데 반해 WITH절을 활용하면 공용 부분을 미리 뽑아서 성능 및 가독성 향상을 꾀할 수 있다.

WITH 절 장점

• SQL에서 동일한 데이터를 반복처리시, 성능개선방법으로 WITH절이 많이 활용된다.
• 데이터 건수는 적지만 데이터 추출시 I/O처리량이 많은 경우 효과적이다.
• 여러 부분에 사용될 데이터를 1회 추출하여, Global Temporary Table에 저장하고, 요청시 저장된 데이터만 읽어 처리하면 되므로 SQL성능개선 및 I/O처리량되 대폭 줄어들게 된다.

데이터 내용

SELECT
    ROW_NUMBER() OVER (ORDER BY ordr) AS num,
    manageid,
    menunm,
    ordr,
    dspyat,
    menuurl
 FROM menumanage
 ORDER BY ordr

 

NUM	MANAGEID	MENUNM	ORDR	DSPYAT	MENUURL
1	1	device	1	Y	/device
2	5	user	1	Y	/user
3	3	store	2	Y	/store
4	6	call	2	Y	/call
5	7	book	3	Y	/book
6	2	car	3	Y	/car
7	4	house	4	Y	/house

 

WITH절로 완성한 전체 쿼리 (ORDR가 2이상인 것만 출력)

WITH menu AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY ordr) AS num,
        manageid,
        menunm,
        ordr,
        dspyat,
        menuurl
    FROM menumanage
    ORDER BY ordr
)
SELECT
    num,
    manageid,
    menunm,
    ordr,
    dspyat,
    menuurl
FROM  menu
WHERE ordr  >= 2;

 

NUM	MANAGEID	MENUNM	ORDR	DSPYAT	MENUURL
1	3	store	2	Y	/store
2	6	call	2	Y	/call
3	7	book	3	Y	/book
4	2	car	3	Y	/car
5	4	house	4	Y	/house