본문 바로가기
빅데이터과정/SQL TUNING

#34_140731_TUNING_JOIN TUNING

하히후후후 2014. 7. 31. 16:36
728x90

# JOIN TUNING






nested loop join
hash join
sort merge join
실행계획 위쪽 테이블
driving table
hash table
driving table
실행계획 아래쪽 테이블
driven table
probe table
driven table


l join tuning 할때 중요한 점

1.     조인 순서
- ordered
- leading

2.     조인 방법
- use_nl
- use_hash
- use_merge

3.     주의사항
- 대용량 테이블을 조인할 떄는 hash join 이나 sort merge join 이 유리하다
- 그래서 hash join 이나 sort merge join 은 주로 index scan 보다는 full table scan이 더 유리하다
- 적은양의 테이블을 join 할 떄는 nested loop join으로 해야 한다. 왜냐하면 hash join을 쓰면hash table을 이용하여 메모리 사용률이 높아진다




l 조인 문법과 조인의 방법은 서로 틀리다

l 조인 문법 : join을 구사하는 문법

1.     1999 ansi 문법
- full outer join

2.     오라클 조인문법
- equi join
- non equi join
- outer join
- self join


l 조인 방법 : 조인의 실행방법

1.     nested loop join
2.     hash join
3.     sort merge join



l join tuning 할때 중요한 점

1.     조인 순서
- ordered
- leading

2.     조인 방법
- use_nl
- use_hash
- use_merge

3.     주의사항
- 대용량 테이블을 조인할 떄는 hash join 이나 sort merge join 이 유리하다
- 그래서 hash join 이나 sort merge join 은 주로 index scan 보다는 full table scan이 더 유리하다
- 적은양의 테이블을 join 할 떄는 nested loop join으로 해야 한다. 왜냐하면 hash join을 쓰면hash table을 이용하여 메모리 사용률이 높아진다

l 조인 순서를 정하려면

1.     만약에 num_rows가 제대로 나오지 않는다면 통계정보를 수집해야 한다

SQL> exec dbms_stats.gather_table_stats('SCOTT', 'SALES100');
SQL> exec dbms_stats.gather_table_stats('SCOTT', 'TIMES100');
SQL> exec dbms_stats.gather_table_stats('SCOTT', 'PRODUCTS100');
SQL> select table_name, num_rows, last_analyzed
from user_tables
where table_name in ('SALES100','TIMES100','PRODUCTS100');
TIMES100
1826
2014-07-31 오후 2:28:43
SALES100
918843
2014-07-31 오후 2:28:37
PRODUCTS100
72
2014-07-31 오후 2:28:43

- 전체건수를 조사하는 이유는 where 절로 인해 필터링 된 데이터가 전체건수에 비해 몇 건인지 조회해서 full tatble scan을 하는 경우도 생기기 때문이다
- 위 표 처럼 결과가 나오면 각각의 where 절에 의해서 필털이 된 데이터들과 비율을 생각해서 어떤 join 방법을 선택할지 정하면 된다


2.     테이블의 건수를 확인

- SQL> select table_name, num_rows, last_analyzed
from user_tables
where table_name in ('SALES100','TIMES100','PRODUCTS100');

select /*+ leading(p s t)  use_hash(s) use_hash(t) */
  p.prod_name, t.CALENDAR_YEAR, sum(s.amount_sold)
  from   sales100  s, times100  t, products100  p
  where   s.time_id = t.time_id
  and  s.prod_id = p.prod_id
    and  t.CALENDAR_YEAR in (2000,2001)   -- 731
    and p.prod_name like 'Deluxe%'     --1
    group  by  p.prod_name, t.calendar_year;



- 수행시간 : 12초
- 각각의 조건절에 필터링 된 데이터를 조회해보면 times100 테이블이 731건, products100 테이블이 1건이 나온다

CREATE INDEX SALES100_TIME_ID ON SALES100(time_id);
CREATE INDEX sales100_prod_id ON SALES100(prod_id);
CREATE INDEX products100_prod_id ON PRODUCTS100(prod_id);
CREATE INDEX times100_time_id ON TIMES100(time_id);

select /*+ leading(p s t)  use_nl(s) use_nl(t) */
  p.prod_name, t.CALENDAR_YEAR, sum(s.amount_sold)
  from   sales100  s, times100  t, products100  p
  where   s.time_id = t.time_id
  and  s.prod_id = p.prod_id
    and  t.CALENDAR_YEAR in (2000,2001)
    and p.prod_name like 'Deluxe%'
    group  by  p.prod_name, t.calendar_year;


- 현재 연결고리는 times100 --- sales100 --- products100 이런 순이기 때문에 sales 바로 다음에 products가 나오면 오래 걸린다
- hash join 은 full table scan을 주로 하고 nested loop join 은 index scan을 주로한다.
- product 테이블은 1건이 나오기 떄문에 그 한건에 대한 prod_id에 인덱스가 있어야 빨라진다
- 실질적으로는 조인되는 모든 컬럼에 index가 걸려야 한다
- 결국은 테이블 조회 건수가 제일적은 products100 테이블을 먼저하고 그 이후에는 바로 조인 순서에 의해서 times를 연결할 수 없으니 sales100을 수행하고 마지막으로times100을 수행한다









  • NESTED LOOP JOIN


1.     Nested loop join
(힌트 : /*+ use_nl(조인할 테이블명 리스트) */ )

- 조인하려는 데이터의 양이 작을 때 유리한 조인이고 주로 인덱스를 통해서 데이터가 엑세스될 때 성능이 좋은 조인방법



SELECT /*+ use_nl(e d) */ e.ename, e.sal, d.loc
FROM EMP e, DEPT d
WHERE e.deptno=d.deptno;
- 괄호안에 emp 혹은 dept라고 쓰는 실수를 조심해야한다
- 무조건 e, d 와 같은 alias를 써야한다



- 수행순서는 emp 수행하고 dept 수행하고 다음에 nested loop 조인을 수행한다








     - 10번 부서번호를 조회하고 10번 부서번호의 위치를 알아내고 그 다음 30번 위치를 알아                            
        내는 방식으로 전부다 진행한다

- 본래는 emp 테이블을 먼저 읽어들이는 것보다는 dept 테이블을 읽어들이고 emp 테이블을 읽어들이는 것이 더 빠르다
- 왜냐하면 emp 테이블을 먼저 읽으면 14번 조인을 수행해야 하지만 dept 테이블은 4번 조인을 수행하면 되기 때문이다




l 조인순서에 대한 힌트 2가지

1.     ordered : from 절에서 기술한 테이블 순서대로 조인
2.     leading : leading 절 힌트안에 쓴 테이블 순서대로 조인



n ordered 

SELECT /*+ ordered use_nl(e d) */ e.ename, e.sal, d.loc
FROM EMP e, DEPT d
WHERE e.deptno=d.deptno;



db block gets        0
consistent gets       105
physical reads        0
SELECT /*+ ordered use_nl(e d) */ e.ename, e.sal, d.loc
FROM DEPT d, EMP e
WHERE e.deptno=d.deptno;




- from 절의 순서를 바꾼 결과 수행결과가 ordered 구문에 의해서 바뀐 것을 확인할 수 있다
- 실행계획을 확인해보면 consistend gets의 갯수가 줄어든 것 또한 확인할 수 있다
db block gets        0
consistent gets       35
physical reads        0




n leading

SELECT /*+ leading(e d) use_nl(e d) */ e.ename, e.sal, d.loc
FROM DEPT d, EMP e
WHERE e.deptno=d.deptno;


SELECT /*+ leading(d e) use_nl(e d) */ e.ename, e.sal, d.loc
FROM DEPT d, EMP e
WHERE e.deptno=d.deptno;



- leading 은 힌트에만 쓰면 되지만 ordered는 from 절을 바꿔줘야 하기 때문에 테이블이 많아지면 복잡해진다
- 그렇기 때문에 leading을 더많이 쓴다




문제42. 아래의 SQL의 조인순서를 결정하시오(조인방법은 nested loop join으로 시도)

# 튜닝 전
SELECT e.ename, e.sal, e.job, d.loc
FROM EMP e, DEPT d
WHERE e.deptno = d.deptno AND ename = 'SCOTT';

# 튜닝 후
SELECT /*+ leading(e d) use_nl(d) */ e.ename, e.sal, e.job, d.loc
FROM EMP e, DEPT d
WHERE e.deptno = d.deptno AND ename = 'SCOTT';
- use_nl(d) 에서 e와 d를 둘다 적어줘도 되고 d 하나만 적어줘도 된다
- 속도에는 차이가 없도 편의상 그렇게 하는 것이다






문제43. 아래의 SQL의 조인 순서를 결정하시오

# 튜닝 전
SELECT /*+ leading(d e) use_nl(e) */ e.ename, e.sal, e.job, d.loc
FROM EMP e, DEPT d
WHERE e.deptno = d.deptno AND job = 'SALESMAN'
AND d.loc = 'CHICAGO';

# 튜닝 후
SELECT /*+ leading(d e) use_nl(e) */ e.ename, e.sal, e.job, d.loc
FROM EMP e, DEPT d
WHERE e.deptno = d.deptno AND job = 'SALESMAN'
AND d.loc = 'CHICAGO';
- 위 조인문장의 성능을 높이기 위해서는 조인되는 연결고리에 양쪽에 인덱스가 있어야 한다
create index emp_deptno on emp(deptno);
create index dept_deptno on dept(deptno);








문제44. 아래의 SQL을 튜닝하시오

# 튜닝 전
select /*+ leading(s t) use_nl(t)   */
  t.CALENDAR_YEAR, sum(s.amount_sold)
from   sales100  s, times100  t
where   s.time_id = t.time_id and  t.week_ending_day_id=1581
group  by  t.calendar_year;

- 수행시간 : 113초

db block gets        0
consistent gets       50540802
physical reads        4480
# 튜닝 후
select /*+ leading(t s) use_nl(s)   */
  t.CALENDAR_YEAR, sum(s.amount_sold)
from   sales100  s, times100  t
where   s.time_id = t.time_id and  t.week_ending_day_id=1581
group  by  t.calendar_year;



- 수행시간 : 0.89초

db block gets        0
consistent gets       31114
physical reads        31031






문제45. 사원이름, 월급, 부서윛, 급여등급(grade)을 출력하고 조인순서를 dept, emp, salgrade 시키시오.


# 튜닝 전
SELECT e.ename, e.sal, d.loc, s.grade
FROM EMP e, DEPT d, SALGRADE s
WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal;

- 수행시간 : 113초

db block gets        0
consistent gets       50540802
physical reads        4480
# 튜닝 후
SELECT /*+ leading(d e s) use_nl(e) use_nl(s) */ e.ename, e.sal, d.loc, s.grade
FROM EMP e, DEPT d, SALGRADE s
WHERE e.deptno=d.deptno AND e.sal BETWEEN s.losal AND s.hisal;
- 만약에 s를 hash로 바꾸고 싶다면 use_nl(s)를 hash_nl(s)로 바꾸기가 쉽기 때문이다



- 수행시간 : 0.89초

db block gets        0
consistent gets       31114
physical reads        31031




문제80. 아래의 SQL을 튜닝하는데 nested loop join 으로 수행되도록 하고 nested loop 조인으로 속도가 개선될 수 있도록 인덱스를 생성하고 조인순서도 직접 결정하시오

create table sales600
as
select * from. sh.sales;

create table customers600
as
select * from sh.customers;

# 튜닝 전
SELECT /*+ leading(c) use_nl(s) */ COUNT(*)
     FROM sales600 s, customers600 c
     WHERE s.cust_id = c.cust_id
     AND c.country_id = 52790 
     AND s.time_id BETWEEN TO_DATE('1999/01/01','YYYY/MM/DD') 
                       AND TO_DATE('1999/12/31','YYYY/MM/DD') ;
- 10분 이상 수행
# 튜닝 후
SELECT COUNT(*)
FROM customers600
WHERE country_id = 52790;
- 18520

SELECT COUNT(*)
FROM sales600
WHERE time_id BETWEEN TO_DATE('1999/01/01','YYYY/MM/DD') 
                       AND TO_DATE('1999/12/31','YYYY/MM/DD')
- 247945

CREATE INDEX sales600_cust_id ON sales600(cust_id);
CREATE INDEX customers600_cust_id ON customers600(cust_id);
- join 양쪽에 index 걸어준다

SELECT /*+ leading(c s) use_nl(s) */ COUNT(*)
     FROM sales600 s, customers600 c
     WHERE s.cust_id = c.cust_id
     AND c.country_id = 52790 
     AND s.time_id BETWEEN TO_DATE('1999/01/01','YYYY/MM/DD') 
                       AND TO_DATE('1999/12/31','YYYY/MM/DD');
- customers600 테이블이 훨씬 작기 크기기 작기 때문에 실행순서는customers600 테이블이 먼저다





- 15초 수행
db block gets        0
consistent gets       456791
physical reads        173672
SELECT COUNT(*)
FROM customers600;
- 55500
- 약 18000개와 총 55500 이기 때문에 약 30%가 넘는다
- 10~20% 정도를 인덱스 스캔으로 권장하고 있다

SELECT COUNT(*)
FROM sales600;
- 918843
- 약 240000개와 910000 이기 때문에 25%정도 된다

CREATE INDEX customers600_country_id ON customers600(country_id);

SELECT /*+ index(customers600 customers600_counrty_id) leading(c s) use_nl(s) */ COUNT(*)
     FROM sales600 s, customers600 c
     WHERE s.cust_id = c.cust_id
     AND c.country_id = 52790 
     AND s.time_id BETWEEN TO_DATE('1999/01/01','YYYY/MM/DD') 
                       AND TO_DATE('1999/12/31','YYYY/MM/DD');
- 30%는 full table scan을 할지 index scan을 할지 애매해서 index를 만들어서 수행해본 결과 수행 시간에는 별반 차이가 없는 것을 알 수 있다


-  약 15 초 수행
db block gets        0
consistent gets       456794
physical reads        166874



문제82. 아래의 SQL을 수행하는데 굵은 글씨로 된 부분이 있는 것이 빠를것이가 없는 것이 더 빠를 것인가

SELECT /*+ leading(c) use_nl(s) */ COUNT(*)
     FROM sales600 s, customers600 c
     WHERE s.cust_id = c.cust_id
     AND c.country_id = 52790 
     AND s.time_id BETWEEN TO_DATE('1999/01/01','YYYY/MM/DD') 
                       AND TO_DATE('1999/12/31','YYYY/MM/DD') ;

- 없는 것이 더 빠르다
- 왜냐하면 두개의 테이블을 조인하면 그 데이터가 맞는 데이터가 되는 것이지만 between문장이 있어서 한번더 검색을 해줘야 한기 때문이다






  • HASH JOIN




l Hash join
( 힌트 : /*+ use_hash(테이블명) */  )

- select e.ename, d.loc, e.sal
from emp e, dept d
where e.deptno = d.deptno;
- 메모리로 로드되는 테이블 : 해쉬 테이블
- 디스크에서 탐색되는 테이블 : probe 테이블
- 메모리에 테이블의 data를 올려놓고 메모리에서 조인하는 방법 대용량 테이블끼리 조인할 때 유리한 조인방법
- 새로운 메모리의 주소를 hash value 라고 한다
- 작은량의 데이터를 메모리에 올리고 다른 테이블과 해쉬함수를 이용해서 조인해나간다



l 해쉬조인의 성능을 높이기 위한 방법

- 조인하려는 테이블중 작은 테이블 또는 조건에 의해서 걸러진 데이터의 양이 작은쪽이 메모리로 로드되서 해쉬 테이블을 구성해야 한다





문제48. 아래의 SQL을 튜닝하시오

# 튜닝 전
select /*+ leading(t s p)  use_nl(s) use_nl(p) */
  p.prod_name, t.CALENDAR_YEAR, sum(s.amount_sold)
  from   sales100  s, times100  t, products100  p
  where   s.time_id = t.time_id
  and  s.prod_id = p.prod_id
    and  t.CALENDAR_YEAR in (2000,2001)
    and p.prod_name like 'Deluxe%'
    group  by  p.prod_name, t.calendar_year;

- 수행시간 : 69초

db block gets        0
consistent gets       5211758
physical reads        3240523
# 튜닝 후
select /*+ leading(t s p)  use_hash(s) use_hash(p) */
  p.prod_name, t.CALENDAR_YEAR, sum(s.amount_sold)
  from   sales100  s, times100  t, products100  p
  where   s.time_id = t.time_id
  and  s.prod_id = p.prod_id
    and  t.CALENDAR_YEAR in (2000,2001)
    and p.prod_name like 'Deluxe%'
    group  by  p.prod_name, t.calendar_year;

- 수행시간 : 12초

db block gets        0
consistent gets       4496
physical reads        4433





문제50. 아래의 SQL의 조인 순서(dept, emp, bonus) 조인방법을 해쉬로 만드시오

SQL> drop table bonus purge;
SQL> create table bonus
    as
    select empno, ename, sal*1.4 as bonus
    from emp;

SELECT e.ename, e.sal, d.loc, B.BONUS
FROM emp e, DEPT d, BONUS b
WHERE e.deptno=d.deptno AND e.empno=b.empno;


SELECT /*+ leading(d e b) use_hash(e) use_hash(b) */ e.ename, e.sal, d.loc, B.BONUS
FROM emp e, DEPT d, BONUS b
WHERE e.deptno=d.deptno AND e.empno=b.empno;




- nested 조인할 때는 leading 절의 순서를 그대로 따르지만 hash 조인은 d e와 조인한 결과와 b둘 중에 어느 것을 메모리에 올릴지는 옵티마이저가 결정한다

- 실행계획에서 위에 있는 테이블이 메모리에 올라가기 때문에 위의 그림에서는 dept와 emp 둘을 hash join 하고 그 결과를 메모리에 올린 후 bonus 테이블과 join 한다






l swap_join_inputs
- 2개의 테이블만 조인할 때는 leading 으로 해쉬테이블을 결정할 수 있는데 3개이 상의 테이블을 조인할 때는 leading 만으로는 해쉬테이블을 결정하기가 어렵다
- 그래서 사용하는 힌트가 swap_join_injputs 인데 이 힌트에 쓴 테이블이 바로 해쉬 테이블이 된다.

l no_swap_join_inputs : probe 테이블을 결정하는 힌트


문제52. 아래의 SQL의 실행계획은 times100과 sales100 join 결과가 hash table에 올라가는데 이것을 product 테이블을 hash table에 올리도록 고치시오


select /*+ leading(t s p)  use_hash(s) use_hash(p) */
  p.prod_name, t.CALENDAR_YEAR, sum(s.amount_sold)
  from   sales100  s, times100  t, products100  p
  where   s.time_id = t.time_id
  and  s.prod_id = p.prod_id
    and  t.CALENDAR_YEAR in (2000,2001)
    and p.prod_name like 'Deluxe%'


select /*+ leading(t s p)  use_hash(s) use_hash(p) swap_join_inputs(p) */
  p.prod_name, t.CALENDAR_YEAR, sum(s.amount_sold)
  from   sales100  s, times100  t, products100  p
  where   s.time_id = t.time_id
  and  s.prod_id = p.prod_id
    and  t.CALENDAR_YEAR in (2000,2001)
    and p.prod_name like 'Deluxe%'
    group  by  p.prod_name, t.calendar_year;




  • SORT MERGE JOIN



문제53. 사원이름, 월급, 급여등급(grade)를 출력하는데 아래와 같이 수행되게 하시오(조인순서 : emp, salgrade. 조인방법 : hash)

SELECT /*+ leading(e s) use_hash(s) */ e.ename, e.sal, s.grade
FROM EMP e, SALGRADE s
WHERE sal BETWEEN losal AND hisal;



- hash join 이 가능하려면 join의 연결고리가 = 조건이어야 한다
(Equijoin에 대해서만 가능)
- 위와 같은 상황일 때 hash join이 안된다면 sort merge join을 써야 한다



l sort merge join
힌트 : /.*+ use_merge(테이블리스트) */
- hash 나 sort merge join은 인덱스를 이용해서 하면 오래걸리기 때문에 주로 full scan을 이용하여 수행한다
- 예 : select /*+ laeding(d e) use_merge(e) */ e.ename, d.loc, e.sal, e.deptno
from emp e, dept d
where e.deptno = d.deptno;


- 결과를 보면 deptno가 정렬작업을 따로 하지 않았어도 오름차순 정렬이 된 것을 확인할 수 있다






  • SEMI JOIN



l 서브쿼리문의 튜닝방법 2가지

1.     SQL 재작성
- 완전히 다른 SQL로 변경

2.     힌트를 이용한 튜닝

1)     순수하게 서브쿼리로 수행되는가 : /*+ no_unnest */
- 서브쿼리로 먼저 수행되는가 : /*+ push_subq */
- 메인쿼리에서 먼저 수행되는가 : /*+ no_push_subq +/
- 위 힌트는 서브쿼리에 써줘야 한다

2)     조인으로 변경이 되어서 수행되는가 : /*+ unnest */
- semi join : 서브쿼리와 메인쿼리 사이의 연산자가 in 일 경우
a.     nested loop semi join
b.     sort merge semi join
c.     hash semi join

- anti join : 서브쿼리와 메인쿼리 사이의 연산자가 not in 일 경우
a.     nested loop anti join
b.     sort merge anti join
c.     hash anti join



문제56. JONES 보다 많은 월급을 받는 사원들의 이름과 월급을 출력하시오

SELECT ename, sal
FROM EMP
WHERE sal > (SELECT sal FROM EMP WHERE ename='JONES');


l 위경우는 순수하게 서브쿼로로 수행되는 것 중에 서브쿼리부터 수행되는 경우이다




l QB_NAME 힌트
- 서브쿼리문장에서 쓰는 힌트인데 서브쿼리부터 실행했는지 메인쿼리부터 실행했는지 확인할 수 있게 하는 힌트
- SQL> SELECT /*+ QB_NAME(main) */ ename, sal
FROM EMP
WHERE sal > (SELECT /*+ QB_NAME(sub) */ sal FROM EMP  WHERE ename='JONES');


l 실행계획 2가지
1.     예상 실행계획 : 실제로 수행하기전에 예상한 실행게획(SQLgate : F7)
2.     실제 실행계획 : 실제로 수행하면서 사용된 실행계획(SQLgate : ctrl+alt+F7)
- Format을 ADVANCED ALLSTATS LAST 를 선택한다

Plan hash value: 587534197

-----------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |     4 (100)|          |      0 |00:00:00.01 |
|*  1 |  TABLE ACCESS FULL | EMP  |      1 |      4 |    80 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |
|*  2 |   TABLE ACCESS FULL| EMP  |      0 |      1 |    20 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |
-----------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - MAIN / EMP@MAIN
   2 - SUB  / EMP@SUB



문제57. 아래의 SQL을 메인쿼리부터 수행되게 하시오


# 튜닝 전
SELECT /*+ QB_NAME(main) */ ename, sal
FROM EMP
WHERE sal > (SELECT /*+ QB_NAME(sub) */ sal FROM EMP  WHERE ename='JONES');
            
# 튜닝 후
SELECT /*+ QB_NAME(main) */ ename, sal
FROM EMP
WHERE sal > (SELECT /*+ QB_NAME(sub) no_push_subq */ sal FROM EMP  WHERE ename='JONES');






n 실제실행계획
Plan hash value: 2561671593

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |     4 (100)|          |      3 |00:00:00.01 |      14 |
|*  1 |  FILTER            |      |      1 |        |       |            |          |      3 |00:00:00.01 |      14 |
|   2 |   TABLE ACCESS FULL| EMP  |      1 |     82 |  1640 |     2   (0)| 00:00:01 |     14 |00:00:00.01 |       7 |
|*  3 |   TABLE ACCESS FULL| EMP  |      1 |      1 |    20 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |
---------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - MAIN
   2 - MAIN / EMP@MAIN
   3 - SUB  / EMP@SUB
- 2번과 3번이 같은 라인에 위치하는데 같은 라인이면 위부터 수행했다는 것을 의미하므로 main 부터 실행한 것을 확인할 수 있다



  • HASH SEMI JOIN



문제58. 사원이름이 SCOTT 인 사원과 같은 월급을 받는 사원들의 이름과 월급과 직업을 출력하시오

SELECT /*+ qb_name(main) */ ename, sal, job
FROM EMP
WHERE sal = (SELECT /*+ qb_name(sub) */ sal FROM EMP WHERE ename='SCOTT');




SELECT /*+ qb_name(main) */ ename, sal, job
FROM EMP
WHERE sal in (SELECT /*+ qb_name(sub) */ sal FROM EMP WHERE ename='SCOTT');


- in을 쓰는 순간 데이터가 하나가 아니라 여러 개라고 생각하기 떄문에 조인으로 변경한다
- 서브쿼리문장을 수행할 떄 메인쿼리와 서브쿼리가 양쪽다 data가 많다면 순수하게 서브쿼리로 수행하는 것보다 조인으로 변경하는 것이 속도가 더 빠르다




문제59. 아래의 SQL을 힌트를 줘서 2가지 실행계획이 나오도록 하시오 (순수하게 서브쿼리로 수행하는 경우, 조인으로 변경)

SELECT /*+ qb_name(main) */ ename, sal, job
FROM EMP
WHERE sal in (SELECT /*+ qb_name(sub) no_unnest */ sal FROM EMP WHERE ename='SCOTT');



SELECT /*+ qb_name(main) */ ename, sal, job
FROM EMP
WHERE sal in (SELECT /*+ qb_name(sub) unnest */ sal FROM EMP WHERE ename='SCOTT');




l 세미조인 방법은 다양하게 사용할 수 있는데 조인 순서가 고정이 된다.
- main query ~> sub query




문제60. 아래의 SQL을 한번은 nested loop semi join 으로 한번은 hash join semi 로 수행하고 통계를 비교해보시오

SELECT /*+ qb_name(main) */ ename, sal, job
FROM EMP
WHERE sal in (SELECT /*+ qb_name(sub) nl_sj no_unnest */ sal FROM EMP WHERE ename='SCOTT');



SELECT /*+ qb_name(main) */ ename, sal, job
FROM EMP
WHERE sal in (SELECT /*+ qb_name(sub) hash_sj no_unnest */ sal FROM EMP WHERE ename='SCOTT');





문제61. 아래의 semi join의 hash join 순서를 dept ~> emp 순서로 되게 하시오

SELECT ename, sal, job, comm
FROM EMP
WHERE deptno IN (SELECT /*+ swap_join_inputs(dept) hash_sj */ deptno FROM DEPT);




저작자표시 비영리 변경금지

'빅데이터과정 > SQL TUNING' 카테고리의 다른 글

#36_140804_TUNING_UNION TUINING  (0) 2014.08.04
#35_140801_TUNING_JOIN TUNING2  (0) 2014.08.01
#34_140730_TUNING_OPERATOR  (0) 2014.07.30
#34_140730_TUNING_SORTING OPERATION  (0) 2014.07.30
#34_140730_TUNING_INVISIBLE INDEX  (0) 2014.07.30

'빅데이터과정/SQL TUNING' Related Articles

티스토리툴바