[Oracle] 서브 쿼리 관련 힌트절 (no_unnest, no_merge, push_subq, push_pred)

1. no_unnest, unnest

no_unnest : 중첩된 서브쿼리(nested subquery) 를 풀어내지 말고 그대로 수행하라고 옵티마이저에게 지시하는 힌트절

필터오퍼레이션으로 처리한다. NL 조인과 처리 루틴이 같지만 필터는 메인쿼리의 한 로우가 서브쿼리의 한 로우와 조인에 성공하는 순간 진행을 멈추고(exists), 메인쿼리의 다음 로우를 계속 처리한다는점과 캐시기능을 갖는 다는 점이 다르다. 

메인 쿼리가 항상 Driving 테이블로 사용되고, 서브쿼리의 filter가 가장 나중에 수행된다.

 

Unnesting : 메인과 서브쿼리 간의 계층구조를 풀어 서로 같은 레벨로 만들어 준다.

 

--## NO_UNNEXT
select employee_id, first_name
from hr.employees c
where exists (
	select /*+ no_unnest */ 'x'
	from hr.departments d 
	where d.manager_id  = c.employee_id 
);

--------------------------------------------------------------------------------------
SELECT STATEMENT
	FILTER
		VIEW                               index$_join$_001
			HASH JOIN
				INDEX (FAST FULL SCAN)     EMP_EMP_ID_PK
				INDEX (FAST FULL SCAN)     EMP_NAME_IX
		TABLE ACCESS (FULL)                DEPARTMENTS
        

--## UNNEST  
-- 서브쿼리 이름 지정으로 조인 순서 제어 
select employee_id, first_name
from hr.employees c
where exists (
	select /*+ unnest */ 'x'
	from hr.departments d 
	where d.manager_id  = c.employee_id 
);

--------------------------------------------------------------------------------------
SELECT STATEMENT
	HASH JOIN (SEMI)
		VIEW                               index$_join$_001
			HASH JOIN
				INDEX (FAST FULL SCAN)     EMP_EMP_ID_PK
				INDEX (FAST FULL SCAN)     EMP_NAME_IX
		TABLE ACCESS (FULL)                DEPARTMENTS


-- unnest + 서브쿼리 이름 지정으로 조인 순서 제어 
select /*+ leading( d@idg c ) */
       employee_id, first_name
from hr.employees c
where exists (
	select /*+ qb_name(idg) unnest */ 'x'
	from hr.departments d 
	where d.manager_id  = c.employee_id 
);

 

 

2. no_merge, merge

no_merge : 메인쿼리와 인라인뷰를 병합하지 말고 인라인뷰먼저 실행해라. 

merge       : 메인쿼리와 인라인뷰 병합

 

--## NO_MERGE
select /*+ no_merge(c) leading(c) use_nl(d) */ employee_id, first_name
from ( select distinct first_name, employee_id from hr.employees ) c, hr.departments d
where c.employee_id = d.manager_id  ;

-----------------------------------------------------------------------------------------------
SELECT STATEMENT
	NESTED LOOPS
		TABLE ACCESS (FULL)                 DEPARTMENTS
		VIEW PUSHED PREDICATE          
			TABLE ACCESS (BY INDEX ROWID)   EMPLOYEES
				INDEX (UNIQUE SCAN)         EMP_EMP_ID_PK

--## MERGE
select employee_id, first_name
from ( SELECT /*+ merge */ DISTINCT FIRST_NAME, EMPLOYEE_ID FROM hr.employees ) c, hr.DEPARTMENTS d
WHERE c.EMPLOYEE_ID = d.MANAGER_ID  ;

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

SELECT STATEMENT
	HASH JOIN
		TABLE ACCESS (FULL)                DEPARTMENTS
		VIEW                               index$_join$_002
			HASH JOIN
				INDEX (FAST FULL SCAN)     EMP_EMP_ID_PK 
				INDEX (FAST FULL SCAN)     EMP_NAME_IX

 

4. push_subq, no_push_subq

Unnesting 되지않은 서브쿼리는 항상 필터방식으로 진행되며 대개 실행 계획상 맨 마지막에 처리된다. 하지만 서브쿼리의 필터 단계에서 많은 데이터를 거를 수 있다면 서브쿼리를 먼저 실행하는것이 이득일 것이다.

 

push_subq 힌트는 서브쿼리 필터링을 가능한 한 앞단계에서 처리하도록 강제하는 기능이며, push_subq/no_push_subq 힌트로 제어한다.

이 기능은 Unnesting 되지 않은 서브쿼리에만 작동한다. 서브쿼리가 Unnesting 되면 필터가 아닌 다양한 조인 방식으로 실행 된다. Unnesting 되는 순간 push_subq는 무용지물이다. 따라서 push_subq 힌트는 항상 no_unnest 힌트와 같이 기술하는것이 올바를 사용법이다. 

--## push_subq
select employee_id, first_name
from hr.employees c
where exists (
	select /*+ no_unnest push_subq */ 'x'
	from hr.departments d 
	where d.manager_id  = c.employee_id 
);

------------------------------------------------------------------------------------------------
SELECT STATEMENT
	VIEW                                 index$_join$_001
		HASH JOIN
			INDEX (FAST FULL SCAN)       EMP_EMP_ID_PK
			INDEX (FAST FULL SCAN)       EMP_NAME_IX
		TABLE ACCESS (FULL)              DEPARTMENTS

 

 

5. push_pred, no_push_pred

 조인 조건 pushdown기능 메인 쿼리를 실행 하면서 조인 조건절 값을 건건이 인라인 뷰 안으로 밀어 넣는 기능. 실행계획에 나타난 'VIEW PUSHED PREDCATE' 오퍼레이션을 통해 이 기능의 작동 여부를 알 수 있다.

select employee_id, first_name
from ( select /*+ no_merge push_pred */ 
              distinct first_name, 
              employee_id 
        from hr.employees ) c
      , hr.departments d
where c.employee_id = d.manager_id  ;

select /*+ no_merge(c) push_pred(c) */  employee_id, first_name
from ( select 
              distinct first_name, 
              employee_id 
        from hr.employees ) c
      , hr.departments d
where c.employee_id = d.manager_id  ;


--------------------------------------------------------------------------------------------------
SELECT STATEMENT
	NESTED LOOPS
		TABLE ACCESS (FULL)                 DEPARTMENTS    
		VIEW PUSHED PREDICATE
			TABLE ACCESS (BY INDEX ROWID)   EMPLOYEES
				INDEX (UNIQUE SCAN)         EMP_EMP_ID_PK