| # 요약 | |
| ■ Hash Join # leading(d,e) use_hash # leading(e,d) use_hash(d) # leading(l,d,e) use_hash(d) use_hash(e) # leading(d,e,l) use_hash(e) use_hash(l) # swap_join_inputs() ■ 서브쿼리 처리 방식(in, exists) 1. filter 방식 2. unnest 방식 unnest no_unnest exists not exists # 조인제거 alter session set "_optimizer_join_elimination_enabled" = true eliminate_join no_eliminate_join ■ 서브쿼리를 조인으로 변환 ■ SEMI JOIN nl_sj merge_sj hash_sj ■ ANTI JOIN nl_aj merge_aj hash_aj # 이슈문장 찾기 : v$sql_plan - hash unique - sort unique |
|
■ Hash Join
- Nested Loop Join은 대량의 데이터에 대해서 Random Access가 부담스럽고 Sort Merge Join은 정렬 작업에 대한 부담이 있다. 이럴 때 Hash Join을 사용하면 유리할 수 있다. - CPU 좋으면 더 효율적
- 조인으로 수행하는 집합 중에 작은 집합을 Build Table(Hash Table)로 생성하고 반대쪽 큰 집합을 Probe해서 읽어 해시 테이블을 탐색하면서 조인하는 방식
- hash_area_size로 설정되어있는 hash area에 hash table이 생성된다.
- hash table로 생성되는 build table이 작은 것도 중요하지만 해시 키값으로 사용되는 칼럼에 중복값이 거의 없을 때 효과적이다. hash key에 충돌이 발생하지 않는 게 좋다.
- 연산자의 제약조건 : = (equal) 조건에서만 사용
> TEST PC에서는 Nested Loop Join이 더 빠를 수 있다.
> 낮에는 sort merge join, hash join 하지 말자.
> full(인덱스못타게) + paralle(병렬처리) + hash(같다)
paralle + nested -> X
paralle + sort merge -> X
# leading(d,e) use_hash(e)
HR@ora11g> select /*+ gather_plan_statistics leading(d,e) use_hash(e) */
d.department_name, d.department_name, e.employee_id, e.last_name, e.salary
from departments d, employees e
where d.department_id = e.department_id;
106 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.01 | 19 | | | |
|* 1 | HASH JOIN | | 1 | 106 | 106 |00:00:00.01 | 19 | 1077K| 1077K| 1254K (0)|
| 2 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 6 | | | |
| 3 | TABLE ACCESS FULL| EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 13 | | | |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
- build : departments -> PGA : SQL작업영역 : hash_area_size -> build table 생성 -> hash key 부여 : select 컬럼 정보 기준
- probe : employees
- hash key 값은 중복가능 > 그래서 hash key + 컬럼정보 같이 체크
> 고유 키값이 있는 build table 만들자
- 메모리에서 hash key 생성 > 부족하면 파티션 > disk : temp > 순환되면 비효율 > hash_area_size 체크
> 작은 결과셋으로 build table 만들자
# leading(e,d) use_hash(d)
HR@ora11g> select /*+ gather_plan_statistics leading(e,d) use_hash(d) */
d.department_name, d.department_name, e.employee_id, e.last_name, e.salary
from departments d, employees e
where d.department_id = e.department_id;
106 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.01 | 17 | | | |
|* 1 | HASH JOIN | | 1 | 106 | 106 |00:00:00.01 | 17 | 1020K| 1020K| 820K (0)|
| 2 | TABLE ACCESS FULL| EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
| 3 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 11 | | | |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
> 더 좋아보이지만 서버에서는 다르다.
# leading(l,d,e) use_hash(d) use_hash(e)
HR@ora11g> select /*+ gather_plan_statistics leading(l,d,e) use_hash(d) use_hash(e) */
d.department_id, d.department_name, e.employee_id, e.last_name, e.salary, l.city, l.street_address
from departments d, employees e, locations l
where d.department_id = e.department_id
and d.location_id = l.location_id;
106 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.01 | 25 | | | |
|* 1 | HASH JOIN | | 1 | 106 | 106 |00:00:00.01 | 25 | 860K| 860K| 1210K (0)|
|* 2 | HASH JOIN | | 1 | 27 | 27 |00:00:00.01 | 12 | 904K| 904K| 1068K (0)|
| 3 | TABLE ACCESS FULL| LOCATIONS | 1 | 23 | 23 |00:00:00.01 | 6 | | | |
| 4 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 6 | | | |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 13 | | | |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
2 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
# leading(d,e,l) use_hash(e) use_hash(l)
HR@ora11g> select /*+ gather_plan_statistics leading(d,e,l) use_hash(e) use_hash(l) */
d.department_id, d.department_name, e.employee_id, e.last_name, e.salary, l.city, l.street_address
from departments d, employees e, locations l
where d.department_id = e.department_id
and d.location_id = l.location_id;
106 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.01 | 23 | | | |
|* 1 | HASH JOIN | | 1 | 106 | 106 |00:00:00.01 | 23 | 910K| 910K| 607K (0)|
|* 2 | HASH JOIN | | 1 | 106 | 106 |00:00:00.01 | 12 | 1020K| 1020K| 1203K (0)|
| 3 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 6 | | | |
| 4 | TABLE ACCESS FULL| EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
| 5 | TABLE ACCESS FULL | LOCATIONS | 1 | 23 | 23 |00:00:00.01 | 11 | | | |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
2 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
> Test PC라 이런거지 서버에서는 효율이 없다.
> LOCATIONS + EMPLOYEES -> 원래부터 조인 불가 (공통 키값이 없다)
> DEPARTMENTS + EMPLOYEES -> 지금은 조인하는 게 맞다 -> 데이터가 크고 중복값이 많으니 probe 위치로 만들고
> LOCATIONS -> build로 올려보자
# swap_join_inputs() (10g) : 테이블을 build 성격으로 만들자
HR@ora11g> select /*+ gather_plan_statistics leading(d,e,l) use_hash(e) use_hash(l) swap_join_inputs(l) */
d.department_id, d.department_name, e.employee_id, e.last_name, e.salary, l.city, l.street_address
from departments d, employees e, locations l
where d.department_id = e.department_id
and d.location_id = l.location_id;
106 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.01 | 25 | | | |
|* 1 | HASH JOIN | | 1 | 106 | 106 |00:00:00.01 | 25 | 904K| 904K| 1089K (0)|
| 2 | TABLE ACCESS FULL | LOCATIONS | 1 | 23 | 23 |00:00:00.01 | 6 | | | |
|* 3 | HASH JOIN | | 1 | 106 | 106 |00:00:00.01 | 19 | 1020K| 1020K| 1195K (0)|
| 4 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 6 | | | |
| 5 | TABLE ACCESS FULL| EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 13 | | | |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
3 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
> 서버에서는 다르다. hash join으로 최적화 한거 맞다.
■ Query Transformation(쿼리 변환) (9i)
- Optimizer가 실행계획을 생성하기 전에 사용자가 작성한 SQL문 결과는 똑같은데 비용(cost)이 더 적게 발생한것 같으면 쿼리를 변경한다.
■ 서브쿼리 처리 방식(in, exists)
1. filter 방식
- 메인 쿼리에서 읽히는 row마다 서브쿼리를 반복수행하면서 조건에 맞는 데이터를 찾는 방식
> 오라클은 in 연산자보다 exists 연산자를 선호
> 메인 데이터만큼 반복 > row가 많으면 성능 저하 > 서브쿼리 = 종속관계 > 개선 : 조인관계인 unnest 방식
> Operation : filter 나오면 체크하자
# 서브쿼리 SQL문
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees
where department_id in (select department_id from departments);
106 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.01 | 13 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 106 | 106 |00:00:00.01 | 13 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPARTMENT_ID" IS NOT NULL)
> d.department_id : primary key
e.department_id : foreign key (d.department_id)
> 메인 쿼리에 대한 실행계획만 나온다
# no_unnest
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees
where department_id in (select /*+ no_unnest */ department_id
from departments);
|| 동일
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees e
where exists (select /*+ no_unnest */ 'x'
from departments
where department_id = e.department_id);
106 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.01 | 24 |
|* 1 | FILTER | | 1 | | 106 |00:00:00.01 | 24 |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 13 |
|* 3 | INDEX UNIQUE SCAN| DEPT_ID_PK | 12 | 1 | 11 |00:00:00.01 | 11 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - access("DEPARTMENT_ID"=:B1)
> exists는 filter 방식
> buffer pinning 작동
> 바인더 변수 B1 = e.department_id -> Transformation
2. unnest 방식
- 서브쿼리를 이용했을 때 동일한 결과를 보장한다면 조인문으로 변환한다.
- 조인으로 변환하면 다양한 액세스 경로, 조인 방법, 조인 순서를 결정할 수 있다.
- Hint : unnest(filter 방식 대신 조인 방식으로 실행계획을 만들어 줘)
no_unnest(기존 filter 방식으로 실행계획을 만들어 줘)
- exists : 조인에 성공하는 서브쿼리에 행을 만나는 순간 결과집합에 담고 다음 메인쿼리 행으로 이동
- not exists : 조인에 성공하는 서브쿼리에 행을 만나는 순간 버리고 다음 메인쿼리 행으로 이동,
서브쿼리에 조인으로 성립되지 않은 행에 대해서 결과 집합에 담는다.
-- 소속부서가 있는 사원 목록
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees
where department_id in (select department_id from departments);
106 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.01 | 13 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 106 | 106 |00:00:00.01 | 13 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPARTMENT_ID" IS NOT NULL)
> primary key, foreign key 제약조건이 있어 적합성이 좋다
-> 서브쿼리를 체크 안함 -> join elmination 기능 수행 (조인제거)
-> 굳이 조인조건으로 만들지 않아도 된다
# 조인제거
- 1:N 관계인 두 테이블을 조인하는 쿼리문에서 1쪽 테이블을 참조하지 않는다면 1쪽 테이블을 읽지 않아도 된다.
즉, 조인제거 기능이 작동되려면 primary key, foreign key 설정이 되어 있어야만 조인제거(join elmination) 기능이 수행된다.
- Hint : eliminate_join(기본값으로 활성화 되어있다)
no_eliminate_join
# 제약조건 확인
SYS@ora11g> select a.table_name, b.column_name, a.constraint_name, a.constraint_type, a.search_condition,a.r_constraint_name, a.index_name
from user_constraints a, user_cons_columns b
where a.constraint_name = b.constraint_name
and a.table_name in ('EMPLOYEES','DEPARTMENTS');
TABLE_NAME COLUMN_NAME CONSTRAINT_NAME CONSTRAINT_TYPE SEARCH_CONDITION R_CONSTRAINT_NAME INDEX_NAME
-------------------- -------------------- ------------------------------ --------------- ------------------------------ ------------------------------ ------------------------------
DEPARTMENTS DEPARTMENT_ID DEPT_ID_PK P DEPT_ID_PK
DEPARTMENTS LOCATION_ID DEPT_LOC_FK R LOC_ID_PK
DEPARTMENTS MANAGER_ID DEPT_MGR_FK R EMP_EMP_ID_PK
DEPARTMENTS DEPARTMENT_NAME DEPT_NAME_NN C "DEPARTMENT_NAME" IS NOT NULL
EMPLOYEES SALARY EMP_SALARY_MIN C salary > 0
EMPLOYEES EMAIL EMP_EMAIL_UK U EMP_EMAIL_UK
EMPLOYEES EMPLOYEE_ID EMP_EMP_ID_PK P EMP_EMP_ID_PK
EMPLOYEES DEPARTMENT_ID EMP_DEPT_FK R DEPT_ID_PK
EMPLOYEES JOB_ID EMP_JOB_FK R JOB_ID_PK
EMPLOYEES MANAGER_ID EMP_MANAGER_FK R EMP_EMP_ID_PK
EMPLOYEES LAST_NAME EMP_LAST_NAME_NN C "LAST_NAME" IS NOT NULL
EMPLOYEES EMAIL EMP_EMAIL_NN C "EMAIL" IS NOT NULL
EMPLOYEES HIRE_DATE EMP_HIRE_DATE_NN C "HIRE_DATE" IS NOT NULL
EMPLOYEES JOB_ID EMP_JOB_NN C "JOB_ID" IS NOT NULL
# 조인 제거 비활성화
HR@ora11g> alter session set "_optimizer_join_elimination_enabled" = false;
Session altered.
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees
where department_id in (select department_id from departments);
106 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.01 | 23 |
| 1 | NESTED LOOPS | | 1 | 106 | 106 |00:00:00.01 | 23 |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 13 |
|* 3 | INDEX UNIQUE SCAN| DEPT_ID_PK | 107 | 1 | 106 |00:00:00.01 | 10 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
> 서브쿼리 : 메인테이블의 컬럼만 출력, 출력원하면 조인으로 변환
> 조인 : 모든 테이블 컬럼 출력
> select절에는 메인 컬럼만 출력
# 조인 제거 활성화 (기본값)
HR@ora11g> alter session set "_optimizer_join_elimination_enabled" = true;
Session altered.
> "히든 파라미터" 수정 시 " " 표시
> 가능하면 힌트로만 사용하자
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees
where department_id in (select department_id from departments);
106 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.01 | 13 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 106 | 106 |00:00:00.01 | 13 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPARTMENT_ID" IS NOT NULL)
# 조인 제거 비활성화 : 서브쿼리에 작성, no_eliminate_join(비활성화 할 테이블)
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees
where department_id in (select /*+ no_eliminate_join(d) */ department_id from departments d);
106 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.01 | 23 |
| 1 | NESTED LOOPS | | 1 | 106 | 106 |00:00:00.01 | 23 |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 13 |
|* 3 | INDEX UNIQUE SCAN| DEPT_ID_PK | 107 | 1 | 106 |00:00:00.01 | 10 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
# 조인 제거 활성화 : 서브쿼리에 작성, eliminate_join(조인제거 할 테이블)
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees
where department_id in (select /*+ eliminate_join(d) */ department_id from departments d);
106 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 106 |00:00:00.01 | 13 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 106 | 106 |00:00:00.01 | 13 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPARTMENT_ID" IS NOT NULL)
■ 서브쿼리를 조인으로 변환
- 메인쿼리의 employees 테이블과 서브쿼리의 departments 테이블이 m:1 관계이기 때문에 조인문으로 바꾸더라도 쿼리결과가 보장된다.
> 오라클은 조인을 더 선호한다
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees
where department_id in (select department_id from departments where location_id = 1500);
ㄴ M쪽집합(중복성) ㄴ 1쪽집합(unique)
↓ 조인으로 변경해도 유지
HR@ora11g> select /*+ gather_plan_statistics */ e.*
from employees e, departments d
where e.department_id = d.department_id
and d.location_id = 1500;
45 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 45 |00:00:00.01 | 12 |
| 1 | NESTED LOOPS | | 1 | 10 | 45 |00:00:00.01 | 12 |
| 2 | NESTED LOOPS | | 1 | 10 | 45 |00:00:00.01 | 7 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 4 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 10 | 45 |00:00:00.01 | 4 |
| 6 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 45 | 10 | 45 |00:00:00.01 | 5 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("LOCATION_ID"=1500)
5 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
> nested loop join, batch I/O
# 서브쿼리를 조인으로 변환의 문제
departments 테이블이 기준 집합이므로 결과집합은 departments 테이블의 총건수를 넘지 않아야 한다.
만약에 조인문장으로 변환한다면 m쪽 집합인 employees 테이블 결과 집합으로 만들어지기 때문에 잘못된 결과가 나온다.
HR@ora11g> select /*+ gather_plan_statistics */ *
from departments d
where exists (select 'x' from employees where department_id = d.department_id);
ㄴ 1쪽집합 ㄴ M쪽집합
↓ 조인 시 결과값이 다르다.
HR@ora11g> select d.*
from departments d, employees e
where d.department_id = e.employee_id;
> 그럼에도 조인으로 바꾸고 싶어서 distinct 사용?
HR@ora11g> select d.*
from departments d, (select distinct department_id from employees) e
where d.department_id = e.department_id;
ㄴ 1쪽집합 ㄴ 1쪽집합
> 쓸데없이 distinct(중복제거) 사용하면 hash unique, sort unique 작동 > 성능 저하
> 오라클이 알아서 조인할께 -> semi join
HR@ora11g> select /*+ gather_plan_statistics */ *
from departments d
where exists (select 'x' from employees where department_id = d.department_id);
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
100 Finance 108 1700
110 Accounting 205 1700
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 11 |00:00:00.01 | 10 |
| 1 | NESTED LOOPS SEMI | | 1 | 10 | 11 |00:00:00.01 | 10 |
| 2 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 7 |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 27 | 41 | 11 |00:00:00.01 | 3 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPARTMENT_ID"="D"."DEPARTMENT_ID")
HR@ora11g> select /*+ gather_plan_statistics */ d.*
from departments d, employees e
where d.department_id = e.employee_id;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
100 Finance 108 1700
110 Accounting 205 1700
120 Treasury 1700
130 Corporate Tax 1700
140 Control And Credit 1700
150 Shareholder Services 1700
160 Benefits 1700
170 Manufacturing 1700
180 Construction 1700
190 Contracting 1700
200 Operations 1700
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 11 |00:00:00.01 | 12 |
| 1 | NESTED LOOPS | | 1 | 27 | 11 |00:00:00.01 | 12 |
| 2 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 7 |
|* 3 | INDEX UNIQUE SCAN| EMP_EMP_ID_PK | 27 | 1 | 11 |00:00:00.01 | 5 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."DEPARTMENT_ID"="E"."EMPLOYEE_ID")
HR@ora11g> select /*+ gather_plan_statistics */ d.*
from departments d, (select distinct department_id from employees) e
where d.department_id = e.department_id;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
40 Human Resources 203 2400
80 Sales 145 2500
30 Purchasing 114 1700
50 Shipping 121 1500
60 IT 103 1400
10 Administration 200 1700
20 Marketing 201 1800
110 Accounting 205 1700
100 Finance 108 1700
70 Public Relations 204 2700
90 Executive 100 1700
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 11 |00:00:00.01 | 9 | | | |
| 1 | VIEW | VM_NWVW_1 | 1 | 106 | 11 |00:00:00.01 | 9 | | | |
| 2 | HASH UNIQUE | | 1 | 106 | 11 |00:00:00.01 | 9 | 853K| 853K| 1038K (0)|
| 3 | NESTED LOOPS | | 1 | 106 | 106 |00:00:00.01 | 9 | | | |
| 4 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 6 | | | |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 27 | 4 | 106 |00:00:00.01 | 3 | | | |
-----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("D"."DEPARTMENT_ID"="DEPARTMENT_ID")
■ SEMI JOIN
- 서브쿼리를 조인으로 바꾼다.
- 조인으로 바뀐 서브쿼리는 항상 후행 처리가 된다.(inner)
- 메인쿼리(선행테이블, outer)의 행이 서브쿼리(후행테이블, inner)의 행과 match 되면 조인조건에 만족되니
해당 행에 대해서 더이상 검색을 진행하지 않는다. 그다음 메인쿼리의 행을 가지고 다시 수행한다.
- Hint : nl_sj, merge_sj, hash_sj
> eqi join + exists
# unnest + nl_sj
HR@ora11g> select /*+ gather_plan_statistics */ *
from departments d
where exists (select /*+ unnest nl_sj */ 'x'
from employees
where department_id = d.department_id);
11 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 11 |00:00:00.01 | 10 |
| 1 | NESTED LOOPS SEMI | | 1 | 10 | 11 |00:00:00.01 | 10 |
| 2 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 7 |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 27 | 41 | 11 |00:00:00.01 | 3 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPARTMENT_ID"="D"."DEPARTMENT_ID")
> d.dept_id=10 -> emp_dept_ix = 10 있다? -> 조인성립 -> 더이상 10번에 대해 스캔 끝, 다음 수행
# unnest + merge_sj
HR@ora11g> select /*+ gather_plan_statistics */ *
from departments d
where exists (select /*+ unnest merge_sj */ 'x'
from employees
where department_id = d.department_id);
11 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
-------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 11 |00:00:00.01 | 5 | | | |
| 1 | MERGE JOIN SEMI | | 1 | 10 | 11 |00:00:00.01 | 5 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 27 | 12 |00:00:00.01 | 4 | | | |
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 27 | 12 |00:00:00.01 | 2 | | | |
|* 4 | SORT UNIQUE | | 12 | 107 | 11 |00:00:00.01 | 1 | 2048 | 2048 | 2048 (0)|
| 5 | INDEX FULL SCAN | EMP_DEPARTMENT_IX | 1 | 107 | 106 |00:00:00.01 | 1 | | | |
-------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("DEPARTMENT_ID"="D"."DEPARTMENT_ID")
# unnest + hash_sj
HR@ora11g> select /*+ gather_plan_statistics */ *
from departments d
where exists (select /*+ unnest hash_sj */ 'x'
from employees
where department_id = d.department_id);
11 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
---------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 11 |00:00:00.01 | 8 | | | |
|* 1 | HASH JOIN SEMI | | 1 | 10 | 11 |00:00:00.01 | 8 | 986K| 986K| 1212K (0)|
| 2 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 6 | | | |
| 3 | INDEX FULL SCAN | EMP_DEPARTMENT_IX | 1 | 107 | 106 |00:00:00.01 | 2 | | | |
---------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPARTMENT_ID"="D"."DEPARTMENT_ID")
# not exists
HR@ora11g> select /*+ gather_plan_statistics */ *
from departments d
where not exists (select /*+ no_unnest */ 'x'
from employees
where department_id = d.department_id);
16 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 16 |00:00:00.01 | 34 |
|* 1 | FILTER | | 1 | | 16 |00:00:00.01 | 34 |
| 2 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 7 |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 27 | 2 | 11 |00:00:00.01 | 27 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NULL)
3 - access("DEPARTMENT_ID"=:B1)
■ ANTI JOIN
- 조인의 값이 존재하지 않는 행을 찾는다.
- 서브쿼리를 조인으로 바꾼다.
- 조인으로 바뀐 서브쿼리는 항상 후행 처리가 된다.(inner)
- 메인쿼리(선행테이블, outer)의 행이 서브쿼리(후행테이블, inner)의 행과 match 되면 버려야 할 데이터이고
서브쿼리에 존재하지 않으면 우리가 찾는 데이터이다. 그 다음 메인쿼리의 행을 가지고 다시 수행한다.
- Hint : nl_aj, merge_aj, hash_aj
# unnest + nl_aj
-- 소속사원이 없는 부서 정보 출력
HR@ora11g> select /*+ gather_plan_statistics */ *
from departments d
where not exists (select /*+ unnest nl_aj */ 'x'
from employees
where department_id = d.department_id);
16 rows selected.
HR@ora11g> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 16 |00:00:00.01 | 11 |
| 1 | NESTED LOOPS ANTI | | 1 | 17 | 16 |00:00:00.01 | 11 |
| 2 | TABLE ACCESS FULL| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 7 |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 27 | 41 | 11 |00:00:00.01 | 4 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPARTMENT_ID"="D"."DEPARTMENT_ID")
> departments : 1쪽집합 : 드라이빙
> NESTED LOOPS ANTI : join + not exists
> d.dept_id=10 -> emp_dept_ix = 10 있다? -> 조인성립 -> 10번 제외, 다음 수행
'Oracle 11g > Tuning' 카테고리의 다른 글
| 90 Pushdown, Pullup, Use_concat, Optimazer (0) | 2024.02.22 |
|---|---|
| 89 Pushing Subquery, View Merging, Pushdown (0) | 2024.02.21 |
| 87 Nested Loop Join, Sort Merge Join (0) | 2024.02.19 |
| 86 PGA, Sort Operation, Join, Table Prefetch, Batch I/O (0) | 2024.02.16 |
| 85 Row Chaining, Row Migration, B*tree, Bitmap Index, session_cached_cursors (0) | 2024.02.15 |