공부하면서 헷갈렸던 두 개념입니다.
서브쿼리 Unnest?
동일한 결과를 보장하는 조인문으로 변환하고 나서 최적화합니다. (즉, 서브쿼리를 조인문처럼 수행한다는 뜻입니다)
NO_UNNEST는 서브쿼리를 원래대로 둔 상태에서 최적화합니다. 메인쿼리와 서브쿼리를 별도의 서브플랜으로 구분해 각각 최적화를 수행하며, 이때 서브쿼리에 필터 오퍼레이션이 나타납니다.
관련 힌트
UNNEST : 조인방식으로 유도
NO_UNNEST : 메인-서브쿼리 방식으로 유도
10g부터는 서브쿼리 Unnesting이 비용기반 쿼리변환 방식으로 전환되었습니다.
NO_UNNEST 힌트 예제
no_unnest 힌트를 사용하면 FILTER 조건으로 서브쿼리가 마지막에 처리되는 것을 확인할 수 있습니다. (ID=5)
select * from emp a,emp b
where a.deptno in (select /*+ no_unnest */ deptno from dept where loc like '%C%')
and a.empno = b.empno
------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 14 | 865K| 865K| 1115K (0)|
| 3 | TABLE ACCESS FULL| EMP | 14 | | | |
| 4 | TABLE ACCESS FULL| EMP | 14 | | | |
|* 5 | TABLE ACCESS FULL | DEPT | 1 | | | |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
2 - access("A"."EMPNO"="B"."EMPNO")
5 - filter(("DEPTNO"=:B1 AND "LOC" LIKE '%C%' AND "LOC" IS NOT NULL))
UNNEST 힌트 예제
unnest 힌트를 사용하여 서브쿼리를 메인쿼리(a.emp)와 동등하게 조인으로 취급하면 제일먼저 처리되어 EMP 테이블과 HASH JOIN을 수행 합니다 (ID = 3 ~ ID= 7)
select * from emp a,emp b
where a.deptno in (select /*+ unnest */ deptno from dept where loc like '%C%')
and a.empno = b.empno
--------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | | | |
|* 3 | HASH JOIN | | 1 | 1517K| 1517K| 647K (0)|
| 4 | SORT UNIQUE | | 1 | 2048 | 2048 | 2048 (0)|
|* 5 | TABLE ACCESS FULL | DEPT | 1 | | | |
| 6 | TABLE ACCESS FULL | EMP | 14 | | | |
|* 7 | INDEX RANGE SCAN | EMP_EMPNO | 1 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | | | |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."DEPTNO"="DEPTNO")
5 - filter(("LOC" LIKE '%C%' AND "LOC" IS NOT NULL))
7 - access("A"."EMPNO"="B"."EMPNO")
성능고도화 4-2 서브쿼리 Unnesting
서브쿼리의 분류 - 오라클메뉴얼은 3가지로 서브쿼리를 분류한다. ● 인라인 뷰 : from 절에 나타나는 서브쿼리를 말한다. ● 중첩된 서브쿼리 : 결과집합을 한정하기 위해 where절에 사용된 서브
karisma3s.tistory.com
NO_UNNEST 힌트와 같이 쓰면 좋은 힌트 PUSH_SUBQ
서브쿼리 NO_UNNEST된 쿼리는 항상 필터방식으로 진행되며 마지막에 서브쿼리가 처리됩니다.
하지만 서브쿼리의 필터 단계에서 많은 데이터를 거를 수 있으면 서브쿼리를 먼저 실행하는 것이 이득일 수도 있습니다.
이 때 사용할 수 있는 힌트입니다. PUSH_SUBQ는 unnesting되지 않은 쿼리에서만 동작하므로 NO_UNNEST 힌트와 같이 사용합니다,
NO_PUSH_SUBQ 예제
NO_UNNEST 힌트를 사용하였으므로 조인방식이 아닌 메인 - 서브쿼리 방식으로 수행하겠다는 뜻이되고 실행계획에서도 그렇게 나타납니다. 서브쿼리인 DEPT는 메인쿼리의 HASH JOIN (ID=2) 후에 처리되어 필터링되어 결과가 수행됩니다.
3 -> 4 ->2 -> 5 -> 1 ->0
select * from emp a,emp b
where a.deptno in (select /*+ no_unnest no_push_subq */deptno from dept where loc like '%C%')
and a.empno = b.empno
------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
|* 1 | FILTER | | | | | |
|* 2 | HASH JOIN | | 14 | 865K| 865K| 1146K (0)|
| 3 | TABLE ACCESS FULL| EMP | 14 | | | |
| 4 | TABLE ACCESS FULL| EMP | 14 | | | |
|* 5 | TABLE ACCESS FULL | DEPT | 1 | | | |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
2 - access("A"."EMPNO"="B"."EMPNO")
5 - filter(("DEPTNO"=:B1 AND "LOC" LIKE '%C%' AND "LOC" IS NOT NULL))
PUSH_SUBQ 예제
PUSH_SUBQ 힌트 사용 결과 서브쿼리인 DEPT가 제일 먼저 수행됩니다.(ID=3)
3 -> 2 -> 4 -> 1 -> 0
select * from emp a,emp b
where a.deptno in (select /*+ no_unnest push_subq */deptno from dept where loc like '%C%')
and a.empno = b.empno
------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
|* 1 | HASH JOIN | | 6 | 858K| 858K| 986K (0)|
|* 2 | TABLE ACCESS FULL | EMP | 6 | | | |
|* 3 | TABLE ACCESS FULL| DEPT | 1 | | | |
| 4 | TABLE ACCESS FULL | EMP | 14 | | | |
------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."EMPNO"="B"."EMPNO")
2 - filter( IS NOT NULL)
3 - filter(("DEPTNO"=:B1 AND "LOC" LIKE '%C%' AND "LOC" IS NOT NULL))
번외 아무런 힌트도 주지 않았을때(=unnest 힌트만 사용했을 때와 동일)
unnest 힌트를 사용했을 때와 마찬가지로 서브쿼리인 DEPT가 메인 쿼리(a.emp)와 동등하게 HASH JOIN으로 수행되는 것을 확인할 수 있습니다 (ID=3)
5 -> 4 ->6 ->3 ->7 ->2 -> 8 -> 1 -> 0
궁금증! 결론적으로 3개의 테이블이 조인이 되는건데 그럼 2번 조인이 되야 되는데 왜 실행계획 상에는 3개가 나타난걸까요? NL 조인이 두번 수행된 것은 좀더 이유를 찾아봐야 할 것 같습니다. 아직까지 지식이 깊지 않아..ㅠㅠ 공부 후 찾게되면 업데이트 하겠습니다.
select * from emp a,emp b
where a.deptno in (select deptno from dept where loc like '%C%')
and a.empno = b.empno
--------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | | | |
|* 3 | HASH JOIN | | 1 | 1517K| 1517K| 649K (0)|
| 4 | SORT UNIQUE | | 1 | 2048 | 2048 | 2048 (0)|
|* 5 | TABLE ACCESS FULL | DEPT | 1 | | | |
| 6 | TABLE ACCESS FULL | EMP | 14 | | | |
|* 7 | INDEX RANGE SCAN | EMP_EMPNO | 1 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | | | |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."DEPTNO"="DEPTNO")
5 - filter(("LOC" LIKE '%C%' AND "LOC" IS NOT NULL))
7 - access("A"."EMPNO"="B"."EMPNO")
참고 : https://velog.io/@kw78999/DB-%EC%BF%BC%EB%A6%AC%EB%B3%80%ED%99%98-PushSubq
[DB] 쿼리 변환 - Push_Subq
먼저 여기서 나오는 Push는 인라인 뷰 안에 조건절을 밀어넣는 조건절 Pushing이 아니므로 헷갈리지 말자.앞서 정리한것 처럼 서브쿼리 Unnesting 되지않은 서브쿼리는 항상 필터방식으로 진행되며
velog.io
SEMI-JOIN?
서브쿼리를 사용하여 서브쿼리에 존재하는 데이터만 메인쿼리에서 추출하는 조인
IN,EXISTS 연산자를 사용한 조인이며 EXISTS (select 1 from emp where empno=7788); 식의 쿼리로 사용합니다.
관련 힌트
NL_SJ
HASH_SJ
NL_AJ
HASH_AJ
조건절 PUSHDOWN
뷰를 액세스하는 쿼리를 최적화할 때 옵티마이저는 1차적으로 뷰 Merging을 고려합니다.
옵티마이저가 어떤이유에서든 뷰 Merging에 실패했을 경우 2차적으로 시도합니다.
(뷰 merging이란 FROM 절의 서브쿼리(인라인 뷰)나 뷰에 사용되며 서브쿼리나 뷰를 해체하여 메인쿼리와 동등하게 쿼리를 수행하라는 뜻입니다. UNNEST와 비슷합니다 UNNEST는 인라인뷰 외의 WHERE 절 등의 서브쿼리에 사용한다고 합니다)
그리고 조건절 Pushing의 종류로는 PULLUP과 PUSHDOWN이 있습니다.
조건절 PULLUP : 쿼리블록 안에 있는 조건들을 쿼리 블록 밖으로 내오는 것을 말하여 그것을 다시 쿼리블록에 PUSHDOWN하는데 사용함
조건절 PUSHDOWN : NL조인 수행 중에 드라이빙 테이블에서 읽은 값을 건건이 INNER쪽 뷰 쿼리 블록 안으로 밀어넣는 것을 말함
PUSHDOWN이 적용 된 쿼리의 실행계획에서는 PUSHED PREDICATE 오퍼레이션이 나타납니다.
관련 힌트
PUSH_PRED : PUSHDOWN 강제 유도
NO_PUSH_PRED : PUSHDOWN 방지
NO_PUSH_PRED 예제
실행계획을 살펴보면 NO_MERGE 힌트가 사용되어 인라인뷰인 EMP_TEST가 먼저 처리되는 것을 볼 수 있습니다(ID=4)
그 후 메인테이블은 EMP와 NL조인을 수행하여 결과값을 처리합니다.
4 -> 3 -> 2 -> 5 -> 1 -> 0
select /*+ NO_MERGE(b) NO_PUSH_PRED(b) */ a.empno,b.ename,b.sal
from emp a,(select empno,max(ename) as ename ,sum(sal) sal from emp_test group by empno) b
where a.empno = b.empno
------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | NESTED LOOPS | | 14 | | | |
| 2 | VIEW | | 14 | | | |
| 3 | HASH GROUP BY | | 14 | 877K| 877K| 1217K (0)|
| 4 | TABLE ACCESS FULL| EMP_TEST | 14 | | | |
|* 5 | INDEX RANGE SCAN | EMP_EMPNO | 1 | | | |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."EMPNO"="B"."EMPNO")
PUSH_PRED 예제
NO_MERGE 힌트가 사용되어 마찬가지로 인라인뷰를 해체하지 않고 서브쿼리부터 처리합니다(ID=6)
그리고 predicate information정보를 보면 6번에 empno = a.empno조건으로 필터링된것을 확인 할 수 있습니다. 해당 조건절은 서브쿼리 밖의 메인쿼리에 있는 조건절이지만, 서브쿼리 안으로 들어와서 필터링된 것을 확인할 수 있습니다.
그 후에 메인테이블인 EMP와 NL조인을 해서 결과값을 반환합니다.
6 -> 5 -> 4 -> 3 -> 2-> 1-> 0
select /*+NO_MERGE(b) PUSH_PRED(b) */ a.empno,b.ename,b.sal
from emp a,(select empno,max(ename) as ename ,sum(sal) sal from emp_test group by empno) b
where a.empno = b.empno
------------------------------------------------------
| Id | Operation | Name | E-Rows |
------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | NESTED LOOPS | | 14 |
| 2 | INDEX FULL SCAN | EMP_EMPNO | 14 |
| 3 | VIEW PUSHED PREDICATE | | 1 |
|* 4 | FILTER | | |
| 5 | SORT AGGREGATE | | 1 |
|* 6 | TABLE ACCESS FULL | EMP_TEST | 1 |
------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(COUNT(*)>0)
6 - filter("EMPNO"="A"."EMPNO")
성능고도화 4-4. 조건절 Pushing
조건절 Pushing - 뷰를 엑세스하는 쿼리를 최적화할 때 옵티마이저는 1차적으로 뷰 Merging을 고려한다. 하지만 아래와 같은 이유로 뷰 Merging에 실해할 수 있다. ● 복합 뷰 Merging 기능이 비활성
karisma3s.tistory.com
참고 : https://hrjeong.tistory.com/241
JPPD(Join Predicate PushDown) 쿼리 변환이 동작하지 않는 사례
JPPD(Join Predicate PushDown) 쿼리 변환은 힌트를 사용해도 동작하지 않는 경우가 많다. 관련 내용을 살펴보자. 테스트를 위해 아래와 같이 테이블을 생성하자. -- 1 DROP TABLE t1 PURGE; DROP TABLE t2 PURG..
hrjeong.tistory.com
UNNEST와 MERGE힌트의 특징적인 차이점은
UNNEST는 서브쿼리쪽에서 사용, MERGE는 메인쿼리쪽에서 사용하는 힌트라는 점입니다.
사용시 주의를 하며..
정보정리에 도움을 주신 여러 블로그지기 분들에게 감사함을 표합니다 :)
'자격증 > SQLP' 카테고리의 다른 글
| [쿼리 작성] SQL 여러 문제 정리 (0) | 2024.02.28 |
|---|---|
| 계층형 쿼리 (0) | 2021.08.26 |
| 서술형 문제 테이블을 한번만 읽고 SQL 작성 (0) | 2021.08.18 |
| 서술형문제 6 야간배치 SQL 튜닝 (0) | 2021.08.16 |
| 서술형문제5. 페이징처리 SQL 작성법 (0) | 2021.08.14 |
댓글