728x90
반응형
SMALL
■ pushing subquery
1. 정의
- 실행 계획의 초기 단계에서 서브쿼리 필터링을 먼저 처리하여, 다음 수행 단계로 전달되는 행의 수를 줄일 수 있는 기능
- Unnesting되지 않은 서브쿼리의 처리 순서를 제어하여 최적화된 실행 계획을 구성하는 방법 중 하나이다.
2. 힌트
- `push_subq` 힌트는 Oracle Optimizer에게 서브쿼리를 최적화하는 방식을 알려주는 역할이다.
- 이 힌트를 사용하면 서브쿼리의 필터링을 가능한 한 빨리 수행하도록 유도하여 효율적인 실행 계획을 생성할 수 있다.
3. 활용
- 서브쿼리가 Unnesting되지 않았을 때, 전체 테이블을 스캔하는 비효율적인 실행 계획을 방지하고자 할 때 Pushing Subquery를 사용한다.
- Pushing Subquery 힌트를 통해 서브쿼리를 먼저 처리하고 필터링하여 최적의 성능을 얻을 수 있다.
※ 요약 ※
- Pushing Subquery는 서브쿼리 필터링을 초기 단계에서 수행하여 전체적인 실행 계획의 성능을 향상시키는 기능.
- `push_subq` 힌트를 사용하여 Oracle Optimizer에게 최적화 방식을 제어할 수 있다.
- Unnesting되지 않은 서브쿼리의 처리 순서를 제어하고, 효율적인 실행 계획을 구성할 수 있다.
#) filter 방식 수행
- 쿼리 최적화 및 실행 계획에서 사용되는 용어 중 하나로, 데이터베이스에서 검색 조건을 처리하는 방법을 나타낸다.
<hr sess>
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');
#) 실행 계획 확인
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 | | | | -> first
| 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)| -> second
| 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)
#) unnest 가 수행되지 않았을 경우, 서브쿼리를 먼저 수행하자
<hr sess>
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');
#) 실행 계획 확인
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 | --outer
|* 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 | --inner
| 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) =: location_id
4 - filter("CITY"='London')
5 - access("LOCATION_ID"=:B1) /* (=: 내부적 exists ) */
6 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
# 3,4,5번 을 수행하는 SQL문
SELECT *
FROM departments d -- 3번
WHERE EXISTS ( SELECT 'x'
FROM location -- 4번
WHERE location_id = d.location_id -- 5번
AND city = 'London')
AND location_id is not null;(이후) 6번인 EMP_DEPARTMENT_IX 과 join
#) unnest 방식
<hr sess>
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');
#) 실행 계획 확인
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 | | | | --1) 서브쿼리 실행
|* 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 | | | | -- 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")
#) 서브쿼리에 있는 table을 join 을 해주면 실행계획 상에서 'sort unique' 사라진다.
<hr sess>
select /*+ gather_plan_statistics */ e.*, d.*
from employees e, departments d, locations l
where e.department_id = d.department_id
and l.location_id = d.location_id
and l.city = 'London';
#) 실행 계획 확인
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 | /* 2. block i/o 1 증가됨 */
| 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 | /* 1. why? buffer pinning 이 돌아가지 않아서 */
|* 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("L"."CITY"='London')
7 - access("L"."LOCATION_ID"="D"."LOCATION_ID")
8 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
■ view merging
1. 정의
- 뷰 병합(View Merging)은 데이터베이스 쿼리 최적화 기술로, 뷰를 사용한 쿼리를 해당 뷰를 구성하는 원본 테이블 또는 뷰로 변환하여 최적화하는 과정이다. 이를 통해 뷰의 정의를 메인 쿼리와 통합하여 성능을 향상시킨다.
2. 힌트
- /*+ MERGE(viewname) */: 뷰 병합을 지시하는 힌트로, 뷰를 메인 쿼리에 통합
- /*+ NO_MERGE(viewname) */: 뷰를 통합하지 않고 그대로 유지
3. 이점
- 성능 향상: 뷰 병합을 통해 불필요한 뷰 계층을 제거하고 메인 쿼리 최적화로 성능을 향상시킵니다.
- 코드 단순화: 복잡한 뷰를 풀어서 메인쿼리에 직접 통합함으로써 코드를 단순화하고 유지보수성을 높인다.
4. 주의사항
- 모든 뷰가 항상 병합 가능한 것은 아니다. 뷰의 정의에 따라서 병합이 불가능한 경우도 있다. 뷰 병합은 쿼리 옵티마이저에 의해 자동으로 수행될 수 있다.
#) inline view
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;
#) 실행 계획 확인
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")
#) inline view에 no_merge 힌트 사용
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;
#) 실행 계획 확인
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| 381K (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)
#) 해체 작업 -> join 문
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;
#) 실행 계획 확인
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") /* : join */=> 4,5번이 파티션곱이 발생했으나 문제가 되지 않는 이유? 데이터가 1건이여서
#) index scan X
=> 악성코드가 됨.
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;
#) 실행 계획 확인
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)
1. simple view merging
- view, inline view 내부에 group bym distinct 등이 없는 view 를 의미한다.
- simple view 를 만나면 view 를 해체하고 메인 쿼리와 통합하는 작업을 수행한다.
#) view merging이 돌아가지 않았을 경우
select /*+ gather_plan_statistics */ *
from employees e, (select * from departments where department_id = 20) d
where e.department_id = d.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 | | 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) /* 굳이 조인을 해야 하나? 파티션곱을 해도 무방한 곳. 조인조건 생략 */
2. no_merge 힌트 사용
select /*+ gather_plan_statistics */ *
from employees e, (select /*+ no_merge */ * from departments where department_id = 20) d
where e.department_id = d.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 | | 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")
#) 맞는 쿼리문으로 수정
(파티션곱이 꼭 나쁘다고 판단할 수는 없다!)
select /*+ gather_plan_statistics */ *
from employees e, departments d
where e.department_id = 20
and d.department_id = 20;
#) 실행 계획 확인
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("D"."DEPARTMENT_ID"=20)
5 - access("E"."DEPARTMENT_ID"=20)
2. complex view merging(복합뷰)
- 기본 group by절이나 distinct(키워드) 가 있는 (create) view, inline view를 merge 한다.
- 기본값 (9i)
alter session set "_complex_view_merging" = true;
- complex view 안에 union, union all, intersect, minus, connect by, rownum, group by 없는 전체 집계값을 구현하는 그룹함수, 분석함수가 포함되어 있을 경우 merging을 할 수 없다.
#) 해당 쿼리문의 문제점
SELECT /*+ gather_plan_statistics */ d.department_id, d.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;
#) 실행 계획 확인
=> full scan이 없다면? 해제한것.
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| 426K (0)| -- hash table
| 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 | | | | -- batch i/o 기법
| 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")
#) 해결 쿼리문
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;
#) 실행 계획 확인
=> view merge 의 이점
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| 426K (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")
■ join 조건 pushdown
- 조인 조건절을 뷰 쿼리 블록안으로 넣어서 조인 수행시 드라이빙 테이블에서 읽은 조인컬럼값을 inner 쪽 뷰 쿼리 블록 내에서 참조할 수 있도록 하는 기능
#) no_merge 힌트 사용
SELECT /*+ gather_plan_statistics */ d.department_id, d.department_name, e.avg_sal
FROM departments d, (SELECT /*+ no_merge */ department_id, avg(salary) avg_sal
FROM employees -- full scan 예상
GROUP BY department_id) e
WHERE d.department_id = e.department_id
AND d.location_id = 1800;
#) 실행 계획 확인
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 | -- hash group by 없음
| 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 | -- where 절에 있는 d.department_id = 1800을 통해서 찾음
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."LOCATION_ID"=1800)
5 - filter(COUNT(*)>0)
8 - access("DEPARTMENT_ID"="D"."DEPARTMENT_ID") : group by 절
# 4,5,6,7,8 번: inline view 쿼리문
=> group by 절을 없애고 메인 쿼리문에 있는 where절을 group by절을 대입해서 수행
SELECT department_id, avg(salary) avg_sal <- 그룹함수 값 (d.location_id=1800)
FROM employees e, departments d
(GROUP BY e.department_id)
WHERE d.department_id = e.department_id <- 제한
AND d.location_id = 1800;
#) no_merge no_push_pred 힌트 사용
SELECT /*+ gather_plan_statistics */ d.department_id, d.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;
#) 실행 계획 확인
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| 243K (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| 670K (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"))
728x90
반응형
LIST
'Data Base > SQL 튜닝' 카테고리의 다른 글
| SEMI JOIN, ANTI JOIN (0) | 2024.02.23 |
|---|---|
| Query Transformation, 서브쿼리 처리 방식(in, exists) (0) | 2024.02.23 |
| hash join (0) | 2024.02.23 |
| Sort Merge Join (0) | 2024.02.20 |
| join 실행 계획 (0) | 2024.02.19 |