조인 순서, 즉 누가 드라이빙 테이블이 되어야 하느냐에 대한 간단하면서도 재미있는 테스트를 소개합니다.

아래와 같이 마스터(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건이므로 전혀 무관하다.

아래 결과를 보기전에 잠깐 생각을 해보시기 바랍니다...

아래에 결과가 있습니다.

SQL> select /*+ gather_plan_statistics
  2  		     leading(t1) use_nl(t2) index(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")


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)


SQL> select /*+ gather_plan_statistics
  2  		     leading(t2) use_nl(t1) index(t2) */
  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")

예상하신대론가요?

해석은 각자의 몫! @_@

출처: http://ukja.tistory.com/357?category=337548 [오라클 성능 문제에 대한 통찰 - 조동욱]