2018.11.06 / 10:25
Á¶ÀÎ ¼ø¼¿¡ ´ëÇÑ °£´ÜÇÑ Å×½ºÆ®
hanulbit
Ãßõ ¼ö 183
Á¶ÀÎ ¼ø¼, Áï ´©°¡ µå¶óÀ̺ù Å×À̺íÀÌ µÇ¾î¾ß ÇÏ´À³Ä¿¡ ´ëÇÑ °£´ÜÇϸ鼵µ Àç¹ÌÀÖ´Â Å×½ºÆ®¸¦ ¼Ò°³ÇÕ´Ï´Ù.
¾Æ·¡¿Í °°ÀÌ ¸¶½ºÅÍ(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 [¿À¶óŬ ¼º´É ¹®Á¦¿¡ ´ëÇÑ ÅëÂû - Á¶µ¿¿í]