조인 순서 테스트
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 조인 순서 테스트[편집]
notifications_active 조인 순서에 대한 간단한 테스트
조인 순서, 즉 누가 드라이빙 테이블이 되어야 하느냐에 대한 간단하면서도 재미있는 테스트를 소개합니다.
아래와 같이 마스터(T1) - 디테일(T2) 관계를 가지는 테이블이 있습니다.
SQL> select * from v$version where rownum = 1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
SQL> create table t1(
2 c1 number,
3 c2 number,
4 constraint t1_pk primary key (c1)
5 );
Table created.
SQL> create table t2(
2 c1 number,
3 c2 number,
4 c3 number,
5 constraint t2_pk primary key (c1, c2),
6 constraint t2_fk foreign key (c1) references t1(c1)
7 );
Table created.
SQL> create index t2_n1 on t2(c3);
Index created.
마스터 T1은 10건입니다. 그리고 디테일 T2는 10,000건입니다.
SQL> insert into t1
2 select
3 level,
4 level
5 from
6 dual
7 connect by level <= 10
8 ;
10 rows created.
SQL> insert into t2
2 select
3 mod(level,10)+1,
4 level,
5 mod(level,1000)
6 from
7 dual
8 connect by level <= 10000
9 ;
10000 rows created.
SQL> exec dbms_stats.gather_table_stats(user, 't1');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user, 't2');
PL/SQL procedure successfully completed.
마스터 T1은 10건이고, C3 = 1 에 해당하는 디테일 T2도 10건입니다.
SQL> select count(*) from t1;
COUNT(*)
----------
10
SQL> select count(*) from t2 where c3 = 1;
COUNT(*)
----------
10
여기서 질문! 두 테이블을 NL 조인으로 조인하는 경우 드라이빙 테이블은 무엇이 되어야 할까요?
테이블 T1은 크기가 작으므로 T1이 드라이빙이 되어야 한다.
테이블 T2가 필터링이 좋으므로(10000건 중 10건) T2가 드라이빙이 되어야 한다.
둘다 실제 건수는 10건이므로 전혀 무관하다.
아래 결과를 보기전에 잠깐 생각을 해보시기 바랍니다...
아래에 결과가 있습니다.
1.1 leading : t1 , use_nl : t2 , index : t2(c3)으로 할경우[편집]
4 - access("T2"."C3"=1) 5 - filter("T1"."C1"="T2"."C1")
SQL> select /*+ gather_plan_statistics
2 leading(t1) use_nl(t2) index(t2 t2(c3)) */
3 t1.c1, t1.c2, t2.c2, t2.c3
4 from
5 t1, t2
6 where
7 t1.c1 = t2.c1
8 and t2.c3 = 1
9 ;
C1 C2 C2 C3
---------- ---------- ---------- ----------
2 2 1 1
2 2 1001 1
2 2 2001 1
2 2 3001 1
2 2 4001 1
2 2 5001 1
2 2 8001 1
2 2 9001 1
2 2 6001 1
2 2 7001 1
10 rows selected.
-----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 | 118 |
| 1 | NESTED LOOPS | | 1 | | 10 | 118 |
| 2 | NESTED LOOPS | | 1 | 10 | 100 | 18 |
| 3 | TABLE ACCESS FULL | T1 | 1 | 10 | 10 | 8 |
|* 4 | INDEX RANGE SCAN | T2_N1 | 10 | 10 | 100 | 10 |
|* 5 | TABLE ACCESS BY INDEX ROWID| T2 | 100 | 1 | 10 | 100 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."C3"=1)
5 - filter("T1"."C1"="T2"."C1")
1.2 leading : t1 , use_nl : t2 , index : t2(c1,c2)으로 할경우[편집]
4 - access("T1"."C1"="T2"."C1") 5 - filter("T2"."C3"=1)
SQL> select /*+ gather_plan_statistics
2 leading(t1) use_nl(t2) index(t2 t2(c1, c2)) */
3 t1.c1, t1.c2, t2.c2, t2.c3
4 from
5 t1, t2
6 where
7 t1.c1 = t2.c1
8 and t2.c3 = 1
9 ;
C1 C2 C2 C3
---------- ---------- ---------- ----------
2 2 1 1
2 2 1001 1
2 2 2001 1
2 2 3001 1
2 2 4001 1
2 2 5001 1
2 2 6001 1
2 2 7001 1
2 2 8001 1
2 2 9001 1
10 rows selected.
-----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 | 278 |
| 1 | NESTED LOOPS | | 1 | | 10 | 278 |
| 2 | NESTED LOOPS | | 1 | 10 | 10000 | 57 |
| 3 | TABLE ACCESS FULL | T1 | 1 | 10 | 10 | 8 |
|* 4 | INDEX RANGE SCAN | T2_PK | 10 | 1000 | 10000 | 49 |
|* 5 | TABLE ACCESS BY INDEX ROWID| T2 | 10000 | 1 | 10 | 221 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."C1"="T2"."C1")
5 - filter("T2"."C3"=1)
1.3 leading : t2 , use_nl : t1 , index : t2_n1 으로 할경우[편집]
- 4 - access("T2"."C3"=1)
5 - access("T1"."C1"="T2"."C1")
SQL> select /*+ gather_plan_statistics
2 leading(t2) use_nl(t1) index(t2 t2_n1) */
3 t1.c1, t1.c2, t2.c2, t2.c3
4 from
5 t1, t2
6 where
7 t1.c1 = t2.c1
8 and t2.c3 = 1
9 ;
C1 C2 C2 C3
---------- ---------- ---------- ----------
2 2 1 1
2 2 1001 1
2 2 2001 1
2 2 3001 1
2 2 4001 1
2 2 5001 1
2 2 8001 1
2 2 9001 1
2 2 6001 1
2 2 7001 1
10 rows selected.
------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 | 27 |
| 1 | NESTED LOOPS | | 1 | | 10 | 27 |
| 2 | NESTED LOOPS | | 1 | 10 | 10 | 17 |
| 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 10 | 10 | 13 |
|* 4 | INDEX RANGE SCAN | T2_N1 | 1 | 10 | 10 | 3 |
|* 5 | INDEX UNIQUE SCAN | T1_PK | 10 | 1 | 10 | 4 |
| 6 | TABLE ACCESS BY INDEX ROWID | T1 | 10 | 1 | 10 | 10 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T2"."C3"=1)
5 - access("T1"."C1"="T2"."C1")
예상하신대론가요? 해석은 각자의 몫! @_@
출처: https://ukja.tistory.com/357?category=337548 [오라클 성능 문제에 대한 통찰 - 조동욱]