728x90
반응형
SMALL
■ join 조건 pushdown
1. 정의
- Join 조건 Pushdown은 뷰(View) 쿼리의 조인 조건절을 뷰 쿼리 블록 안으로 내리는 기술을 말합니다. 이는 드라이빙 테이블(Driving Table)에서 읽은 조인 컬럼 값들을 내부(inner) 쪽 뷰 쿼리 블록에서 참조할 수 있도록 하는 것이다.
2. 동작 방식
- 뷰 쿼리에 있는 조인 조건절이 뷰가 아닌 실제 테이블에 조인되도록 변경된다.
- 이렇게 변경된 조인 조건절은 드라이빙 테이블의 조인 컬럼 값을 뷰 내부 블록에서 참조할 수 있도록 한다.
- 조인 조건 Pushdown은 뷰를 사용하는 쿼리의 성능을 최적화하기 위한 방법 중 하나이다.
3. 특징
1) 성능 향상: Join 조건 Pushdown을 통해 뷰의 조인이 더 효율적으로 처리될 수 있어 성능 향상을 기대할 수 있다.
2) 내부 조인 컬럼 참조: 드라이빙 테이블에서 가져온 조인 컬럼 값을 내부 블록에서 참조할 수 있다.
3) SQL 최적화: 쿼리 실행 계획을 최적화하여 조인 조건 Pushdown이 유용한 상황에서 적용된다.
4. 활용
- Join 조건 Pushdown은 복잡한 뷰를 사용하는 쿼리에서 성능 최적화를 위해 고려될 수 있다
- 쿼리 옵티마이저의 기능을 활용하여 조인 조건 Pushdown을 적용할 수 있다.
#) no_merge 힌트 사용
=> 이렇게 쿼리문을 작성하는 것은 좋은 쿼리문이 아니다!
<hr sess>
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; /* and로 제한 */
#) 실행 계획 확인
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 | -- outer
| 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 | --inner
|* 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 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 힌트 사용
=> 메인 쿼리문대로 inline view 문대로 실행해줘!
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)| -- build table
| 2 | JOIN FILTER CREATE | :BF0000 | 1 | 1 | 1 |00:00:00.01 | 2 | | | | -- probe 생성
| 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 | | | | -- probe
| 6 | HASH GROUP BY | | 1 | 11 | 1 |00:00:00.01 | 6 | 894K| 894K| 670K (0)| -- probe 생성 후 사용
| 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"))=> hash 알고리즘
■ BLOOM FILTER
1. 정의
- 해시 조인(hash join) 시, 후행 집합(probe set)에서 조인에 참여하는 건수를 줄이는 알고리즘.
- 블룸 필터는 원소의 존재 여부를 압축하여 표현하며, 정확성보다는 공간 효율성을 추구함.
2. 동작 방식
- 블룸 필터는 해시 함수와 비트 배열을 사용하여 데이터 집합의 멤버십(Membership) 여부를 효율적으로 확인.
- 해시 함수를 통해 여러 해시 위치에 해당하는 비트를 설정하고, 검색 시에 해당 비트가 모두 설정되어 있으면 해당 원소가 존재할 확률이 높다고 가정.
3. 장점
- 해시 조인 시에 후행 집합에서 실제 조인에 참여할 가능성이 있는 데이터를 효과적으로 필터링하여 전체 데이터 양을 줄임.
- 메모리를 적게 사용하면서도 빠른 검색 속도를 제공함.
4. 한계
- 정확성 문제: 블룸 필터는 정확한 결과를 보장하지 않으며, 일부 오진(거짓 양성)이 발생할 수 있음.
- 해시 충돌 가능성: 해시 함수 충돌로 인해 여러 값이 동일한 비트 위치에 설정될 수 있음.
5. 활용
- 해시 조인 시에 큰 규모의 데이터를 효율적으로 필터링하여 조인 성능을 향상시킬 때 사용.
- 대량의 데이터를 메모리 내에 효과적으로 압축하고 검색하는 경우에 유용.
■ 조건절 pushdown
1. 정의
- 그룹화(group by)된 뷰 쿼리에서 조인되는 테이블의 조건절을 쿼리 블록 안으로 이동하여 처리 성능을 최적화하는 기술.
2. 동작 방식
- 그룹화된 뷰를 병합(merge)하려는 경우에 실패했을 때, 조건절을 뷰 쿼리 블록 안쪽으로 이동시켜 조인되는 데이터 양을 줄임.
3. 장점
- 그룹화된 뷰의 조인 처리에서 성능 최적화를 위해 조건절을 뷰 쿼리 블록 안쪽으로 이동함으로써 전체 데이터 양을 줄이고 쿼리 성능을 향상.
4. 활용
- 그룹화된 뷰를 사용하는 쿼리에서 성능 문제가 발생했을 때, 조건절 Pushdown을 고려하여 최적화.
<hr sess>
SELECT /*+ gather_plan_statistics */ *
FROM (SELECT department_id, sum(salary) sum_sal FROM employees GROUP BY department_id)
WHERE department_id = 20;DEPARTMENT_ID SUM_SAL
------------- ----------
20 20900
#) 실행 계획 확인
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 | 2 |
| 1 | SORT GROUP BY NOSORT | | 1 | 1 | 1 |00:00:00.01 | 2 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 | 2 |00:00:00.01 | 2 |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 2 | 2 |00:00:00.01 | 1 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPARTMENT_ID"=20)
■ 조건절 pullup
1. 정의
- 조건절 pullup은 쿼리 블록 안쪽에 있는 조건들을 바깥쪽으로 이동시켜, 조건 처리의 효율성을 높이는 기술.
- 주로 하위 쿼리의 조건을 상위 쿼리로 이동시켜 상위 쿼리에서 조건 처리를 수행하는 방식.
2. 동작 방식
- 하위 쿼리에서 필터링되는 데이터의 양을 최소화하기 위해, 하위 쿼리의 조건들을 상위 쿼리로 올려서 최대한 조건 처리를 쿼리 블록 밖에서 수행함.
3. 장점
- 상위 쿼리에서 조건 처리를 수행하므로, 더 효율적인 데이터 필터링이 가능하며 성능 향상에 기여함.
- 쿼리 최적화를 위해 조건을 효과적으로 배치할 수 있음.
4. 활용
- 하위 쿼리에서의 조건 처리 비용이 상위 쿼리보다 크거나 최적화가 필요한 경우, 조건절 pullup을 고려하여 쿼리 최적화 수행.
#) 그룹함수 사용
SELECT /*+ gather_plan_statistics */ a.department_id, a.sum_sal, b.max_sal, b.min_sal
FROM (SELECT department_id, sum(salary) sum_sal FROM employees WHERE department_id = 20 GROUP BY department_id) a,
(SELECT department_id, max(salary) max_sal, min(salary) min_sal FROM employees GROUP BY department_id) b
WHERE a.department_id = b.department_id;DEPARTMENT_ID SUM_SAL MAX_SAL MIN_SAL
------------- ---------- ---------- ----------
20 20900 14300 6600
#) 실행 계획 확인
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 JOIN | | 1 | 1 | 1 |00:00:00.01 | 4 | 1280K| 1280K| 373K (0)|
| 2 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 2 | | | | -- inline view: a
| 3 | HASH GROUP BY | | 1 | 1 | 1 |00:00:00.01 | 2 | 943K| 943K| 670K (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 | 2 |00:00:00.01 | 2 | | | |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 2 | 2 |00:00:00.01 | 1 | | | |
| 6 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 2 | | | | -- inline view : b
| 7 | HASH GROUP BY | | 1 | 1 | 1 |00:00:00.01 | 2 | 833K| 833K| 673K (0)|
| 8 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 | 2 |00:00:00.01 | 2 | | | |
|* 9 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 2 | 2 |00:00:00.01 | 1 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")
5 - access("DEPARTMENT_ID"=20)
9 - access("DEPARTMENT_ID"=20)
#1) pullup 순서 : a table의 where 절을 메인쿼리문 밖으로
SELECT /*+ gather_plan_statistics */ a.department_id, a.sum_sal, b.max_sal, b.min_sal
FROM (SELECT department_id, sum(salary) sum_sal FROM employees WHERE department_id = 20 GROUP BY department_id) a,
(SELECT department_id, max(salary) max_sal, min(salary) min_sal FROM employees GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND department_id = 20;
#2) pullup 순서 : 메인 쿼리문의 where절을 b table 안으로
SELECT /*+ gather_plan_statistics */ a.department_id, a.sum_sal, b.max_sal, b.min_sal
FROM (SELECT department_id, sum(salary) sum_sal FROM employees WHERE department_id = 20 GROUP BY department_id) a,
(SELECT department_id, max(salary) max_sal, min(salary) min_sal FROM employees WHERE department_id = 20 GROUP BY department_id) b
WHERE a.department_id = b.department_id;
# rollup, cube, grouping sets 개념 정리 #
1. rollup: 열 리스트를 그룹화 만드는 연산자 | group by rollup (a,b,c) => (a,b,c) (a,b) (a)
2. cube: 모든 컬럼을 그룹화 하는 연산자 | group by cube (a,b,c) => (a,b,c) (a,b) (a,c) (b,c) (a) (b) (c)
3. grouping sets: 원하는 컬럼을 그룹화 하는 연산자 | group by grouping sets ((a,b), (a,c)) => (a,b) (a,c)
#) 맞는 쿼리문
SELECT /*+ gather_plan_statistics */ a.department_id, a.sum_sal, b.max_sal, b.min_sal
FROM (SELECT department_id, sum(salary) sum_sal FROM employees WHERE department_id = 20 GROUP BY department_id) a,
(SELECT department_id, max(salary) max_sal, min(salary) min_sal FROM employees WHERE department_id = 20 GROUP BY department_id) b
WHERE a.department_id = b.department_id;DEPARTMENT_ID SUM_SAL MAX_SAL MIN_SAL
------------- ---------- ---------- ----------
20 20900 14300 6600
#) 실행 계획 확인
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 JOIN | | 1 | 1 | 1 |00:00:00.01 | 4 | 1280K| 1280K| 371K (0)|
| 2 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
| 3 | HASH GROUP BY | | 1 | 1 | 1 |00:00:00.01 | 2 | 943K| 943K| 681K (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 | 2 |00:00:00.01 | 2 | | | |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 2 | 2 |00:00:00.01 | 1 | | | |
| 6 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 2 | | | |
| 7 | HASH GROUP BY | | 1 | 1 | 1 |00:00:00.01 | 2 | 833K| 833K| 683K (0)|
| 8 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 | 2 |00:00:00.01 | 2 | | | |
|* 9 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 2 | 2 |00:00:00.01 | 1 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")
5 - access("DEPARTMENT_ID"=20)
9 - access("DEPARTMENT_ID"=20)
#) 대용량 data 이라면?
SELECT /*+ gather_plan_statistics */ *
FROM employees
WHERE job_id = 'IT_PROG' OR 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 | | 7 |00:00:00.01 | 7 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 7 | 7 |00:00:00.01 | 7 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("JOB_ID"='IT_PROG' OR "DEPARTMENT_ID"=20))=> full scan 하는이유? 'or 조건'
#) job_id
SELECT /*+ gather_plan_statistics */ *
FROM employees
WHERE job_id = 'IT_PROG';
#) 실행 계획 확인
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 | | 5 |00:00:00.02 | 4 | 1 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 5 | 5 |00:00:00.02 | 4 | 1 |
|* 2 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 5 | 5 |00:00:00.02 | 2 | 1 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB_ID"='IT_PROG')
#) department_id
SELECT /*+ gather_plan_statistics */ *
FROM employees
WHERE 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 | 4 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 | 2 |00:00:00.01 | 4 |
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 2 | 2 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID"=20)
#1) UNION
SELECT /*+ gather_plan_statistics */ *
FROM employees
WHERE job_id = 'IT_PROG'
UNION
SELECT *
FROM employees
WHERE 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 | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 7 |00:00:00.01 | 4 | | | |
| 1 | SORT UNIQUE | | 1 | 7 | 7 |00:00:00.01 | 4 | 2048 | 2048 | 2048 (0)|
| 2 | UNION-ALL | | 1 | | 7 |00:00:00.01 | 4 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 5 | 5 |00:00:00.01 | 2 | | | |
|* 4 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 5 | 5 |00:00:00.01 | 1 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 | 2 |00:00:00.01 | 2 | | | |
|* 6 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 2 | 2 |00:00:00.01 | 1 | | | |
----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("JOB_ID"='IT_PROG')
6 - access("DEPARTMENT_ID"=20)
#2) UNION ALL 로 확장
SELECT /*+ gather_plan_statistics */ *
FROM employees
WHERE job_id = 'IT_PROG'
UNION ALL
SELECT *
FROM employees
WHERE 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 | | 7 |00:00:00.01 | 6 |
| 1 | UNION-ALL | | 1 | | 7 |00:00:00.01 | 6 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 5 | 5 |00:00:00.01 | 4 |
|* 3 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 5 | 5 |00:00:00.01 | 2 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 | 2 |00:00:00.01 | 2 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 2 | 2 |00:00:00.01 | 1 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("JOB_ID"='IT_PROG')
5 - access("DEPARTMENT_ID"=20)=> (문제) 중복되는 data 가 있을 수 있다!
#3) union all + 중복 data X
=> department_id에서 20번 부서가 포함되지 않아야 하고 null 값이 누락되지 않게 해야 한다.
SELECT /*+ gather_plan_statistics */ *
FROM employees
WHERE job_id = 'IT_PROG'
AND (department_id <> 20 OR department_id is null);
#) 실행 계획 확인
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 | | 5 |00:00:00.01 | 4 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 5 | 5 |00:00:00.01 | 4 |
|* 2 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 5 | 5 |00:00:00.01 | 2 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("DEPARTMENT_ID"<>20 OR "DEPARTMENT_ID" IS NULL))
2 - access("JOB_ID"='IT_PROG')
#3-1) 함수 사용: LNNVL() 부정함수
SELECT /*+ gather_plan_statistics */ *
FROM employees
WHERE job_id = 'IT_PROG'
AND LNNVL(department_id = 20);
-- LNNVL(department_id = 20) = ("DEPARTMENT_ID"<>20 OR "DEPARTMENT_ID" IS NULL)
#) 실행 계획 확인
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 | | 5 |00:00:00.01 | 4 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 5 | 5 |00:00:00.01 | 4 |
|* 2 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 5 | 5 |00:00:00.01 | 2 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(LNNVL("DEPARTMENT_ID"=20))
2 - access("JOB_ID"='IT_PROG')
#4) 원래 코드
SELECT /*+ gather_plan_statistics */ *
FROM employees
WHERE job_id = 'IT_PROG'
AND (department_id <> 20 department_id is null)
UNION ALL
SELECT *
FROM employees
WHERE department_id = 20;
#4-1) 함수인 LNNVL() 활용
SELECT /*+ gather_plan_statistics */ *
FROM employees
WHERE job_id = 'IT_PROG'
AND LNNVL(department_id = 20)
UNION ALL
SELECT *
FROM employees
WHERE 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 | | 7 |00:00:00.01 | 6 |
| 1 | UNION-ALL | | 1 | | 7 |00:00:00.01 | 6 |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 5 | 5 |00:00:00.01 | 4 |
|* 3 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 5 | 5 |00:00:00.01 | 2 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 | 2 |00:00:00.01 | 2 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 2 | 2 |00:00:00.01 | 1 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(LNNVL("DEPARTMENT_ID"=20))
3 - access("JOB_ID"='IT_PROG')
5 - access("DEPARTMENT_ID"=20)
■ OR-Expansion (OR 확장형)
1. 정의
- OR-Expansion은 사용자의 쿼리를 직접 변경하지 않고, 옵티마이저가 OR 조건을 Full Table Scan이 아닌 Index Range Scan을 수행하도록 유도하면서, UNION ALL 형태로 변경하여 처리하는 기능.
- `use_concat` 힌트를 사용하여 OR-Expansion을 유도하거나, `no_expand` 힌트를 사용하여 OR-Expansion을 유도하지 않을 수 있음.
2. 동작 방식
- OR 조건이 포함된 쿼리를 Full Table Scan이 아닌 Index Range Scan으로 처리하도록 최적화.
- `use_concat` 힌트를 사용하면 옵티마이저에게 OR-Expansion을 유도하는 신호를 주며, `no_expand` 힌트를 사용하면 OR-Expansion을 유도하지 않도록 함.
- 값의 분포도에 따라 Index Scan 또는 Full Scan으로 결정되며, 변수 처리 시 고르게 분포되어 있을 경우 Index Scan으로 처리됨.
3. 주의사항
- OR-Expansion은 옵티마이저의 판단에 따라 수행되므로, 모든 경우에 사용할 때 항상 성능 향상을 기대할 수 없음.
- 값의 분포도와 변수 처리 여부 등에 따라 옵티마이저가 최적의 실행 계획을 선택함.
#) 힌트 사용: use_concat
SELECT /*+ gather_plan_statistics use_concat */ *
FROM employees
WHERE job_id = 'IT_PROG' OR 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 | | 7 |00:00:00.01 | 6 |
| 1 | CONCATENATION | | 1 | | 7 |00:00:00.01 | 6 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 2 | 2 |00:00:00.01 | 4 |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 2 | 2 |00:00:00.01 | 2 |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 5 | 5 |00:00:00.01 | 2 |
|* 5 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 5 | 5 |00:00:00.01 | 1 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPARTMENT_ID"=20)
4 - filter(LNNVL("DEPARTMENT_ID"=20))
5 - access("JOB_ID"='IT_PROG')
=> 쿼리문 분리됨!
=> CONCATENATION: 내부적으로 union all이 돌아가는 것이다
#) 힌트 사용: no_expand
SELECT /*+ gather_plan_statistics no_expand */ *
FROM employees
WHERE job_id = 'IT_PROG' OR 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 | | 7 |00:00:00.01 | 7 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 7 | 7 |00:00:00.01 | 7 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("JOB_ID"='IT_PROG' OR "DEPARTMENT_ID"=20))
#) department_id 별로 건수
SELECT department_id, count(*)
FROM employees
GROUP BY department_id
ORDER BY 2 DESC;
#) job_id 별로 건수
SELECT job_id, count(*)
FROM employees
GROUP BY job_id
ORDER BY 2 DESC;
SELECT /*+ gather_plan_statistics */ *
FROM employees
WHERE job_id = 'IT_PROG' OR 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 | | 7 |00:00:00.01 | 7 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 7 | 7 |00:00:00.01 | 7 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("JOB_ID"='IT_PROG' OR "DEPARTMENT_ID"=20))
#) use_concat 힌트 사용
SELECT /*+ gather_plan_statistics use_concat */ *
FROM employees
WHERE job_id = 'IT_PROG' OR department_id = 50;
#) 실행 계획 확인
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 | | 50 |00:00:00.01 | 13 |
| 1 | CONCATENATION | | 1 | | 50 |00:00:00.01 | 13 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 5 | 5 |00:00:00.01 | 4 |
|* 3 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 5 | 5 |00:00:00.01 | 2 |
|* 4 | TABLE ACCESS FULL | EMPLOYEES | 1 | 43 | 45 |00:00:00.01 | 9 | -- full scan
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("JOB_ID"='IT_PROG')
4 - filter(("DEPARTMENT_ID"=50 AND LNNVL("JOB_ID"='IT_PROG')))
#) 함수 + index scan
SELECT /*+ gather_plan_statistics */ *
FROM employees
WHERE job_id = 'IT_PROG'
AND LNNVL(department_id = 50)
UNION ALL
SELECT /*+ index(e EMP_DEPARTMENT_LX) */ *
FROM employees e
WHERE department_id = 50;
#) 실행 계획 확인
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 | | 50 |00:00:00.01 | 13 |
| 1 | UNION-ALL | | 1 | | 50 |00:00:00.01 | 13 |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 3 | 5 |00:00:00.01 | 4 |
|* 3 | INDEX RANGE SCAN | EMP_JOB_IX | 1 | 5 | 5 |00:00:00.01 | 2 |
|* 4 | TABLE ACCESS FULL | EMPLOYEES | 1 | 45 | 45 |00:00:00.01 | 9 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(LNNVL("DEPARTMENT_ID"=50))
3 - access("JOB_ID"='IT_PROG')
4 - filter("DEPARTMENT_ID"=50)
#) dept_id = 10 확인
SELECT /*+ gather_plan_statistics */ *
FROM employees
WHERE department_id = 10;
#) 실행 계획 확인
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 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 1 | 1 |00:00:00.01 | 3 |
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 1 | 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID"=10)
#) dept_id = 50 확인
SELECT /*+ gather_plan_statistics */ *
FROM employees
WHERE department_id = 50;
#) 실행 계획 확인
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 | 9 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 45 | 45 |00:00:00.01 | 9 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPARTMENT_ID"=50)
#) index scan 유도
SELECT /*+ gather_plan_statistics index(e EMP_DEPARTMENT_IX) */ *
FROM employees e
WHERE department_id = 50;
#) 실행 계획 확인
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 | 9 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 45 | 45 |00:00:00.01 | 9 |
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | 45 | 45 |00:00:00.01 | 4 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID"=50)
[문제] 자신의 부서 평균 급여보다 더 많은 받는 사원들의 employee_id, salary, department_name 을 출력해주세요.
#1) 동일한 table(employees)을 2번 access 하는 문제점
SELECT /*+ gather_plan_statistics */ e.employee_id, e.salary, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.salary > (SELECT avg(salary)
FROM employees
WHERE 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 | | 38 |00:00:00.01 | 8 | | | |
|* 1 | VIEW | VW_WIF_1 | 1 | 106 | 38 |00:00:00.01 | 8 | | | |
| 2 | WINDOW BUFFER | | 1 | 106 | 106 |00:00:00.01 | 8 | 9216 | 9216 | 8192 (0)|
| 3 | MERGE JOIN | | 1 | 106 | 106 |00:00:00.01 | 8 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 2 | | | |
| 5 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 27 | 27 |00:00:00.01 | 1 | | | |
|* 6 | SORT JOIN | | 27 | 107 | 106 |00:00:00.01 | 6 | 9216 | 9216 | 8192 (0)|
| 7 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("VW_COL_4" IS NOT NULL)
6 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")WINDOW BUFFER: 분석함수 사용.
#2) 히든 파라미터 사용
alter session set "_remove_aggr_subquery" = false;
SELECT /*+ gather_plan_statistics */ e.employee_id, e.salary, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.salary > (SELECT avg(salary)
FROM employees
WHERE 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 | | 38 |00:00:00.01 | 17 | | | |
|* 1 | HASH JOIN | | 1 | 5 | 38 |00:00:00.01 | 17 | 959K| 959K| 862K (0)|
| 2 | MERGE JOIN | | 1 | 11 | 11 |00:00:00.01 | 8 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 2 | | | |
| 4 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 27 | 27 |00:00:00.01 | 1 | | | |
|* 5 | SORT JOIN | | 27 | 11 | 11 |00:00:00.01 | 6 | 2048 | 2048 | 2048 (0)|
| 6 | VIEW | VW_SQ_1 | 1 | 11 | 12 |00:00:00.01 | 6 | | | |
| 7 | HASH GROUP BY | | 1 | 11 | 12 |00:00:00.01 | 6 | 889K| 889K| 1668K (0)|
| 8 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
| 9 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 9 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."SALARY">"AVG(SALARY)")
5 - access("ITEM_1"="D"."DEPARTMENT_ID")
filter("ITEM_1"="D"."DEPARTMENT_ID")
#3) inline view 확인, 동일한 table(employees)을 2번 access 하는 문제점
select /*+ gather_plan_statistics */ e2.employee_id, e2.last_name, d.department_name
from (select department_id, avg(salary) avg_sal
from employees
group by department_id) e1, employees e2, departments d
where e1.department_id = d.department_id
and e2.department_id = d.department_id
and e2.salary > e1.avg_sal;
#) 실행 계획 확인
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 | | 38 |00:00:00.01 | 17 | | | |
|* 1 | HASH JOIN | | 1 | 5 | 38 |00:00:00.01 | 17 | 959K| 959K| 835K (0)|
| 2 | MERGE JOIN | | 1 | 11 | 11 |00:00:00.01 | 8 | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 2 | | | |
| 4 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 27 | 27 |00:00:00.01 | 1 | | | |
|* 5 | SORT JOIN | | 27 | 11 | 11 |00:00:00.01 | 6 | 2048 | 2048 | 2048 (0)|
| 6 | VIEW | | 1 | 11 | 12 |00:00:00.01 | 6 | | | |
| 7 | HASH GROUP BY | | 1 | 11 | 12 |00:00:00.01 | 6 | 889K| 889K| 1668K (0)|
| 8 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
| 9 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 9 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E2"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E2"."SALARY">"E1"."AVG_SAL")
5 - access("E1"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E1"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
#4) 히든 파라미터 변경
alter session set "_remove_aggr_subquery" = true;
SELECT /*+ gather_plan_statistics */ e.employee_id, e.salary, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.salary > (SELECT avg(salary)
FROM employees
WHERE 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 | | 38 |00:00:00.01 | 8 | | | |
|* 1 | VIEW | VW_WIF_1 | 1 | 106 | 38 |00:00:00.01 | 8 | | | |
| 2 | WINDOW BUFFER | | 1 | 106 | 106 |00:00:00.01 | 8 | 9216 | 9216 | 8192 (0)|
| 3 | MERGE JOIN | | 1 | 106 | 106 |00:00:00.01 | 8 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 2 | | | |
| 5 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 27 | 27 |00:00:00.01 | 1 | | | |
|* 6 | SORT JOIN | | 27 | 107 | 106 |00:00:00.01 | 6 | 9216 | 9216 | 8192 (0)|
| 7 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("VW_COL_4" IS NOT NULL)
6 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
#5) case when절 사용
SELECT /*+ gather_plan_statistics */ employee_id, salary, department_name
FROM ( SELECT e.employee_id, e.salary, d.department_name, CASE WHEN e.salary > AVG(salary) OVER(PARTITION BY e.department_id) THEN e.rowid END vw_col_4
FROM employees e, departments d
WHERE e.department_id = d.department_id )
WHERE vw_col_4 is not null;
#) 실행 계획 확인
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 | | 38 |00:00:00.01 | 8 | | | |
|* 1 | VIEW | | 1 | 106 | 38 |00:00:00.01 | 8 | | | |
| 2 | WINDOW BUFFER | | 1 | 106 | 106 |00:00:00.01 | 8 | 9216 | 9216 | 8192 (0)|
| 3 | MERGE JOIN | | 1 | 106 | 106 |00:00:00.01 | 8 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 2 | | | |
| 5 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 27 | 27 |00:00:00.01 | 1 | | | |
|* 6 | SORT JOIN | | 27 | 107 | 106 |00:00:00.01 | 6 | 9216 | 9216 | 8192 (0)|
| 7 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("VW_COL_4" IS NOT NULL)
6 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
※ _remove_aggr_subquery(10g) : correlate subquery(상호관련 서브쿼리)를 사용할 때 분석함수를 사용하여 서브쿼리를 제거한다.
■ optimizer
- 사용자가 요청한 SQL문을 가장 효율적이고 빠르고 수행할 수 있는 최저 비용의 처리 경로를 선택해주는 엔진
1. Rule Based Optimization(RBO)
- 미리 정해진 규칙에 의한 순위에 따라 실행 계획을 결정.(순위 결정 방식)
- 데이터에 대한 통계 내지는 실제 SQL문을 수행할 경우에 소요될 비용에 대한 고려하지 않음.
ex) 인덱스가 있다면 무조건 사용: 테이블 크기, 인덱스의 효율 등 무시.
- 10g 이후부터 RBO 지원중단.
- ex1) 조건절 컬럼에 인덱스가 있으면 무조건 인덱스 사용.
(1순위. rowid scan ~ 15순위. full scan)
- ex2) order by 절에 사용된 컬럼에 인덱스가 있으면 무조건 인덱스 사용.
- ex3) 부등호 조건의 인덱스 보다 between and 조건의 인덱스가 우선 사용.
select *
from emp
where dept_id >= 100
and sal between 1000 and 10000; -- 순위상 먼저 사용될 수 있다.=> 값의 분포도는 sal가 dept_id보다 많아도 between-and 순위 우선도 높음.
2. Cost Based Optimization(CBO)
- oracle 7 부터 지원.
- cost 를 기반으로 SQL 최적화 수행.
- 실제 SQL을 수행할 때 소요될 비용을 미리 예측하고 그 값을 기준으로 실행 계획을 설정.
- object 통계 정보 : table의 통계, column의 통계, index의 통계
- system 통계 정보: cup 속도, disk I/O
<sys sess>
show parameter optimizer_modeNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode string ALL_ROWS
■ optimizer_mode
choose : 통계 정보 => 있으면 all_rows, 없으면 rule (9i 기본값)
rule : 통계 정보와 상관없이 RBO 사용
=> CBO 나오고 부터 나온 것 3가지
all_rows : 전체 처리율의 최적화(10g 기본값), 보편적으로 설정
first_rows : 최조 응답속도 최적화
first_rows_n(1,10,100,1000) : 처음 결과가 나올때까지의 시간을 줄이기 위해 최적화
#) optimizer_mode 설정 변경
<sys sess>
-- 함부로 system set 하지 말것!
alter system set optimizer_mode = first_rows;
#) 파라미터에서 설정
alter session set optimizer_mode = first_rows_10;
#) 보편적으로 힌트로 사용
#1) all_rows
select /*+ gather_plan_statistics all_rows */ *
from hr.employees
where department_id = 50;
#) 실행 계획 확인
=> (해석) 정확한 실행 계획 요청, 전체 data 보기
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 | 9 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 45 | 45 |00:00:00.01 | 9 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPARTMENT_ID"=50)
#2) first_rows(10)
select /*+ gather_plan_statistics first_rows(10) */ *
from hr.employees
where department_id = 50;
#) 실행 계획 확인
(해석) 빠르게 실행 계획 생성 요청
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 | 9 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 10 | 45 |00:00:00.01 | 9 |
|* 2 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | | 45 |00:00:00.01 | 4 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPARTMENT_ID"=50)
SQL문
parser 처리
Quert Transformer -> Estimator -> Plan Generator
1. Quert Transformer : SQL 을 최적화 하기 쉬운 형태로 변환
2. Estimator
- selectivity : 전체 대상 행 중에 특정한 조건에 의해 선택될 것으로 예상되는 row의 수(비율: E-Rows) (1/num_distinct)
- cardinality : 특정 access 단계를 거치고 나서 출력될 것으로 예상되는 결과 건수 (총 로우의 수 * 선택도 = num_rows/num_distinct)
- cost
1) 특정 명령문을 실행하는데 필요한 표준화된 i/o에 대한 옵티마이저의 최적 '예측비용'
2) _optimizer_cost_model(히든파라미터) = {io|cpu|choose} => i/o 비용(8i) | cpu 비용(10g) | choose 비용: 시스템 통계가 있으면 cpu비용, 없으면 i/o 비용(9i)
3. Plan Generator : 후보군이 될만한 실행계획들을 생성(그 중 저렴한 걸로 리턴)
<hr sess>
#) emp 테이블 삭제 후 재생성
drop table emp purge;
create table emp as select * from employees;
#) 값의 분포도 확인
select t.num_rows, c.column_name, c.num_nulls, c.num_distinct, 1/c.num_distinct selectivity, num_rows/c.num_distinct cardinality
from user_tables t, user_tab_columns c
where t.table_name = 'EMP'
and c.table_name = t.table_name; NUM_ROWS COLUMN_NAME NUM_NULLS NUM_DISTINCT SELECTIVITY CARDINALITY
---------- ------------------------------ ---------- ------------ ----------- -----------
EMPLOYEE_ID
FIRST_NAME
LAST_NAME
EMAIL
PHONE_NUMBER
HIRE_DATE
JOB_ID
SALARY
COMMISSION_PCT
MANAGER_ID
DEPARTMENT_ID
11 rows selected.
# method_opt=>'for all columns size 1' : histogtm 생성하지 X
#) gather 작업, 컬럼들의 히스토그램을 생성하지 않겠다는 의미
exec dbms_stats.gather_table_stats('HR', 'EMP', method_opt=>'for all columns size 1')
#) 값의 분포도, null 값 유무 확인
select t.num_rows, c.column_name, c.num_nulls, c.num_distinct, 1/c.num_distinct selectivity, num_rows/c.num_distinct cardinality
from user_tables t, user_tab_columns c
where t.table_name = 'EMP'
and c.table_name = t.table_name; NUM_ROWS COLUMN_NAME NUM_NULLS NUM_DISTINCT SELECTIVITY CARDINALITY
---------- ------------------------------ ---------- ------------ ----------- -----------
107 EMPLOYEE_ID 0 107 .009345794 1
107 FIRST_NAME 0 91 .010989011 1.17582418
107 LAST_NAME 0 102 .009803922 1.04901961
107 EMAIL 0 107 .009345794 1
107 PHONE_NUMBER 0 107 .009345794 1
107 HIRE_DATE 0 98 .010204082 1.09183673
107 JOB_ID 0 19 .052631579 5.63157895
107 SALARY 0 59 .016949153 1.81355932
107 COMMISSION_PCT 72 7 .142857143 15.2857143
107 MANAGER_ID 1 18 .055555556 5.94444444
107 DEPARTMENT_ID 1 11 .090909091 9.72727273
11 rows selected.
#) job_id 별로 건수 확인
=> data의 값(값의 분포도)이 다양할 경우, 히스토그램 생성해주는 것이 좋다.
select job_id, count(*) from emp group by job_id;JOB_ID COUNT(*)
---------- ----------
AC_MGR 1
AC_ACCOUNT 1
IT_PROG 5
ST_MAN 5
AD_ASST 1
PU_MAN 1
SH_CLERK 20
AD_VP 2
FI_ACCOUNT 5
MK_MAN 1
PR_REP 1
FI_MGR 1
PU_CLERK 5
SA_MAN 5
MK_REP 1
AD_PRES 1
SA_REP 30
HR_REP 1
ST_CLERK 20explain plan for select* from emp where job_id = 'IT_PROG';select * from table(dbms_xplan.display);--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 414 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 6 | 414 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB_ID"='IT_PROG')
#) selectivity , cardinality 계산해보기
selectivity = 1/num_distinct = 1/19 = .052631579
cardinality = num_rows*selectivity = 107*.052631579 = 5.63157895 =: 6
#) job_id = 'SA_REP' 해보기
explain plan for select * from emp where job_id = 'SA_REP';
select * from table(dbms_xplan.display);--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 414 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 6 | 414 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB_ID"='SA_REP')
#) 히스토그램 생성
exec dbms_stats.gather_table_stats('HR', 'EMP', method_opt=>'for columns size 20 job_id');
#) 생성되었는 지 확인
select column_name, num_distinct, num_buckets, histogram
from user_tab_col_statistics
where table_name = 'EMP';COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
------------------------------ ------------ ----------- ---------------
EMPLOYEE_ID 107 1 NONE
FIRST_NAME 91 1 NONE
LAST_NAME 102 1 NONE
EMAIL 107 1 NONE
PHONE_NUMBER 107 1 NONE
HIRE_DATE 98 1 NONE
JOB_ID 19 19 FREQUENCY
SALARY 59 1 NONE
COMMISSION_PCT 7 1 NONE
MANAGER_ID 18 1 NONE
DEPARTMENT_ID 11 1 NONEFREQUENCY: 값 별로 빈도수를 저장하는 도수 분포 히스토그램(=:그룹형 막대그래프)
#) 히스토그램 생성 후 확인해보기!
explain plan for select* from emp where job_id = 'IT_PROG';
select * from table(dbms_xplan.display);
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 345 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 5 | 345 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB_ID"='IT_PROG')
#) job_id = 'SA_REP' 해보기
explain plan for select * from emp where job_id = 'SA_REP';
select * from table(dbms_xplan.display);--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30 | 2070 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 30 | 2070 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB_ID"='SA_REP')
#) 바인드 변수 처리 후 확인
(: 별도로 선언하지 않고 실행해 본다.)
explain plan for select * from emp where job_id = :b1;#) cardinality 값으로 처리 되어서 실행계획에 확인된다.
select * from table(dbms_xplan.display);--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 414 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 6 | 414 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB_ID"=:B1)
=> selectivity = 1/num_distinct
=> cardinality = num_rows*selectivity
#) 비교연산자 사용 후 확인
explain plan for select * from emp where job_id > :b1;
select * from table(dbms_xplan.display);--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 483 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 7 | 483 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB_ID">:B1)=> selectivity = 0.05(5%) 고정 | 오라클이 도저히 selectivity 계산 할수 없을 경우 5% 사용
=> cardinality = num_rows*selectivity
#) salary로 변경 후 확인.
explain plan for select * from emp where salary > :b1;
select * from table(dbms_xplan.display);--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 345 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 5 | 345 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SALARY">TO_NUMBER(:B1))
#) between-and 연산자 사용
explain plan for select * from emp where salary between :b1 and :b2;
select * from table(dbms_xplan.display);---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 207 | 3 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| EMP | 3 | 207 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER(:B2)>=TO_NUMBER(:B1))
2 - filter("SALARY">=TO_NUMBER(:B1) AND "SALARY"<=TO_NUMBER(:B2))
=> selectivity = 0.025(2.5%)
=> cardinality = num_rows*selectivity
728x90
반응형
LIST
'Data Base > SQL 튜닝' 카테고리의 다른 글
| PARTITION, 수동 파티셔닝(manual partitioning) (1) | 2024.02.26 |
|---|---|
| 통계 수집 (0) | 2024.02.26 |
| SEMI JOIN, ANTI JOIN (0) | 2024.02.23 |
| Query Transformation, 서브쿼리 처리 방식(in, exists) (0) | 2024.02.23 |
| pushing subquery, view merging, join 조건 pushdown (0) | 2024.02.23 |