태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

'outer join'에 해당되는 글 2건

  1. 2010.11.09 아우터 조인 제거(Outer Join Elimination) (4)
  2. 2008.12.27 View Merging이 실패하는 또 다른 원인 - Multiple Table에 대한 Outer Join (7)

아우터 조인 제거(Outer Join Elimination)

오라클/옵티마이저 2010.11.09 13:12
아우터 조인에서 다음과 같은 패턴의 오류가 종종 발생하는 것 같습니다.
select *
from t1, t2
where t1.c1 = t2.c1 (+)
         and t2.c2 = 1
;
위의 쿼리의 문제는 무엇일까요?

아우터 조인 자체가 불필요하다는 것입니다. 아우터 조인에 의해서 얻은 데이터 중 t1에만 있고 t2에는 없는 데이터의 t2.c2는 NULL입니다. 따라서 t2.c2 = 1 조건이 있다는 것은 아우터 조인에 의해 생긴 데이터중 t2가 존재하지 않는(NULL인) 데이터를 제거한다는 것과 같은 의미입니다. 즉 위의 쿼리는 아래 쿼리와 논리적으로 동일합니다.

select *
from t1, t2
where t1.c1 = t2.c1
         and t2.c2 = 1
;
하지만 아우터 조인의 고유의 특성 때문에 위의 두 쿼리는 전혀 다른 실행 계획을 가지게 됩니다.

아우터 조인의 특징 중 하나는 조인의 방향이 고정되어 있다는 것입니다.

가령 { where t1.c1 = t2.c1(+) }으로 정의되었다면, 반드시 테이블 t1이 드라이빙 테이블이 됩니다. 따라서 { t2.c2 = 1 } 조건은 드라이빙 조건으로 사용될 수 없죠. 따라서 Hash Join + Table Full Scan이 선택될 확률이 매우 높습니다.

반면에 { where t1.c1 = t2.c1 }으로 정의되었다면, 테이블 t1과 t2 중 로우 수가 작은 것이 드라이빙 테이블이 될 것입니다. 따라서 { t2.c2 = 1 } 조건이 드라이빙 조건이 될 수 있습니다. 따라서 테이블 t2를 드라이빙으로 하는 Nested Loops Join + Index Range Scan이 선택될 활률이 높습니다.

단, 여기서 오라클이 두 가지 편법을 사용합니다.

  1. 아우터 조인(Outer Join)을 이너 조인(Inner Join)으로 바꿀려고 시도합니다. 이것을 아우터 조인 제거(Outer Join Elimination)라고 합니다. 위에서 예로 든 아우터 쿼리는 아우터 조인 제거의 대상이 됩니다.
  2. 조인 순서를 바꿀려고 시도합니다. { where t1.c1 = t2.c1(+) } 조인은 항상 t1 -> t2 의 순서를 따라야하지만, Hash Outer Join인 경우에 한해서는 t2를 드라이빙으로 바꿀 수 있습니다. 즉 가능한 적은 크기의 데이터가 드라이빙 위치에 오도록 바꿀 수 있습니다.
위의 두가지 편법에 의해 어느 정도 문제가 해결되기는 하지만, 조인 방향이 고정되어 있다는 아우터 조인의 특성에 기인하는 본질적인 문제는 항상 조심해야합니다. 아우터 조인을 잘못 사용하면 성능이 느리다다는 불평 중 상당 수가 이 특징으로 인해 나타나는 것으로 봅니다.

아우터 조인의 특성으로 인해 발생하는 재미있는 문제가 Ask 엑셈에 등록되어 있어서 소개합니다.

위에서 소개한 개념을 이해한 뒤라면 매우 재미있게 읽을 수 있을 것 같습니다.
저작자 표시
신고

'오라클 > 옵티마이저' 카테고리의 다른 글

아우터 조인 제거(Outer Join Elimination)  (4) 2010.11.09
Trackback 1 : Comments 4
  1. 궁금이 2010.11.15 09:13 신고 Modify/Delete Reply

    위의 글은 t1이 1이고 t2가 M 이라는 상황에서 진행된것인가요 아우터조인은 알듯하면서도 헷갈릴때가 있어서요

    • 욱짜 2010.11.15 19:07 신고 Modify/Delete

      최초의 질문을 보면 동일한 테이블(T)에 T1, T2의 알리아스를 붙여서 사용하고 있습니다. 아마 테스트용 데이터를 만드는 과정에서 편의상 그렇게 한 것 같습니다.

      아우터 조인에서 "헷갈리는 부분"을 정리해주시면 그 문제를 심도깊에 논의해보는 것도 재미있겠는데요.

  2. salvationism 2010.11.16 10:21 신고 Modify/Delete Reply

    여기에 질문 내용이 맞는지 모르겠는데 ask엑셈 글을 스마트폰으로 보면 어떤글은 제대로 보이는데 어떤글은 html소스.그 자체로 보입니다 수정되면참 편할거같습니다^^

    • 욱짜 2010.11.16 11:04 신고 Modify/Delete

      저희들도 그 문제를 인식했는데, 내부적으로 사용하는 공개소프트웨어의 문제라서 추적이 쉽지 않은 상태입니다.

      HTML 소스가 보이는 경우 새로 고침(리도르)하시면 정상적으로 보이실겁니다.

Write a comment


View Merging이 실패하는 또 다른 원인 - Multiple Table에 대한 Outer Join

오라클 2008.12.27 00:31
다음과 같은 문의를 받았다.


Inner Join을 사용하는 경우에는 View Merging이 성공적으로 이루어져서 양호한 실행 계획이 수행되는 반면, Outer Join을 사용하는 경우에는 View Merging과 Predicate Pushing이 모두 실패해서 매우 불량한 실행 계획이 수립된다.


문제가 무엇인가?


백문이 불여일견이다.

실제로 발생한 상황을 간략하게 흉내내 보자.

논의를 간단하게 하기 위해 Cost Based Query Transformation을 Disable시킨다.

alter session set "_optimizer_cost_based_transformation" = off;
alter session set "_optimizer_push_pred_cost_based" = false;

테이블 구성은 다음과 같다.

크기가 작은 Table t1이 있다.

create table t1
as
select level as c1, level as c2, level as c3
from dual
connect by level <= 100
;



크기가 (매우) 큰 Table t2와 t3가 있다.

create table t2
as
select level as c1, level as c2, level as c3
from dual
connect by level <= 100000
;

create index t2_n1 on t2(c2);

create table t3
as
select level as c1, level as c2, level as c3
from dual
connect by level <= 100000
;

create index t3_n1 on t3(c2);



다음과 같이 두 개의 Inline View x, y를 Inner Join하는 Query는...

select *
from
  (select c2, c3 from t1 where c1 between 1 and 10) x,
  (select t3.c2 from t2, t3
    where t2.c1 = t3.c1
    group by t3.c2
  ) y
where x.c2 = y.c2
;


다음과 같은 실행 계획을 보인다.

-------------------------------------------------------
| Id  | Operation                     | Name  | Rows  |
-------------------------------------------------------
|   0 | SELECT STATEMENT              |       |    10 |
|   1 |  HASH GROUP BY                |       |    10 |
|*  2 |   HASH JOIN                   |       |    10 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T3    |     1 |
|   4 |     NESTED LOOPS              |       |    10 |
|*  5 |      TABLE ACCESS FULL        | T1    |    10 |
|*  6 |      INDEX RANGE SCAN         | T3_N1 |     1 |
|   7 |    TABLE ACCESS FULL          | T2    |   100K|
-------------------------------------------------------
                                                      
Predicate Information (identified by operation id):   
---------------------------------------------------   
                                                      
   2 - access("T2"."C1"="T3"."C1")                    
   5 - filter("C1"<=10 AND "C1">=1)                   
   6 - access("C2"="T3"."C2")                         

                                                      
두 개의 View가 모두 Merging되었고 그로 인해 T1-->T3(Nested Loops)-->T2(Hash)의 가장 이상적인 실행 계획을 보인다.


만일 Inner Join을 Outer Join으로 변경하면?

select *
from
  (select c2, c3 from t1 where c1 between 1 and 10) x,
  (select t3.c2 from t2, t3
    where t2.c1 = t3.c1
    group by t3.c2
  ) y
where x.c2 = y.c2(+)
;


실행 계획은 다음과 같다.

------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes |
------------------------------------------------------
|   0 | SELECT STATEMENT      |      |    10 |   220 |
|*  1 |  HASH JOIN OUTER      |      |    10 |   220 |
|*  2 |   TABLE ACCESS FULL   | T1   |    10 |    90 |
|   3 |   VIEW                |      |   100K|  1269K|
|   4 |    HASH GROUP BY      |      |   100K|  1464K|
|*  5 |     HASH JOIN         |      |   100K|  1464K|
|   6 |      TABLE ACCESS FULL| T2   |   100K|   488K|
|   7 |      TABLE ACCESS FULL| T3   |   100K|   976K|
------------------------------------------------------
                                                     
Predicate Information (identified by operation id):  
---------------------------------------------------  
                                                     
   1 - access("C2"="Y"."C2"(+))                      
   2 - filter("C1"<=10 AND "C1">=1)                  
   5 - access("T2"."C1"="T3"."C1")           
        

무슨 일이 생긴 것인가? 두번째 Inline View(y)가 View Merging에 실패했다.

덕분에 Table t2와 t3가 Full Table Scan을 경유한 Hash Join을 수행하고 그 결과가 다시 Table t1과 Hash Join이 수행된다.

좋지 않다. 왜 그런가?
Outer Join에 참여하는(정확하게 말하면 오른쪽에서) Inline View가 Multiple Table을 포함하는 경우에는 View Merge가 실패한다. 논리적으로 생각해보면 당연한 일이기도 하다.

View Merge에 실패했기 때문에 Inline View y는 별개의 Query Block이 되고, 따라서 별개로 Optimization이 이루어진다. 유일한 선택은 두 개의 Table을 Full Scan으로 읽어서 Hash Join하는 것이다.

이를 확실하게 증명할 수 있는 방법은 10053 Trace를 이용하는 것이다. Oracle 10gR2부터는 Query Transformation 과정이 완벽하게 기록되기 때문이다.

아래 내용은 10053 Trace의 일부를 발췌한 것으로 내가 한 말을 믿을 수 밖에 없을 것이다.

CVM:     CVM bypassed: View on right side of outer join contains more than one table.
CVM:     CVM bypassed: View on right side of outer join + multiple table



여기서 한가지 의문을 더 가지게 된다. 아래 Query를 보면...

select *
from
  (select c2, c3 from t1 where c1 between 1 and 10) x,
  (select t3.c2 from t2, t3
    where t2.c1 = t3.c1
    group by t3.c2
  ) y
where x.c2 = y.c2(+)
;


집합의 수가 더 작은 View x에서 추출된 c2 값을 View y로 Push할 수 있으면 일량을 줄일 수 있다는 것을 짐작할 수 있다. 이것을 흔히 Join Predicate Pushing이라고 부른다. 말 그대로 Join 조건을 Push하겠다는 것이다. 이것은 왜 실패했는가?


이것 역시 10053 Trace 파일에 기록된다. 아래와 같이

JPPD:     JPPD bypassed: View contains a group by.

                                         
           

즉, View y가 Group By 절을 포함하고 있기 때문에 Join Predicate Pushing을 하지 않겠다는 것이다. 아래와 같이 (불필요해 보이는) Group By 구문을 없애면 Join Predicate Pushing이 성공적으로 이루어지고 보다 효율적인 실행 계획이 수립된다.

select *
from
  (select c2, c3 from t1 where c1 between 1 and 10) x,
  (select t3.c2 from t2, t3
    where t2.c1 = t3.c1
    -- group by t3.c2
  ) y
where x.c2 = y.c2(+)
;

--------------------------------------------------------
| Id  | Operation                      | Name  | Rows  |
--------------------------------------------------------
|   0 | SELECT STATEMENT               |       |    10 |
|   1 |  NESTED LOOPS OUTER            |       |    10 |
|*  2 |   TABLE ACCESS FULL            | T1    |    10 |
|   3 |   VIEW PUSHED PREDICATE        |       |     1 |
|*  4 |    HASH JOIN                   |       |     1 |
|   5 |     TABLE ACCESS BY INDEX ROWID| T3    |     1 |
|*  6 |      INDEX RANGE SCAN          | T3_N1 |     1 |
|   7 |     TABLE ACCESS FULL          | T2    |   100K|
--------------------------------------------------------
                                                       
Predicate Information (identified by operation id):    
---------------------------------------------------    
                                                       
   2 - filter("C1"<=10 AND "C1">=1)                    
   4 - access("T2"."C1"="T3"."C1")                     
   6 - access("T3"."C2"="C2")                          


                                                       
위에서 보는 일련의 예는 실행 계획 분석 시에 View Merging이나 Predicate Pushing과 같은 Query Transformation을 해석하는 능력이 필요한 경우가 많다는 것을 잘 보여준다. 불행히도 이에 대해서 체계적으로 설명된 책이나 자료가 별로 없었다. 이번에 출간된 [Optimizing Oracle Optimizer]가 도움이 될 수 있을 것이다. (책 홍보가 아니라 정말로 제대로 된 다른 자료가 없다)

PS)
[Optimizing Oracle Optimizer]에서 View Merging에 실패하는 다양한 경우를 설명하는데 위의 경우는 빠져 있다. 추후 개정판이 나오면 추가해야할 거 같다.


신고
Trackback 0 : Comments 7
  1. 엄마사랑해요 2008.12.27 19:42 신고 Modify/Delete Reply

    그렇네요.. 오늘 선생님 책을 보고 살짝 충격을 받았습니다.
    감사합니다..^^

  2. ohmydb 2008.12.29 13:12 신고 Modify/Delete Reply

    질문드렸던 사람입니다.
    10053 트레이스에 대해 공부를 좀 해야겠네요.
    좋은거 배웠습니다. 감사합니다.

    지금 당장이라도 달려가서 새로 나온 책을 읽어보고 싶네요. ^^

    그런데, 질문 내용에도 말씀드린것처럼 select 절에 sub query 로 구현할 경우 합리적인 실행계획으로 유도가 가능한데, proc 환경처럼 select 절에 sub query 를 구현할 수 없는 경우를 위해 위의 구문 자체에 힌트 등을 적용하여 강제로 predicate pushing 이나 merging 을 적용하는 방법은 정녕 없는건가요?

  3. 욱짜 2008.12.29 14:03 신고 Modify/Delete Reply

    Pro*C는 예전에 사용해보고 한동안 보지 않았는데, 아직 그런 제약조건이 있나보네요.

    Pro*C에서 Dynamic Query를 사용할 수 있으니까 그걸 사용하면 그런 제약이 없을 것으로 봅니다.

  4. ohmydb 2008.12.29 15:11 신고 Modify/Delete Reply

    질문이 거듭되어 죄송합니다. ^^

    OLTP 환경이라 Dynamic SQL 은 사용이 제한되어 있습니다.

    대안으로 생각하고 있는 것은 tbl1을 view 내에서 다시 한번 참조하는 방식으로 쿼리를 재작성하는 방식인데 동일한 테이블을 두번 참조해야하는 비효율이 있는지라 여타의 방법이 존재하지 않을 경우에 적용할 예정이어서 도저히 다른 방법(힌트나 sql 문의 수정을 통한)이 없는지를 알고 싶습니다.

  5. 욱짜 2008.12.29 16:02 신고 Modify/Delete Reply

    메일 보냈습니다~

  6. oracler 2008.12.30 23:38 신고 Modify/Delete Reply

    오늘 드뎌 실물을 봤습니다.

    책이 내 품에 있다는것만으로도 행복합니다.

    선생님의 전작을 접해본 사람들은 공감이 갈것같은데여.

    1년에 책을 한권 읽을까 말까한데 올해만 오라클로 세개... 쩝쩝..

  7. 욱짜 2008.12.31 00:09 신고 Modify/Delete Reply

    부끄럽습니다.
    많은 사람들에게 도움이 되기를 바랄 뿐입니다.

Write a comment

티스토리 툴바