📔설명

세미 조인과 해시 안티조인을 알아보자.

---

🍔SEMI JOIN

select /*+ gather_plan_statistics */ *
from customers100 c
where c.cust_id in (select /*+ unnest hash_sj */ cust_id
					from sales100 s
                    where amount_sold between 0 and 10000);

unnest hash_sj 힌트를 통해 해시 세미 조인으로 실행할 수 있다.
양쪽 다 큰 테이블이면 세미 조인을 실행하는 것이 낫다.

select /*+ gather_plan_statistics */ ename
from emp
where empno in (select mgr
                from emp);
                
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
--관리자인 사원들 이름 뽑는 SQL

위의 SQL은 서브쿼리를 먼저 수행했는지, 메인 쿼리를 먼저 수행했는지 알 수 없다.
같은 테이블로 서브쿼리문을 작성할 때는 QB_NAME 힌트를 사용하면 된다.

select /*+ gather_plan_statistics QB_NAME(mainquery) */ ename
from emp
where empno in (select /*+ QB_NAME(subquery) */ mgr
                from emp);
                
select * from table(dbms_xplan.display_cursor(format=>'advanced'));

위의 SQL은 메인쿼리먼저 수행한 것을 확인할 수 있다.

왜 세미 조인이냐면, 완전한 조인이 아니기 때문이다.
세미조인은 메인 쿼리부터 무조건 액세스하게 되어있다.

만약, 서브쿼리의 테이블을 해시테이블로 사용하려면

select /*+ gather_plan_statistics */ *
from customers100 c
where c.cust_id in (select /*+ unnest hash_sj 
						swap_join_inputs(s) */ cust_id
					from sales100 s
                    where amount_sold between 0 and 10000);

위처럼 swap_join_inputs를 사용해서 HASH RIGHT SEMI JOIN을 사용하면 된다.

select /*+ gather_plan_statistics QB_NAME(main) */ ename
from emp
where empno in (select /*+ QB_NAME(sub) unnest hash_sj
                        swap_join_inputs(emp@sub)*/ mgr
                from emp);
                
select * from table(dbms_xplan.display_cursor(format=>'advanced') );

메인쿼리랑 서브쿼리 테이블 명이 같기 때문에, QB_NAME을 이용해서 emp@sub으로 힌트를 줘야 한다.

---

🍿HASH ANTI-JOIN

서브쿼리의 데이터가 많은데, not in인 서브쿼리부터 실행하게 되면 매우 오래 걸린다.
이때, 메인쿼리부터 실행되게 했도 똑같이 오래걸린다.

select ename
from emp
where empno not in (select mgr
                from emp
                where mgr is not null); --null값 있으면 안됨

select /*+ gather_plan_statistics QB_NAME(main) */ ename
from emp
where empno not in (select /*+ QB_NAME(sub) unnest hash_aj*/ mgr
                from emp
                where mgr is not null)
and empno is not null; --사원번호도 null이 아니라는 조건을 주는 게 좋음

select * from table(dbms_xplan.display_cursor(format=>'advanced') );

emp 테이블 중 메인 쿼리를 먼저 읽은 것을 확인할 수 있다.

해시 안티 조인은 세미조인과 같은 원리지만, not in시 사용해서 anti 조인이 된다.
not in을 사용했기 때문에 main쿼리먼저 실행될수 밖에 없다.

• hash_aj힌트를 사용한다.

hash right anti-join으로 실행되게 하려면 똑같이 swap_join_inputs 힌트를 사용하면 된다.

select /*+ gather_plan_statistics QB_NAME(main) */ ename
from emp
where empno not in (select /*+ QB_NAME(sub) unnest hash_aj
                        swap_join_inputs(emp@sub) */ mgr
                from emp
                where mgr is not null)
and empno is not null;

select * from table(dbms_xplan.display_cursor(format=>'advanced') );

서브쿼리부터 실행되게 바꿨다.