태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

'조인 순서'에 해당되는 글 1건

  1. 2010.11.04 조인 순서 제어하기 2 (2)

조인 순서 제어하기 2

오라클/SQL 튜닝 2010.11.04 15:16
아래 포스트에서 조인 순서 제어에 대한 짤막한 이야기를 한 바 있습니다. 이번에는 서브 쿼리가 포함된 조금 더 복잡한 예를 보겠습니다.

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

'오라클 > SQL 튜닝' 카테고리의 다른 글

오라클 실행 계획을 보는 새로운 방법  (10) 2010.11.29
조인 순서에 대한 간단한 테스트  (3) 2010.11.25
조인 순서 제어하기 2  (2) 2010.11.04
조인 순서 제어하기  (3) 2010.10.25
비주얼 SQL 튜닝?  (6) 2010.10.21
Trackback 0 : Comments 2
  1. hellower 2010.11.08 16:23 신고 Modify/Delete Reply

    참고로, 서브쿼리를 푼후에
    아래와 같이 하면 query block 과 leading 으로도 조인 순서를 M -> D1 -> D2 -> D3 이렇게 바꿀수 있습니다.

    SELECT /*+ use_nl(d1 d3) index(d1) index(d3) leading(@SEL$5DA710D3 m@sel$1 d1@sel$1 d2@sel$2 d3@sel$1) */
    *
    FROM
    m, d1, d3
    WHERE
    m.c1 = d1.c1
    AND m.c1 = d3.c1
    AND EXISTS (SELECT /*+ unnest index(d2) */ 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
    ;

Write a comment

티스토리 툴바