| # 요약 | |
| ■ Pushing Subquery push_subq ■ view merging merge no_merge 1. simple view merging 2. complex view merging ■ 조인 조건 pushdown no_push_pred ■ Bloom Filter |
|
■ Pushing Subquery
- 실행계획상 가능한 앞 단계에서 서브쿼리의 필터링을 먼저 처리함으로써 다음 수행 단계로 넘어가는 행 수를 줄일 수 있는 기능
- unnesting 되지 않은 서브쿼리의 처리 순서를 제어하는 기능
- Hint : push_subq
# unnest 방식
HR@ora11g> select /*+ gather_plan_statistics */ e.*, d.*
from employees e, departments d
where e.department_id = d.department_id
and exists (select 'x'
from locations
where location_id = d.location_id
and city = 'London');
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 | | 1 |00:00:00.01 | 8 | | | |
| 1 | NESTED LOOPS | | 1 | 15 | 1 |00:00:00.01 | 8 | | | |
| 2 | NESTED LOOPS | | 1 | 40 | 1 |00:00:00.01 | 7 | | | |
| 3 | NESTED LOOPS | | 1 | 4 | 1 |00:00:00.01 | 5 | | | |
| 4 | SORT UNIQUE | | 1 | 1 | 1 |00:00:00.01 | 2 | 2048 | 2048 | 2048 (0)|
| 5 | TABLE ACCESS BY INDEX ROWID| LOCATIONS | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
|* 6 | INDEX RANGE SCAN | LOC_CITY_IX | 1 | 1 | 1 |00:00:00.01 | 1 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 1 | 4 | 1 |00:00:00.01 | 3 | | | |
|* 8 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 4 | 1 |00:00:00.01 | 2 | | | |
|* 9 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 10 | 1 |00:00:00.01 | 2 | | | |
| 10 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 4 | 1 |00:00:00.01 | 1 | | | |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("CITY"='London')
8 - access("LOCATION_ID"="D"."LOCATION_ID")
9 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
> query trasformation 작동 > 조인방식
> 서브쿼리 LOC_CITY_IX > DEPT_LOCATION_IX > EMP_DEPARTMENT_IX
> 근데 SORT UNIQUE 왜 나옴? > LOC_CITY_IX : nonunique index라서 중복값 있을까바 optimizer가 불안했던 듯?
↓ JOIN 방식으로 변경
HR@ora11g> select /*+ gather_plan_statistics */ e.*, d.*
from employees e, departments d, locations l
where e.department_id = d.department_id
and d.location_id = l.location_id
and city = 'London';
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 | | 1 |00:00:00.01 | 9 |
| 1 | NESTED LOOPS | | 1 | 15 | 1 |00:00:00.01 | 9 |
| 2 | NESTED LOOPS | | 1 | 40 | 1 |00:00:00.01 | 8 |
| 3 | NESTED LOOPS | | 1 | 4 | 1 |00:00:00.01 | 6 |
| 4 | TABLE ACCESS BY INDEX ROWID| LOCATIONS | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 5 | INDEX RANGE SCAN | LOC_CITY_IX | 1 | 1 | 1 |00:00:00.01 | 2 |
| 6 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 4 | 1 |00:00:00.01 | 3 |
|* 7 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 4 | 1 |00:00:00.01 | 2 |
|* 8 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 10 | 1 |00:00:00.01 | 2 |
| 9 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 4 | 1 |00:00:00.01 | 1 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("CITY"='London')
7 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
8 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
> 5번 buffer 2번? : INDEX RANGE SCAN = 1+1 scan = buffer pinning 미작동
> sort 작업은 하지 않았으니 이게 더 효율
# filter 방식 수행 (no_unnest 적용)
HR@ora11g> select /*+ gather_plan_statistics */ e.*, d.*
from employees e, departments d
where e.department_id = d.department_id
and exists (select /*+ no_unnest */ 'x'
from locations
where location_id = d.location_id
and city = 'London');
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 | | 1 |00:00:00.01 | 24 | | | |
|* 1 | FILTER | | 1 | | 1 |00:00:00.01 | 24 | | | |
| 2 | MERGE JOIN | | 1 | 106 | 106 |00:00:00.01 | 10 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 4 | | | |
| 4 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 27 | 27 |00:00:00.01 | 2 | | | |
|* 5 | SORT JOIN | | 27 | 107 | 106 |00:00:00.01 | 6 | 15360 | 15360 |14336 (0)|
| 6 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
|* 7 | TABLE ACCESS BY INDEX ROWID | LOCATIONS | 7 | 1 | 1 |00:00:00.01 | 14 | | | |
|* 8 | INDEX UNIQUE SCAN | LOC_ID_PK | 7 | 1 | 7 |00:00:00.01 | 7 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
7 - filter("CITY"='London')
8 - access("LOCATION_ID"=:B1)
> 메인쿼리 Full scan - MERGE JOIN
# push_subq : unnest가 수행 안되었을 경우 서브쿼리를 먼저 수행하자
HR@ora11g> select /*+ gather_plan_statistics */ e.*, d.*
from employees e, departments d
where e.department_id = d.department_id
and exists (select /*+ no_unnest push_subq */ 'x'
from locations
where location_id = d.location_id
and city = 'London');
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 | | 1 |00:00:00.01 | 24 |
| 1 | NESTED LOOPS | | 1 | 13 | 1 |00:00:00.01 | 24 |
| 2 | NESTED LOOPS | | 1 | 13 | 1 |00:00:00.01 | 23 |
|* 3 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 1 | 1 |00:00:00.01 | 21 |
|* 4 | TABLE ACCESS BY INDEX ROWID| LOCATIONS | 7 | 1 | 1 |00:00:00.01 | 14 |
|* 5 | INDEX UNIQUE SCAN | LOC_ID_PK | 7 | 1 | 7 |00:00:00.01 | 7 |
|* 6 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 10 | 1 |00:00:00.01 | 2 |
| 7 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 10 | 1 |00:00:00.01 | 1 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter( IS NOT NULL)
4 - filter("CITY"='London')
5 - access("LOCATION_ID"=:B1)
6 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
> outer : DEPARTMENTS
inner : EMP_DEPARTMENT_IX
> 3 ~ 5 수행하는 SQL문
HR@ora11g> select *
from departments d
where exists (select 'x'
from locations
where location_id = d.location_id
and city = 'London')
and location_id is not null; --> d.location_id에 not null 제약조건이 없기 때문
■ view merging
- view를 해체하여 메인쿼리와 통합한다.
- Hint : merge, no_merge
# merge
HR@ora11g> select /*+ gather_plan_statistics */ *
from (select * from employees where manager_id = 145) e,
(select * from departments where location_id = 2500) d
where e.department_id = d.department_id;
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 | | 6 |00:00:00.01 | 7 |
| 1 | NESTED LOOPS | | 1 | 1 | 6 |00:00:00.01 | 7 |
| 2 | NESTED LOOPS | | 1 | 6 | 6 |00:00:00.01 | 5 |
| 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_MANAGER_IX | 1 | 6 | 6 |00:00:00.01 | 2 |
|* 6 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 6 | 1 | 6 |00:00:00.01 | 2 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("LOCATION_ID"=2500)
5 - access("MANAGER_ID"=145)
6 - filter("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID")
# no_merge
HR@ora11g> select /*+ gather_plan_statistics */ *
from (select /*+ no_merge */ * from employees where manager_id = 145) e,
(select /*+ no_merge */ * from departments where location_id = 2500) d
where e.department_id = d.department_id;
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 | | 6 |00:00:00.01 | 6 | | | |
|* 1 | HASH JOIN | | 1 | 1 | 6 |00:00:00.01 | 6 | 1033K| 1033K| 350K (0)|
| 2 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
|* 4 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 1 | 1 |00:00:00.01 | 1 | | | |
| 5 | VIEW | | 1 | 6 | 6 |00:00:00.01 | 4 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 6 | 6 |00:00:00.01 | 4 | | | |
|* 7 | INDEX RANGE SCAN | EMP_MANAGER_IX | 1 | 6 | 6 |00:00:00.01 | 2 | | | |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
4 - access("LOCATION_ID"=2500)
7 - access("MANAGER_ID"=145)
↓ merge 수행 동일
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees e, departments d
where e.department_id = d.department_id
and d.location_id = 2500
and e.manager_id = 145;
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 | | 6 |00:00:00.01 | 7 |
| 1 | NESTED LOOPS | | 1 | 1 | 6 |00:00:00.01 | 7 |
| 2 | NESTED LOOPS | | 1 | 6 | 6 |00:00:00.01 | 5 |
| 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_MANAGER_IX | 1 | 6 | 6 |00:00:00.01 | 2 |
|* 6 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 6 | 1 | 6 |00:00:00.01 | 2 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."LOCATION_ID"=2500)
5 - access("E"."MANAGER_ID"=145)
6 - filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
> 처음부터 6개 행 추출
> DEPT_LOCATION_IX + EMP_MANAGER_IX 조인 안되는건데? > Cartesian Product (카티시안 곱) 발생
↓ e.manager_id 인덱스 스캔을 막아보자.
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees e, departments d
where e.department_id = d.department_id
and d.location_id = 2500
and to_number(e.manager_id) = 145;
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 | | 6 |00:00:00.01 | 7 |
| 1 | NESTED LOOPS | | 1 | 1 | 6 |00:00:00.01 | 7 |
| 2 | NESTED LOOPS | | 1 | 10 | 34 |00:00:00.01 | 5 |
| 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 | 34 |00:00:00.01 | 2 |
|* 6 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 34 | 1 | 6 |00:00:00.01 | 2 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("D"."LOCATION_ID"=2500)
5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
6 - filter(TO_NUMBER(TO_CHAR("E"."MANAGER_ID"))=145)
> 34행 데이터에서 필터링으로 6개 추출, 버퍼피닝 덕분에 버퍼가 적게 나왔을 뿐 비효율
1. simple view merging
- view, inline view 내부에 group by, distinct 등이 없는 view를 의미한다.
- simple view를 만나면 view를 해체하고 메인쿼리와 통합하는 작업을 수행한다.
# no_merge
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees e, (select /*+ no_merge */ * from departments where department_id = 20) d
where e.department_id = d.department_id;
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 | | 2 |00:00:00.01 | 6 |
| 1 | NESTED LOOPS | | 1 | 10 | 2 |00:00:00.01 | 6 |
| 2 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 2 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 4 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | 1 | 1 |00:00:00.01 | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 10 | 2 |00:00:00.01 | 4 |
|* 6 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 10 | 2 |00:00:00.01 | 2 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPARTMENT_ID"=20)
6 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
# merging
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees e, (select * from departments where department_id = 20) d
where e.department_id = d.department_id;
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 | | 2 |00:00:00.01 | 6 |
| 1 | NESTED LOOPS | | 1 | 2 | 2 |00:00:00.01 | 6 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 3 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | 1 | 1 |00:00:00.01 | 1 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 | 2 |00:00:00.01 | 4 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 2 | 2 |00:00:00.01 | 2 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPARTMENT_ID"=20)
5 - access("E"."DEPARTMENT_ID"=20)
> d.department_id = 20 값이 정해져 있으니 조인 필요없다 -> 카티시안 곱 발생
↓ Transformation
HR@ora11g> select /*+ gather_plan_statistics */ *
from employees e, departments d
where e.department_id = 20
and d.department_id = 20;
> 의도적으로 카티시안 곱 발생
2. complex view merging
- group by 나 distinct 가 있으면 view, inline view를 merging 한다.
alter session set "_complex_view_merging" = true; (9i 기본값)
- complex view 안에 union, union all, intersect, minus, connect by, rownum,
group by 없는 전체 집계값을 구현하는 그룹함수, 분석함수가 포함되어 있을 경우 merging 할 수 없다.
HR@ora11g> select /*+ gather_plan_statistics */ d.department_id, department_name, e.avg_sal
from departments d, (select department_id, avg(salary) avg_sal
from employees
group by department_id) e
where d.department_id = e.department_id
and d.location_id = 1800;
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 | | 1 |00:00:00.01 | 4 | | | |
| 1 | HASH GROUP BY | | 1 | 1 | 1 |00:00:00.01 | 4 | 810K| 810K| 425K (0)|
| 2 | NESTED LOOPS | | 1 | 10 | 2 |00:00:00.01 | 4 | | | |
| 3 | NESTED LOOPS | | 1 | 10 | 2 |00:00:00.01 | 3 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
|* 5 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 1 | 1 |00:00:00.01 | 1 | | | |
|* 6 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 10 | 2 |00:00:00.01 | 1 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 2 | 10 | 2 |00:00:00.01 | 1 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("D"."LOCATION_ID"=1800)
6 - access("D"."DEPARTMENT_ID"="DEPARTMENT_ID")
- HASH GROUP BY : group by 사용
↓ JOIN
HR@ora11g> select /*+ gather_plan_statistics */ d.department_id, d.department_name, avg(e.salary)
from departments d, employees e
where d.department_id = e.department_id
and d.location_id = 1800
group by d.department_id, d.department_name;
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 | | 1 |00:00:00.01 | 4 | | | |
| 1 | HASH GROUP BY | | 1 | 1 | 1 |00:00:00.01 | 4 | 850K| 850K| 425K (0)|
| 2 | NESTED LOOPS | | 1 | 10 | 2 |00:00:00.01 | 4 | | | |
| 3 | NESTED LOOPS | | 1 | 10 | 2 |00:00:00.01 | 3 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
|* 5 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 1 | 1 |00:00:00.01 | 1 | | | |
|* 6 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 10 | 2 |00:00:00.01 | 1 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 2 | 10 | 2 |00:00:00.01 | 1 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("D"."LOCATION_ID"=1800)
6 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
■ 조인 조건 pushdown
- 조인조건절을 뷰 쿼리 블록안으로 넣어서 조인수행 시 드라이빙 테이블에 읽은 조인컬럼값을 inner쪽 뷰 쿼리 블록내에서 참조할 수 있도록 하는 기능
# no_merge
HR@ora11g> select /*+ gather_plan_statistics */ d.department_id, department_name, e.avg_sal
from departments d, (select /*+ no_merge */ department_id, avg(salary) avg_sal
from employees
group by department_id) e
where d.department_id = e.department_id
and d.location_id = 1800;
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 | | 1 |00:00:00.01 | 5 |
| 1 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 5 |
| 2 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 3 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 1 | 1 |00:00:00.01 | 2 |
| 4 | VIEW PUSHED PREDICATE | | 1 | 1 | 1 |00:00:00.01 | 2 |
|* 5 | FILTER | | 1 | | 1 |00:00:00.01 | 2 |
| 6 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 2 |
| 7 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 10 | 2 |00:00:00.01 | 2 |
|* 8 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 10 | 2 |00:00:00.01 | 1 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."LOCATION_ID"=1800)
5 - filter(COUNT(*)>0)
8 - access("DEPARTMENT_ID"="D"."DEPARTMENT_ID") --> 인라인뷰안에 그룹바이 자리에 위치
- VIEW PUSHED PREDICATE : Transformation
- FILTER : 반복
- no_merge : merge가 안되었을 시 재연용으로 사용했을 뿐
- and 조건이 없었다면 잘 쓴 SQL문
- 메인where절 -> group by 대신 사용
- inner : employees
■ Bloom Filter
- hash join 시 probe(후행) 집합에서 조인에 참여하는 건수를 줄임으로써 join 시간을 단축시키는 알고리즘
# no_merge no_push_pred : 조인조건을 인라인뷰에 푸쉬하지마라
HR@ora11g> select /*+ gather_plan_statistics */ d.department_id, department_name, e.avg_sal
from departments d, (select /*+ no_merge no_push_pred*/ department_id, avg(salary) avg_sal
from employees
group by department_id) e
where d.department_id = e.department_id
and d.location_id = 1800;
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 | | 1 |00:00:00.01 | 8 | | | |
|* 1 | HASH JOIN | | 1 | 1 | 1 |00:00:00.01 | 8 | 1114K| 1114K| 245K (0)|
| 2 | JOIN FILTER CREATE | :BF0000 | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
|* 4 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 1 | 1 |00:00:00.01 | 1 | | | |
| 5 | VIEW | | 1 | 11 | 1 |00:00:00.01 | 6 | | | |
| 6 | HASH GROUP BY | | 1 | 11 | 1 |00:00:00.01 | 6 | 894K| 894K| 680K (0)|
| 7 | JOIN FILTER USE | :BF0000 | 1 | 107 | 2 |00:00:00.01 | 6 | | | |
|* 8 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 2 |00:00:00.01 | 6 | | | |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("D"."DEPARTMENT_ID"="E"."DEPARTMENT_ID")
4 - access("D"."LOCATION_ID"=1800)
8 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"DEPARTMENT_ID"))
- SYS_OP_BLOOM_FILTER -> Bloom Filter
- build : DEPARTMENTS
probe : EMPLOYEES : M쪽집합
- JOIN FILTER CREATE
- JOIN FILTER USE
>> 알고리즘
'Oracle 11g > Tuning' 카테고리의 다른 글
| 91 통계정보 (0) | 2024.02.23 |
|---|---|
| 90 Pushdown, Pullup, Use_concat, Optimazer (0) | 2024.02.22 |
| 88 Hash Join, unnest, eliminate_join, SEMI JOIN, ANTI JOIN (0) | 2024.02.20 |
| 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 |