행위

조인 순서 제어하기

DB CAFE

Dbcafe (토론 | 기여)님의 2021년 5월 12일 (수) 09:18 판
(차이) ← 이전 판 | 최신판 (차이) | 다음 판 → (차이)
thumb_up 추천메뉴 바로가기


조인 순서 제어하기[편집]

오라클/SQL 튜닝 2010. 10. 25. 14:57

오라클이 제공하는 힌트가 너무 강력해서 가끔 기본적인 것을 지나칠 때가 있습니다.

가끔은 힌트가 없다면 어떻게 실행 계획을 제어할 수 있을까를 고민해보면 재미있는 발견을 할 때가 있습니다.

예를 들어 아래와 같은 5개의 테이블이 있습니다.

create table t1(c1, c2)
as 
select
	 level, level
from dual
connect by level <= 5000;

create table t2(c1, c2)
as 
select
	 level, level
from dual
connect by level <= 4000;

create table t3(c1, c2)
as 
select
	 level, level
from dual
connect by level <= 3000;

create table t4(c1, c2)
as 
select
	 level, level
from dual
connect by level <= 2000;

create table t5(c1, c2)
as 
select
	 level, level
from dual
connect by level <= 1000;

create index t1_n1 on t1(c1);
create index t2_n1 on t2(c1);
create index t3_n1 on t3(c1);
create index t4_n1 on t4(c1);
create index t5_n1 on t5(c1);

exec dbms_stats.gather_table_stats(user, 't1');
exec dbms_stats.gather_table_stats(user, 't2');
exec dbms_stats.gather_table_stats(user, 't3');
exec dbms_stats.gather_table_stats(user, 't4');
exec dbms_stats.gather_table_stats(user, 't5');


테이블 T1, T2, T3, T4, T4를 조인하는 쿼리에서 조인 순서는 어떻게 될까요?

테이블의 데이터 분포를 보면 T1(c1 between 1 and 10 조건 때문에)이 드라이빙 테이블이 되고 그 다음은 T5(1000건), T4(2000건), T3(3000건), T2(4000건)이 됨을 알 수 있습니다.

explain plan for
select 
	*
from
	t1, t2, t3, t5, t4
where
	t1.c1 = t2.c1
	and t1.c1 = t3.c1
	and t1.c1 = t4.c1
	and t1.c1 = t5.c1
	and t1.c2 between 1 and 10
;

--------------------------------------
| Id  | Operation             | Name |
--------------------------------------
|   0 | SELECT STATEMENT      |      |
|*  1 |  HASH JOIN            |      |
|*  2 |   HASH JOIN           |      |
|*  3 |    HASH JOIN          |      |
|*  4 |     HASH JOIN         |      |
|*  5 |      TABLE ACCESS FULL| T1   |
|   6 |      TABLE ACCESS FULL| T5   |
|   7 |     TABLE ACCESS FULL | T4   |
|   8 |    TABLE ACCESS FULL  | T3   |
|   9 |   TABLE ACCESS FULL   | T2   |
--------------------------------------

만일 조인 순서를 T1 -> T2 -> T3 -> T4 -> T5로 만들고 싶다면? 아래와 같이 ORDERED 힌트를 사용하면 되겠죠?

또는 LEADING(t1 t2 t3 t4 t5) 힌트를 사용해도 됩니다.

explain plan for
select /*+ ordered */
	*
from
	t1, t2, t3, t5, t4
where
	t1.c1 = t2.c1
	and t1.c1 = t3.c1
	and t1.c1 = t4.c1
	and t1.c1 = t5.c1
	and t1.c2 between 1 and 10
;

--------------------------------------
| Id  | Operation             | Name |
--------------------------------------
|   0 | SELECT STATEMENT      |      |
|*  1 |  HASH JOIN            |      |
|*  2 |   HASH JOIN           |      |
|*  3 |    HASH JOIN          |      |
|*  4 |     HASH JOIN         |      |
|*  5 |      TABLE ACCESS FULL| T1   |
|   6 |      TABLE ACCESS FULL| T2   |
|   7 |     TABLE ACCESS FULL | T3   |
|   8 |    TABLE ACCESS FULL  | T4   |
|   9 |   TABLE ACCESS FULL   | T5   |
--------------------------------------

만일 힌트를 사용하지 않고(그리고 RBO가 아닌 CBO라고 하면), 조인 순서가 항상 위와 같이 나오게 하고 싶다면 어떻게 해야될까요?

힌트에 익숙해지다보면 이런 간단한 질문에 대한 답이 선뜻 나오지 않는 경우가 많습니다.

가장 전형적인 방법은 아래와 같이 t1.c1 = t3.c1 조인 조건을 t1.c1 + 0*t2.c1 = t3.c1 같은 형태로 사용하는 것입니다.

t3.c1에서 조인이 이루어지려면 t2.c1 값을 알아야 하므로 반드시 T2 -> T3 순서로 조인이 됩니다.

비슷한 원리로 조건절을 만들어가면 됩니다.

explain plan for
select 
	*
from
	t1, t2, t3, t4, t5
where
	t1.c1 = t2.c1
	and t1.c1 + 0*t2.c1 = t3.c1  -- t2.c1 값을 알아야 t3.c1 조인 가능
	and t1.c1 + 0*t3.c1= t4.c1  -- t3.c1 값을 알아야 t4.c1 조인 가능
	and t1.c1 + 0*t4.c1 = t5.c1  -- t4.c1 값을 알아야 t5.c1 조인 가능
	and t1.c2 between 1 and 10
;

--------------------------------------
| Id  | Operation             | Name |
--------------------------------------
|   0 | SELECT STATEMENT      |      |
|*  1 |  HASH JOIN            |      |
|*  2 |   HASH JOIN           |      |
|*  3 |    HASH JOIN          |      |
|*  4 |     HASH JOIN         |      |
|*  5 |      TABLE ACCESS FULL| T1   |
|   6 |      TABLE ACCESS FULL| T2   |
|   7 |     TABLE ACCESS FULL | T3   |
|   8 |    TABLE ACCESS FULL  | T4   |
|   9 |   TABLE ACCESS FULL   | T5   |
--------------------------------------

위의 방법을 응용하면 조인 순서를 자유롭게 제어할 수 있습니다.

예를 들어 조인 순서를 T1 -> T2 -> T4 -> T3 -> T5(T4가 T3보다 먼저 조인되게)로 만들려면 어떻게 해야할까요?

explain plan for
select 
	*
from
	t1, t2, t3, t4, t5
where
	t1.c1 = t2.c1
	{ 여기에 어떤 조건이 들어가야 아래의 실행 계획이 나올까요? }
	and t1.c2 between 1 and 10
;

--------------------------------------
| Id  | Operation             | Name |
--------------------------------------
|   0 | SELECT STATEMENT      |      |
|*  1 |  HASH JOIN            |      |
|*  2 |   HASH JOIN           |      |
|*  3 |    HASH JOIN          |      |
|*  4 |     HASH JOIN         |      |
|*  5 |      TABLE ACCESS FULL| T1   |
|   6 |      TABLE ACCESS FULL| T2   |
|   7 |     TABLE ACCESS FULL | T4   |
|   8 |    TABLE ACCESS FULL  | T3   |
|   9 |   TABLE ACCESS FULL   | T5   |
--------------------------------------

정답은 이미 공개된 것이나 마찬가지이기 때문에 여기에 적지는 않겠습니다. 혹시 잠깐 시간내서 도전해보고 싶은 분이 있으면 댓글로 남기셔도 좋겠습니다. 그래도 역시 힌트가 편하긴 편합니다!


출처: https://ukja.tistory.com/350 [오라클 성능 문제에 대한 통찰 - 조동욱]