서브쿼리의 종류와 WITH 쿼리

서브쿼리의 종류 

SELECT col1, (SELECT * ...) - 스칼라 서브쿼리 -> 하나의 컬럼처럼 사용한다.

FROM ( SELECT ... ) - 인라인 뷰 : 하나의 테이블 처럼 사용(테이블 대체 용도)

WHERE col = (SELECT ...) -- 일반 서브쿼리 : 하나의 변수(상수) 처럼 사용, 서브쿼리의 결과에 따라 달라지는 조건절

WITH 쿼리 

-> 이름이 부여된 서브쿼리라고 생각하면 이해하기 편하다.

WITH 별칭 1 AS (서브쿼리)

    ,별칭 2 AS (서브쿼리)

...

select

from 별칭 1, 별칭 2

특징으로는 임시 테이블을 만들어 사용하여 한번의 실행계획만 성립된다.

그러므로 여러번 사용될수록 성능향상을 이룰 수 있다.

또한, 일반 서브쿼리는 서로 참조 할 수 없지만 with절은 서로 참조가 가능하고 with절을 여러번 참조할 수록 그 효과가 

더 높아질것이라고 예상 할 수 있다. ( 여기서 말하는 효과란 실행시간이나 자원의 소모)

성능이 높아지는 이유

with로 만든다면 그 쿼리내에서 임시테이블 생성해서 버퍼캐쉬에 올립니다.

-> 메모리 사용량이 높아지지만 with 쿼리를 여러번 재사용하게 되면 그만큼의 실행계획을 생성하는 것을 줄일 수 있음. ( 여러번 서브쿼리 사용시 각각의 실행계획 생성)

결국 with -> 버퍼에서 풀 스캔 -> 재사용시 버퍼에서 계속 가져다 사용하는 과정이므로 with문으로 해당 쿼리를 버퍼

에 올리는 과정에서 처리 범위를 못줄인다면 단점이 될 수 있지만 재사용성이 높다면 매우 큰 장점이 될 수 있다.( with 쿼리는 버퍼캐시에 올릴 때 full scan 사용하므로 재사용 할 일 없다면 효율적이지 않음)

버퍼캐시

버퍼캐시 -> SGA(System global Area)의 일부로서 디스크에 존재하는 데이터를 읽어 저장하는 메모리 공간

with 쿼리 -> 버퍼캐시 올림 -> sga의 일부 -> sga는 데이터베이스에 접속하는 모든 사용자들이 공유하는 영역!

with query -> full scan -> 서브쿼리를 재사용할일 없다면 효율적이지 않음!