아래 포스트에서 조인 순서 제어에 대한 짤막한 이야기를 한 바 있습니다.

• 조인 순서 제어하기

이번에는 서브 쿼리가 포함된 조금 더 복잡한 예를 보겠습니다.

우선 아래와 같이 마스터 테이블 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에서도 위와 같은 방법을 사용하면 조인 순서를 거의 원하는 대로 제어할 수 있지 않을까요?