서브쿼리 변환이 필요한 경우
- 옵티마이저는 비용(Cost)를 평가하고 실행계획을 생성하기 앞서 사용자로부터 전달받은 SQL을 최적화에 가장 유리한 형태로 변환
- 쿼리 변환(Query Transformation)
- 옵티마이저가 SQL을 분석해 같은 결과집합을 생성하면서도 더 나은 성능이 기대되는 형태로 재작성하는 것
- 서브쿼리(Subquery)
- 하나의 SQL문 안에 괄호로 묶은 별도의 쿼리 블록(Query Block)
- 인라인 뷰(Inline View): FROM절에 사용한 서브쿼리
- 중첩된 서브쿼리(Nested SubQuery): 결과집합을 한정하기 위해 WHERE절에 사용한 서브쿼리
- 서브쿼리가 메인쿼리 컬럼을 참조하는 형태를 '상관관계 있는(Correlated) 서브쿼리'라고 함
- 스칼라 서브쿼리(Scalar Subquery): 한 레코드당 정확히 하나의 값을 반환하는 서브쿼리
- 주로 SELECT-LIST에 사용하나, 몇 가지 예외사항을 제외하면 컬럼이 올 수 있는 대부분의 위치에 사용 가능
- 서브쿼리를 참조하는 메인 쿼리도 하나의 쿼리 블록이며, 옵티마이저는 쿼리 블록 단위로 최적화 수행
서브쿼리와 조인
- 메인쿼리와 서브쿼리 간에는 부모와 자식이라는 종속적이고 계층적인 관계가 존재
- 서브쿼리는 메인쿼리에 종속되므로 단독으로 실행할 수 없음
- 메인쿼리 건수만큼 값을 받아 반복적으로 필터링하는 방식으로 실행
필터 오퍼레이션
- no_unnest 힌트는 서브쿼리를 풀어내지 말고 그대로 수행하라는 힌트
select c.고객번호, c.고객명
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and exists (
select 'x'
from 거래
where 고객번호 = c.고객번호
and 거래일시 >= trunc(sysdate, 'mm') )
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=289 Card=1 Bytes=39)
1 0 FILTER
2 1 TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) (Cost=4 Card=190 ...)
3 2 INDEX (RANGE SCAN) OF '고객_X01' (INDEX) (Cost=2 Card=190)
4 1 INDEX (RANGE SCAN) OF '거래_X01' (INDEX) (Cost=3 Card=4K Bytes=92K)
- 필터(Filter) 오퍼레이션은 기본적으로 NL조인과 처리 루틴이 같음
- 위 실행계획의 FILTER를 NESTED LOOPS로 치환하고 처리 루틴을 해석하면 됨
- 부분범위 처리도 가능
- 차이점
- 필터는 메인쿼리의 한 로우가 서브쿼리의 한 로우와 조인에 성공하는 순간 진행을 멈추고, 메인쿼리의 다음 로우를 게속 처리
- 이렇게 해야 메인쿼리 결과집합이 서브쿼리 M쪽 집합 수준으로 확장되는 현상(고객번호 중복)을 막을 수 있음
- 필터는 캐싱기능을 가짐
- 필터 처리한 결과, 즉 서브쿼리 입력 값에 따른 반환 값(T/F)을 캐싱하는 기능
- 서브쿼리를 수행하기 전에 항상 캐시부터 확인
- 캐시에서 T/F 여부를 확인해 서브쿼리를 수행하지 않아도 되므로 성능을 높이는데 도움
- 캐싱은 쿼리 단위로 이뤄짐
- 쿼리를 시작할 때 PGA 메모리에 공간 할당
- 쿼리를 수행하면서 공간을 채워나감
- 쿼리를 마치는 순간 공간 반환
- 필터 서브쿼리는 NL 조인과 달리 메인쿼리에 종속되므로 조인 순서가 고정
begin
for outer in (select 고객번호, 고객명 from 고객 where … )
loop
for inner in (select 'x' from 거래 where 고객번호 = outer.고객번호 and … )
loop
dbms_output.put_line(outer.고객번호 || ',' || outer.고객명);
exit;
end loop;
end loop;
end;
서브쿼리 Unnesting
- 서브쿼리 Unnesting은 메인과 서브쿼리 간의 계층구조를 풀어 서로 같은 레벨(flat한 구조)로 만들어 준다는 의미
select c.고객번호, c.고객명
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and exists (
select 'x'
from 거래
where 고객번호 = c.고객번호
and 거래일시 >= trunc(sysdate, 'mm') )
Execution Plan
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=384 Card=190 Bytes=11K)
1 0 NESTED LOOPS (SEMI) (Cost=384 Card=190 Bytes=11K)
2 1 TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) (Cost=4 Card=190 …)
3 2 INDEX (RANGE SCAN) OF '고객_X01' (INDEX) (Cost=2 Card=190)
4 1 INDEX (RANGE SCAN) OF '거래_X01' (INDEX) (Cost=2 Card=427K Bytes=9M)
- 서브쿼리 그대로 두면 필터 방식을 사용할 수밖에 없지만, Unnesting을 하면 일반 조인처럼 다양한 최적화 기법 사용 가능
- 위 예시는 unnest와 nl_sj 힌트를 통해 NL 세미조인 방식으로 실행
- NL 세미 조인은 NL 조인과 같은 프로세스
- 조인에 성공하는 순간 진행을 멈추고 메인 쿼리의 다음 로우을 계속 처리한다는 점이 다름
- 필터 오퍼레이션의 기능이기도 함
- 10g부터 NL 세미조인이 캐싱기능도 갖게 됐으므로 사실상 필터 오퍼레이션과 차이 없음
- Unnesting된 서브쿼리는 NL 세미조인 외에도 다양한 방식으로 실행될 수 있음
- 필터방식은 항상 메인쿼리가 드라이빙 집합
- Unnesting된 서브쿼리는 메인 쿼리 집합보다 먼저 처리될 수 있음
select c.고객번호, c.고객명
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and exists (
select 'x'
from 거래
where 고객번호 = c.고객번호
and 거래일시 >= trunc(sysdate, 'mm') )
Execution Plan
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=253K Card=190 Bytes=11K)
1 0 NESTED LOOPS
2 1 NESTED LOOPS (Cost=253K Card=190 Bytes=11K)
3 2 SORT (UNIQUE) (Cost=2K Card=427K Bytes=9M)
4 3 TABLE ACCESS (BY INDEX ROWID) OF '거래' (TABLE) (Cost=2K … )
5 4 INDEX (RANGE SCAN) OF '거래_X02' (INDEX) (Cost=988 Card=427K)
6 2 INDEX (RANGE SCAN) OF '고객_X01' (INDEX) (Cost=1 Card=190)
7 1 TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) (Cost=3 Card=1 … )
- 서브쿼리를 그대로 풀어서 조인하면 메인쿼리 결과집합이 서브쿼리 M쪽 집합 수준으로 확장될 수 있으므로 서브쿼리 집합에 대한 Sort Unique 오퍼레이션부터 수행
select c.고객번호, c.고객명
from (select distinct 고객번호
from 거래
where 거래일시 >= trunc(sysdate, 'mm')) t, 고객 c
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and c.고객번호 = t.고객번호
select c.고객번호, c.고객명
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and exists (
select 'x'
from 거래
where 고객번호 = c.고객번호
and 거래일시 >= trunc(sysdate, 'mm') )
Execution Plan
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2K Card=38 Bytes=2K)
1 0 FILTER
2 1 HASH JOIN (SEMI) (Cost=2K Card=38 Bytes=2K)
3 2 TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) (Cost=3 Card=38 … )
4 3 INDEX (RANGE SCAN) OF '고객_X01' (INDEX) (Cost=2 Card=38)
5 2 TABLE ACCESS (BY INDEX ROWID) OF '거래' (TABLE) (Cost=2K … )
6 5 INDEX (RANGE SCAN) OF '거래_X02' (INDEX) (Cost=988 Card=427K)
- 서브쿼리를 Unnesting해서 메인쿼리와 같은 레벨로 만들면, 다양한 조인 메소드를 선택하거나 조인 순서 임의 지정이 가능
- 옵티마이저는 많은 조인 테크닉을 가지기 때문에 조인 형태로 변환했을 때 필터 오퍼레이션보다 더 좋은 실행경로를 찾을 가능성이 높아짐
서브쿼리에서 ROWNUM
- 조건절을 만족하는 레코드를 지정한 개수만큼 찾고 나면 조건 필터링을 더 진행하지 않고 멈추게 하고 싶을 때 rownum을 사용
- 쿼리 성능을 높이는 데 효과적이나, 병렬 쿼리나 서브쿼리에 사용할 경우 성능이 떨어질 수 있음
select 글번호, 제목, 작성자, 등록일시
from 게시판 b
where 게시판구분 = '공지'
and 등록일시 >= trunc(sysdate-1)
and exists (select 'x'
from 수신대상자
where 글번호 = b.글번호
and 수신자 = :memb_no
and rownum <= 1)
- Exists는 매칭되는 데이터 존재 여부를 확인하는 연산자
- 조건절을 만족하는 레코드를 만나는 순간 멈추는 기능을 갖고 있음
- Exists 서브쿼리에 rownum 조건까지 사용하면 의미의 중복
- 서브쿼리에 rownum을 사용하면, Unnesting을 사용할 수 없음
- 필터 오퍼레이션보다 더 좋은 실행경로를 찾을 가능성이 차단
서브쿼리 Pushing
- Unnesting 되지 않은 서브쿼리는 항상 필터 방식으로 처리
select count(distinct p.상품번호), sum(t.주문금액)
from 상품 p, 주문 t
where p.상품번호 = t.상품번호
and p.등록일시 >= trunc(add_months(sysdate, -3), 'mm')
and t.주문일시 >= trunc(sysdate - 7)
and exists (select 'x' from 상품분류
where 상품분류코드 = p.상품분류코드
and 상위분류코드 = 'AK' )
Call Count CPU Time Elapsed Time Disk Query Current Rows
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.484 3.493 650 38103 0 1
Total 4 0.484 3.493 650 38103 0 1
Rows Row Source Operation
0 STATEMENT
1 SORT AGGREGATE (cr=38103 pr=650 pw=0 time=3493306 us)
3000 FILTER (cr=38103 pr=650 pw=0 time=3486253 us)
60000 NESTED LOOPS (cr=38097 pr=650 pw=0 time=3602032 us)
1000 TABLE ACCESS FULL 상품 (cr=95 pr=0 pw=0 time=342023 us)
60000 TABLE ACCESS BY INDEX ROWID 주문 (cr=38002 pr=650 pw=0 time= … )
60000 INDEX RANGE SCAN 주문_PK (cr=2002 pr=90 pw=0 time=964606 us)
1 TABLE ACCESS BY INDEX ROWID 상품분류 (cr=6 pr=0 pw=0 time=78 us)
3 INDEX UNIQUE SCAN 상품분류_PK (cr=3 pr=0 pw=0 time=36 us)
- 상품으로부터 주문 테이블로 1000번의 조인 엑세스가 있었고, 조인에 성공한 주문 데이터는 60000개
- 60000개의 조인 결과집합은 서브쿼리 필터링을 수행하고 나서 3000개로 줄었음
- 총 읽은 블록 수는 38103
- 대부분 I/O가 조인 과정에서 발생
- 만약 서브쿼리 필터링을 먼저 처리함으로써 조인 단계로 넘어가는 로우 수를 크게 줄일 수 있다면 성능은 향상됨
select count(distinct p.상품번호), sum(t.주문금액)
from 상품 p, 주문 t
where p.상품번호 = t.상품번호
and p.등록일시 >= trunc(add_months(sysdate, -3), 'mm')
and t.주문일시 >= trunc(sysdate - 7)
and exists (select 'x' from 상품분류
where 상품분류코드 = p.상품분류코드
and 상위분류코드 = 'AK' )
Call Count CPU Time Elapsed Time Disk Query Current Rows
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.125 0.129 0 1903 0 1
Total 4 0.125 0.129 0 1903 0 1
Rows Row Source Operation
0 STATEMENT
1 SORT AGGREGATE (cr=1903 pr=0 pw=0 time=128934 us)
3000 NESTED LOOPS (cr=1903 pr=0 pw=0 time=153252 us)
150 TABLE ACCESS FULL 상품 (cr=101 pr=0 pw=0 time=18230 us)
1 TABLE ACCESS BY INDEX ROWID 상품분류 (cr=6 pr=0 pw=0 time=135 us)
3 INDEX UNIQUE SCAN 상품분류_PK (cr=3 pr=0 pw=0 time=63 us)
3000 TABLE ACCESS BY INDEX ROWID 주문 (cr=1802 pr=0 pw=0 time=100092 us)
3000 INDEX RANGE SCAN 주문_PK (cr=302 pr=0 pw=0 time=41733 us)
- Pushing 서브쿼리는 서브쿼리 필터링을 가능한 한 앞 단계에서 처리하도록 강제하는 기능
- Unnesting 되지 않은 서브쿼리에만 작동
- Unnesting 되면 필터가 아닌 다양한 조인 방식으로 실행
- push_subq 힌트는 항상 no_unnest 힌트와 같이 사용할 것
뷰(View)와 조인
- 최적화 단위가 쿼리 블록이므로, 옵티마이저가 뷰 쿼리를 변환하지 않으면 뷰 쿼리 블록을 독립적으로 최적화
select c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래
from 고객 c
, (select 고객번호, avg(거래금액) 평균거래
, min(거래금액) 최소거래, max(거래금액) 최대거래
from 거래
where 거래일시 >= trunc(sysdate, 'mm')
group by 고객번호) t
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and t.고객번호 = c.고객번호
Execution Plan
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1M Card=1K Bytes=112K)
1 0 NESTED LOOPS
2 1 NESTED LOOPS (Cost=1M Card=1K Bytes=112K)
3 2 VIEW (Cost=2K Card=427K Bytes=21M)
4 3 HASH (GROUP BY) (Cost=2K Card=427K Bytes=14M)
5 4 TABLE ACCESS (BY INDEX ROWID) OF '거래' (TABLE) (Cost=2K … )
6 5 INDEX (RANGE SCAN) OF '거래_X01' (INDEX) (Cost=988 Card=427K)
7 2 INDEX (RANGE SCAN) OF '고객_X01' (INDEX) (Cost=1 Card=190)
8 1 TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) (Cost=3 Card=1 … )
- 고객 테이블에서 '전월 이후 가입한 고객'을 필터링하는 조건이 인라인 뷰 밖에 있음
- 이 조건이 있음에도 인라인 뷰 안에서는 당월에 거래한 '모든' 고객의 거래 데이터를 읽어야 함
select c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래
from 고객 c
,(select 고객번호, avg(거래금액) 평균거래
, min(거래금액) 최소거래, max(거래금액) 최대거래
from 거래
where 거래일시 >= trunc(sysdate, 'mm')
group by 고객번호) t
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and t.고객번호 = c.고객번호
Execution Plan
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=27)
1 0 HASH (GROUP BY) (Cost=4 Card=1 Bytes=27)
2 1 NESTED LOOPS (Cost=3 Card=5 Bytes=135)
3 2 TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) (Cost=2 Card=1 … )
4 3 INDEX (RANGE SCAN) OF '고객_X01' (INDEX) (Cost=1 Card=1)
5 2 TABLE ACCESS (BY INDEX ROWID) OF '거래' (TABLE) (Cost=1 Card=5 … )
6 5 INDEX (RANGE SCAN) OF '거래_X02' (INDEX) (Cost=0 Card=5)
select c.고객번호, c.고객명
, avg(t.거래금액) 평균거래, min(t.거래금액) 최소거래, max(t.거래금액) 최대거래
from 고객 c, 거래 t
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and t.고객번호 = c.고객번호
and t.거래일시 >= trunc(sysdate, 'mm')
group by c.고객번호, c.고객명
- 가장 먼저 엑세스하는 고객_X01 인덱스는 가입일시가 선두컬럼
- 거래_X02는 (고객번호 + 거래일시) 순이라고 가정
- 실행 계획을 보면 고객테이블을 먼저 읽음
- 인덱스를 이용해 전월 이후 가입한 고객만 읽음
- 거래 테이블과 조인할 때는 해당 고객들에 대한 당월 거래만 읽음
- 거래 테이블을 거래_X02 인덱스를 이용해 NL 조인하기 때문
- 단점은 조인에 성공한 전체 집합을 Group By 한 이후에 데이터 출력 가능
- 부분범위 처리가 불가
- 전월 이후 가입한 고객이 매우 많고 당월 거래도 매우 많다면, NL 조인이 좋지 않음
Execution Plan
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=1 Bytes=27)
1 0 HASH (GROUP BY) (Cost=8 Card=1 Bytes=27)
2 1 HASH JOIN (Cost=7 Card=5 Bytes=135)
3 2 TABLE ACCESS (FULL) OF '고객' (TABLE) (Cost=3 Card=1 Bytes=20)
4 2 TABLE ACCESS (FULL) OF '거래' (TABLE) (Cost=3 Card=14 Bytes=98)
- 해시 조인이 더 유리할 수 있음
- 뷰 머징한 거래 테이블을 고객과 해시 조인한 후 Group By
조인 조건 Pushdown
- 메인 쿼리를 실행하면서 조인 조건절 값을 건건이 뷰 안으로 밀어넣는 기능
- VIEW PUSHED PREDICATE 오퍼레이션을 통해 작동 여부를 알 수 있음
select c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래
from 고객 c
, (select
고객번호, avg(거래금액) 평균거래
, min(거래금액) 최소거래, max(거래금액) 최대거래
from 거래
where 거래일시 >= trunc(sysdate, 'mm')
group by 고객번호) t
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and t.고객번호 = c.고객번호
Execution Plan
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=61)
1 0 NESTED LOOPS (Cost=4 Card=1 Bytes=61)
2 1 TABLE ACCESS (BY INDEX ROWID BATCHED) OF '고객' (TABLE) (Cost=2 … )
3 2 INDEX (RANGE SCAN) OF '고객_X01' (INDEX) (Cost=1 Card=1)
4 1 VIEW PUSHED PREDICATE (Cost=2 Card=1 Bytes=41)
5 4 SORT (GROUP BY) (Cost=2 Card=1 Bytes=7)
6 5 TABLE ACCESS (BY INDEX ROWID BATCHED) OF '거래' (TABLE) (Cost=2 … )
7 6 INDEX (RANGE SCAN) OF '거래_X02' (INDEX) (Cost=1 Card=5)
select c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래
from 고객 c
,(select 고객번호, avg(거래금액) 평균거래
, min(거래금액) 최소거래, max(거래금액) 최대거래
from 거래
where 거래일시 >= trunc(sysdate, 'mm')
and 고객번호 = c.고객번호
group by 고객번호) t
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
- 전월 이후 가입한 고객을 대상으로 '건건이' 당월 거래 데이터만 읽어서 조인하고 Group By 수행
- 중간에 멈출 수 있어, 부분범위 처리가 가능
- 뷰를 독립적으로 실행할 때처럼 당월 거래를 모두 읽지 않아도 됨
- 뷰를 머징할 때처럼 조인에 성공한 전체 집합을 Group By 하지 않아도 됨
- push_pred 힌트로 제어하고, 옵티마이저가 뷰를 머징하면 힌트가 작동하지 않으니 no_merge와 함께 사용
Lateral 인라인 뷰, Cross/Outer Apply 조인
- 뷰 안에서 메인쿼리 테이블을 참조하면 ORA-00904
- 인라인 뷰를 Lateral로 선언하면, 인라인 뷰 안에서 메인 쿼리 테이블 컬럼을 참조할 수 있음
select * from 사원 e,
LATERAL (select *
from 조직
where 조직코드 = e.조직코드)
select * from 사원 e,
LATERAL (select *
from 조직
where 조직코드 = e.조직코드)(+)
- Outer 조인이 필요하면, Outer Apply 조인 구문을 사용할 수도 있음
select * from 사원 e
OUTER APPLY (select *
from 조직
where 조직코드 = e.조직코드)
- Cross Apply도 Lateral 인라인 뷰와 기능적으로 동일
select * from 사원 e
CROSS APPLY (select *
from 조직
where 조직코드 = e.조직코드);
- 최근 오라클 버전에선 '조인 조건 Pushdown'이 잘 작동하기 때문에 굳이 사용할 이유가 없음
- 튜닝 과정에서 알 수 없는 이유로 조인 조건 Pushdown이 잘 동작하지 않을 때만 활용
스칼라 서브쿼리 조인
스칼라 서브쿼리의 특징
- 아래처럼 함수를 만들어 쿼리를 실행하면, 함수 안에 있는 쿼리를 메인쿼리 건수만큼 재귀적으로 반복 실행
create or replace function GET_DNAME(p_deptno number) return varchar2
is
l_dname dept.dname%TYPE;
begin
select dname into l_dname from dept where deptno = p_deptno;
return l_dname ;
exception
when others then
return null;
end;
/
select empno, ename, sal, hiredate
, GET_DNAME(e.deptno) as dname
from emp e
where sal >= 2000
- 스칼라 서브쿼리는 메인쿼리 레코드마다 정확히 하나의 값만 반환함
- 메인쿼리 건수만큼 DEPT 테이블을 반복해서 읽는다는 측면에서 함수와 유사
- 함수처럼 재귀적으로 실행하는 구조는 아님
- 컨텍스트 스위칭 없이 메인 쿼리와 서브쿼리를 한 몸체처럼 실행
select empno, ename, sal, hiredate
, (select d.dname from dept d where d.deptno = e.deptno) as dname
from emp e
where sal >= 2000
select e.empno, e.ename, e.sal, e.hiredate, d.dname
from emp e, dept d
where d.deptno(+) = e.deptno
and e.sal >= 2000
- 스칼러 서브쿼리를 사용한 쿼리문은 Outer 조인문처럼 NL 조인 방식으로 실행
- DEPT와 조인에 실패하는 EMP 레코드는 DNAME에 NULL 값을 출력한다는 점도 동일
- 단, 스칼라 서브쿼리는 처리 과정에서 캐싱 작용이 일어남
스칼라 서브쿼리 캐싱 효과
- 스칼라 서브쿼리로 조인하면 오라클은 조인 횟수를 최소화하려고 입력 값과 출력 값을 내부 캐시(Query Execution Cache)에 저장
- 조인할 때마다 캐시에서 '입력 값'을 찾아보고, 찾으면 '출력값'을 반환
- 캐시에 찾지 못할 때만 조인을 수행
- 입력값 = 서브쿼리 안에서 참조하는 메인 쿼리의 컬럼 값
select empno, ename, sal, hiredate
, (
select d.dname
from dept d
where d.deptno = e.deptno
)
from emp e
where sal >= 2000
- 스칼라 서브쿼리 캐싱은 필터 서브쿼리 캐싱과 같은 기능
- 메인쿼리 집합이 아무리 커도 조인할 데이터를 대부분 캐시에서 찾는다면, 조인 수행횟수를 최소화해 성능 향상에 도움이 됨
- 캐싱은 쿼리 단위로 이뤄짐
- 쿼리를 시작할 때 PGA 메모리에 공간 할당
- 쿼리를 수행하면서 공간을 채워나감
- 쿼리를 마치는 순간 공간을 반환
- SELECT-LIST에 사용한 함수는 메인쿼리 결과 건수만큼 반복 수행되는데, 스칼라 서브쿼리를 덧씌우면 호출 횟수를 최소화할 수 있음
select empno, ename, sal, hiredate
, (select GET_DNAME(e.deptno) from dual) dname
from emp e
where sal >= 2000
스칼러 서브쿼리 캐싱의 부작용
- 캐시 공간은 늘 부족하고, 스칼라 서브쿼리에 사용하는 캐시도 매우 작은 메모리 공간
- 8i, 9i 기준으로 256개 엔트리 캐싱
- 10g 이후로는 입력과 출력 값 크기, _query_execution_cache_max_size 파라미터에 의해 사이즈 결정
- 스칼라 서브쿼리 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 작을 때 효과
- 반대의 경우 캐시를 매번 확인하는 비용 때문에 오히려 성능이 나빠지고 CPU와 메모리 사용률만 높아짐
- 거래구분코드로 20개 값이 존재하는 경우 모든 값을 캐시에 저장하고도 남음
- 메인쿼리에서 50000개의 거래를 읽는 동안 거래구분코드별 조인 엑세스는 최소 한 번씩만 발생
- 이후 모두 캐시에서 데이터를 찾게 되므로 조인 성능을 높이는 데 도움
select 거래번호, 고객번호, 영업조직ID, 거래구분코드
, (select 거래구분명 from 거래구분 where 거래구분코드 = t.거래구분코드) 거래구분명
from 거래 t
where 거래일자 >= to_char(add_months(sysdate, -3), 'yyyymmdd')
- 고객이 100만명인 경우, 캐시에 고객번호를 다 담을 수 없음
- 50000개의 거래를 읽는 동안 캐시에서 대부분의 데이터를 찾지 못해 조인해야 함
select 거래번호, 고객번호, 영업조직ID, 거래구분코드
, (select 고객명 from 고객 where 고객번호 = t.고객번호) 고객명
from 거래 t
where 거래일자 >= to_char(add_months(sysdate, -3), 'yyyymmdd')
- 함수 호출 횟수를 줄이기 위해 스칼라 서브쿼리를 덧씌우는 경우
- 체결 테이블에 입력된 매도/매수계좌번호가 무수히 많다면 스칼라 서브쿼리 캐싱 효과를 기대할 수 없고, 오히려 성능이 떨어짐
select 매도회원번호
, 매수회원번호
, 매도투자자구분코드
, 매수투자자구분코드
, 체결유형코드
, 매도계좌번호, (select acnt_nm(매도계좌번호) from dual) 매도계좌명
, 매수계좌번호, (select acnt_nm(매수계좌번호) from dual) 매수계좌명
, 체결시각
, 체결수량
, 체결가
, 체결수량 * 체결가 체결금액
from 체결
where 종목코드 = :종목코드
and 체결일자 = :체결일자
and 체결시각 between sysdate-10/24/60 and sysdate
- 메인 쿼리 집합이 매우 작은 경우, 스칼라 서브쿼리 캐싱이 큰 도움이 안 됨
- 서브쿼리 캐싱은 쿼리 단위로 쓰고 버림
- 다음 예시는 고객당 계좌가 많지 않아 캐싱 효과를 거의 기대할 수 없음
- 쓰지도 않을 캐시를 할당해서 값을 채웠다가 바로 버림
select 계좌번호, 계좌명, 고객번호, 개설일자, 계좌종류구분코드, 은행개설여부, 은행연계여부
, (select brch_nm(관리지점코드) from dual) 관리지점명
, (select brch_nm(개설지점코드) from dual) 개설지점명
from 계좌
where 고객번호 = :고객번호
두개 이상의 값 반환
select c.고객번호, c.고객명
,(select round(avg(거래금액), 2) 평균거래금액
from 거래
where 거래일시 >= trunc(sysdate, 'mm')
and 고객번호 = c.고객번호)
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
Execution Plan
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=4 Bytes=80)
1 0 SORT (AGGREGATE) (Card=1 Bytes=7)
2 1 TABLE ACCESS (BY INDEX ROWID BATCHED) OF '거래' (TABLE) (Cost=2 … )
3 2 INDEX (RANGE SCAN) OF '거래_X02' (INDEX) (Cost=1 Card=5)
4 0 TABLE ACCESS (FULL) OF '고객' (TABLE) (Cost=3 Card=4 Bytes=80)
5 4 INDEX (RANGE SCAN) OF '고객_X01' (INDEX) (Cost=1 Card=1)
- 메인쿼리가 실행계획 아래쪽에 있고, 스칼라 서브쿼리 부분은 위쪽
- 표현방식은 NL조인과 다르지만, 프로세싱 과정은 같음
- NL조인처럼 부분범위 처리도 가능 - 캐싱 효과가 있다는 차이점
- 스칼라 서브쿼리는 두 개 이상의 값을 반환할 수 없음
select c.고객번호, c.고객명
,(select avg(거래금액), min(거래금액), max(거래금액)
from 거래
where 거래일시 >= trunc(sysdate, 'mm')
and 고객번호 = c.고객번호)
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
- 부분범위 처리 가능한 스칼라 서브쿼리의 장점을 이용하고자 쿼리를 아래처럼 작성하면, 거래 테이블에서 같은 데이터를 반복해서 읽는 비효율
select c.고객번호, c.고객명
,(select avg(거래금액) from 거래
where 거래일시 >= trunc(sysdate, 'mm') and 고객번호 = c.고객번호)
,(select min(거래금액) from 거래
where 거래일시 >= trunc(sysdate, 'mm') and 고객번호 = c.고객번호)
,(select max(거래금액) from 거래
where 거래일시 >= trunc(sysdate, 'mm') and 고객번호 = c.고객번호)
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
select 고객번호, 고객명
, to_number(substr(거래금액, 1, 10)) 평균거래금액
, to_number(substr(거래금액, 11, 10)) 최소거래금액
, to_number(substr(거래금액, 21)) 최대거래금액
from (
select c.고객번호, c.고객명
, (select lpad(avg(거래금액), 10) || lpad(min(거래금액), 10) || max(거래금액)
from 거래
where 거래일시 >= trunc(sysdate, 'mm')
and 고객번호 = c.고객번호) 거래금액
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
)
create or replace type 거래금액_T as object
( 평균거래금액 number, 최소거래금액 number, 최대거래금액 number )
/
select 고객번호, 고객명
, 거래.금액.평균거래금액, 거래.금액.최소거래금액, 거래.금액.최대거래금액
from (
select c.고객번호, c.고객명
, (select 거래금액_T( avg(거래금액), min(거래금액), max(거래금액) ) 금액
from 거래
where 거래일시 >= trunc(sysdate, 'mm')
and 고객번호 = c.고객번호) 거래
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
)
- 두 개 이상의 값을 반환하고 싶을 때, 인라인 뷰를 사용하면 편하긴 함
select c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래
from 고객 c
, (select 고객번호, avg(거래금액) 평균거래
, min(거래금액) 최소거래, max(거래금액) 최대거래
from 거래
where 거래일시 >= trunc(sysdate, 'mm')
group by 고객번호) t
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and t.고객번호(+) = c.고객번호
Execution Plan
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=27)
1 0 HASH (GROUP BY) (Cost=4 Card=1 Bytes=27)
2 1 NESTED LOOPS (OUTER) (Cost=3 Card=5 Bytes=135)
3 2 TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) (Cost=2 Card=1 ...)
4 3 INDEX (RANGE SCAN) OF '고객_X01' (INDEX) (Cost=1 Card=1)
5 2 TABLE ACCESS (BY INDEX ROWID) OF '거래' (TABLE) (Cost=1 Card=5 ...)
6 5 INDEX (RANGE SCAN) OF '거래_X02' (INDEX) (Cost=0 Card=5)
- 뷰를 사용하면
- 뷰가 머징되지 않았을 때 당월 거래 전체를 읽어야 함
- 뷰가 머징되면 Group By 때문에 부분범위 처리가 안 됨
- 11g 이후로 '조인 조건 PushDown' 을 통해 인라인 뷰를 편히 사용할 수 있음
select c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래
from 고객 c
, (select
고객번호, avg(거래금액) 평균거래
, min(거래금액) 최소거래, max(거래금액) 최대거래
from 거래
where 거래일시 >= trunc(sysdate, 'mm')
group by 고객번호) t
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and t.고객번호(+) = c.고객번호
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=61)
1 0 NESTED LOOPS (OUTER) (Cost=4 Card=1 Bytes=61)
2 1 TABLE ACCESS (BY INDEX ROWID BATCHED) OF '고객' (TABLE) (Cost=2 ...)
3 2 INDEX (RANGE SCAN) OF '고객_X01' (INDEX) (Cost=1 Card=1)
4 1 VIEW PUSHED PREDICATE (Cost=2 Card=1 Bytes=41)
5 4 SORT (GROUP BY) (Cost=2 Card=1 Bytes=7)
6 5 TABLE ACCESS (BY INDEX ROWID BATCHED) OF '거래' (TABLE) (Cost=2 ...)
7 6 INDEX (RANGE SCAN) OF '거래_X02' (INDEX) (Cost=1 Card=5)
스칼러 서브쿼리 Unnesting
- 스칼라 서브쿼리도 NL 방식으로 조인하므로 캐싱 효과가 크지 않으면 랜덤 I/O 부담이 있음
- 특히, 병렬(Parallel) 쿼리에서는 될 수 있으면 스칼라 서브쿼리를 사용하지 않아야 함
- 12c부터 스칼라 서브쿼리도 Unnesting이 가능해져, 옵티마이저가 사용자 대신 자동으로 쿼리를 변환해줄 수 있음
- _optimizer_unnest_scalar_sq가 true이면 스칼라 서브쿼리 Unnesting 여부를 옵티마이저가 결정
- false면 옵티마이저가 이 기능을 사용하지 않지만 사용자가 unnest 힌트로 유도 가능
select c.고객번호, c.고객명
,(select round(avg(거래금액), 2) 평균거래금액
from 거래
where 거래일시 >= trunc(sysdate, 'mm')
and 고객번호 = c.고객번호)
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
Execution Plan
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=4 Bytes=184)
1 0 HASH JOIN (OUTER) (Cost=7 Card=4 Bytes=184)
2 1 TABLE ACCESS (FULL) OF '고객' (TABLE) (Cost=3 Card=4 Bytes=80)
3 1 VIEW OF 'SYS.VW_SSQ_1' (VIEW) (Cost=4 Card=3 Bytes=78)
4 3 HASH (GROUP BY) (Cost=4 Card=3 Bytes=21)
5 4 TABLE ACCESS (FULL) OF '거래' (TABLE) (Cost=3 Card=14 Bytes=98)
Execution Plan
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=15 Bytes=405)
1 0 HASH (GROUP BY) (Cost=7 Card=15 Bytes=405)
2 1 HASH JOIN (OUTER) (Cost=6 Card=15 Bytes=405)
3 2 TABLE ACCESS (FULL) OF '고객' (TABLE) (Cost=3 Card=4 Bytes=80)
4 2 TABLE ACCESS (FULL) OF '거래' (TABLE) (Cost=3 Card=14 Bytes=98)
- 12g 이후 스칼라 서브쿼리 Unnesting으로 인해 일부 쿼리에 문제가 생겼을 때, _optimzer_unenest_scalar_sq 파라미터를 false로 설정하지 않고 no_unnest 힌트를 이용해 부분적으로 문제 해결 가능
select c.고객번호, c.고객명
,(select round(avg(거래금액), 2) 평균거래금액
from 거래
where 거래일시 >= trunc(sysdate, 'mm')
and 고객번호 = c.고객번호)
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
Execution Plan
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=4 Bytes=80)
1 0 SORT (AGGREGATE) (Card=1 Bytes=7)
2 1 TABLE ACCESS (BY INDEX ROWID) OF '거래' (TABLE) (Cost=2 ...)
3 2 INDEX (RANGE SCAN) OF '거래_X02' (INDEX) (Cost=1 Card=5)
4 0 TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) (Cost=3 Card=4 Bytes=80)
5 4 INDEX (RANGE SCAN) OF '고객_X01' (INDEX) (Cost=1 Card=1)