아래 포스트에서 조인 순서 제어에 대한 짤막한 이야기를 한 바 있습니다.
이번에는
서브 쿼리가 포함된 조금 더 복잡한 예를 보겠습니다.
우선 아래와 같이 마스터 테이블 M1과 디테일 테이블 D1, D2, D3를 만듭니다.
create table m
as
select level as c1, level as c2
from dual connect by level <= 1000;
create table d1
as
select mod(level,1000) as c1, level as c2
from dual connect by level <= 10000;
create table d2
as
select mod(level,1000) as c1, level as c2
from dual connect by level <= 20000;
create table d3
as
select mod(level,1000) as c1, level as c2
from dual connect by level <= 30000;
create index m_n1 on m(c1);
create index m_n2 on m(c2);
create index d1_n1 on d1(c1);
create index d2_n1 on d2(c1);
create index d3_n1 on d3(c1);
@gather m
@gather d1
@gather d2
@gather d3
M, D1, D3를 조인하고, D2를 서브쿼리를 이용해 필터(NO_UNNEST 힌트를 통해 강제로 Unnesting을 막음)하는 쿼리가 있습니다. 우리가 원하는 것은
{ M -> D1 -> D2 -> D3 }의 순서로(이것이 최상의 조인 순서로 가정하고) 만드는 것입니다.
1.
PUSH_SUBQ 힌트를 이용해 Subquery를 Push하면 아래와 같이 { M -> D2 -> D1 -> D3 }의 조인 순서를 따릅니다.
explain plan for
select /*+ use_nl(d1 d3) index(d1) index(d3) */
*
from
m, d1, d3
where
m.c1 = d1.c1
and m.c1 = d3.c1
and exists (select /*+ no_unnest index(d2) push_subq */ null from d2 where m.c1 = d2.c1 and d2.c2 between 1 and 1000)
and m.c2 between 1 and 100
and d1.c1 = 1
;
TPACK@ukja1120> select * from table(dbms_xplan.display(format=>'basic'));
--------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | |
| 4 | TABLE ACCESS BY INDEX ROWID | M |
| 5 | INDEX RANGE SCAN | M_N1 |
| 6 | TABLE ACCESS BY INDEX ROWID| D2 |
| 7 | INDEX RANGE SCAN | D2_N1 |
| 8 | TABLE ACCESS BY INDEX ROWID | D1 |
| 9 | INDEX RANGE SCAN | D1_N1 |
| 10 | INDEX RANGE SCAN | D3_N1 |
| 11 | TABLE ACCESS BY INDEX ROWID | D3 |
--------------------------------------------------
2.
NO_PUSH_SUBQ 힌트를 이용해 Subquery Pushing을 못하게 하면 { M -> D1 -> D3 -> D2 }의 조인 순서를 따릅니다.
explain plan for
select /*+ use_nl(d1 d3) index(d1) index(d3) */
*
from
m, d1, d3
where
m.c1 = d1.c1
and m.c1 = d3.c1
and exists (select /*+ no_unnest index(d2) no_push_subq */ null from d2 where m.c1 = d2.c1 and d2.c2 between 1 and 1000)
and m.c2 between 1 and 100
and d1.c1 = 200
;
-------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | FILTER | |
| 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | |
| 4 | NESTED LOOPS | |
| 5 | TABLE ACCESS BY INDEX ROWID| M |
| 6 | INDEX RANGE SCAN | M_N1 |
| 7 | TABLE ACCESS BY INDEX ROWID| D1 |
| 8 | INDEX RANGE SCAN | D1_N1 |
| 9 | INDEX RANGE SCAN | D3_N1 |
| 10 | TABLE ACCESS BY INDEX ROWID | D3 |
| 11 | TABLE ACCESS BY INDEX ROWID | D2 |
| 12 | INDEX RANGE SCAN | D2_N1 |
-------------------------------------------------
3. 흐음... 다음과 같이 QB_NAME 힌트와 LEADING 힌트를 사용하면? QB_NAME 힌트로 제어가 힘들어보입니다.
explain plan for
select /*+ use_nl(d1 d3) index(d1) index(d3) leading(m d1 @sq d3 ) */
*
from
m, d1, d3
where
m.c1 = d1.c1
and m.c1 = d3.c1
and exists (select /*+ qb_name(sq) no_unnest index(d2) no_push_subq */ null from d2 where m.c1 = d2.c1 and d2.c2 between 1 and 1000)
and m.c2 between 1 and 100
and d1.c1 = 200
;
-------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | FILTER | |
| 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | |
| 4 | NESTED LOOPS | |
| 5 | TABLE ACCESS BY INDEX ROWID| M |
| 6 | INDEX RANGE SCAN | M_N1 |
| 7 | TABLE ACCESS BY INDEX ROWID| D1 |
| 8 | INDEX RANGE SCAN | D1_N1 |
| 9 | INDEX RANGE SCAN | D3_N1 |
| 10 | TABLE ACCESS BY INDEX ROWID | D3 |
| 11 | TABLE ACCESS BY INDEX ROWID | D2 |
| 12 | INDEX RANGE SCAN | D2_N1 |
-------------------------------------------------
4. 이 때 이전 글
조인 순서 제어하기에서 소개한 방법을 쓸 수 있습니다. 아래와 같이 Subquery 내에서
WHERE m.c1 + 0*d1.c1 = d2.c1 조건을 사용하면 D1이 항상 D2의 선행이 되어야 합니다. 그리고 PUSH_SUBQ 힌트에 의해 D3 보다는 앞으로 가게 됩니다. 따라서 { M -> D1 -> D2 -> D3 }의 순서로 액세스하게 할 수 있습니다.
explain plan for
select /*+ use_nl(d1 d3) index(d1) index(d3) */
*
from
m, d1, d3
where
m.c1 = d1.c1
and m.c1 = d3.c1
and exists (select /*+ no_unnest index(d2) push_subq */ null from d2 where m.c1 + 0*d1.c1 = d2.c1 and d2.c2 between 1 and 10)
and m.c2 between 1 and 100
and d1.c1 = 200
;
--------------------------------------------------
| Id | Operation | Name |
--------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | NESTED LOOPS | |
| 4 | TABLE ACCESS BY INDEX ROWID | M |
| 5 | INDEX RANGE SCAN | M_N1 |
| 6 | TABLE ACCESS BY INDEX ROWID | D1 |
| 7 | INDEX RANGE SCAN | D1_N1 |
| 8 | TABLE ACCESS BY INDEX ROWID| D2 |
| 9 | INDEX RANGE SCAN | D2_N1 |
| 10 | INDEX RANGE SCAN | D3_N1 |
| 11 | TABLE ACCESS BY INDEX ROWID | D3 |
--------------------------------------------------
오라클과 같이 힌트가 풍부하지 않은 다른 DBMS에서도 위와 같은 방법을 사용하면 조인 순서를 거의 원하는 대로 제어할 수 있지 않을까요?