■서브쿼리 문장 튜닝
*서브쿼리 문장의 튜닝 방법 2가지
1. 순수하게 서브쿼리로 수행하면서 튜닝 : no_unnest (힌트)
-서브쿼리 부터 수행 : push_subq
-메인쿼리 부터 수행 : no_push_subq
*서브쿼리절에 힌트를 쓴다.
2. 서브쿼리를 조인으로 변경하면서 튜닝 : unnest
-in 사용시
1. nested loop semi 조인 : nl_sj
2. hash semi 조인 : hash_sj
3. merge semi 조인 : merge_sj
-not in 사용시
1. nested loop anti 조인 : nl_aj
2. hash anti 조인 : hash_aj
3. merge anti 조인 : merge_aj
문제68. SCOTT 과 같은 월급을 받는 사원의 이름,월급을 출력하시오
select ename, sal
from emp
where sal in (select sal
from emp
where ename = 'SCOTT')
and ename ^= 'SCOTT';
오답:
SELECT e.ename, ee.sal
from ( select sal
from emp
where ename = 'SCOTT') ee, emp e;
select /*+ QB_NAME(main) */ ENAME, SAL
FROM EMP
WHERE SAL = (SELECT /*+ QB_NAME(sub) */ SAL
FROM EMP
WHERE ENAME = 'SCOTT');
실행계획-실제실행계획-ADVANCED ALLSTATS LAST
2번 TABLE ACCESS FULL 이 제일 먼저 수행되었고,
그것이 SUB 다. (즉 SUB가 먼저 수행 됨)
※설명:
QB_NAME(main) : 쿼리에 이름을 붙여주는 힌트
QB_NAME(sub) : 서브에 이름을 붙여주는 힌트
*실행계획의 종류 2가지?
1. 예측 실행계획: SQL 을 실행해보기전에 미리 예측하는 것.
2. 실제 실행계획: SQL 을 실행하고 실행할 때 사용했던
실행계획.
*QB_NAME 힌트에 대한 결과는 실제 실행계획에서만
볼 수 있다.
문제69. 위의 실행계획이 main query 부터 수행되게 하시오
select /*+ QB_NAME(main) */ ENAME, SAL
FROM EMP
WHERE SAL = (SELECT /*+ QB_NAME(sub) no_push_subq */ SAL
FROM EMP
WHERE ENAME = 'SCOTT');
화면 캡처: 2018-11-12 오후 2:31
*no_push_subq 는 메인부터 수행하라는 힌트
(서브쿼리 절에 쓴다)
2번 TABLE ACCESS FULL 부터 수행했고
그것이 MAIN 임을 알 수 있다.
문제70. 아래의 SQL을 서브쿼리부터 수행되게도 해보고
메인쿼리부터 수행되게도 해보시오.
select count(*)
from sales
where time_id in(select time_id
from times100
where week_ending_day_id = 1581);
화면 캡처: 2018-11-12 오후 2:39
옵티마이져가 판단하에 더 성능 좋은걸로
자기마음대로 조인탔다.
no_unnest : 서브쿼리 하라.(서브쿼리로 강하게 감싸라)
↓
select count(*)
from sales
where time_id in(select /*+ no_unnest */
time_id
from times100
where week_ending_day_id = 1581);
옵티마이져 마음대로 조인타던걸 unnest 로 막아버림.
1. 서브쿼리부터
select count(*)
from sales
where time_id in(select /*+ no_unnest push_subq */
time_id
from times100
where week_ending_day_id = 1581);
2. 메인쿼리부터
select count(*)
from sales
where time_id in(select /*+ no_unnest no_push_subq */
time_id
from times100
where week_ending_day_id = 1581);
no_unnest 랑 (no_)push_subq 는 set다. 함께 다닌다.
문제72. 아래의 sql 이 순수하게 서브쿼리로 수행되게 하고
서브쿼리부터 실행되게 하시오
select ename, sal, job
from emp
where deptno in (select deptno
from dept);
서브쿼리로, 서브쿼리부터 수행:
select ename, sal, job
from emp
where deptno in (select /*+ unnest push_subq */
deptno
from dept);
문제73. 위의 sql 이 메인쿼리부터 수행되게 하시오.
서브쿼리로 감싸고, 메인쿼리부터 수행하라:
select ename, sal, job
from emp
where deptno in (select /*+ unnest no_push_subq */
deptno
from dept);
***서브쿼리부터 수행하는게 더 빠르다. unnest push_subq
**만약 no_unnest 안쓰면?
select ename, sal, job
from emp
where deptno in (select /*+ push_subq */ deptno
from dept);
※no_unnest 없이 쓰는 push_subq 는 의미없다.
옵티마이져가 hash join 타버림.
그래서 push_subq 나 no_push_subq 힌트를 사용하려면
no_unnest 와 같이 사용해야 한다.
문제73. 안혜진 학생과 같은 전공인 학생들의
이름, 전공을 출력하시오
select ename, major
from emp2
where major in (select /*+ unnest push_subq */
major
from emp2
where ename = '안혜진' );
IN 으로 연결해서
대용량 값이 나올거라 예상하는 옵티마이저가
HASH 로 돌려버렸다.
select ename, major
from emp2
where major = (select /*+ unnest push_subq */
major
from emp2
where ename = '안혜진' );
= 로 연결해서
단일연산자가 나올거라 예상하는 옵티마이저가
HASH 없이 돌렸다.
문제74. 아래의 SQL 이 조인으로 풀리게 힌트를 주고
조인 방법 중에 NESTED LOOP 조인이 되게 하시오
select ename, major
from emp2
where major in (select /*+ unnest nl_sj */
major
from emp2
where ename = '안혜진' );
서브쿼리를 조인으로 변경하면서 튜닝 : unnest
-in 사용시
1. nested loop semi 조인 : nl_sj
2. hash semi 조인 : hash_sj
3. merge semi 조인 : merge_sj
※ 서브쿼리로 수행되는 SQL 이 양쪽 다 대용량이면
서브쿼리로 수행되는 것 보다는 조인으로 수행되는 게
더 성능이 좋다.
조인방법 중에 해쉬조인을 사용할 수 있기 때문이다.
select ename, major
from emp2
where major in (select /*+ unnest hash_sj */
major
from emp2
where ename = '안혜진' );
※세미조인 (SEMI JOIN) ? 절반의 조인.
완전한 조인이 아니라 절반의 조인인 이유는
조인 방법은 3가지 중에 아무거나 다 사용할 수 있는데
조인 순서는 고정이 된다.
(메인쿼리 테이블--->서브쿼리 테이블로 고정 됨)
문제75. 위의 HASH 세미 조인의 조인순서를
DEPT ----> EMP 로 변경하시오
select ename, sal, job
from emp
where deptno in (select /*+ unnest hash_sj
swap_join_inputs(d) */
deptno
from dept d);
문제76. 아래의 sql 을 hash join right semi 조인이
되게 하시오
튜닝전:
select count(*)
from sales100
where time_id in (select /*+ no_unnest
no_push_subq */
time_id
from times100
where week_ending_day_id = 1581 );
튜닝후:
select count(*)
from sales100
where time_id in (select /*+ unnest
hash_sj
swap_join_inputs(t) */
time_id
from times100 t
where week_ending_day_id = 1581 );
문제77. 아래와 같이 실행계획이 나오게 하시오
화면 캡처: 2018-11-12 오후 4:13
select /*+ leading (s t) */ count(*)
from SALES100 s
where time_id in (select /*+ unnest hash_sj */
time_id
from times100 t
where week_ending_day_id=1581);
**왠만한 힌트는
서브쿼리 쪽인데, 안되면 메인쿼리에도 써보고 하는거다.
※서브쿼리문 튜닝 방법 정리
서브쿼리문의 데이터가 적을 때는 순수하게 서브쿼리로
수행되는게 좋은 성능을 보이나,
대용량 테이블 일 경우에는
해시세미조인으로 수행되게끔 힌트를 주면 유리하다.
그리고 swap_join_inputs 힌트를 이용해서
작은 테이블 부터 드라이빙 되게끔 조정할 수 있다.
'sql_tuning' 카테고리의 다른 글
| 6. 병렬처리 (0) | 2019.04.02 |
|---|---|
| 5. 파티셔닝 (0) | 2019.04.02 |
| 3. 조인 튜닝 (0) | 2019.04.02 |
| 2-1. 인덱스 엑세스 방법 (0) | 2019.04.02 |
| 2. 인덱스 튜닝 (0) | 2019.04.02 |
