3 부작의 마지막 편
첫 번째,
Oracle Data Access Pattern을 정복하라
두 번째, Data Access Pattern중의 파티션에 관련된
Partition Access Pattern 에 이어서 마지막 편이다.

지난 글에서 Data Access Pattern 과 Join Method 이 두 가지는 기본 중에 기본이라고 하였다.
또한 이 두 가지를 정복한다면 SQL 튜닝중의 많은 부분을 커버할 수 있다고 하였다.
튜닝에서 이것보다 중요한 것이 있을까? 이것들 중에 하나라도 빠트린다면 제대로 된 튜닝을 할 수 없다.

단순 분류 5가지
데이터의 연결방법은 단순분류해 보면 다음과 같다.

1.Nested Loop Join
2.Sort Merge Join
3.Hash Join
4.Outer Join
5.Using Subquery

세분화
하지만 이것으로는 부족하다. Join Method를 좀더 자세히 나타내면 다음과 같다.

01. Nested Loop Join
02. Sort Merge Join
03. Hash Join
04. Cartesian Join (혹은 Cross Join)
05. Sub Query-In,
06. Sub Query-Any
07. Sub Query-All
08. Sub Query-Exists
09. Subquery Factoring
10. Semi Join-Nested Loop
11. Semi Join-Sort Merge
12. Semi Join-Hash
13. Semi Join-Hash Join Right
14. Anti Join-Nested Loop
15. Anti Join-Sort Merge
16. Anti Join-Hash
17. Anti Join-Hash Join Right
18. Index Join
19. Outer Join-Full
20. Outer Join-Nested Loop
21. Outer Join-Sort Merge
22. Outer Join-Hash
23. Outer Join-Hash Join Right
24. Partition Outer Join
25. Star Query Transformation
 
극한의 세분화
물론 여기서 더 세분화 시킬 수 있다. 예를 들면 Nested Loop Join은 아래와 같이 분류할 수 있다.

Full(선행집합)-Unique (후행집합)
Full(선행집합)-Range (후행집합)
Range(선행집합)-Range (후행집합)
Unique(선행집합)-Unique (후행집합)
....중간생략

이런 방법으로 Sort Merge Join과 Hash Join까지 계속 나열한다면 아마 끝이 없을 것이다.

단 한 줄도 놓치지 마라 
아래의 첨부파일에는 Nested Loop Join도 위와 같은 방법으로 가능한 세분화 하였다. 따라서 이 파일에 담긴 Join method는 25가지가 넘는다. 오늘 이야기하는 조인방법들은 튜닝을 하려면 반드시 정복해야 할 주제이니 꼼꼼히 보기 바란다.


Oracle Data Join Method.pdf

Oracle Data Join Method



PS
Star Join은 Star Query Transformation이 나온 후로 설 땅을 잃었으므로 나타내지 않았다.

신고
Posted by extremedb

댓글을 달아 주세요

  1. Kai 2010.02.26 17:45 신고  댓글주소  수정/삭제  댓글쓰기

    열심히 공부하겠습니다. ( '')/☆

  2. starroot 2010.02.26 17:52 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 정보 감사합니다.

  3. feelie 2010.03.02 13:08 신고  댓글주소  수정/삭제  댓글쓰기

    oracle data access pattern, partition access pattern,
    그리고 oracle join 25가지 까지....
    좋은정보에 감사할 따름입니다..

  4. 이쁜이 2010.03.02 22:04 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 정보 감사합니다.

  5. daemon 2010.03.05 15:06 신고  댓글주소  수정/삭제  댓글쓰기

    오동규 컨설턴트님의 글을 항상 감사하게 보고 있습니다.
    이렇게 귀중한 정보를 아무 댓가도 없이 공유해주시는 모습 정말 존경스럽습니다.
    컨설턴트님의 책을 손꼽아 기다리는 한사람으로서 항상 올려주시는 자료에 감사의 말씀이라도
    꼭 드리고 싶었습니다.

  6. Ejql 2010.07.26 10:15 신고  댓글주소  수정/삭제  댓글쓰기

    이제.. 오동규님의 책을 읽을 준비가 되었다고 판단해서. 먼저 기초 공부로 티스토리부터 시작하겠습니다.
    감사합니다.

  7. Favicon of http://bluegom.tistory.com BlogIcon 파란곰팅 2010.12.10 19:47 신고  댓글주소  수정/삭제  댓글쓰기

    오옷... 이런 좋은 자료가 감사합니다.^^....

  8. 힘돌이 2011.10.12 11:46 신고  댓글주소  수정/삭제  댓글쓰기

    오동규님 정말 감사합니다.
    여기서 정말 많은것을 배우게 되네요.

개발자의 질문
개발자 한명이 DBA 에게 질문을 던졌다. ‘스칼라 서브쿼리를 사용한 SQL 인라인뷰로 싸고 인라인뷰 외부에서 스칼라 서브쿼리를 Filter 조건으로 사용하면 스칼라 서브쿼리가 없어지는 현상이 발생합니다. 현상이 정상인가요?’  그런데 DBA 그런일은 발생할 수가 없다고 하였다. 과연 사실일까? 정답은 스칼라 서브쿼리가 없어진다는 것이다. 예리한 눈을 가진 개발자 임에 틀림없다.

 

백견이 불여일행
SSTS( Scalar Subquery To Subquery )
스칼라 서브쿼리를 서브쿼리로 변경시키는 Transformation 과정이다. 하지만 항상 변환되지 않는다. 스칼라 서브쿼리를 인라인뷰 외부에서 Filter 조건으로 사용할때만 가능하다. 아래의 SQL 보자

인덱스 상황 :

EMP_JOB_IX : employee (job_id)

DEPT_ID_PK1 : department(department_id)

SELECT a.employee_id, a.first_name, a.last_name, a.email

  FROM (SELECT e.employee_id, e.first_name, e.last_name, email,

               (SELECT location_id

                  FROM department d

                 WHERE d.department_id = e.department_id) AS location_id

          FROM employee e

         WHERE e.job_id = 'IT_PROG') a

 WHERE a.location_id > 0;


위의 SQL Location 스칼라 서브쿼리로 구현하였다. 하지만 스칼라 서브쿼리를 Select 하는데는 사용하지 않고 Where 조건으로 사용하는 것을 주목하라.

----------------------------------------------------------------------------------

| Id  | Operation                    | Name        | E-Rows |E-Bytes| Cost (%CPU)|

----------------------------------------------------------------------------------

|*  1 |  FILTER                      |             |        |       |            |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEE    |      5 |   195 |     2   (0)|

|*  3 |    INDEX RANGE SCAN          | EMP_JOB_IX  |      5 |       |     1   (0)|

|   4 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT  |      1 |     7 |     1   (0)|

|*  5 |    INDEX UNIQUE SCAN         | DEPT_ID_PK1 |      1 |       |     0   (0)|

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(>0)

   3 - access("E"."JOB_ID"='IT_PROG')

   5 - access("D"."DEPARTMENT_ID"=:B1)

 

위의 Plan 보면 본능적으로 스칼라 서브쿼리가 서브쿼리로 바뀐 것을 알수 있어야 한다. 다시말하면 옵티마이져가 SQL 아래처럼 바꾼 이다.

 

SELECT e.employee_id, e.first_name, e.last_name, email

  FROM employee e

 WHERE e.job_id = 'IT_PROG'

   AND (SELECT location_id

          FROM department d

         WHERE d.department_id = e.department_id) > 0 ;

 

----------------------------------------------------------------------------------

| Id  | Operation                    | Name        | E-Rows |E-Bytes| Cost (%CPU)|

----------------------------------------------------------------------------------

|*  1 |  FILTER                      |             |        |       |            |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEE    |      5 |   195 |     2   (0)|

|*  3 |    INDEX RANGE SCAN          | EMP_JOB_IX  |      5 |       |     1   (0)|

|   4 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT  |      1 |     7 |     1   (0)|

|*  5 |    INDEX UNIQUE SCAN         | DEPT_ID_PK1 |      1 |       |     0   (0)|

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(>0)

   3 - access("E"."JOB_ID"='IT_PROG')

   5 - access("D"."DEPARTMENT_ID"=:B1)

 

실행계획과 Predicate Information 완전히 같음을 알수 있다. 이제 10053 Trace 분석해보자.

***************************

Order-by elimination (OBYE)

***************************

중간생략

CVM:   Merging SPJ view SEL$2 (#0) into SEL$1 (#0)

Registered qb: SEL$F5BB74E1 0xc4643d0 (VIEW MERGE SEL$1; SEL$2)

---------------------

QUERY BLOCK SIGNATURE

---------------------

  signature (): qb_name=SEL$F5BB74E1 nbfros=1 flg=0

    fro(0): flg=0 objn=70296 hint_alias="E"@"SEL$2"

 

View Merging 발생하여 SEL$2(인라인뷰 a) SEL$1(메인쿼리) 통합 되어 버렸다. View Merging 발생하여 새로운 쿼리블럭인 SEL$F5BB74E1 생성 되었다. 하지만 쿼리블럭 SEL$F5BB74E1 From 절을 보면 employee(Alias 로는 E) 존재하고 department(D) 존재 하지 않는다. 그렇다면 스칼라 서브쿼리는 어디로 갔을까? 해답은 FPD(Filter Push Down) 기능에 있다.

 

**************************

Predicate Move-Around (PM)

**************************

중간생략

query block SEL$F5BB74E1 (#0) unchanged

FPD: Considering simple filter push in query block SEL$F5BB74E1 (#0)

 (SELECT "D"."LOCATION_ID" FROM "DEPARTMENT" "D")>0 AND "SYS_ALIAS_1"."JOB_ID"='IT_PROG'

FPD: Considering simple filter push in query block SEL$3 (#0)

"D"."DEPARTMENT_ID"=:B1

try to generate transitive predicate from check constraints for query block SEL$3 (#0)

finally: "D"."DEPARTMENT_ID"=:B1


FPD
기능에 의해서 위에서 새로 생성된 쿼리블럭 SEL$F5BB74E1 조건절에 서브쿼리를 생성하고 있다. 또한 새로 생성된 서브쿼리에 "D"."DEPARTMENT_ID"=:B1 조건을 밀어넣고 있다.

 

검증

스칼라  서브쿼리가 서브쿼리로 바뀌었으므로 서브쿼리에 사용할수 있는 힌트 Push_subq 사용해보자. 이것이 가능해야지만 진정한 서브쿼리이다.

 

SELECT /*+ PUSH_SUBQ(@SUB) */

        a.employee_id, a.first_name, a.last_name, a.email

  FROM (SELECT e.employee_id, e.first_name, e.last_name, email,

               (SELECT /*+ QB_NAME(SUB) */ location_id

                  FROM department d

                 WHERE d.department_id = e.department_id) AS location_id

          FROM employee e

         WHERE e.job_id = 'IT_PROG') a

 WHERE a.location_id > 0;

 

----------------------------------------------------------------------------------

| Id  | Operation                    | Name        | E-Rows |E-Bytes| Cost (%CPU)|

----------------------------------------------------------------------------------

|*  1 |  TABLE ACCESS BY INDEX ROWID | EMPLOYEE    |      1 |    39 |     2   (0)|

|*  2 |   INDEX RANGE SCAN           | EMP_JOB_IX  |      5 |       |     1   (0)|

|   3 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENT  |      1 |     7 |     1   (0)|

|*  4 |    INDEX UNIQUE SCAN         | DEPT_ID_PK1 |      1 |       |     0   (0)|

----------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(>0)

   2 - access("E"."JOB_ID"='IT_PROG')

   4 - access("D"."DEPARTMENT_ID"=:B1)

 

실행계획을 보면 알겠지만 Subquery Pushing 이 발생하여 Id 기준으로 2번과 3번이 동일 Level 상에 존재한다. Subquery Pushing 이 성공적으로 수행된것을 알수 있다.

 

한단계 나아가 보자

CREATE INDEX HR.EMP_JOB_DEPT_IX ON HR.EMPLOYEE (JOB_ID, DEPARTMENT_ID);

 

EMPLOYEE 테이블에 JOB_ID, DEPARTMENT_ID 생성 하였다. 바로 위에서 실행한 SQL 다시 실행 해보자.

--> 여기서 PUSH_SUBQ 를 적용한 SQL 실행한다.

 

---------------------------------------------------------------------------------------

| Id  | Operation                     | Name            | E-Rows |E-Bytes| Cost (%CPU)|

---------------------------------------------------------------------------------------

|   1 |  TABLE ACCESS BY INDEX ROWID  | EMPLOYEE        |      1 |    39 |     2   (0)|

|*  2 |   INDEX RANGE SCAN            | EMP_JOB_DEPT_IX |      1 |       |     1   (0)|

|   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENT      |      1 |     7 |     1   (0)|

|*  4 |     INDEX UNIQUE SCAN         | DEPT_ID_PK1     |      1 |       |     0   (0)|

---------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("E"."JOB_ID"='IT_PROG')

       filter(>0)

   4 - access("D"."DEPARTMENT_ID"=:B1)

  

이것은 제공자 서브쿼리가 아니다

실행계획은 마치 제공자 서브쿼리처럼 바뀌었지만 이것은 제공자 서브쿼리가 아니고 Subquery Pushing 에 의한 효과이다. Predicate Information 을 주목하라. ID 기준으로 2번에서 Filter 가 수행된다. . (JOB_ID, DEPARTMENT_ID) 인덱스를 사용하지 않은 SQLPredicate Information을 보면 EMPLOYEE 테이블 엑세스 시에 filter 가 발생함을 알수 있다. 이것이 바로 Subquery Pushing 과 인덱스에 의한 Early Filter 의 효과이다. 물론 Early Filter 의 개념은 조인순서상에 서브쿼리를 최대한 먼저 조인 하는것으로 바꾸는 것이다. 하지만 위와 같은 Early Filter의 부가적인 기능도 있음을 알아야 한다.

 Subquery Pushing 에 의한 Early Filter 기능은 Using Sub query Method( Filter / Access sub Query ) 글을 참조하라

결론:
이처럼
Oracle Query Transformer
스칼라 서브쿼리를 Filter 조건으로 사용할 SSTS를 발생시킨다.. 여러분이 Query Transformation 의도 하던 의도하지 않던 말이다.

PS :
제목이 이상하다는 의견이 있다. 제목은 옵티마이져가  변환을 수행한다는 의미이다. 수동으로 스칼라 서브쿼리를 서브쿼리로 고치라는 의미가 아니므로 착오가 없길 바란다.


신고
Posted by extremedb

댓글을 달아 주세요

  1. 궁금이 2009.09.10 17:20 신고  댓글주소  수정/삭제  댓글쓰기

    스칼라 서브쿼리는 Outer join을 사용하는 대신 사용하기도 합니다.
    이 경우 서브쿼리로 바뀌게 되면 결과 건수가 달라지지는 않나요..?
    그러니까 스칼라 서브쿼리의 값이 null이 되는 경우가 있다고 가정할 때 말입니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.09.10 17:44 신고  댓글주소  수정/삭제

      스칼라 서브쿼리의 결과가 null 이라고 해도 결과건수가 달라지지 않습니다.
      예를 들어 보겟습니다.
      스칼라 값이 null 일경우:
      a.location_id > 0 이렇게 되면 null 인값은 당연히 빠져야 하겠죠. 우리가 상수나 변수 조건을 줄때는 null 은 빠지고 나옵니다.
      만약 a.location_id > 0 인 데이터 + location_id 값이 null 인 데이터를 보고자 하시면
      a.location_id > 0 or a.location_id is null 로 하시면 됩니다.

  2. Favicon of http://performeister.tistory.com BlogIcon novathinker 2009.09.10 18:13 신고  댓글주소  수정/삭제  댓글쓰기

    그건 의도적으로 SQL을 변경한 것이구요.
    이것 처럼 Oracle이 최적화 과정에서 ReWrite하는 경우 결과 셋이 달라질 수 있는지를 물어본 것입니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.09.10 18:18 신고  댓글주소  수정/삭제

      제가 말씀드린것처럼 SSTS( Scalar Subquery To Subquery ) 기능은 where 절에 스칼라 서브쿼리의 조건을 준 경우에만 발생하므로 결과가 달라지지 않습니다.
      이것을 질문한것이 아니라면 결과가 달라질것으로 의심되는 SQL 을 주시기 바랍니다.

  3. feelie 2009.09.10 18:33 신고  댓글주소  수정/삭제  댓글쓰기

    새로운 내용을 볼때마다 머리가 띵합니다.
    아마도 아직 정확한 지식부족이라는 생각이 듭니다.
    내용 잘보고, 앞으로도 계속 잘 보겠습니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.09.10 18:54 신고  댓글주소  수정/삭제

      여기에 올려지는 모든 글들은 어느정도 난위도가 있는것들 입니다. 머리가 아프시더라도 실망 하지 마시고 꾸준히 보신다면 분명히 성과가 있을것입니다.

  4. Favicon of http://blog.naver.com/xsoft BlogIcon 강정식 2009.09.11 10:38 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요. 동규님. 오랜만에 글을 남겨봅니다 ^^

    이번에도 재미난 포스팅을 올려주셔서 재밌게 보았습니다.
    저도 실제로 튜닝을 할 때 복잡한 SQL에서 스칼라 서브쿼리가 실행계획에서 변경되었을 때 순간적으로 해석하기가 힘든 경우가 있더라구요.

    이번 내용처럼 스칼라 서브쿼리임에도 서브쿼리로 변형이 된다던가, 또는 스칼라 서브쿼리가 Hidden되어 보이지 않는다던가 말이죠.

    특히나 View 안에서 GROUP BY를 사용하면서 스칼라 서브쿼리를 사용할 때 뷰 밖에서 조건을 주고 실행계획을 볼 경우 스칼라 서브쿼리가 안보여서 인위적으로 해체(?)의 수순을 밟기도 합니다.

    제가 참여하는 스터디에서 간단하게 테스트 한 내용입니다.

    http://wiki.oracleclub.com/pages/viewpage.action?pageId=3899396#9.%EC%BF%BC%EB%A6%AC%EB%B3%80%ED%99%98-2.%EC%8A%A4%EC%B9%BC%EB%9D%BC%EC%84%9C%EB%B8%8C%EC%BF%BC%EB%A6%AC

    항상 좋은 글 올려주셔서 감사하게 생각하고 있습니다.

    ps. 저는 블로그에 포스팅을 못한지 근 3개월째 되가고 있어서 거의 방문자가 없는데 동규님은 컨설팅 업무도 하시면서 이렇게 포스팅도 꾸준히 하시는것을 보고 그 열정에 매번 감탄을 하고 있습니다. 저도 빨리 좋은 내용을 가지고 찾아뵙도록 하겠습니다. 오늘 하루도 행복하세요

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.09.11 11:01 신고  댓글주소  수정/삭제

      오랜만 입니다.
      테스트 내용을 잘보았습니다.
      좋은 정보 감사드립니다.
      상당한 수준의 책을 스터디 하셨네요.
      분명히 성과가 있을것입니다.
      좋은 하루되세요

  5. Favicon of http://ukja.tistory.com BlogIcon 욱짜 2009.12.15 11:04 신고  댓글주소  수정/삭제  댓글쓰기

    Query Transformation을 이해하지 못하면 옵티마이저를 이해할 수 없다는게 제 철학중 하나인데, 그걸 실제로 잘 설명해주시고 계십니다. ^^

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.12.15 15:40 신고  댓글주소  수정/삭제

      저도 같은 생각을 가지고 있습니다. 현장에서 고급 DBA들이 풀지못하는 많은 문제들이 Query Transformation 과 관련된 문제였습니다.
      감사합니다.

  6. Favicon of http://1ststreet.tistory.com BlogIcon SITD 2012.03.13 15:21 신고  댓글주소  수정/삭제  댓글쓰기

    마지막 실행계획 만봤을 땐
    4 - access("D"."DEPARTMENT_ID"=:B1)
    가 가장 먼저 풀리는 것 처럼 보이는데,

    실제론
    2 - access("E"."JOB_ID"='IT_PROG')
    filter(>0)
    의 필터가 수행될 때 E의 부서아이디가 4에 상수로 삽입되는거 같은데 맞나요?

    실행계획은 위에서 아래로, 오른쪽에서 왼쪽으로 봐야 한다고 배웠는데, 그게 안통하는 경우가 간혹 있는거 같네요.

    좋은 지식 나눠주셔서 감사합니다 ^^

  2007년 가을이었던가? 사내 컨설턴트로 부터 조금 어려운 질문이 들어왔다.
"다른종류의 쿼리변환은 모두 내가 수동으로 쿼리를 만들수 있습니다. 하지만 JPPD 는 쿼리를 만들수 없습니다.
혹시 오라클이 JPPD 를 적용한 SQL 을 보여줄수 있는지요?"

  JPPD 는 수도없이 봐왔지만 막상 개념을 적용한 SQL 을 내손으로 작성하려 하니 전혀 작성할수가 없었다.
여러분이 알만한 미국및 영국의 유명한 컨설턴트들과 접촉을 해보았지만 역시 그들도 마찬가지였다. 필자는 이문제로 1주간 고생을한 끝에 직접 원리를 알아내었다. 따라서 어떤 메뉴얼에도 어떤 튜닝책에도 이런 이야기는 없음을 먼저 밝혀둔다.

JPPD ? 그게뭐야?
  쿼리변환의 중요성을 알았으므로 이제 쿼리변환중에 가장 자주나오는 Unnesting 과 JPPD 의 개념을 알아보자.
Unnesting 이란 서브쿼리를 인라인뷰로 만들어 from 절로 끌어올리는 쿼리변환을 의미한다. JPPD 란 (Join Predicate Push Down)의 약자로서 인라인뷰 외부의 조인 조건이 인라인뷰 안쪽으로 파고드는 것을 의미한다.
물론 인라인뷰는 대신에 뷰로 사용해도 마찬가지 이다.

그럼 쿼리변환을 한번 해보자.
  지난번 오라클에 트랜스포머가 있다? 라는 글에서 다단계 쿼리변환(Unnesting + View Merging) 사례를 설명한바 있다. 이번에는 다단계 쿼리변환 이면서 서브쿼리 Unnsting 후에 View Merging 이 실패하는 경우에 JPPD가 수행되는 사례를 알아보자.

환경 : Oracle 10.2.0.4

select /*+ gather_plan_statistics PUSH_PRED(OUTER) */
       outer.*
 from (SELECT * FROM emp outer
         UNION ALL
         SELECT * FROM emp outer) OUTER
where outer.sal > ( select /*+ QB_NAME(SUB) UNNEST */  avg(inner.sal)
                              from emp inner
                           where inner.deptno = outer.deptno
                          ) ;

---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                  |           |      1 |     10 |00:00:00.03 |      27 |
|   2 |   VIEW                         | VW_SQ_1   |      1 |      5 |00:00:00.02 |       7 |
|   3 |    HASH GROUP BY               |           |      1 |      5 |00:00:00.02 |       7 |
|   4 |     TABLE ACCESS FULL          | EMP       |      1 |     14 |00:00:00.02 |       7 |
|   5 |   VIEW                         |           |      5 |     10 |00:00:00.01 |      20 |
|   6 |    UNION ALL PUSHED PREDICATE  |           |      5 |     10 |00:00:00.01 |      20 |
|*  7 |     TABLE ACCESS BY INDEX ROWID| EMP       |      5 |      5 |00:00:00.01 |      11 |
|*  8 |      INDEX RANGE SCAN          | IX_EMP_N3 |      5 |     13 |00:00:00.01 |       5 |
|*  9 |     TABLE ACCESS BY INDEX ROWID| EMP       |      5 |      5 |00:00:00.01 |       9 |
|* 10 |      INDEX RANGE SCAN          | IX_EMP_N3 |      5 |     13 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------
 

Predicate Information (identified by operation id):
---------------------------------------------------
   7 - filter("OUTER"."SAL">"VW_COL_1")
   8 - access("OUTER"."DEPTNO"="DEPTNO")
   9 - filter("OUTER"."SAL">"VW_COL_1")
  10 - access("OUTER"."DEPTNO"="DEPTNO")

위실행계획은 쿼리변환이 2단계로 쿼리변환이 수행되었다.
지금부터 과정을 하나하나 살펴보자.

1.단계 : Unnesting 수행
 먼저 서브쿼리가 인라인뷰로 바뀌어 from 절로 올라간다.
그리고 쿼리의 바깥쪽에 WHERE 조건이 생성되며 조인절도 생성된다.
이것은 아래의 쿼리와 같다.

select /*+ gather_plan_statistics LEADING(SUB OUTER) USE_NL(OUTER) NO_PUSH_PRED(OUTER) */
       outer.*
 from (SELECT * FROM SI31041.emp outer                    --> JPPD not yet
       UNION ALL
       SELECT * FROM SI31041.emp outer) OUTER ,
       ( select deptno, avg(sal) AS VW_COL_1
          from emp
         group by deptno
       ) SUB
where outer.sal > SUB.VW_COL_1
   and outer.deptno = SUB.deptno ;


------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------
|   1 |  NESTED LOOPS        |      |      1 |     12 |00:00:00.01 |      50 |
|   2 |   VIEW               |      |      1 |      3 |00:00:00.01 |       7 |
|   3 |    HASH GROUP BY     |      |      1 |      3 |00:00:00.01 |       7 |
|   4 |     TABLE ACCESS FULL| EMP  |      1 |  
  14 |00:00:00.01 |       7 |
|*  5 |   VIEW               |      |      3 |     12 |00:00:00.01 |      43 |
|   6 |    UNION-ALL         |      |      3 |     84 |00:00:00.01 |      43 |
|   7 |     TABLE ACCESS FULL| EMP  |      3 |     42 |00:00:00.01 |      22 |
|   8 |     TABLE ACCESS FULL| EMP  |      3 |     42 |00:00:00.01 |      21 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter(("OUTER"."SAL">"SUB"."VW_COL_1" AND "OUTER"."DEPTNO"="SUB"."DEPTNO"))

위의 Predicate Information을 보면 서브쿼리가 인라인뷰로 바뀌었지만 아직 인라인뷰 내로 WHERE 조건및
조인조건이 파고들지 못했다. 따라서 뷰를 만들고 FILTER 처리가 된것이다.

2단계: JPPD 수행
조인조건및 WHERE 조건이 UNION ALL 로 분리된 각각의 SQL 에 파고든다. 
최종적으로 완성된 쿼리는 아래와 같다.


select /*+ push_pred (outer) */
        outer.*
  from  (select /*+ unnest qb_name (sub) */
                avg(inner.sal) vw_col_1,inner.deptno deptno
           from emp inner
          group by inner.deptno
        ) vw_sq_1,
        (  select *                            --> JPPD OK  using lateral view
             from emp outer
            where outer.deptno=vw_sq_1.deptno -->  조건이 인라인뷰 내로 들어옴 (Lateral view)
            and outer.sal>vw_sq_1.vw_col_1
            union all 
           select *
             from emp outer
            where outer.deptno=vw_sq_1.deptno  --> 조건이 인라인뷰 내로 들어옴(Lateral view)
            and outer.sal>vw_sq_1.vw_col_1
        ) outer


JPPD 의 비밀이 풀리다!
  위의 SQL 에서 이상한점을 발견할수 있는가?
인라인뷰 OUTER 에서 다른 인라인뷰 VW_SQ_1 의 컬럼을 참조하고 있다. 이것은 놀라운 일이다. 인라인뷰 내에서 마치 스칼라 서브쿼리처럼 from 절의 다른 테이블 혹은 다른 인라인뷰의 정보를 사용한것이다. 바로 이것이 Lateral View 의 개념이다. 다시말하면 Lateral View란 스칼라 서브쿼리처럼 사용할수 있는 "스칼라 인라인뷰" 라고 생각하면 된다. 위의 SQL 을 보면 아래의 실행계획과 같을수 밖에 없다는것을 알수 있다.

---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   1 |  NESTED LOOPS                  |           |      1 |     10 |00:00:00.03 |      27 |
|   2 |   VIEW                         | VW_SQ_1   |      1 |      5 |00:00:00.02 |       7 |
|   3 |    HASH GROUP BY               |           |      1 |      5 |00:00:00.02 |       7 |
|   4 |     TABLE ACCESS FULL          | EMP       |      1 |     14 |00:00:00.02 |       7 |
|   5 |   VIEW                         |           |      5 |     10 |00:00:00.01 |      20 |
|   6 |    UNION ALL PUSHED PREDICATE  |           |      5 |     10 |00:00:00.01 |      20 |
|*  7 |     TABLE ACCESS BY INDEX ROWID| EMP       |      5 |      5 |00:00:00.01 |      11 |
|*  8 |      INDEX RANGE SCAN          | IX_EMP_N3 |      5 |     13 |00:00:00.01 |       5 |
|*  9 |     TABLE ACCESS BY INDEX ROWID| EMP       |      5 |      5 |00:00:00.01 |       9 |
|* 10 |      INDEX RANGE SCAN          | IX_EMP_N3 |      5 |     13 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------
 

Predicate Information (identified by operation id):
---------------------------------------------------
   7 - filter("OUTER"."SAL">"VW_COL_1")
   8 - access("OUTER"."DEPTNO"="DEPTNO")
   9 - filter("OUTER"."SAL">"VW_COL_1")
  10 - access("OUTER"."DEPTNO"="DEPTNO")

오라클만이 Lateral View를 사용할수 있다.
  아쉽게도 Lateral View 는 오라클만이 내부적으로 사용할수 있다. 필자나 여러분이 사용할 경우 에러가 발생한다. 그렇다면 위의 SQL은 어디서 나온것인가?  그것은 쿼리 트랜스포머의 쿼리변환작업을 10053 이벤트를 이용하여 Trace 파일에서 추출한 결과 이다.

결론:
 이상으로 우리는 2가지 사실을 알아내었다.
첫번째는 서브쿼리 Unnsting 후에 View Merging 이 실패하는 경우에 JPPD를 시도한다는것.
두번째는 쿼리 트랜스포머는 JPPD 수행시 Lateral View를 사용한다는것이다.
마지막으로 가까운 미래에 Lateral View를 User가 직접 사용할수 있는날을 기대하면서 이글을 마치고자 한다.

Further Reading : 
Lateral View 개념  : http://scidb.tistory.com/search/lateral%20view
SubQuery Unnesting : http://scidb.tistory.com/entry/SubQuery-Using-Method-1

신고
Posted by extremedb

댓글을 달아 주세요

9월 달에 Using Sub query Method( Filter / Access sub Query ) 이란 글을 통하여 Access 서브쿼리를
설명한적이 있다.
오늘은 서브쿼리 사용시 함정이 몇가지 있지만 그중에서 가장 자주발생하는 Access 서브쿼리의 경우를 소개한다.

서브쿼리를 사용할때 Access sub Query plan (제공자 서브쿼리 plan)이 나오더라도 방심하면 안된다.
Plan 의 모습만 Access sub Query 일뿐 사실은 Filter sub Query(확인자 서브쿼리) 로 풀리는 경우가 많이 있기 때문이다.
 
실행환경 : Oracle 10.2.0.4
아래는 전형적인  Access sub Query plan 을 보여준다.

select small_vc
  from min_max mm1
 where mm1.id_parent = 100
   and mm1.id_child = ( select max(mm2.id_child)
                                    from min_max mm2
                                 where mm2.id_parent = 100 )  ;

         
--------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |     1 |   108 |     4 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | MIN_MAX |     1 |   108 |     2 |
|*  2 |   INDEX UNIQUE SCAN            | MM_PK   |     1 |       |     1 |
|   3 |    SORT AGGREGATE              |         |     1 |     8 |       |
|   4 |     FIRST ROW                  |         |    10 |    80 |     2 |
|*  5 |      INDEX RANGE SCAN (MIN/MAX)| MM_PK   |    10 |    80 |     2 |
--------------------------------------------------------------------------

위 쿼리의 실행순서는 id 기준으로 5 -> 4 -> 3 -> 2 -> 1 이라고 설명 하였다.
하지만 항상 그렇게 되는 것은 아니다.
아래 스크립트를 보자  

인덱스 현황 :
1)EMP 테이블  : EMP_IX04  (JOB, DEPTNO, HIREDATE)
2)DEPT 테이블 : DEPT_IDX1 (LOC)

SELECT /*+ GATHER_PLAN_STATISTICS INDEX_RS(A EMP_IX04) */ *
  FROM EMP A
 WHERE A.JOB = 'CLERK'
   AND A.DEPTNO IN (SELECT /*+ NO_UNNEST INDEX(B DEPT_IDX1) */  B.DEPTNO
                                  FROM DEPT B
                                WHERE B.LOC = 'CHICAGO')
   AND A.HIREDATE BETWEEN '19801010' AND '19820101';


SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|*  1 |  FILTER                        |           |      1 |      1 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | EMP       |      1 |      1 |00:00:00.01 |       7 |
|*  3 |    INDEX RANGE SCAN            | EMP_IX04  |      1 |      1 |00:00:00.01 |       6 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| DEPT      |      2 |      1 |00:00:00.01 |       4 |
|*  5 |      INDEX RANGE SCAN          | DEPT_IDX1 |      2 |      2 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------


위 SQL 의 의도는 부서 테이블에서 위치가 CHICAGO 인 부서를 찾아서 EMP 테이블에 제공 하는것이다.
따라서 의도적으로 서브쿼리에 NO_UNNEST 힌트를 사용하였다.
PLAN 을 보면 정상적 이라고 생각할 수 있다.
그런데 Starts 컬럼(빨강색)을 보면 서브쿼리가 2번 실행된걸 알수 있다.
뭔가 이상하다.
제공자 서브쿼리는 단 한번만 실행 되어야 하는데...
아래의 Predicate Information 을 보면 더욱 이상한것을 발견할수 있다.

Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_DATE('19801010')<=TO_DATE('19820101'))
   3 - access("A"."JOB"='CLERK' AND "A"."HIREDATE">='19801010' AND "A"."HIREDATE"<='19820101')
       filter(("A"."HIREDATE"<='19820101' AND "A"."HIREDATE">='19801010' AND  IS NOT NULL))

   4 - filter("B"."DEPTNO"=:B1)
   5 - access("B"."LOC"='CHICAGO')
 
id 3에서  access 정보를 보면 DEPTNO 가 빠져있다.
인덱스가 (JOB, DEPTNO, HIREDATE) 인 상황에서 JOB, HIREDATE 만 사용하였다.
또한 id 4에서 filter("B"."DEPTNO"=:B1) 이 발생하였다.
이것은 확인자 서브쿼리에서만 나올수 있는 정보이다.
뭔가 이상하지 않은가?

이것을 결정적으로 확인할수 있는것은
DBMS_XPLAN.DISPLAY_CURSOR 가 아니라
DBMS_XPLAN.DISPLAY 이다.

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY);


---------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |     1 |    32 |     3   (0)|
|*  1 |  FILTER                        |           |       |       |            |
|   2 |   TABLE ACCESS BY INDEX ROWID  | EMP       |     1 |    32 |     1   (0)|
|*  3 |    INDEX RANGE SCAN            | EMP_IX04  |     1 |       |     1   (0)|
|*  4 |     TABLE ACCESS BY INDEX ROWID| DEPT      |     1 |     9 |     1   (0)|
|*  5 |      INDEX RANGE SCAN          | DEPT_IDX1 |     1 |       |     1   (0)|
---------------------------------------------------------------------------------

 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_DATE('19801010')<=TO_DATE('19820101'))
   3 - access("A"."JOB"='CLERK' AND "A"."HIREDATE">='19801010' AND
              "A"."HIREDATE"<='19820101')
       filter("A"."HIREDATE"<='19820101' AND "A"."HIREDATE">='19801010'
              AND  EXISTS (SELECT /*+ NO_UNNEST INDEX ("B" "DEPT_IDX1") */ 0 FROM  --> EXISTS 발생
              "DEPT" "B" WHERE "B"."LOC"='CHICAGO' AND "B"."DEPTNO"=:B1))
   4 - filter("B"."DEPTNO"=:B1)
   5 - access("B"."LOC"='CHICAGO')

Predicate Information 을 보면 분명히 Filter sub Query(확인자 서브쿼리) 로 풀리는걸 확인할수 있다.
많은 튜닝책에서 이런식으로 결합인덱스를 만들면 제공자 서브쿼리로 풀린다고 가이드 하고 있지만
불행히도 버젼 9i 이후부터는 그렇지못하다.
Access sub Query 으로 예상 했지만  Filter sub Query 로 풀리면 심각한 성능저하 현상이 발생할수 있다.
그렇다면 어떻게  Filter sub Query 를 Access sub Query(제공자 서브쿼리) 로 만들것인가?
오라클은 옵티마이져는 이러한 경우에 왠만 해서는 Access sub Query 를 선택하지 않는다.
이때 간단한 트릭을 생각할수 있다.
몇가지 방법이 있지만 가장 간단한 두가지의 방법을 소개한다.
서브쿼리가 항상 1건만 Return 한다는 정보를 옵티마이져에게 주면된다.

1.min/max 함수 사용하기

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(A EMP_IX04) */ *
  FROM EMP A
 WHERE A.JOB = 'CLERK'
   AND A.DEPTNO IN (SELECT /*+ NO_UNNEST INDEX(B DEPT_IDX1) */  MAX(B.DEPTNO)
                                  FROM DEPT B
                                 WHERE B.LOC = 'CHICAGO')
   AND A.HIREDATE BETWEEN '19801010' AND '19820101' ;

----------------------------------------------------------------------------------------------
| Id  | Operation                       | Name      | Starts | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|*  1 |  FILTER                         |           |      1 |      1 |00:00:00.01 |       5 |
|   2 |   TABLE ACCESS BY INDEX ROWID   | EMP       |      1 |      1 |00:00:00.01 |       5 |
|*  3 |    INDEX RANGE SCAN             | EMP_IX04  |      1 |      1 |00:00:00.01 |       4 |
|   4 |     SORT AGGREGATE              |           |      1 |      1 |00:00:00.01 |       2 |
|   5 |      TABLE ACCESS BY INDEX ROWID| DEPT      |      1 |      1 |00:00:00.01 |       2 |
|*  6 |       INDEX RANGE SCAN          | DEPT_IDX1 |      1 |      1 |00:00:00.01 |       1 |
----------------------------------------------------------------------------------------------

 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_DATE('19801010')<=TO_DATE('19820101'))
   3 - access("A"."JOB"='CLERK' AND "A"."DEPTNO"= AND "A"."HIREDATE">='19801010' AND
              "A"."HIREDATE"<='19820101')
   6 - access("B"."LOC"='CHICAGO')

정상적으로 Access sub Query plan 이 나온것을 확인할수 있다.

2. IN 서브쿼리 대신에 = 서브쿼리로 바꾸기

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(A EMP_IX04) */ *
  FROM EMP A
 WHERE A.JOB = 'CLERK'
   AND A.DEPTNO = (SELECT /*+ NO_UNNEST INDEX(B DEPT_IDX1) */  B.DEPTNO
                                 FROM DEPT B
                               WHERE B.LOC = 'CHICAGO')
   AND A.HIREDATE BETWEEN '19801010' AND '19820101';

---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|*  1 |  FILTER                        |           |      1 |      1 |00:00:00.01 |       5 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | EMP       |      1 |      1 |00:00:00.01 |       5 |
|*  3 |    INDEX RANGE SCAN            | EMP_IX04  |      1 |      1 |00:00:00.01 |       4 |
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT      |      1 |      1 |00:00:00.01 |       2 |
|*  5 |      INDEX RANGE SCAN          | DEPT_IDX1 |      1 |      1 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------

 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_DATE('19801010')<=TO_DATE('19820101'))
  3 - access("A"."JOB"='CLERK' AND "A"."DEPTNO"= AND "A"."HIREDATE">='19801010' AND
              "A"."HIREDATE"<='19820101')
   5 - access("B"."LOC"='CHICAGO')

두방법 모두 성공적으로 Access sub Query plan 으로 풀린다.
Buffers 도 7 에서 5 로 줄어들었다.
하지만 서브쿼리에서 2건 이상 RETURN 되는 경우는 어떻게 할것인가?
필자의 생각은 Access sub Query plan 을 포기하고 UNNEST 서브쿼리를 선택하라는 것이다.

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(A EMP_IX04) */ *
  FROM EMP A
 WHERE A.JOB = 'CLERK'
   AND A.DEPTNO IN (SELECT /*+ UNNEST INDEX(B DEPT_IDX1) */  B.DEPTNO
                                  FROM DEPT B
                                WHERE B.LOC = 'CHICAGO')
   AND A.HIREDATE BETWEEN '19801010' AND '19820101';

---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|*  1 |  FILTER                        |           |      1 |      1 |00:00:00.01 |       6 |
|   2 |   TABLE ACCESS BY INDEX ROWID  | EMP       |      1 |      1 |00:00:00.01 |       6 |
|   3 |    NESTED LOOPS                |           |      1 |      3 |00:00:00.01 |       5 |
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPT      |      1 |      1 |00:00:00.01 |       3 |
|*  5 |      INDEX RANGE SCAN          | DEPT_IDX1 |      1 |      1 |00:00:00.01 |       2 |
|*  6 |     INDEX RANGE SCAN           | EMP_IX04  |      1 |      1 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_DATE('19801010')<=TO_DATE('19820101'))
   5 - access("B"."LOC"='CHICAGO')
   6 - access("A"."JOB"='CLERK' AND "A"."DEPTNO"="B"."DEPTNO" AND "A"."HIREDATE">='19801010'
              AND "A"."HIREDATE"<='19820101')

서브쿼리가 조인으로 바뀌었고 정상적으로  Access sub Query plan 과 같이
(JOB, DEPTNO, HIREDATE) 인덱스를 사용하였다.
위와 같은 상황에서는 Unnesting 을 적극 추천한다.
혹자는 "_unnest_subquery 파라미터 등을 세션단위에서 수정하여 제공자 서브쿼리로 만들면 되지않냐?"
고 말한다.
맞는 말이지만 연구소에서나 사용하여야 한다.
Production 시스템에서는 위와같이 히든 파라미터를 수정하는 것은 아주 위험하다.
현재는 버젼이 10g 이지만 Oracle을 Upgrade를 할때는 아마도 Oracle 버젼 13 혹은 14버젼을
사용할것이다.
그때에도 위와 같은 히든 파라미터가 있다고 보장할수 없다.
Deprecate 된 힌트도 마찬가지 일것이다.(대부분 3~4 버젼이 지나면 힌트가 없어짐)
물론 미래(Oralce Upgrade 시)에 모든 AS-IS 의 소스코드를 모조리 체크해서 새로운 힌트및 파라미터로 바꿀 각오가 되어 있다면 가능하다.

결론 :
마지막 예제와 같이 서브쿼리의 집합이 PK 컬럼이나 Unique 인덱스의 컬럼을 모두 RETURN 하는 경우는
Unnesting 될때 UNQUE 가 보장되므로 별도의 SORT UNIQUE 작업이나 HASH UNIQUE 작업이 일어나지 않는다.
따라서 서브쿼리의 결과 건수가 작고 별도의 Unique 작업이 없을경우 거의 Access sub Query 와
같은 성능을 보장하므로 굳이 Access sub Query plan (제공자 plan) 을 고집할 필요가  없다는 점을
기억 해야 한다.
서브쿼리 Unnesting 은 Using Sub query Method (Sub query Flattening ) 글을 참고하기 바란다.

저작자 표시 비영리 동일 조건 변경 허락
신고
Posted by extremedb

댓글을 달아 주세요

  1. 타락천사 2009.03.25 16:48 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요 항상 좋은 글 자~알 보구 있습니다.
    궁금한 점이 있어서 질문 드려요 ^^;
    Predicate Information 정보에서
    Access 나 Filter 가 같이 나오는 경우 어떻게 해석 해야하나요 ?
    Access 는 제공자 ( 먼저 수행 ), Filter 는 체크자 ( 나중 수행 ) 이런 개념으로 인식 하고 있는데..
    예제에서 들어주신 것처럼 2가지 case 가 다 나온 경우 어떻게 해석 해야 하는요 ?

    3 - access("A"."JOB"='CLERK' AND "A"."HIREDATE">='19801010' AND "A"."HIREDATE"<='19820101')
    filter(("A"."HIREDATE"<='19820101' AND "A"."HIREDATE">='19801010' AND IS NOT NULL))


    감사드립니다.
    (__)(--)(__)

  2. 타락천사 2009.03.26 20:25 신고  댓글주소  수정/삭제  댓글쓰기

    쉬운 설명 감사드립니다.
    좋은 하루 되세요 ^^*

  3. Favicon of http://scidb.tistory.com BlogIcon extremedb 2009.03.26 23:20 신고  댓글주소  수정/삭제  댓글쓰기

    네 반갑습니다.
    문의 하신 filter 는 인덱스 SCAN 시 filter 처리된 것입니다.
    id 기준으로 3번 입니다.
    3번은 index scan 이므로 filter 는 index filtering 이 되겟습니다.
    다른말로 하면 인덱스 "A"."JOB"='CLERK' 조건을 만족하는 인덱스를 모조리 다읽은 후에 A"."HIREDATE" 조건은 인덱스 filtering 을 해서 조건을 만족하지 않으면 버립니다.
    따라서 인덱스 효율이 별로 좋지 않습니다.
    하지만 인덱스 에서 A"."HIREDATE" 조건으로 미리 filtering 을 하였으므로 해당되지 않는건에 대해서 테이블로 access 하는것을 방지하는 효과는 있습니다.
    감사합니다.

  4. 속눈썹 2011.06.09 18:56 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요.
    좋은 글 정말 감사하게 잘 읽었습니다. 제가 아직 초보라서 여기 나온 예제를 test 하던 중 다른 결과가 나와서 문의합니다.

    마지막 sql문장에 UNNEST hint 를 사용해서 Using Sub query Method (Sub query Flattening ) 문서에 나온 1.Unnesting 서브쿼리 방식으로 실행계획이 만들어진걸로 확인했습니다.
    하지만 제가 동일한 sql과 hint로 실행한 결과 아래와 같습니다. (2. Semi Join/Anti Join 방식)
    제가 궁금한 내용은 왜 이런 현상이 나왔는지 궁금합니다. 그리고 Starts 는 실제 실행횟수를 의미하는 건가요..?

    [Test 환경]
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

    [실행sql]
    SELECT /*+ GATHER_PLAN_STATISTICS INDEX(A EMP_IX04) */ *
    FROM EMP A
    WHERE A.JOB = 'CLERK'
    AND A.DEPTNO IN (SELECT /*+ UNNEST INDEX(B DEPT_IDX1) */ B.DEPTNO
    FROM DEPT B
    WHERE B.LOC = 'CHICAGO')
    AND A.HIREDATE BETWEEN '19801010' AND '19820101';

    [실행계획 및 결과]
    내용이 제대로 안올라가서 제블로그에 이미지로 올렸습니다.
    실행계획 및 결과 : http://blog.naver.com/kazki7074/120131837057


    읽어주셔서 감사합니다.

오늘은 지난시간에 논의 했던 SubQuery Flattening 에 이어서 쿼리변형이 발생하지 않는 Access 서브쿼리  Filter 서브쿼리, Early Filter 서브쿼리 에 대해서 이야기 할것이다.


튜닝관점의 서브쿼리의 분류

1.Unnesting 서브쿼리 : 참조
                 
2.Semi Join/Anti Join : 참조

3.Access
서브쿼리 : 쿼리변형이 없음
    1)
흔히말하는 제공자 서브쿼리임.(서브쿼리부터 풀려서 메인쿼리에 값이 제공된다.)
    2)
위의 1 2번과 다르게 Plan 에 메인쿼리와 서브쿼리의 Join 이 없다.
    3)
힌트: 특별한 힌트없음.
              
다만  /*+ no_unnest */ 를 사용하여 SubQuery Flattening 을 방지하고
              
서브쿼리로부터 제공되는 메인쿼리의 컬럼에 인덱스가 생성되어 있으면됨.
    4)
주의사항: corelate 서브쿼리는 제공자 서브쿼리가 될수 없음.

4.Filter
서브쿼리 : 쿼리변형이 없음
    1)
흔히 말하는 확인자 서브쿼리임.(메인쿼리의 값을 제공받아 서브쿼리에서 체크하는 방식임)
    2)
위의 1 2번과 다르게 Plan 에 메인쿼리와 서브쿼리의 Join 이 없고 Filter 로 나온다.
    3)Filter SubQuery
의 특징은 메인쿼리의 From 절에 있는 모든 테이블을 엑세스후에 가장마지막에
     
서브쿼리가 실행된다는 것이다.
    4)
힌트: 특별한 힌트없음.
               
다만  /*+ no_unnest */ 를 사용하여 SubQuery Flattening 을 방지하고
               
메인쿼리로부터 제공되는 서브쿼리의 조인컬럼에 인덱스가 생성되어 있으면됨.

5.Early Filter
서브쿼리 : 쿼리변형이 없음
    1)Filter SubQuery
와 같은 방식이지만 서브쿼리를 최대한 먼저 실행하여 데이터를 걸러낸다.
    2)
힌트 : 메인쿼리에 push_subq 힌트사용 (10g 이후부터는 서브쿼리에 힌트사용해야함)
    3)
주의사항: 많은 튜닝책에서 "Push_subq 힌트를 사용하면 제공자 서브쿼리를 유도한다" 라고
                    
되어 있으나 이는 잘못된 것이다.
                     push_subq
힌트를 사용하면 확인자 서브쿼리(Filter 서브쿼리)를 유도하지만 최대한
                     
먼저 수행된다.


아래의 스크립트를 보자

 3.Access 서브쿼리 

select    small_vc

 from     min_max mm1

where     mm1.id_parent = 100

  and     mm1.id_child = (

                    select    max(mm2.id_child)

                    from      min_max mm2

                    where     mm2.id_parent = 100

          )  ;                 

 

--------------------------------------------------------------------------

| Id  | Operation                      | Name    | Rows  | Bytes | Cost  |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |         |     1 |   108 |     4 |

|   1 |  TABLE ACCESS BY INDEX ROWID   | MIN_MAX |     1 |   108 |     2 |

|*  2 |   INDEX UNIQUE SCAN            | MM_PK   |     1 |       |     1 |

|   3 |    SORT AGGREGATE              |         |     1 |     8 |       |

|   4 |     FIRST ROW                  |         |    10 |    80 |     2 |

|*  5 |      INDEX RANGE SCAN (MIN/MAX)| MM_PK   |    10 |    80 |     2 |

--------------------------------------------------------------------------

위의 plan 을 보면 실행순서가 헷갈릴수 있다.
결론을 이야기 하자면 id 기준으로 실행 순서는 5 -> 4 -> 3 -> 2 -> 1 이다.

즉 맨밑에서부터 위로 실행된다. (서브쿼리부터 실행해서 메인쿼리에 1건을 제공하였다)
특정일자에 max 일련번호를 찾아서 처리해야할때 많이 사용하는 SQL 패턴이다.
corelate
서브쿼리가 아니고 서브쿼리로부터 제공되는 메인쿼리의 컬럼에 인덱스가 생성되어 있는 경우만이
Access
서브쿼리로 풀린다.(mm1.id_child 컬럼에 인덱스가 있어야 한다)

다음의 두가지 경우에서만 Access 서브쿼리를 사용해야한다.
1) 서브쿼리의 엑세스건수가 적고 서브쿼리의 결과를 제공받은 메인쿼리도 엑세스 건수가 적어야 한다.

2)
비록 서브쿼리의 엑세스 건수가 많지만 그결과를 제공받은 메인쿼리의 엑세스 건수가 적다면 사용할수 있다.
   
왜냐하면 Access 서브쿼리는 단한번만 수행되기 때문이다.   
   
이경우 메인쿼리의 테이블이 mm1.id_child 컬럼기준으로 클러스트링 팩터가 좋다면 서브쿼리가 힘을 얻게 된다.
    
하지만 이경우는 반드시 Semi Join 이나 Unnesting 서브쿼리, Filter 서브쿼리등과 성능을 비교해 보아야 한다.

 4.Filter 서브쿼리

select    small_vc

from      t1

where     n2 between 100 and 200

  or      exists    (

                    select    null

                    from      t2

                    where     t2.n1 = t1.n1

                    and       t2.mod1 = 15

          );

------------------------------------------------------------------------

| Id  | Operation                      | Name  | Rows  | Bytes | Cost  |

------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |       |   597 | 11343 |    28 |

|*  1 |  FILTER                        |       |       |       |       |

|   2 |   TABLE ACCESS FULL            | T1    | 10000 |   185K|    28 |

|*  3 |   TABLE ACCESS BY INDEX ROWID  | T2    |     1 |     7 |     2 |

|*  4 |    INDEX RANGE SCAN            | T2_PK |     1 |       |     1 |

------------------------------------------------------------------------


메인쿼리의 WHERE 절에 조건이 있고 OR EXISTS 를 사용하게 되면 CBQT(Cost Based Query Transformation) 가 작동을 하지않는다.
따라서 위의 경우처럼 OR 가 있는 서브쿼리는 SubQuery Flattening 이 발생하지 않고 확인자 서브쿼리로 풀리게 된다.
위의 경우와는 반대로 10g 에 와서는 옵티마이져가 왠만하면  Semi Join 이나 서브쿼리 Unnesing등의 쿼리변형을 하게되므로 
대부분의 경우 강제로 NO_UNNEST 힌트를 사용해야지만  Filter 서브쿼리로 풀리게 된다.
주의할점은 10g 에서 서브쿼리가 filter 로 풀릴경우 Plan 에서는 Filter Operation 이 사라지는 경우가 많이 있다.
Plan
이 잘못된것이 아니니 참고하기 바란다.
상식이지만 노파심에서 다시한번 이야기 하지만 Filter 서브쿼리는 메인쿼리로부터 조인되는 컬럼(t2.n1) 반드시 인덱스가
만들어져 있어야 한다.
그렇지 않으면 성능은 기대할수 없다.

Filter
서브쿼리는 다음의 두가지 경우에 사용하여야 한다.
1) 메인쿼리의 where 절에 똑똑한 조건들이 많아서 엑세스 건수가 적을때
   
이경우는 filter Operation 이 몇번 발생하지 않게 되므로 당연히 유리하다.

2)
메인쿼리는 비록 엑세스건수가 많지만 서브쿼리의 체크조건이 True 인경우가 많은경우
  
이경우는 특히 부분범위처리시 유리하다.
  
왜냐하면  비록 건수가 많지만 서브쿼리의 체크조건이 True 인경우가 많으므로 화면에 바로바로 나오게 된다.
  
하지만 배치 프로그램처럼 전체범위를 목적으로 하는경우는 성능이 저하되므로 주의하여야 한다.
  
이때도 서브쿼리의 t2.n1 컬럼기준으로 서브쿼리 테이블의 클러스트링 팩터가 좋다면 성능이 향상되는데 물론 메인쿼리가
   sort
되는경우 이거나 인덱스의 사용등으로 자동 sort 가 되어 서브쿼리에 데이터가 공급되는 경우에 한해서다.

 5.Early Filter 서브쿼리

 

SELECT par.small_vc1, chi.small_vc1
  FROM PARENT par,
            CHILD chi
 WHERE par.id1 BETWEEN 100 AND 200
   AND chi.id1 = par.id1
   AND EXISTS (
                        SELECT /*+ push_subq  */
                                      NULL
                           FROM subtest sub
                         WHERE sub.small_vc1 = par.small_vc1
                            AND sub.id1 = par.id1
                            AND sub.small_vc2 >= '2'
                        );

 

사용자 삽입 이미지


Early Filter
서브쿼리를 설명하려면 최소한 메인쿼리에 2개의 테이블이 있어야 한다.
위의 서브쿼리를 보면 PARENT 쪽 메인쿼리만 풀리면 서브쿼리가 동작할수 있다.
다시말하면 CHILD 쪽의 컬럼이 서브쿼리에 없으므로 PARENT 쪽의 컬럼만 상수화 되면 서브쿼리가 작동할수 있게
되는 것이다.
실행순서는 PLAN 에서 보는것과 같이 PARENT -> subtest -> CHILD 이다 하지만 불행하게도 오라클은 대부분의 Filter 쿼리에서 서브쿼리는 가장마지막에 작동한다.
즉 대부분의  Filter 쿼리에서 PARENT ->CHILD -> subtest 순으로 풀리게 된다.
이때 사용할수 있는 힌트가 push_subq 힌트이다.
최대한 먼저 데이터를 걸러내어 그다음 테이블과 조인시 건수를 줄이고 싶을때 탁월한 효과를 내는 힌트이다.
반드시 여러분의 환경에서 여러분들의 쿼리로  push_subq 힌트가 있을때와 없을때의 차이를 느껴보기 바란다.

결론:
이상으로 서브쿼리의 5가지 분류에 대하여 알아 보았다.
서브쿼리는 실제 프로젝트 환경에서 자주 사용하므로 5개의 분류는 나름대로 특징과 장단점이 있어서 적재적소에 사용할경우
엄청난 이득을 가져올수 있지만 그렇지 않은경우 독이 될수 있음을 기억하자.

Reference :
1)Query Optimization in Oracle Database10g Release 2(White Paper)
2)COST BASED QUERY TRANSFORMATIONS CONCEPT
   AND ANALYSIS USING 10053 TRACE(Riyaj Shamsudeen)
3)Cost Based Oracle Fundamentals(Jonathan Lewis) with Blog (http://jonathanlewis.wordpress.com)

편집후기 :
요즘 필자의 프로젝트가 막바지로 치달리고 있어서 엄청 바쁘지만 블로그는 블로그대로 관리를 해야하니 엄청 스트레스가 된다.
블로그만 쓰며 살수는 없는걸까?^^

저작자 표시 비영리 동일 조건 변경 허락
신고
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://ukja.tistory.com BlogIcon 욱짜 2008.09.29 18:08 신고  댓글주소  수정/삭제  댓글쓰기

    두 가지 정도만 첨부했으면 하네요. 우선, qb_name hint와 push_subq hint를 조합하면 main query에서 push_subq hint를 사용할 수 있을 거 같습니다. 개인적으로 가능한 main query에서 hint를 관리하는 것이 더 좋은 hint convention이라고 생각됩니다. 그리고 filter optimization(cache?)가 작동하는 경우 filter가 생각보다 성능이 좋은 경우도 있다는 것도 언급이 되면 좋겠습니다.

  2. Favicon of http://scidb.tistory.com BlogIcon extremedb 2008.09.30 09:07 신고  댓글주소  수정/삭제  댓글쓰기

    말씀하신대로 쿼리블럭을 사용해서 control 하는것이 더 직관적이라고 봅니다.
    다만 쿼리블럭을 사용하지 않을 경우 push_subq 와 unnest, semi join 힌트등은 서브쿼리에 사용해야 한다는것을 나타낸것 입니다.
    지적해주신 filter cache 효과는 원리가 scalar subquery 와 같기 때문에 그때 언급 하도록 하겟습니다.
    감사합니다.

  3. 금땡이 2008.10.07 10:02 신고  댓글주소  수정/삭제  댓글쓰기

    바쁜 와중에서도 이런 글을 남겨 주시니, 무민한 백성이 견문을 넓힐 수 있음을 알아 주시기 바랍니다.
    무릇 글은 자신을 위한 것만이 아니지요.^^

  4. Favicon of http://scidb.tistory.com BlogIcon extremedb 2008.10.07 17:04 신고  댓글주소  수정/삭제  댓글쓰기

    요즘 프로젝트 때문에 자주쓰진 못하지만 그래도 시간나는대로 연구결과를 올리도록 하겠습니다.
    다음에 술한잔 하시지요.^^

  5. 2009.02.12 17:20  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  6. Favicon of http://1ststreet.tistory.com BlogIcon SITD 2011.05.12 17:08 신고  댓글주소  수정/삭제  댓글쓰기

    이제야 좀 정리가 되는거 같네요 정말 감사드립니다 ^^

  7. 속눈썹 2011.06.07 19:20 신고  댓글주소  수정/삭제  댓글쓰기

    혼자 2주 넘게 subquery에 대한 고민을 했는데 이 글을 정리가 됐습니다. 정말 감사합니다. ^^

  8. 김영석 2013.06.06 13:00 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 글 잘 읽고 있습니다.
    오동규님은 저의 롤모델이십니다.
    감사합니다.

    ps) 레퍼런스 링크는 안되나요?

튜닝 컨설팅을 하다보면 개발자들이 서브쿼리에 대하여 많은 관심을 보이며 또한 자주 사용하곤 한다.
하지만 정작 튜닝관점및  외형적인 관점에서 서브쿼리에 대하여 정의를 내릴수 있는 사람은 많지않다.  
오늘은 서브쿼리에 대하여 정의를 내려보자.

외형적관점에서 서브쿼리의 종류는 2가지 밖에 없다.

1.Non Corelate 서브쿼리  : (서브쿼리 내에 서브쿼리와 메인쿼리의 조인절이 없음)
2.Corelate 서브쿼리: (서브쿼리 내에 서브쿼리와 메인쿼리의 조인절이 있음)
그렇다면 튜닝관점에서 서브쿼리를 어떻게 분류해야 할까?
튜닝관점의 서브쿼리는 아래처럼 5가지로 분류할수 있다.

튜닝관점의 서브쿼리의 분류

1.Unnesting 서브쿼리 : 옵티마이져가 서브쿼리를 조인으로 변형시킴
    1)서브쿼리를 조인으로 바꾸는 방식 (일반적으로 서브쿼리의 테이블이 Driving 이된다.)
    2)오라클은 서브쿼리를 인라인뷰로 바꾸고 서브쿼리 집합이 Distinct 하지 않을경우 Sort Unique 나
        Hash Unique 작업을 추가로 진행한다.
      이는 메인쿼리의 건수를 보존하기 위해서 이다.
    3) 힌트 :유도 힌트 : /*+ unnest */
                               (서브쿼리에 사용하거나 메인쿼리에서 쿼리블럭 힌트(qb_name)를 사용하여야 한다.)
                방지 힌트 : /*+ no_unnest */ (서브쿼리에 사용)
            
2.Semi Join/Anti Join : 옵티마이져가 서브쿼리를 조인으로 변형시킴
    1)서브쿼리를 조인으로 바꾸는 방식 (일반적으로 서브쿼리의 테이블은 Driving 이 되지 못한다.)
    2)이방식은 버젼 8i 부터 사용되었으며 아래에 소개되는  Filter SubQuery 를 발전시킨 형태이다.
       메인쿼리의 값을 상수로 받은다음 서브쿼리쪽 테이블에서 만족하는 건이 하나라도 있으면 다음건으로
       넘어간다.(Filter 처리와 원리가 같음.)
       Unnesting 서브쿼리와 Semi Join 을 같이 보는 사람들이 있는데 이건 잘못된것이다.
       오라클에서 Unnesting 개념과 Semi Join 을 같이 보면 안된다.
       물론 10053 보고서에는 둘다 su(sub query unnesting) 로 나오긴 한다.
       필자는 10053 보고서도 마음에 들지 않는다.
       굳이 같이사용할려고 한다면 "SubQuery Flattening" 이라고 해야 한다.
       이렇게 해야만 Unnesting 과 Semi Join이 헷갈리지 않는다.    
    3)세미조인과 안티조인의 차이는 긍정형 (EXISTS 혹은 IN) 은 세미조인으로 풀리고
       부정형 (NOT EXISTS 혹은 NOT IN) 등은 안티조인으로 풀린다.
       물론 안티조인이 되려면 조인되는 양측의 컬럼이 NOT NULL 이거나 WHERE 절에
       NOT NULL 을 명시해야 한다.
     4) 힌트 :유도 힌트 : use_nl 혹은 use_hash 혹은 use_merge (서브쿼리에 사용 해야한다.)
                                 10g 이전버전에서는 세미조인및 안티조인 힌트가 따로 있음.
                 방지 힌트 : /*+ no_unnest */ --> 특이하게도 방지힌트는 unnest 형식과 같다.


3.Access 서브쿼리 : 쿼리변형이 없음
    1)흔히말하는 제공자 서브쿼리임.(서브쿼리부터 풀려서 메인쿼리에 값이 제공된다.)
    2)위의 1번 2번과 다르게 Plan 에 메인쿼리와 서브쿼리의 Join 이 없다.
    3)힌트: 특별한 힌트없음.
               다만  /*+ no_unnest */ 를 사용하여 SubQuery Flattening 을 방지하고
               서브쿼리로부터 제공되는 메인쿼리의 컬럼에 인덱스가 생성되어 있으면됨.
    4) 주의사항: corelate 서브쿼리는 제공자 서브쿼리가 될수 없음.

4.Filter 서브쿼리 : 쿼리변형이 없음
    1)흔히 말하는 확인자 서브쿼리임.(메인쿼리의 값을 제공받아 서브쿼리에서 체크하는 방식임)
    2)위의 1번 2번과 다르게 Plan 에 메인쿼리와 서브쿼리의 Join 이 없고 Filter 로 나온다.
    3)Filter SubQuery 의 특징은 메인쿼리의 From 절에 있는 모든 테이블을 엑세스후에 가장마지막에
      서브쿼리가 실행된다는 것이다.
    4) 힌트: 특별한 힌트없음.
                다만  /*+ no_unnest */ 를 사용하여 SubQuery Flattening 을 방지하고
                메인쿼리로부터 제공되는 서브쿼리의 조인컬럼에 인덱스가 생성되어 있으면됨.

5.Early Filter 서브쿼리 : 쿼리변형이 없음
    1)Filter SubQuery 와 같은 방식이지만 서브쿼리를 최대한 먼저 실행하여 데이터를 걸러낸다.
    2)힌트 : 메인쿼리에 push_subq 힌트사용 (10g 이후부터는 서브쿼리에 힌트사용해야함)
    3)주의사항: 많은 튜닝책에서 "Push_subq 힌트를 사용하면 제공자 서브쿼리를 유도한다" 라고
                     되어 있으나 이는 잘못된 것이다.
                     push_subq 힌트를 사용하면 확인자 서브쿼리(Filter 서브쿼리)를 유도하지만 최대한
                     먼저 수행된다.


오늘은 5가지 중에서 Query Transformation(쿼리변형)과 관련이 있는 Unnesting 서브쿼리 와
Semi Join/Anti Join 에 대해서 이야기 할것이다.
그럼 1번과 2번을 스크립트로 살펴보자.
 
1.Unnesting 서브쿼리 : (원본쿼리)

select small_vc

from    t1

where   n2 between 10 and 200

and     exists  (select  /*+ unnest */ null

                   from   t2

                  where   t2.no_indexed_column = t1.n1

                    and   t2.n2 = 15) ;      


옵티마이져는 위의 쿼리를 아래의 쿼리로 변형시킨다.(아래의 힌트는 이해를 돕기위한 것임)

select /*+ leading(t2 t1) */ t1.small_vc

from    t1,
        (select distinct t2.no_indexed_column  
           from t2
          where t2.n2 = 15 ) t2

where   t1.n2 between 10 and 200
  and   t1.n1 = t2.no_indexed_column



          

------------------------------------------------------------------------

| Id  | Operation                      | Name  | Rows  | Bytes | Cost  |

------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |       |     1 |    26 |    24 |

|*  1 |  TABLE ACCESS BY INDEX ROWID   | T1    |     1 |    19 |     2 |

|   2 |   NESTED LOOPS                 |       |     1 |    26 |    24 |

|   3 |    SORT UNIQUE                 |       |     1 |     7 |     2 |

|   4 |     TABLE ACCESS BY INDEX ROWID| T2    |     1 |     7 |     2 |

|*  5 |      INDEX RANGE SCAN          | T2_N2 |     1 |       |     1 |

|*  6 |    INDEX RANGE SCAN            | T1_PK |     1 |       |     1 |

------------------------------------------------------------------------

위의 plan 에서 보는 바와 같이 서브쿼리가 Driving 집합이 되었으며 메인쿼리의 집합을 보존하기 위해
Distinct 작업(Sort Unique) 를 실행 하였다.
또한 옵티마이져는 서브쿼리내에 조인되는 컬럼에 인덱스도 없고 선택성도 좋지않으므로 Semi Join 보다는 Unnesting 서브쿼리를 선호한다.


2.Semi Join /Anti Join : (원본쿼리)

select small_vc

from    t1

where   n2 between 10 and 200

and     exists  (select  /*+ use_nl(t1 t2) */ null

                   from   t2

                  where   t2.Indexed_column = t1.n1

                    and   t2.n2 = 15) ;  

옵티마이져는 위의 서브쿼리를 아래의 조인쿼리로 변형시킨다..(아래의 힌트는 이해를 돕기위한 것임)
아래 조인절의 (s) 는 세미조인을 의미한다. (세미조인은 메인쿼리의 건수를 변화시키지 않는다)

select  /*+ leading(t1 t2) */
        t1.small_vc

from    t1,
        t2

where   t1.n1 = t2.Indexed_column(s)
  and   t1.
n2 between 10 and 200
  and   t2.n2 = 15

 

             

------------------------------------------------------------------------

| Id  | Operation                      | Name  | Rows  | Bytes | Cost  |

------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |       |     1 |    23 |     3 |

|   1 |  NESTED LOOPS SEMI             |       |     1 |    23 |     3 |

|*  2 |   TABLE ACCESS BY INDEX ROWID  | T1    |     1 |    19 |     2 |

|*  3 |    INDEX RANGE SCAN            | T1_PK |     1 |       |     1 |

|*  4 |   INDEX RANGE SCAN             | T2_N2 |     1 |     4 |     1 |

------------------------------------------------------------------------


위의 plan 에서 보는 바와 같이 서브쿼리의 조인되는 컬럼에 인덱스가 있고 선택성이 좋으면 옵티마이져는
세미조인을 선택한다.
세미조인의 특징은
1)Plan 에 Join 정보가 나오며(위의 경우 Nested Loop) --> 바로 이부분이 Unnesting 서브쿼리와 다르다.
2)Driving 이 되지못하고
3)Filter 서브쿼리처럼 한건만 만족하면 바로 다음건으로 넘어가는 조인이라고 했다.
그렇다면 Filter 서브쿼리와 다른점은?
세미조인과 Filter 서브쿼리의 다른점은 세미조인은 필요에 따라서 Hash Join/Sort Merge Join/Nested Loop Join 등을 골라서 사용할수 있다는 점이 다르다.
Filter 서브쿼리는 선택할수 있는 옵션이 없다.

결론:
오늘은 튜닝관점의 서브쿼리의 5가지 종류 중에서 Unnesting 서브쿼리 와 Semi Join /Anti Join 에 대하여 알아보았다.
서브쿼리의 사용법및 서브쿼리 관련 힌트는 서브쿼리의 이해 뿐만아니라 Query Transformation (쿼리변형)을
이해하기 위해서도 반드시 숙지하여야 한다.
다음 시간에는 쿼리변형이 없는 서브쿼리의 3가지 유형(3,4,5번)에 대하여 심도깊게 이야기 할것이다.

Reference :
1)Query Optimization in Oracle Database10g Release 2(White Paper)
2)COST BASED QUERY TRANSFORMATIONS CONCEPT
   AND ANALYSIS USING 10053 TRACE(Riyaj Shamsudeen)
3)Cost Based Oracle Fundamentals(Jonathan Lewis) with Blog (http://jonathanlewis.wordpress.com)

신고
Posted by extremedb

댓글을 달아 주세요

  1. 백면서생 2008.09.09 10:55 신고  댓글주소  수정/삭제  댓글쓰기

    정리 잘하셨네요.remind 잘 했습니다~
    근데 오타가 보이네요.
    Non Correlated Subq와 Correlate Subq의 내용이 바뀐듯~

  2. Favicon of http://ukja.tistory.com BlogIcon 욱짜 2008.09.09 18:03 신고  댓글주소  수정/삭제  댓글쓰기

    제가 요즘 Query Transformation과 관련된 세미나를 준비하고 있는데 참조가 되네요.

  3. 시연 2008.09.09 18:59 신고  댓글주소  수정/삭제  댓글쓰기

    좋은글 잘보고 갑니다. 몰랐던것을 알게됬네요. ^^

  4. Favicon of http://scidb.tistory.com BlogIcon extremedb 2008.09.10 02:26 신고  댓글주소  수정/삭제  댓글쓰기

    조만간 저도 CBQT 에 대하여 연재할 생각을 가지고 있습니다.
    참 욱짜님께는 PARALLEL 모니터링 관련 블로그를 올리겠다는 약속을 지키지 못해 죄송합니다.
    프로젝트를 핑계로 할수 밖에 없네요^^

  5. 금땡이 2008.09.17 09:22 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 자료네요. 덕분에 정리가 잘 되었습니다.

  6. Favicon of http://1ststreet.tistory.com BlogIcon SITD 2011.05.12 17:08 신고  댓글주소  수정/삭제  댓글쓰기

    따로따로 하나씩 공부하니까 정리가 진짜 안되던데, 이렇게 모아두니까 쏘옥쏘옥 들어오네요
    좋은 글 감사드립니다 ^^

  7. 김영석 2013.06.06 12:20 신고  댓글주소  수정/삭제  댓글쓰기

    서브쿼리 unnesting의 미묘한 차이를 잘 보았습니다.
    nested loop semi는 원리가 이해가 되는데,
    hash semi와 sort merge semi는 내부동작을 어떻게 하는지
    감이 잘 오지 않습니다.
    이에 대한 글을 아직 쓰지 않았으면 부탁드립니다.

    감사합니다.

    • Favicon of http://scidb.tistory.com BlogIcon extremedb 2013.06.08 17:26 신고  댓글주소  수정/삭제

      기본적으로 semi join은 hash join, sort merge join, nested loop join 을 사용할 수 있으므로 타 조인과 차이점은 없습니다. 하지만 특징이 있습니다. 서브쿼리가 unnesting 된다고 해도 semi로 풀리면 sort unique 혹은 hash unique가 발생하지 않습니다. 왜냐하면 semi 는 항상 후행집합이 되어 존재유무만 체크하기 때문입니다. 따라서 메인쿼리의 건수를 보존하기 위한 distinct 작업은 필요하지 않지요. 하나의 예외가 hash join right semi 입니다. 이때는 서브쿼리가 선행집합이 됩니다.