- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/display/DBSTUDY/Non+Unnestable+Subquery?
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.
Non Unnestable Subquery
- Subquery 가 항상 Unnesting 되지는 않는다 는 사실을 다시 한번 명심하자.
- 항상 실행 계획을 통해 Subquery Unnesting 에 실패하곤 하기 때문이다.
- 아래 예제를 보면 Oracle 의 Transformation 이 아직 얼마나 불완전한지 잘 알 수 있다.
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production SQL>SELECT /*+ gather_plan_statistics */ t1.c1, t1.c2 FROM t1 WHERE t1.c1 IN (SELECT c1 FROM t2) OR t1.c1 BETWEEN 1 AND 100 ; SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last')); SQL_ID 21dns20d9m1d5, child number 0 ------------------------------------- SELECT /*+ gather_plan_statistics */ t1.c1, t1.c2 FROM t1 WHERE t1.c1 IN (SELECT c1 FROM t2) OR t1.c1 BETWEEN 1 AND 100 Plan hash value: 2243065295 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 11 (100)| 1000 |00:00:00.09 | 10022 | |* 1 | FILTER | | 1 | | | 1000 |00:00:00.09 | 10022 | | 2 | TABLE ACCESS FULL| T1 | 1 | 10000 | 11 (0)| 10000 |00:00:00.02 | 105 | |* 3 | INDEX RANGE SCAN | T2_N1 | 9900 | 1 | 1 (0)| 900 |00:00:00.04 | 9917 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter((("T1"."C1"<=100 AND "T1"."C1">=1) OR IS NOT NULL)) 3 - access("C1"=:B1) |
- 매우 간단한 형태의 Subquery 임에도 불구하고 Semi Join 으로 Unnesting 되지 못하고 Filter Operation 이 사용되는 것을 알 수 있다.
- Join 으로의 변환에 실패한 것이다.
- Oracle 은 Or Operation 이 사용되는 경우 Subquery Unnesting 이 수행하지 못하는 한계를 지니고 있다.
- 이를 해결하는 유일한 방법은 다음과 같이 Query 를 재 작성하는 것이다.
SQL>SELECT /*+ gather_plan_statistics */ t1.c1, t1.c2 FROM t1 WHERE t1.c1 IN (SELECT c1 FROM t2 WHERE t2.c1 NOT BETWEEN 1 AND 100) UNION ALL SELECT t1.c1, t1.c2 FROM t1 WHERE t1.c1 BETWEEN 1 AND 100 ; SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'allstats cost last')); SQL_ID 1smz9p2rf7yc0, child number 0 ------------------------------------- SELECT /*+ gather_plan_statistics */ t1.c1, t1.c2 FROM t1 WHERE t1.c1 IN (SELECT c1 FROM t2 WHERE t2.c1 NOT BETWEEN 1 AND 100) UNION ALL SELECT t1.c1, t1.c2 FROM t1 WHERE t1.c1 BETWEEN 1 AND 100 Plan hash value: 2931889415 ---------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 17 (100)| 1000 |00:00:00.02 | 123 | | | | | 1 | UNION-ALL | | 1 | | | 1000 |00:00:00.02 | 123 | | | | |* 2 | HASH JOIN RIGHT SEMI | | 1 | 901 | 14 (8)| 900 |00:00:00.02 | 106 | 1066K| 1066K| 1180K (0)| |* 3 | INDEX FAST FULL SCAN | T2_N1 | 1 | 901 | 2 (0)| 900 |00:00:00.01 | 8 | | | | | 4 | TABLE ACCESS FULL | T1 | 1 | 10000 | 11 (0)| 10000 |00:00:00.01 | 98 | | | | | 5 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 100 | 3 (0)| 100 |00:00:00.01 | 17 | | | | |* 6 | INDEX RANGE SCAN | T1_N1 | 1 | 100 | 2 (0)| 100 |00:00:00.01 | 9 | | | | ---------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("T1"."C1"="C1") 3 - filter(("T2"."C1">100 OR "T2"."C1"<1)) 6 - access("T1"."C1">=1 AND "T1"."C1"<=100) |
- 이런 간단한 경우 외에도 매우 복잡한 Subquery 에서는 Unnesting 이 실패하거나 비효율적인 방식으로 Unnesting 이 이루어지는 경우가 얼마든지 존재할 수 있다.
- 중요한 것은 실행 계획을 통해 Subquery Unnesting 이 어떤 방식으로 이루어졌는지 관찰할 수 있어야 한다는 것이다.
문서에 대하여
- 최초작성일 : 2011년 04월 13일
- 이 문서는 오라클클럽 대용량 데이터베이스 스터디 모임에서 작성하였습니다.
- 이 문서의 내용은 (주)엑셈에서 출간한 'Optimizing Oracle Optimizer'를 참고하였습니다.
문서정보
- 이 문서는 구루비에서 작성하였습니다.
- 이 문서를 다른 블로그나 홈페이지에 게재하실 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 출처 : http://wiki.gurubee.net/display/DBSTUDY/Non+Unnestable+Subquery?
- 구루비 지식창고의 모든 문서는 크리에이티브 커먼즈의 저작자표시-비영리-동일조건변경허락(BY-NC-SA) 라이선스에 따라 자유롭게 사용할 수 있습니다.