728x90
반응형
SMALL
■ Query Transformation(=: 쿼리 변환)
- optimizer(실행계획을 생성)가 실행계획을 생성하기 전에 사용자가 작성한 SQL문 결과를 동일하나 비용이(cost 값) 더 적게 발생한 것 같으면 쿼리를 변경한다.
(in보다는 exists)
■ 서브쿼리 처리 방식(in, exists)
1. filter 방식
메인 쿼리에서 읽히는 row마다 서브쿼리를 반복수행 하면서 조건에 맞는 데이터를 찾는 방식.
<hr sess>
select /*+ gather_plan_statistics */ *
from employees
where department_id in (select /*+ no_unnest */ department_id from departments);
#) 실행 계획 확인
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
select /*+ gather_plan_statistics */ *
from employees e
where exists (select /*+ no_unnest */ 'x'
from departments
where department_id = e.department_id);
/*바인드 변수 처리됨*/
#) 실행 계획 확인
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)
-> filter 방식이란?
-> nested loop 방식? random i/o 발생량 높음 => (해결방법)sort/hash join
2. unnest 방식
- 동일한 결과를 보장한다면 서브쿼리를 join문으로 변환한다.(이점)
- 조인으로 변환하면 다양한 액세스 경로, 조인 방법, 조인 순서를 결정할 수 있다.
- 힌트 : unnest(filter 방식 대신 조인 방식으로 실행계획을 만들어 줘),
no_unnest(기존의 filter 방식으로 실행계획을 만들어줘)
- exists : 조인 조건에 성립하면 서브쿼리에 행을 만나는 순간 결과집합에 담고 다음 메인쿼리 행으로 이동
- not exists : 조인에 성공하는 서브쿼리에 행을 만나는 순간 버리고 다음 메인쿼리 행으로 이동, 서브쿼리에 조인으로 성립되지 않은 행에 대해서 결과 집합에 담는다.
#) 소속 부서가 있는 사원 출력
-> 제약조건
select /*+ gather_plan_statistics */ *
from employees
where department_id in (select department_id from departments);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)
#) 변환 후의 쿼리문
-> 조건: 소속 부서의 속했을 경우
select /*+ gather_plan_statistics */ *
from employees
where department_id is not null;
※ 조인제거
- 1 : m 관계인 두 테이블을 조인하는 쿼리문에서 1쪽 테이블을 참조하지 않는다면 1쪽 테이블을 읽지 않아도 된다.
즉 조인제거 기능이 작동 되려면 primary key, foreign key 설정 되어 있어야만 (이점) 조인제거(join elmination) 기능이 수행된다.
- 힌트 : eliminate_join, no_eliminate_join
select u.table_name, c.column_name, u.constraint_name, u.constraint_type,u.search_condition, u.r_constraint_name, u.index_name
from user_constraints u, user_cons_columns c
where u.constraint_name = c.constraint_name
and u.table_name in ('EMPLOYEES','DEPARTMENTS');
# 조인 제거 비활성화 (=> 시물레이션화 하기 위함. 종종 실무적으로는 활용하지 않음)
alter session set "_optimizer_join_elimination_enabled" = false;
select /*+ gather_plan_statistics */ *
from employees
where department_id in (select department_id from departments);
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 |
-------------------------------------------------------------------------------------------
/* 조인문X, departments를 access 하지 않음. */
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
# 조인 제거 활성화 (기본값) :히든파라미터 설정가능하다.
alter session set "_optimizer_join_elimination_enabled" = true;
select /*+ gather_plan_statistics */ *
from employees
where department_id in (select department_id from departments);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)
select /*+ gather_plan_statistics */ *
from employees
where department_id is not null;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
# 조인 제거 비활성화 (힌트로 활용): no_eliminate_join()
select /*+ gather_plan_statistics */ *
from employees
where department_id in (select /*+ no_eliminate_join(d) */ department_id from departments d);
#) 실행 계획 확인
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
# 조인 제거 활성화 (힌트로 활용): eliminate_join()
select /*+ gather_plan_statistics */ *
from employees
where department_id in (select /*+ eliminate_join(d) */ department_id from departments d);
#) 실행 계획 확인
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
■ 서브쿼리를 조인으로 변환
메인쿼리의 employees 테이블과 서브쿼리의 departments 테이블이 m : 1 관계이기 때문에 조인문으로 바꾸더라도 쿼리 결과가 보장된다.
select /*+ gather_plan_statistics */ *
from employees
where department_id in (select department_id from departments where location_id = 1500);-> 데이터 변경되지 않을 경우
-> 메인쿼리절 m : 1 서브쿼리절 || 무조건 변경하진 않는다.
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 45 |00:00:00.01 | 12 | 1 |
| 1 | NESTED LOOPS | | 1 | 10 | 45 |00:00:00.01 | 12 | 1 |
| 2 | NESTED LOOPS | | 1 | 10 | 45 |00:00:00.01 | 7 | 1 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 1 | 1 |00:00:00.01 | 3 | 1 |
|* 4 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 1 | 1 |00:00:00.01 | 2 | 1 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 10 | 45 |00:00:00.01 | 4 | 0 |
| 6 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 45 | 10 | 45 |00:00:00.01 | 5 | 0 |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("LOCATION_ID"=1500)
5 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
#) 서브쿼리문 실행
select /*+ gather_plan_statistics */ *
from employees e
where exists (select /*+ no_unnest */ 'x'
from departments
where department_id = e.department_id
and location_id = 1500 );
#) 실행 계획 확인
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 | 31 |
|* 1 | FILTER | | 1 | | 45 |00:00:00.01 | 31 |
| 2 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 9 |
|* 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 12 | 1 | 1 |00:00:00.01 | 22 |
|* 4 | 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 - filter("LOCATION_ID"=1500)
4 - access("DEPARTMENT_ID"=:B1)
#) 위의 서브쿼리절과 동일함.
select /*+ gather_plan_statistics */ e.*
from employees e, departments d
where e.department_id = d.department_id
and d.location_id = 1500;
#) 실행 계획 확인
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("D"."LOCATION_ID"=1500)
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
# 소속 사원이 있는 부서 정보만 출력
<hr sess>
select *
from departments d
where exists (select 'x' from employees where department_id = d.department_id);
- 메인 1 : m 서브
# 위(서브쿼리)와 아래(join문)의 쿼리문의 결과가 다름 #
select d.*
from departments d, employees e
where d.department_id = e.department_id;
#) hash 유니크, sort 유니크 인 중복제거 -> 'distinct' 를 사용 -> 불필요한 i/o 발생
=> v$sql 을 통해 범위 스캔, hash_unique, sort_unique -> sql_id 찾기 -> 문제되는 쿼리문
select /*+ gather_plan_statistics */ d.*
from departments d, (select distinct department_id from employees) e
where d.department_id = e.department_id;
#) 실행 계획 확인
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| 1041K (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")
※ 서브쿼리를 조인으로 변환의 문제
departments 테이블이 기준 집합이므로 결과집합은 departments테이블의 총 건수를 넘지 않아야한다. 만약에 조인문장으로 변환한다면 m쪽 집합인 employees테이블 결과집합으로 만들어지기 때문에 잘못된 결과가 나온다.
select /*+ gather_plan_statistics */ *
from departments d
where exists (select 'x' from employees where department_id = d.department_id);select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
- emp테이블은 안거치고 emp의 index만 거쳐서 join후 exists로 있는지 없는지 체크하고 원하는 데이터를 뽑아온다.
- nested loops semi : equi join과 exists가 혼합 why? 1쪽집합처럼 만드려고
select /*+ gather_plan_statistics */ *
from departments d
where exists (select 'x' from employees where department_id = d.department_id);메인 1 : m 서브
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")728x90
반응형
LIST
'Data Base > SQL 튜닝' 카테고리의 다른 글
| join 조건 pushdown, BLOOM FILTER, 조건절 pushdown (0) | 2024.02.26 |
|---|---|
| SEMI JOIN, ANTI JOIN (0) | 2024.02.23 |
| pushing subquery, view merging, join 조건 pushdown (0) | 2024.02.23 |
| hash join (0) | 2024.02.23 |
| Sort Merge Join (0) | 2024.02.20 |