★점심시간 문제 emp19 테이블과 telecom_table 과 조인하여 이름과 나이와 통신사와 통신사 가격을 출력하는 조인문을 nested loop 조인으로 수행하고 실행계획을 검사받으세요
1) 튜닝전
select e.ename, t.telecom, t.t_price
from emp19 e, telecom_table t
where e.telecom = t.telecom;
2) 인덱스 생성 후 튜닝
create index emp19_telecom on emp19(telecom);
create index telecom_table_telecom on telecom_table(telecom);
select /*+ leading(t e) use_nl(e) index(e emp19_telecom) */ e.ename, t.telecom, t.t_price
from emp19 e, telecom_table t
where e.telecom = t.telecom;
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
[TIL 26] 240619
23. nested loop
예제23. 3개 이상의 테이블을 nested loop조인으로 조인할 때 힌트 사용법을 알아야 해요 !
※ 조인 방법 3가지 ?
1. nested loop join
use_nl 힌트
조인되는 데이터의 양이 작고 조인 연결고리 컬럼에 인덱스가 있는 경우에 유리한 조인 방법
2. hash join
use_hash 힌트
use_hash 힌트 : 조인되는 데이터의 양이 많고 조인 연결고리 컬럼에 인덱스가 없거나 있어도 인덱스 효과를 보기 어려울때 유리한 조인방법
3. sort merge join
use_merge 힌트
조인되는 데이터의 양이 많고 조인 연결고리 컬럼에 인덱스가 없거나 있어도 인덱스 효과를 보기 어려울때 유리한 조인방법인데 조인된 결과가 연결고리 컬럼을 기준으로 정렬까지 해야할 때 유용합니다.
※ 인덱스 효과를 보기 어려운 경우? 너무 많은 데이터를 인덱스를 통해서 검색하려할 때
문제 1. emp와 dept를 조인해서 이름, 월급, 부서위치, 급여등급을 출력하시오.
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;
문제 2. 위의 sql의 실행계획을 다음과 같이 수행되게 하시오 - 조인순서: dept -> emp -> salgrade - 조인방벙: nl조인 nl조인
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;
문제3. 위의 sql의 조인순서와 조인방법이 다음과 같이 되도록 하시오. - 조인순서: salgrade -> emp -> dept - 조인방벙: nl조인 nl조인
select /*+ leading(s e d) use_nl(e) use_nl(d) */ e.ename, e.sal, d.loc, s.grade
from salgrade s, emp e, dept d
where e.sal between s.losal and s.hisal and e.deptno=d.deptno;
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
문제 4. 아래의 조인의 성능을 높이기 위해서 연결고리 컬럼에 인덱스를 생성하시오.
select /*+ leading(s e d) use_nl(e) use_nl(d) */ 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;
※ nested loop 조인의 검색성능을 높이려면 연결고리가 되는 컬럼에 인덱스가 있어야 합니다.
1) 인덱스 생성
create index emp_deptno on emp(deptno);
create index dept_deptno on dept(deptno);
2) 실행계획 재 출력
select /*+ leading(s e d) use_nl(e) use_nl(d) */ 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;
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
※ nested loop 조인이 3번이 나온 이유는 advanced nested loop조인이라고 해서 nested loop 조인을 더 빠르게 수행하게 처리됬다는 뜻입니다.
문제 5. emp 테이블을 가지고 bonus 테이블을 생성하시오 !
drop table bonus;
create table bonus
as
select empno , sal*1.2 as comm2
from emp;
문제 6. emp 와 dept 와 bonus 를 조인해서 이름과 부서위치와 comm2 를 출력하시오
select e.ename, d.loc, b.comm2
from emp e, dept d, bonus b
where e.deptno =d.deptno and e.empno = b.empno;
문제 7. 위의 결과를 다시 출력하는데 이름이 scott인 사원의 데이터만 출력하시오.
select e.ename, d.loc, b.comm2
from emp e, dept d, bonus b
where e.deptno =d.deptno and e.empno = b.empno
and e.ename= 'SCOTT';
문제 8. 위의 sql 조인 순서와 방법을 아래와 같이 되도록 힌트를 주시오. - 조인순서: emp -> dept -> bonus - 조인방법: nl nl
select /*+ leading(e d b) use_nl(d) use_nl(b) */ e.ename, d.loc, b.comm2
from emp e, dept d, bonus b
where e.deptno =d.deptno and e.empno = b.empno
and e.ename= 'SCOTT';
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
24. HASH JOIN
예제24. 대량의 데이터를 조인할 때는 해쉬조인을 사용하세요 ! 해쉬조인(hash join) 이란 ? -> 해쉬 알고리즘을 사용하고 있는 해쉬 함수를 이용해서 메모리에 올라온 데이터를 찾아 조인하는 조인방법입니다. -> data 를 메모리에 올려놓고 메모리에서 조인하는 조인방법입니다.
select rowid, deptno, dname, loc
from dept;
이 rowid 는 해당 로우의 물리적인 주소인데 file# + block# + row# 로 되어있습니다. 이 rowid 는 테이블이 디스크에 있을때만 사용할 수 있습니다. 메모리로 테이블이 올라가게되면 이제 이 번호는 소용이 없는 번호입니다. 메모리의 새로운 주소를 사용해야하는데 그 주소가 해쉬값입니다.
메모리에 테이블을 올려놓고 조인하는게 디스크에서 조인하는것보다 훨씬 속도가 빠릅니다. 회사의 데이터가 점점 대용량이 되고 있기 때문에 해쉬조인을 사용안할수가 없습니다.
문제1. emp 와 dept 를 해쉬조인해서 이름과 부서위치를 출력하시오!
select /*+ leading(d e) use_hash(e) */ e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno;
설명: 작은 테이블이 메모리로 올라오게끔 조인순서를 잘 정해줘야합니다.
문제2 .emp 와 dept 를 해쉬조인해서 이름과 부서위치를 출력하는데 emp 가 해쉬 테이블이 되게 하시오 !
select /*+ leading(e d) use_hash(d) */ e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno;
설명: 해쉬조인 튜닝 전후 비교는 버퍼의 갯수로 비교하면 안되고 A-TIME인 수행시간으로 비교해줘야됨. ( 메모리 사용 사이즈를 비교해서 확인)
문제 3. 아래의 SQL을 튜닝하시오!
1) 튜닝전
select /*+ leading(s t) use_hash(t) */
t.calendar_year, sum(amount_sold)
from sales200 s, times200 t
where s.time_id = t.time_id
and t.week_ending_day_id =1582
group by t.calendar_year
2) 튜닝후
select /*+ leading(t s) use_hash(s) */
t.calendar_year, sum(amount_sold)
from sales200 s, times200 t
where s.time_id = t.time_id
and t.week_ending_day_id =1582
group by t.calendar_year;
문제 4. 아래 sql을 튜닝하시오.
1) 튜닝전
select /*+ leading(s t) use_hash(t) */
count(*)
from sales200 s, times200 t
where s.time_id = t.time_id;
2) 튜닝 후
select /*+ leading(t s) use_hash(s) */
count(*)
from sales200 s, times200 t
where s.time_id = t.time_id;
작은 테이블인 times200 을 메모리로 올려서 해쉬 테이블로 구성했더니 시간이 더 빨라졌습니다.
select /*+ leading(e d b) use_hash(d) use_hash(b) */e.ename, d.loc, b.comm2
from emp e , dept d, bonus b
where e.deptno=d.deptno and e.empno=b.empno;
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
설명: emp와 dept가 서로 해쉬조인을 하는데 이때 emp과 hash 테이블이 됩니다. 그리고 emp와 dept를 해쉬 조인한 결과 데이터가 메모리로 올라가서 hash테이블이 되고 bonus 와 해쉬조인을 합니다.
문제 6. 위 sql 조인 순서와 조인방법이 다음과 같이 되게하시오. 조인순서 : dept -----> emp -----> bonus ↑ ↑ 조인방법 : 해쉬조인 해쉬조인
select /*+ leading(d e b) use_hash(e) use_hash(b) */e.ename, d.loc, b.comm2
from emp e , dept d, bonus b
where e.deptno=d.deptno and e.empno=b.empno;
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
25. 3개의 테이블을 해쉬 조인
예제 25. 3개의 테이블을 해쉬 조인 할때 해쉬 테이블을 선택할 수 있어요 ! : 2개의 테이블을 해쉬조인할 때 해쉬 테이블을 선택하는 방법은 leading 힌트의 괄호 안에 첫번째 테이블로 작성하기만 하면 되는데 3개의 테이블 인 경우에는 leading 말고 특별한 힌트를 써야합니다.
select /*+ leading(d e b) user_hash(e) use_hash(b) */ e.ename, d.loc, b.comm2
from emp e , dept d, bonus b
where e.deptno=d.deptno and e.empno = b.empno;
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
설명: dept와 emp와 조인할때 dept가 해쉬테이블이 됩니다. dept와 emp가 조인한 결과 데이터가 해쉬테이블이 되고 이 해쉬테이블이 bonus와 해쉬조인을 합니다.
Q2. 위의 실행계획이 다음과 같이 되게하시오.
select /*+ leading(d,e,b) use_hash(e) use_hash(b)
swap_join_inputs(b) */ e.ename, d.loc, b.comm2
from emp e, dept d, bonus b
where e.deptno = d.deptno and e.empno = b.empno;
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
※ 3개의 테이블을 해쉬 조인 할 때 꼭 알아야하는 힌트 2가지
1. swap_join_inputs
해쉬테이블을 결정하는 힌트(메모리로 올라가는 테이블)
2. no_swap_join_inputs
탐색(prob) 테이블을 결정하는 힌트(디스크에 있는 테이블)
메모리로 올라가는 테이블은 상대적으로 크기가 작은 테이블이어야 합니다. 또는 검색조건에 의해서 액세스되는 건수가 작은 테이블이 해쉬 테이블이어야합니다.
문제1. 아래의 조인문의 실행계획이 다음과 같이 나오게 하시오 !
select /*+ leading(b,e,d) use_hash(e) use_hash(d)
swap_join_inputs(d) */ e.ename, d.loc, b.comm2
from emp e, dept d, bonus b
where e.deptno = d.deptno and e.empno = b.empno;
문제 2. 아래의 환경을 구성하고 아래의 SQL을 수행하시오!
-환경 구성
drop table sales200;
drop table time200;
drop table products200;
create table sale200 as select * from sh.sales;
create table times200 as select * from sh.times;
create table products200 as select * from sh.products;
select p.prod_name, t.CALENDAR_YEAR, sum(s.amount_sold)
from sales200 s, times200 t, products200 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;
문제3. 위 sql 의 조인 순서와 조인방법이 다음과 같이 수행되게하시오. 조인순서 : times200 -----> sales200 -----> products200 ↑ ↑ 조인방법 : 해쉬조인 해쉬조인
select /*+ leading(t,s,p) use_hash(s) use_hash(p) */ p.prod_name, t.CALENDAR_YEAR, sum(s.amount_sold)
from sales200 s, times200 t, products200 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;
설명: times200과 sales200테이블을 조인할 때 time200에 해쉬 테이블이 되었고 times200과 sales200을 조인한 결과와 products200을 조인할 때 해쉬 테이블이 products200이 되었습니다.
문제 4. 위 sql 의 조인 순서와 조인방법이 다음과 같이 수행되게하시오.
select /*+ leading(t,s,p) use_hash(s) use_hash(p)
no_swap_join_inputs(p) */
p.prod_name, t.CALENDAR_YEAR, sum(s.amount_sold)
from sales200 s, times200 t, products200 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;
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
문제 5. 위의 SQL의 조인 순서를 어떻게 줘야하는지 알아내기 위해서다음과 같이 사전 조사를 수행합니다.
select count(*) from sales200;
select count(*) from times200;
select count(*) from products200;
select count(*) from times200 where CALENDAR_YEAR in (2000,2001) ; --731건
select count(*) from products200 where prod_name like 'Deluxe%'; -- 1건
select /*+ leading(p,s,t) user_hash(s) use_hash(t) */ p.prod_name, t.CALENDAR_YEAR, sum(s.amount_sold)
from sales200 s, times200 t, products200 p -- 918843, 1826, 72
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;
26. 테이블 안되는 연산자
예제26. 해쉬조인이 안되는 연산자가 있어요 ! : 해쉬 조인이 가능하려면 조인의 연결고리가 반드시 = 조건이어야 합니다.문제
select /*+ leading(s e) use_hash(e) */ e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;
설명: 해쉬조인이 되려면 무조건 조인 연결고리가 = 조건이어야합니다. between.. and 조건이면 해쉬조인을 할 수 없습니다. 만약에 emp 와 salgrade 가 모두 대용량 테이블이어서 nested loop 조인을 하면 성능이 느리다면 어떻게 튜닝을 해야하는가 ?
답: sort merge join 을 하면 됩니다.
select /*+ leading(s e) use_merge(e) */ e.ename, e.sal, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
27. sort merge join
예제27. 대량의 테이블을 조인하는데 해쉬조인을 할 수 없다면 sort merge join 을 하세요 !
select /*+ leading(d e) use_merge(e) */ e.ename, e.loc, e.deptno
from emp e, dept d
where e.deptno = d.deptno ;
그림에 나온것처럼 emp 와 dept 를 조인하기 전에 deptno 를 ascending하게 미리 정렬을 해놨기 때문에 조인이 빨라집니다. 왜냐면 dept 테이블에 10번 부서번호를 조인하려고 하면 10번을 모아놨기 때문에 빠르게 스캔할 수 가있는겁니다. 20번도 마찬가지고 30번도 마찬가지입니다. 대신 정렬 해야된다는 부담감은 존재합니다.
※ sort merge join 이 유용한때는?
1. 해쉬조인을 해야하는데 해쉬조인을 할 수 없을때
2. 해쉬조인된 결과를 정렬해서 봐야할 때
Q1. 아래의 SQL이 sort merge join 이 되게 하시오 !
create table customers200
as
select * from sh.customers;
1)튜닝전
select /*+ leading(s c) use_nl(c) */ count(*)
from sales200 s, customers200 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');
sort merge join 도 조인 순서를 정할 때 테이블 크기가 작거나 검색조건에 의해서 엑세스 되는 건수가 작은 테이블 부터 선두 테이블로 둬야됨
select count(*) from sales200 ; -- 918843
select count(*) from customers200; -- 55500
select count(*) from sales200
where time_id between to_date('1999/01/01','YYYY/MM/DD')
and to_date('1999/12/31','YYYY/MM/DD'); -- 247945
select count(*) from customers200
where country_id = 52790; -- 18520
select /*+ leading(c s) use_merge(s) */ count(*)
from sales200 s, customers200 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');
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
2. 1999 ANSI 조인문법 : 1. on 절을 사용한 조인문법 2. using 절을 사용한 조인문법 3. natural 조인 4. left/right/full 아우터 조인 5. cross 조인
조인 방법은 3가지가 있습니다. 1. nested loop 조인 2. hash 조인 3. sort merge 조인
문제1. 이름, 부서위치를 조인하는데 boston도 출력되게 작성하시오.
select e.ename, d.loc
from emp e , dept d
where e.deptno(+) = d.deptno ;
outer join(+) 은 데이터가 모자라게 나오는 쪽에 붙여주면 됨.
문제2.아래의 데이터를 입력하고 아우터 조인을 써서 출력하시오 !
insert into emp(empno, ename, sal, deptno )
values( 1123, 'JONES', 3000, 70 );
commit;
select e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno (+) ;
문제 3. 아래의 SQL의 조인 순서가 dept ---> emp 순이 되게하고조인 방법은 해쉬조인이 되게하시오 !
select /*+ leading(d e) use_hash(e) */ e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno (+) ;
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
설명: 실행 계획을 보면 힌트를 씀에도 불구하고 emp -> dept순으로 조인하고 있습니다. 아우타 조인 (+) 이 있으면 (+)이 없는 쪽에서 있는쪽으로 고정해서 조인을합니다.
dept 테이블이 emp 테이블 보다 작은 테이블이므로 dept 테이블을 먼저 driving 하는게 맞습니다. 그런데 아우터 조인 사인때문에 조인 순서가 고정이 되어버렸습니다. 그래서 이럴때 튜닝하는 힌트가 바로 알려줬던 힌트 입니다.
** swap_join_input 사용하면 dept먼저 읽음
select /*+ leading(d e) use_hash(e)
swap_join_inputs(d) */ e.ename, d.loc
from emp e, dept d
where e.deptno = d.deptno (+) ;
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
문제4. 아래의 SQL을 해쉬 조인으로 수행하는데 해쉬 조인 순서를 올바르게 정해서 해쉬 조인하시오 !
튜닝전 :
select t.calendar_year, sum(s.amount_sold)
from sales200 s, times200 t
where s.time_id = t.time_id (+)
and t.week_ending_day_id (+) = 1581
group by t.calendar_year;
답:
select /*+ leading(t,s) use_hash(s)
swap_join_inputs(t) */ calendar_year, sum(s.amount_sold)
from sales200 s, times200 t
where s.time_id = t.time_id (+)
and t.week_ending_day_id (+) = 1581
group by t.calendar_year;
★ 마지막 반드시 전부 nested loop 조인으로 수행하면서 조인관련된 힌트를 쓰시오 버퍼의 갯수가 가장 작은 조인순서로 결과를 올리시오 !
@demo
1) 튜닝전
select e.ename, 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
and s.grade in ( 3, 4 ) and d.loc='DALLAS';
select /*+ leading(d ,s,e) use_nl(s) use_nl(e) */ e.ename, 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
and s.grade in ( 3, 4 ) and d.loc='DALLAS';
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
/*+ leading(d ,s ,e) use_nl(s) use_nl(e) */ ->24
/*+ leading(d ,e ,s) use_nl(e) use_nl(s) */ ->44
/*+ leading(s ,d ,e) use_nl(d) use_nl(e) */ ->31
/*+ leading(s ,e ,d) use_nl(e) use_nl(d) */->66
/*+ leading(e ,s ,d) use_nl(s) use_nl(d) */ ->140
/*+ leading(e ,d ,s) use_nl(d) use_nl(s) */->135
