[Oracle] 서브쿼리 동작 방식을 제어하는 힌트들

서브쿼리 동작 방식을 제어하는 힌트들 HINT명설명

NO_UNNEST	서브쿼리를 FILTER동작방식으로 처리하고 싶을 경우, 서브쿼리에 NO_UNNEST 힌트를 사용
UNNEST	FILTER 동작방식을 선택하지 않고 조인 동작방식으로 처리하고자 할 때. 서브쿼리에 UNNEST 힌트 사용
NL_SJ	EXISTS나 IN조건 사용시 서브쿼리에 UNNEST와 함께 NL_SJ힌트를 사용하면, NESTED LOOPS JOIN SEMI로 처리되도록 유도
HASH_SJ	EXISTS나 IN조건을 사용한 경우 서브쿼리에 UNNEST와 함께 HASH_SJ 힌트를 부여하면 HASH JOIN SEMI로 처리하도록 제어
NL_AJ	NOT EXISTS나 NOT IN 조건을 사용한 경우 서브쿼리에 UNNEST와 함께 NL_AJ 힌트를 사용하면, NESTED LOOPS JOIN ANTI로 처리하도록 제어
HASH_AJ	NOT EXISTS나 NOT IN 조건을 사용한 경우 서브쿼리에 UNNEST와 함께 HASH_AJ 힌트를 사용하면 HASH JOIN ANTI로 처리하도록 제어
ORDERED	FROM절에 나열된 순서대로 수행하도록 조인 순서를 정하는 힌트. 서브쿼리가 존재한다면 서브쿼리가 가장 먼저 수행
QB_NAME	QUERY BLOCK의 이름을 지정
SWAP_JOIN_INPUTS	HASH JOIN시 조인 순서를 변경 가능. 명시된 테이블이 BUILD 테이블이 됨
NO_SWAP_JOIN_INPUTS	HASH JOIN시 조인 순서가 바뀌는 경우, 이를 강제적으로 변경되지 못하도록 제어
PUSH_SUBQ	서브쿼리가 먼저 수행하도록 제어. FILTER로 수행됨

 

--------------------------------------------------------------------------------------------

select ... from ... 

where not exists (select /*+  UNNEST HASH_AJ parallel(4) */ 'x' from ... ) ;

--------------------------------------------------------------------------------------------

 

서브쿼리를 FILTER 동작 방식으로 수행하도록 제어

NO_UNNEST 힌트를 부여하면, Filter 동작 방식으로 수행하도록 제어할 수 있다.

SELECT C4, C5, C6 FROM SUBQUERY_T1 T1

WHERE C6 >= :B1

AND C6 <= :B2

AND EXISTS(SELECT /*+ NO_UNNEST*/ 'X' FROM SUBQUERY_T2 T2 WHERE T2.C1 = T1.C4 AND T2.C3 >= :B3 AND T2.C3 <= :B4);

 

NL SEMI JOIN으로 수행되도록 제어

NO_UNNEST 힌트를 부여하면, Filter 동작 방식으로 수행하도록 제어할 수 있다.

SELECT C4, C5, C6 FROM SUBQUERY_T1 T1

WHERE C6 >= :B1 AND C6 <= :B2

AND EXISTS(SELECT /*+ UNNEST NL_SJ*/ 'X' FROM SUBQUERY_T2 T2 WHERE T2.C1 = T1.C4 AND T2.C3 >= :B3 AND T2.C3 <= :B4); 

 

HASH JOIN SEMI JOIN으로 수행되며, 서브쿼리를 Main SQL 테이블 보다 먼저 수행하도록 제어

UNNEST와 HASH_SJ, SWAP_JOIN_INPUTS힌트를 사용하면 서브쿼리부터 수행하도록 실행계획 제어 가능

SELECT C4, C5, C6 FROM SUBQUERY_T1 T1

WHERE C6 >= :B1 AND C6 <= :B2

AND EXISTS(SELECT /*+ UNNEST HASH_SJ SWAP_JOIN_INPUTS(T2)*/ 'X' FROM SUBQUERY_T2 T2 WHERE T2.C1 = T1.C4 AND T2.C3 >= :B3 AND T2.C3 <= :B4); 

 

SQL 서브쿼리를 HASH SEMI JOIN으로 수행하되, Main SQL 테이블을 먼저 수행하도록 제어

UNNEST와 HASH_SJ 힌트를 사용하면, HASH SEMI JOIN으로 수행하도록 제어 SEMI JOIN은 MAIN SQL쪽 테이블을 먼저 수행하는 것이 기본이나 HASH RIGHT SEMI JOIN으로 수행되면 조인 순서가 변경되므로 NO_SWAP_JOIN_INPUTS 힌트를 명시적으로 사용

SELECT C4, C5, C6 FROM SUBQUERY_T1 T1

WHERE C6 >= :B1 AND C6 <= :B2

AND EXISTS(SELECT /*+ UNNEST HASH_SJ NO_SWAP_JOIN_INPUTS(T2) */ 'X' FROM SUBQUERY_T2 T2 WHERE T2.C1 = T1.C4 AND T2.C3 >= :B3 AND T2.C3 <= :B4); 

 

SQL을 NL JOIN으로 수행하되, 서브쿼리를 수행하도록 제어

QB_NAME 힌트를 사용해 QUERY BLOCK명을 지정한 후, QUERY BLOCK 명을 지정한 후, 조인 순서와 조인 방법을 제어

SELECT /*+ QB_NAME(MAIN) LEADING(T2@SUB) USE_NAME(T1@MAIN) */ C4, C5, C6 FROM SUBQUERY_T1 T1

WHERE C6 >= :B1 AND C6 <= :B2AND EXISTS(SELECT /*+ UNNEST QB_NAME(SUB) */ 'X' FROM SUBQUERY_T2 T2 WHERE T2.C1 = T1.C4 AND T2.C3 >= :B3 AND T2.C3 <= :B4); 

 

HASH JOIN으로 처리하되, 허브쿼리로부터 수행하도록 제어

SELECT /*+ QB_NAME(MAIN) LEADING(T2@SUB) USE_HASH(T1@MAIN) */ C4, C5, C6 FROM SUBQUERY_T1 T1

WHERE C6 >= :B1 AND C6 <= :B2 AND EXISTS(SELECT /*+ UNNEST QB_NAME(SUB) */ 'X' FROM SUBQUERY_T2 T2 WHERE T2.C1 = T1.C4 AND T2.C3 >= :B3 AND T2.C3 <= :B4); 

 

NOT EXISTS로 작성된 SQL을 NL JOIN ANTI로 수행하도록 제어

NOT EXISTS의 경우 NL JOIN ANTI로 수행 제어 하기 위해서 UNNEST, NL_AJ힌트를 부여

SELECT C4, C5, C6 FROM SUBQUERY_T1 T1 WHERE C6 >= :B1 AND C6 <= :B2 AND EXISTS(SELECT /*+ UNNEST NL_AJ */ 'X' FROM SUBQUERY_T2 T2 WHERE T2.C1 = T1.C4 AND T2.C3 >= :B3 AND T2.C3 <= :B4); 

 

NOT EXISTS로 작성된 SQL을 HASH JOIN ANTI 조인으로 수행하도록 제어

SELECT C4, C5, C6 FROM SUBQUERY_T1 T1

WHERE C6 >= :B1 AND C6 <= :B2 AND EXISTS(SELECT /*+ UNNEST HASH_AJ */ 'X' FROM SUBQUERY_T2 T2 WHERE T2.C1 = T1.C4 AND T2.C3 >= :B3 AND T2.C3 <= :B4);

 

서브쿼리를 먼저 읽은 후, NL 으로 수행

SELECT * FROM EMP E WHERE EMPNO IN (SELECT MAX(EMPNO) FROM EMP X GROUP BY DEPTNO);

-- 서브쿼리를 먼저 읽은 후, NL로 수행

SELECT /*+ LEADING(X@SUB) QB_NAME(MAIN) USE_NL(E@MAIN) */ * FROM EMP E

WHERE EMPNO IN (SELECT /*+ UNNEST QB_NAME(SUB) */ MAX(EMPNO) FROM EMP X GROUP BY DEPTNO);

• 의도한대로 제어되지 않는 이유는 OPTIMIZER가 서브쿼리를 인라인 뷰로 변경하는 SQL 최적화 작업을 수행했기 때문이다.
• VW_NSO_1이란 점에서 추축 가능
• SQL이 변경되고, 이론 인해 QUERY BLOCK명도 변경되어 QB_NAME 힌트는 물론, 다른 힌트들도 무시
• 이런 경우는 FROM절에 나열된 순서대로 조인 순서를 결정하는 ORDERED 힌트를 사용하면 유도 할 수 있음
• LOGICAL OPTIMIZER가 서브쿼리를 인라인 뷰로 변경할 때 FROM절의 맨 앞에 위치 시키기 대문에 ORDERED로 유도 가능

SELECT /*+ ORDERED USE_NL(E) */ * FROM EMP E

WHERE EMPNO IN (SELECT /*+ UNNEST */ MAX(EMPNO) FROM EMP X GROUP BY DEPTNO); 

• SUBQUERY가 여러개 일 경우 ORDERED 힌트로 제어 불가.

출처: https://argolee.tistory.com/89 [놀멍:티스토리]