- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/display/DBSTUDY/Comples+View+Merging?
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
| Complex View Merging |
- Complex View 는 Distinct 나 Group By 절이 포함된 View 를 의미한다.
Oracle 8i 까지는 Complex View 는 기본적으로 Merging 을 수행하지 않았다.
하지만 Oracle 9i 부터는 Complex View 또한 Merging 을 수행한다.
Complex View Merging 이 주는 효과는 Simple View Merging 과 동일하다.
Query Block 수를 줄임으로써 Optimization 이 보다 원활하게 이루어지도록 도와주는 것이다.
- Complex View 를 사용하는 Query 에 대해 NO_MERGE Hint 를 사용해 View Merging 을 강제로 비활성화한 경우에는 다음과 같은 실행 계획을 보인다.
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production SELECT /*+ gather_plan_statistics */ t1.c1, v.c2 FROM t1, (SELECT /*+ no_merge */ c1, COUNT(*) as c2 FROM t2 GROUP BY c1) v WHERE t1.c1 = v.c1 ; 997 1 998 1 999 1 1000 1 1000 개의 행이 선택되었습니다. SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last')); SQL_ID 5ug56utp0uncz, child number 0 ------------------------------------- SELECT /*+ gather_plan_statistics */ t1.c1, v.c2 FROM t1, (SELECT /*+ no_merge */ c1, COUNT(*) as c2 FROM t2 GROUP BY c1) v WHERE t1.c1 = v.c1 Plan hash value: 1333811612 --------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 12 (100)| 1000 |00:00:00.03 | 99 | | | | |* 1 | HASH JOIN | | 1 | 1000 | 12 (17)| 1000 |00:00:00.03 | 99 | 968K| 968K| 1237K (0)| | 2 | VIEW | | 1 | 1000 | 4 (25)| 1000 |00:00:00.01 | 7 | | | | | 3 | HASH GROUP BY | | 1 | 1000 | 4 (25)| 1000 |00:00:00.01 | 7 | 879K| 879K| 1263K (0)| | 4 | TABLE ACCESS FULL | T2 | 1 | 1000 | 3 (0)| 1000 |00:00:00.01 | 7 | | | | | 5 | INDEX FAST FULL SCAN| T1_N1 | 1 | 10000 | 7 (0)| 10000 |00:00:00.01 | 92 | | | | --------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T1"."C1"="V"."C1") |
- View Merging 이 이루어지지 않기 때문에, Oracle 은 Inline View 에 해당하는 Query Block을 Table Full Scan 으로 Optimization 하고 그 결과를 다시 Table t1 에 대한 Index Fast Full Scan 과 Hash Join 하는 것으로 Optimization 한다.
- View Merging 이 성공적으로 이루어지는 경우에는 전혀 다른 실행 계획을 보인다.
아래에 그 결과가 있다.
SELECT /*+ gather_plan_statistics */ t1.c1, v.c2 FROM t1, (SELECT /*+ merge */ c1, COUNT(*) AS c2 FROM t2 GROUP BY c1) v WHERE t1.c1 = v.c1 ; 981 1 990 1 993 1 997 1 1000 1 1000 개의 행이 선택되었습니다. SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last')); SQL_ID 10db4x15xct6x, child number 0 ------------------------------------- SELECT /*+ gather_plan_statistics */ t1.c1, v.c2 FROM t1, (SELECT /*+ merge */ c1, COUNT(*) as c2 FROM t2 GROUP BY c1) v WHERE t1.c1 = v.c1 Plan hash value: 4227326106 ---------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 11 (100)| 1000 |00:00:00.03 | 33 | | | | | 1 | HASH GROUP BY | | 1 | 1000 | 11 (19)| 1000 |00:00:00.03 | 33 | 792K| 792K| 1266K (0)| |* 2 | HASH JOIN | | 1 | 1000 | 10 (10)| 1000 |00:00:00.02 | 33 | 1066K| 1066K| 1194K (0)| | 3 | INDEX FAST FULL SCAN| T2_N1 | 1 | 1000 | 2 (0)| 1000 |00:00:00.01 | 8 | | | | | 4 | INDEX FAST FULL SCAN| T1_N1 | 1 | 10000 | 7 (0)| 10000 |00:00:00.01 | 25 | | | | ---------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T1"."C1"="C1") |
- 위의 실행계획을 이용해 역으로 추론해보면 Complex View Merging 에 의해 Query 가 다음과 같이 Transformation 되었다는 것을 알 수 있다.
SELECT t2.c1, COUNT(*) AS c2 FROM t1, t2 WHERE t1.c1 = t2.c1 GROUP BY t2.c1 ; SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last')); SQL_ID 2rbvq1tgav575, child number 0 ------------------------------------- SELECT t2.c1, COUNT(*) AS c2 FROM t1, t2 WHERE t1.c1 = t2.c1 GROUP BY t2.c1 Plan hash value: 4227326106 ---------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 11 (100)| 1000 |00:00:00.02 | 33 | | | | | 1 | HASH GROUP BY | | 1 | 1000 | 11 (19)| 1000 |00:00:00.02 | 33 | 879K| 879K| 1246K (0)| |* 2 | HASH JOIN | | 1 | 1000 | 10 (10)| 1000 |00:00:00.02 | 33 | 1066K| 1066K| 1212K (0)| | 3 | INDEX FAST FULL SCAN| T2_N1 | 1 | 1000 | 2 (0)| 1000 |00:00:00.01 | 8 | | | | | 4 | INDEX FAST FULL SCAN| T1_N1 | 1 | 10000 | 7 (0)| 10000 |00:00:00.01 | 25 | | | | ---------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T1"."C1"="T2"."C1") |
- View Merging 에 의해 Inline View 가 없어지면서 Query 가 단순해지고 이로 인해 보다 합리적인 실행 계획을 수립할 수 있게 된다.
문서에 대하여
- 최초작성일 : 2011년 04월 27일
- 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
- 이 문서의 내용은 (주)엑셈에서 출간한 'Optimizing Oracle Optimizer'를 참고하였습니다.
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/display/DBSTUDY/Comples+View+Merging?
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.