join 실행 계획

# test #
#) join 중의 data 확인

select  /*+ gather_plan_statistics */ e.last_name, e.job_id, d.department_name, l.city
from emp e, dept d, loc l
where e.department_id = d.department_id
and d.location_id = l.location_id
and l.city = 'Seattle'
and e.job_id = 'AD_VP';

#) 실행 계획 확인

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

Id. 9  <- index 없음 = 1,2번에서 i/o가 많이 발생 
-> 먼저 드라이빙 해줘야 한다. outer 

---

# 해결 방안_인덱스 설정 #
#1) 인덱스 설정

create index hr.emp_job_idx on hr.emp(job_id);

#2) 인덱스 확인

select ix.index_name, ix.uniqueness, ic.column_name
from user_indexes ix, user_ind_columns ic
where ix.index_name = ic.index_name
and ix.table_name in ('EMP', 'DEPT', 'LOC');

#3) leading(e,d,l) use_nl(d), use_nl(l) 힌트 사용

select  /*+ gather_plan_statistics leading(e,d,l) use_nl(d) use_nl(l) */ e.last_name, e.job_id, d.department_name, l.city
from emp e, dept d, loc l
where e.department_id = d.department_id
and d.location_id = l.location_id
and l.city = 'Seattle'
and e.job_id = 'AD_VP';

#4) 실행 계획 확인

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

Id. 8번 => 파티션곱 발생

# index 변경_loc_idx #
#) 힌트 사용: leading(e,d,l) use_nl(d) use_nl(l) use_nl_with_index(l loc_idx) 지정

select  /*+ gather_plan_statistics leading(e,d,l) use_nl(d) use_nl(l) use_nl_with_index(l loc_idx) */ e.last_name, e.job_id, d.department_name, l.city
from emp e, dept d, loc l
where e.department_id = d.department_id
and d.location_id = l.location_id
and l.city = 'Seattle'
and e.job_id = 'AD_VP';

#) 실행 계획 확인 (=계층적 질의 )

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

 

---

# test #
#) leading(e,d) use_nl(d) vs leading(d,e) use_nl(e) 차이점
#1) 1쪽 집합 : departments | n쪽 집합 : employees

select  /*+ gather_plan_statistics leading(e,d) use_nl(d) */ e.employee_id, e.job_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id;

#2) 실행계획 확인

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

---

#) 조인조건술어 뿐 비조인조건술어 X

select  /*+ gather_plan_statistics leading(d,e) use_nl(e) */ e.employee_id, e.job_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id;

#2) 실행계획 확인

select * from table(dbms_xplan.display_cursor(null, null, 'allstats last'));

   4 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID") => buffer pinning 돌아감.