다른 명령
조인 순서 테스트
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
아래 결과를 보기전에 잠깐 생각을 해보시기 바랍니다...
아래에 결과가 있습니다.
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")
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)
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 [오라클 성능 문제에 대한 통찰 - 조동욱]