'2010/02'에 해당되는 글 4건

  1. 2010.02.26 Oracle 조인 방법 25가지 (15)
  2. 2010.02.19 한방 Query를 사용하지 말아야 할 때 (17)
  3. 2010.02.11 Connect By VS ANSI SQL (6)
  4. 2010.02.04 해결사 되기 (15)

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 신고  댓글주소  수정/삭제  댓글쓰기

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

SQL의 길이에 따른 분석시간

언제부터인가 복잡한 업무의 배치작업에 한방 SQL이 유행하기 시작했다. 좋은 현상이다. 하지만 이제 정도가 지나친 SQL들이 가끔 눈에 뛴다. 한방 SQL을 사용하지 말아야 할 때와 사용해야 할 때를 구분할 줄 알아야 한다. SQL이 어느 정도 길어지면 PL/SQL 이나 PRO*C 등을 이용하여 절차형으로 바꾸어야 한다. 이렇게 하더라도 Bulk CollectFor all 등으로 처리하거나 배열처리를 병행한다면 만족할 만한 속도를 낼 수 있다..

 

아래는 SQL의 길이와 SQL을 전체적으로 이해하는데 걸리는 시간을 조합한 그래프이다.

사용자 삽입 이미지

SQL이 길어지면 이해하기 힘들어

이 그래프를 본다면 SQL을 길게 작성하는 것이 얼마나 위험한지 알 수 있다. SQL의 길이가 짧으면 짧을수록 그것을 이해하는 데 걸리는 시간은 얼마 되지 않음을 알 수 있다. 반대로 SQL의 길이가 길수록 이해하는 데 걸리는 시간은 무한대로 늘어난다. 누구도 위의 그래프에 예외일 수 없다.

만약 여러분이 업무 인수인계를 받는 입장인데 SQL 하나가 A4 용지 기준으로 40페이지 라면? 아마 인수인계 받는데 한달이 걸려도 전체 SQL을 이해하기 힘들 것이다. 하지만 1페이지짜리 SQL 40개 라면 웃으며 차근 차근 인수인계를 받을 수 있다. 하루에 SQL 3~4개 혹은 그 이상도 인수인계 받을 수 있다. 하루에 4개씩 인수 인계 받는다면 10일 이면 인수 인계가 끝난다. 40 페이지나 되는 한방 Query는 유지보수 하기가 대단히 어려움을 알아야 한다.

 

이제 위의 그래프에 근거하여 한방 SQL을 사용해도 되는 경우와 사용하지 말아야 할 경우를 구분해 보자.

 

한방 SQL을 사용해도 되는 경우

첫 번째, SQLA4 용지 기준으로 4페이지 이하인 경우.

4페이지라고 한 것은 꼭 정해진 것은 아니다. 하지만 유지보수의 관점에서 가독성이 좋아야 한다. 4페이지면 조금 길어서 가독성이 낮아진다고 생각할 수 있지만 필자의 경우 SQL을 출력할 때 한 면에 인쇄할 페이지 수를 2로 설정하면 2페이지만 보면 전체 SQL이 출력 되므로 4페이지 까지는 조금만 노력해도 분석이 용이했다. 하지만 한면에 인쇄할 페이지 수를 4로 하자 글자가 너무 작아져서 볼 수 없는 수준이었다. 필자의 경우 기준은 4페이지 이지만 개인에 따라 기준은 2페이지 일 수도 있고 6페이지 일 수도 있다. 하지만 아무리 SQL에 능통한 사람도 SQL의 길이가 A4 용지 기준으로 8페이지 이상이 된다면 분석시간이 급속도로 늘어날 것이다..

 

두 번째, SQL5페이지가 넘어 가더라도 Union 혹은 Minus 등으로 명확히 구분되거나 누가 보더라도 이해가 빠른 SQL인 경우.

이 경우는 5페이지가 넘어가지만 빠른 시간에 분석할 수 있으므로 5페이지가 넘어 가더라도 유지보수가 용이하다. 하지만 이 경우에도 8페이지가 넘어간다면 고민해야 한다.

 

세 번째, SQL5페이지가 넘어 가고 업무의 변경이 있더라도 SQL을 변경하는 것이 아니라 SQL을 새로 작성하기로 합의하거나 혹은 이러한 정책이 수립된 경우.

이 경우는 SQL을 수정할 일이 없으므로 길어도 상관없다. 하지만 SQL을 새로 작성하는 사람이 모델과 업무를 잘 알고 있고 튜닝을 할 줄 알아야 고품질의 SQL을 작성할 수 있다.

 

네 번째, 유지보수의 중요성 보다 성능이 더 중요한 경우.

대용량의 복잡한 업무를 처리하는데 일주일이 넘어간다면 견딜 수 없을 것이다. 예를 들면 요금청구 작업의 성능은 기업의 흥망을 좌우할 수 있다. 이런 경우는 유지보수를 희생하더라도 한방 Query를 사용할 수 있다.

 

다섯 번째, SQL5페이지가 넘어 가지만 업무의 변경이 전혀 없어 SQL을 수정 할 일이 없는 경우.

유지보수를 할 필요가 없는 경우이다. 하지만 이런 상황은 아주 예외적인 경우일 것이다.

 

위의 5가지 경우가 아니라면 한방 SQL을 작성해서는 안 된다.

한방 Query와 관련한 유명한 일화

HR(인적자원 관리) 프로젝트에서 급여를 계산하는 프로그램을 한방 SQL로 개발하였고 40페이지가 넘는다고 했다. 급여계산은 한방 Query의 성능이 빨라 Open을 성공적으로 했다고 한다. 하지만 문제는 Open2년 뒤에 찾아왔다. 업무가 변경되어 급여계산의 SQL을 수정해야 하는데 아무도 SQL을 수정할 수 있는 사람이 없었다. 조직내부에서 몇 주간 고민해 보았으나 결국 분석을 포기하고 원작자를 불렀다.  

 

핵심은 이렇다. 돈을 많이 쳐줄 테니 SQL을 고쳐달라는 것이었다. 하지만 누가 그랬던가? 사람은 망각의 동물이라고...... 결국 원작자도 2년이 지난 이상 40페이지가 넘는 SQL을 외우고 다닐 수는 없는 노릇이 아닌가? 그는 분석을 포기하였다고 한다. 아래는 원작자가 분석을 포기한 이유이다.

 

원작자: 돈을 아무리 준다고 해도 그 기간 내에는 할 수가 없습니다. 인라인 뷰가 80개가 넘는데 분석하는 데만 2~3달 걸릴 것 같습니다.

요청자:  두달 안에 변경된 업무를 반영해야 하는데 큰일 났네....

 

결국 원작자는 돌려보내고 급여 담당자가 프로그램을 절차형으로 모두 새로 작성했다고 한다. 새로 작성하는데 꼬박 한달이 걸렸다고 한다. 위의 원작자는 분석하는데만 두 달이 넘는다고 하였다. 하지만 급여담당자는 한달안에 모든 프로그램이 작성 완료되었음을 주목하라. 담당자는 한방 Query 보다 성능은 떨어졌지만 상관이 없다고 하였다. 아래는 급여 담당자의 이야기이다.

 

급여 담당자: 급여 배치가 30분 정도 결렸는데 절차형으로 바꾸니 두 시간이 걸리네요. 하지만 상관 없습니다. 오늘 저녁에 급여 배치를 돌리고 내일 급여가 지급되기 때문에 내일 오후 1시까지 배치가 끝나면 됩니다.

원작자는 유지보수의 중요성을 무시한채 Critical 하지도 않은 성능만 고려한 것이다. 아무리 좋은것 이라도 지나치면 괴로워진다. 이제는 한방 Query를 남발하지 않았으면 한다.


신고
Posted by extremedb

댓글을 달아 주세요

  1. feelie 2010.02.19 17:21 신고  댓글주소  수정/삭제  댓글쓰기

    잘봤습니다...

  2. Favicon of http://blog.ngelmaum.org BlogIcon 천사마음 2010.02.19 17:35 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 정보 고맙습니다. 앞으로 자주 찾아뵐께요.
    좋은 글 많이 부탁드립니다.

  3. Favicon of http://smallhuman.egloos.com BlogIcon 긁적 2010.02.19 21:11 신고  댓글주소  수정/삭제  댓글쓰기

    오오.... 모든 프로그래머가 명심해야 할 만한 내용입니다.
    잘 읽었습니다. ^^

  4. baind 2010.02.21 01:55 신고  댓글주소  수정/삭제  댓글쓰기

    와..한방SQL버전...무조건 좋은 것 만도 아니군요!
    정말로...잘봤습니다.
    감사합니다~~

  5. 혈기린 2010.02.22 10:36 신고  댓글주소  수정/삭제  댓글쓰기

    예전 엔코아에서모델링 교육들을때 강사가 했던말이 기억나는군요 자기는 자기가100점이라고 생각한 모델이 최고인줄 알았는데 그모델을 인수받는 고객이 그모델을 이해못하면 50점짜리가 된다 인수받는 고객을 자기수준으로 끌어올리지 못하면 그 고객이 이해할수 있는 100점짜리 모델을 만들어야 한다고 했는데 비슷한 맥락일까요 ㅎㅎ
    어김없이 좋은글로 한주를 시작하네요 감사드려요 ^^

  6. Favicon of http://oraclepro.pe.kr BlogIcon davidkim 2010.02.22 13:45 신고  댓글주소  수정/삭제  댓글쓰기

    한번쯤 생각해보게 만드는 글인것 같습니다.
    매번 좋은 글 잘 읽고 갑니다. 행복한 한주가 되시길~ ^^

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

      반갑습니다.
      사실 이 문제로 사람들이 고통받고 있지만 One Query가 대세처럼 되어있기 때문에 말도 못꺼내는 사람들이 지금도 있습니다.^^

  7. Favicon of http://www.soqool.com BlogIcon 쏘쿨 2010.02.22 15:43 신고  댓글주소  수정/삭제  댓글쓰기

    한가지 질문을 할까 합니다.
    위에 올리신 'SQL길이에 따른 분석시간' 그래프는 어떻게 얻어진 건가요?
    그래프를 만들기 위해 사용된 통계치나 혹은 수식을 가지고 계신가요?
    알려주신다면 원글을 이해하는데 더 많은 도움이 될듯 합니다.

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

      X 와 Y 축에 대한 방정식은 가지고 있지 않습니다.
      통계는 가지고 있습니다. 통계는 SQL 분석에 일주일 이상 걸리거나 분석에 실패한 것들을 모아서 공통점을 분석한 것입니다.
      하지만 공개하지는 않습니다.
      그 소스로 저희 회사에서 DBA나 개발자들의 약한 부분에 대해서 책도 쓰고 교육도 하므로 일종의 밥줄(대외비) 입니다.
      양해해 주시기 바랍니다.
      지금부터 쏘쿨님께서 분석시간에 대한 통계를 내어본다면 같은 결과를 얻을수 있을 것 입니다.
      만약에 이글에 대해 반대의견을 가지고 계시다면 무엇 때문인지 말씀해 주시기 바랍니다.

  8. Favicon of http://www.soqool.com BlogIcon 쏘쿨 2010.02.22 15:53 신고  댓글주소  수정/삭제  댓글쓰기

    답변 감사합니다.

  9. 로또 2010.05.15 19:22 신고  댓글주소  수정/삭제  댓글쓰기

    매우 좋은 내용이고, 글 전체의 취지에 적극 동감합니다.
    이런거 신경쓰는 사람이 의외로 드문데 중요한걸 지적해주셨습니다.

    헌데, 한방으로 해도 된다고하신 다섯번째는
    끝부분에 소개하신 일화와 약간 다르네요.

    지금으로 봐선 업무변경이 없을것 같아 보이지만
    정말로 미래에 변경이 있을지 없을지는 노스트라다무스가 오면 모를까,
    당장 눈앞에 얻는것보다 미래에 잃을것이 더 많을듯합니다.
    그래서 변경될 가능성이 없더라도 가독성은 항상 중요하다고 보여집니다.

    원 저작자도 분석을 포기했다는건 많은걸 시사합니다.
    전에 decode 한개만 한페이지가 넘어가는걸 봤는데 개발했던 담당자도 이해를 못한다고 합니다. ㅎㅎ
    (select 절의 많은 컬럼중 decode 한개만 한페이지씩)

  10. KIDO 2011.08.18 15:03 신고  댓글주소  수정/삭제  댓글쓰기

    네 공감이 가는 글입니다.

    역시 맹신은 위험한 생각 방식임을 다시한번 깨닫게 됩니다.

    적시 적소 라는 말이 생각이 나네요 `^^

    감사합니다.

필자는 예전에 Recursive SQL이라는 글을 통하여 IBM DB2 Microsoft SQL Serve에서 사용하는 Recursive With문을 소개한적이 있다. 시간이 흘러 이제는 Oracle11gR2에서 Recursive With문을 사용할 수 있게 되었다. 오라클에서도 DB2SQL Serve처럼 ANSI SQL을 사용할 수 있게 된 것이다.

 

DBMS에 능숙한 사람들에게는 환영할만한 일이나 오라클을 사용한 사람들은 별로 달가워하지 않는 것 같다. 왜냐하면 기존의 오라클 사용자들은 Connect By의 기능이 워낙 막강하였으므로 굳이 ANSI SQL을 사용할 필요가 없다고 생각하기 때문이다.

 

하지만 과연 그럴까? 모든 기능은 용도가 있다.

 

이 글을 보고 알 수 있는 사항은 다음과 같다.

 

1. Connect By Recursive With의 문법 비교

2. Connect By Recursive With의 기능 비교

3. Connect By Recursive With의 성능 비교

4. 1, 2, 3번을 통하여 Connect By Recursive With의 장단점 파악

 

1) 문법 관점에서 Connect By VS Recursive With

 

 SELECT employee_id, first_name, manager_id, LEVEL

   FROM employee

  START WITH manager_id IS NULL

CONNECT BY PRIOR employee_id = manager_id;

 

EMPLOYEE_ID FIRST_NAME           MANAGER_ID      LEVEL

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

        100 Steven                                   1

        101 Neena                       100          2

        108 Nancy                       101          3

        109 Daniel                      108          4

        110 John                        108          4

...중간생략

        202 Pat                         201          3

 

107 rows selected.

 

 

WITH recursive(employee_id, name, manager_id, recursive_level) AS

(   SELECT employee_id, first_name, manager_id, 1 recursive_level

      FROM employee

     WHERE manager_id IS NULL                  --> START WITH 절에 해당함

    UNION ALL

    SELECT e.employee_id, e.first_name, e.manager_id, recursive_level + 1

      FROM employee e, recursive

     WHERE e.manager_id = recursive.employee_id --> CONNECT BY 절에 해당함

)

SELECT *

  FROM recursive;

 

 

EMPLOYEE_ID NAME                 MANAGER_ID RECURSIVE_LEVEL

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

        100 Steven                                        1

        201 Michael                     100               2

        101 Neena                       100               2

        102 Lex                         100               2

        114 Den                         100               2

...중간생략

        107 Diana                       103               4

 

107 rows selected.

 

SQL문이 길어졌다. 하지만 가독성은?

위의 예제에서 볼 수 있듯이 Recursive With 문은 Connect By에 비하여 가독성이 좋아졌다. 왜냐하면 시작조건과 찾아가는 조건이 Union All로 분기되어 있으므로 SQL이 조금 길어지긴 하였으나 해석하는데 전혀 어려운 점이 없기 때문이다. 또한 SQL이 분리되어 있으므로 각각의 성능 최적화도 쉽게 할 수 있다. 주의 사항이 있다. Recursive With 문에서는 LEVEL을 사용할 수 없지만 위의 경우처럼 숫자 초기값을 지정하고 거기에 1을 계속 더해가면 같은 기능을 사용할 수 있다. 또 하나의 주의사항은 Sort의 순서가 다르다는 것인데 Order By 기능을 설명하는 부분에서 자세히 다루어진다.

 

무한루프 발생 테스트

먼저 무한루프에 만들기 위하여 TOP LEVEL manager를 조작하고 Connect ByRecursive With문을 각각 실행시켜보자.

 

UPDATE employee

   SET manager_id = 101

 WHERE employee_id = 100;

 

commit;

 


 SELECT employee_id, first_name, manager_id, LEVEL

   FROM employee

  START WITH manager_id = 100

CONNECT BY PRIOR employee_id = manager_id;

 

ORA-01436: CONNECT BY loop in user data

 

WITH recursive(employee_id, name, manager_id, recursive_level) AS

(   SELECT employee_id, first_name, manager_id, 1 recursive_level

      FROM employee

     WHERE manager_id = 100

    UNION ALL

    SELECT e.employee_id, e.first_name, e.manager_id, recursive_level + 1

      FROM employee e, recursive

     WHERE e.manager_id = recursive.employee_id

)

SELECT *

  FROM recursive;

 

ORA-32044: cycle detected while executing recursive WITH query

 

위에서 보는 것처럼 에러코드가 서로 다르다. 기존의 Connect By문은 이러한 무한루프를 성공적으로 제거하는 기능이 존재했다. 아래가 그 기능을 구현한 SQL이다.

 

 

SELECT employee_id,

        manager_id,

        CONNECT_BY_ISCYCLE AS iscycle

   FROM employee

  START WITH employee_id = 100

CONNECT BY NOCYCLE PRIOR employee_id = manager_id ;

 

107 rows selected.

 

Connect By절에 NOCYCLE을 명시하면 무한루프를 방지할 수 있다. 뿐만 아니라 CONNECT_BY_ISCYCLE 기능을 사용하면 무한루프를 발생시키는 Row를 발견할 수 있다. 또한 Oracle 9i 10g를 거치면서 기능이 막강해져서 아래와 같이 사용할 수 있게 되었다.

 

SELECT  employee_id,

        manager_id,

        LTRIM(SYS_CONNECT_BY_PATH (last_name, '-'),'-') as name_tree,

        CONNECT_BY_ROOT last_name as root_name,

        CONNECT_BY_ISLEAF as isleaf,

        CONNECT_BY_ISCYCLE as iscycle

   FROM employee

  START WITH employee_id = 100

CONNECT BY NOCYCLE PRIOR employee_id = manager_id

ORDER SIBLINGS BY employee_id ;

 

107 rows selected.

 

위의 기능 중에 하나라도 모르는 것이 있다면 Hierarchical Queries를 참조하라

 

2) 기능 관점에서 Connect By VS Recursive With

위에서 보았던 막강한 기능들을 Recursive With에서 모두 사용할 수 있는지 아닌지는 매우 중요하다. 왜냐하면 기능의 사용 가능 유무에 의해서 Recursive With의 존재가치가 판가름 날 것이기 때문이다. 위의 예제에서 사용한 모든 기능들을 Recursive With문에서 사용할 수 있으면 좋겠지만 아쉽게도 위에서 굵게 표시된 모든 예약어와 함수 등을 사용할 수 없다. 하지만 Recursive With문에서는 모든 기능을 예약어나 함수가 아닌 수동으로 재연할 수 있다. 그것도 아주 간편하게 말이다. 손 맛을 느끼게 해주는 SQL이다. 아래를 보자.

 

WITH recursive(employee_id, manager_id, name_tree) as

( SELECT employee_id, manager_id, last_name

    FROM employee

   WHERE employee_id = 100

  UNION ALL

  SELECT e.employee_id, e.manager_id,

         recursive.name_tree||'-'||e.last_name   --> SYS_CONNECT_BY_PATH  함수에 해당함

    FROM employee e, recursive

   WHERE e.manager_id = recursive.employee_id

)

SEARCH DEPTH FIRST BY employee_id SET IDX         --> ORDER SIBLINGS BY 기능에 해당함

CYCLE manager_id SET iscycle TO "1" DEFAULT "0"   --> CONNECT_BY_ISCYCLE 기능에 해당함

SELECT employee_id, manager_id, name_tree,

       regexp_substr(name_tree, '[^-]*' ) root_name, --> CONNECT_BY_ROOT 기능에 해당함

       decode( regexp_count(lead(name_tree) OVER(ORDER BY IDX), name_tree), 0, 1, null, 1, 0) isleaf,

       --> CONNECT_BY_ISLEAF 기능에 해당함

       iscycle

FROM recursive;

 

102 rows selected.

 

이것 봐라?

너무나 쉽게 기능을 구현하였다. 이 정도라면 기존의 Connect By 사용자도 어라 이것 봐라?” 라고 이야기 할 것이다. 기능을 살펴보자. 먼저 SEARCH DEPTH FIRST BY 기능은 ORDER SIBLINGS BY 기능에 해당한다. 물론 employee_id 컬럼에 ASC DESC를 사용할 수 있다. SET IDX라는 기능은 Sort된 순서대로 일련번호를 생성하는 기능이다. 이것은 ROWNUM과 기능이 비슷하지만 다른 점은 정렬된 순서대로 채번된다는 것이다. 주의사항은 SEARCH DEPTH FIRST 기능을 사용하지 않고 보통의 Order By 구문을 사용하게 되면 Tree 구조가 유지되지 않는다는 것이다. 이런 관점은 Connect ByORDER SIBLINGS BY 기능과 같다.

CYCLE manager_id SET iscycle TO "1" DEFAULT "0"
라고 선언하면 manager_id의 값이 잘못되어 무한루프가 발생될 때 식별할 수 있다. 이때 iscycle을 가상의 컬럼으로 이용하게 된다. 주의사항은 iscycle의 값은 숫자가 될 수 없으며 1 Byte 문자만 가능하다. “1” “0”을 사용한 이유도 여기에 있다. regexp_substr를 사용한 이유는 문자를 첫번째 ‘-‘ 까지 잘라내야 root_name을 구할 수 있기 때문이다. Regular Expression에 대하여 자세히 알고 싶은 독자는 오라클 Regular Expressions 완전정복을 참조하라. regexp_count는 버전 11g에 추가된 기능이다. 이 기능은 다음처럼 사용할 수 있다.

 

ex) regexp_count(text1, text2) :

text1 text2가 포함된 횟수를 Return한다. 예를 들면 regexp_count(‘yahoo.co.kr’, ‘o’) 라고 했다면 o 3번 포함되어 있으므로 3 Return 된다.  

 

 

건수가 차이 난다

하지만 문제가 있다. 결과건수가 차이가 난다. Connect By는 결과가 107건이며 Recursive With문은 102건을 Return 하였다. 5건의 차이는 무엇인가? 아래는 차이의 분석을 위하여 두 SQL의 결과값 중에서 필요한 부분만 표로 정리한 것이다.

 

Connect By 사용

Recursive With 사용

EMP_ID

NAME_TREE

IS
CYCLE

EMP_ID

NAME_TREE

IS
CYCLE

101

King-Kochhar

1

101

King-Kochhar

0

출력되지 않음

100

King-Kochhar-King

1

204

King-Kochhar-Baer

0

204

King-Kochhar-Baer

1

108

King-Kochhar-Greenberg

0

108

King-Kochhar-Greenberg

1

110

King-Kochhar-Greenberg-Chen

0

출력되지 않음

109

King-Kochhar-Greenberg-Faviet

0

113

King-Kochhar-Greenberg-Popp

0

111

King-Kochhar-Greenberg-Sciarra

0

112

King-Kochhar-Greenberg-Urman

0

205

King-Kochhar-Higgins

0

205

King-Kochhar-Higgins

1

206

King-Kochhar-Higgins-Gietz

0

출력되지 않음

203

King-Kochhar-Mavris

0

203

King-Kochhar-Mavris

1

200

King-Kochhar-Whalen

0

200

King-Kochhar-Whalen

1

 

어떤 차이가 있나?

결론을 이야기하면 Connect By NoCycle, IsCycle 기능과 Recursive WithCycle IsCycle 기능과는 차이가 있다.즉 각각의 기능이 다르므로 용도를 구분할 줄 알아야 한다는 뜻이다.

 

Connect By NoCycle 기능은 KingKochhar의 관계가 반복되는 것을 Skip하고 표시된다. 즉 직원번호 204 name_tree 항목을 보면 King-Kochhar-Baer 라고 되어 있지만 사실은 King-Kochhar- King-Kochhar - King-Kochhar ....무한반복... Baer 처럼 표시할 수 있다. 하지만 Connect By + NoCycle을 사용하면 무한반복을 Skip하고 한번만 나타낸다. 또한 IsCycle 도 반복이 시작되기 직전의 데이터를 식별해주는 기능을 한다.

 

Connect By + NoCycle을 사용하면 직원번호 101의 데이터는 Cycle 로 표시되는데 사실은 아직 Cycle이 아니다. 왜냐하면 오른쪽의 Recursive With를 사용한 경우를 보면 직원번호 100번의 데이터는 King-Kochhar-King인데 King이 두 번 반복 된 것을 볼 수 있고 이 경우가 첫 번째 반복되는 Cycle 이기 때문이다. Recursive With IsCycle 항목에는 이런 관점이 잘 반영되어 나타난다. 이 데이터는 Connect By + NoCycle을 사용하면 볼 수 없다.

 

마지막으로 Connect By를 사용하면 데이터가 출력되지만 Recursive With를 사용하면 출력되지 않는 Row가 있다. 그 이유는 Connect By는 관계의 반복만 제거하고 마지막까지 데이터를 찾아가지만 Recursive With는 반복되는 첫 번째 데이터만 찾고 거기서 멈춘다. Connect By를 사용하면 King-Kochhar-Greenberg-Chen로 데이터의 끝까지 찾아가지만 Recursive With를 사용하면 King-Kochhar-Greenberg 여기서 멈춘다. 위의 표를 유심히 비교해 보기 바란다. 지금까지 문법과 여러 가지 기능의 차이를 알아보았다.

 

3) 성능관점에서 Connect By VS Recursive With

이제 가장 중요한 성능을 비교해보자.

 

SELECT /*+ GATHER_PLAN_STATISTICS */

        employee_id,

        manager_id,

        LTRIM(SYS_CONNECT_BY_PATH (last_name, '-'),'-') name_tree,

        CONNECT_BY_ROOT last_name root_name,

        CONNECT_BY_ISLEAF isleaf,

        TO_CHAR(CONNECT_BY_ISCYCLE) iscycle

   FROM employee

  START WITH employee_id = 100

CONNECT BY NOCYCLE PRIOR employee_id = manager_id

ORDER SIBLINGS BY employee_id;

 

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

| Id  | Operation                     | Name           | A-Rows | Buffers | Used-Mem |

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

|   0 | SELECT STATEMENT              |                |    107 |      16 |          |

|*  1 |  CONNECT BY WITH FILTERING    |                |    107 |      16 | 2048  (0)|

|   2 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEE       |      1 |       2 |          |

|*  3 |    INDEX UNIQUE SCAN          | EMP_EMP_ID_PK  |      1 |       1 |          |

|   4 |   NESTED LOOPS                |                |    106 |      14 |          |

|   5 |    CONNECT BY PUMP            |                |    107 |       0 |          |

|   6 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEE       |    106 |      14 |          |

|*  7 |     INDEX RANGE SCAN          | EMP_MANAGER_IX |    106 |       5 |          |

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

Predicate Information (identified by operation id):

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

   1 - access("MANAGER_ID"=PRIOR NULL)

   3 - access("EMPLOYEE_ID"=100)

   7 - access("connect$_by$_pump$_002"."PRIOR employee_id "="MANAGER_ID")

 

 

WITH recursive(employee_id, manager_id, name_tree) as

( SELECT employee_id, manager_id, last_name

    FROM employee

   WHERE employee_id = 100

  UNION ALL

  SELECT e.employee_id, e.manager_id,

         recursive.name_tree||'-'||e.last_name

    FROM employee e, recursive

   WHERE e.manager_id = recursive.employee_id

)

SEARCH DEPTH FIRST BY employee_id SET IDX

CYCLE manager_id SET iscycle TO "1" DEFAULT "0"

SELECT /*+ GATHER_PLAN_STATISTICS */

       employee_id, manager_id, name_tree,

       regexp_substr(name_tree, '[^-]*' ) root_name,

 decode(regexp_count(lead(name_tree) OVER(ORDER BY IDX), name_tree), 0, 1, null, 1, 0) isleaf,

       iscycle

  FROM recursive;

 

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

| Id  | Operation                                | Name           | A-Rows | Buffers | Used-Mem |

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

|   0 | SELECT STATEMENT                         |                |    107 |      16 |          |

|   1 |  WINDOW BUFFER                           |                |    107 |      16 | 8192  (0)|

|   2 |   VIEW                                   |                |    107 |      16 |          |

|   3 |    UNION ALL (RECURSIVE WITH) DEPTH FIRST|                |    107 |      16 |          |

|   4 |     TABLE ACCESS BY INDEX ROWID          | EMPLOYEE       |      1 |       2 |          |

|*  5 |      INDEX UNIQUE SCAN                   | EMP_EMP_ID_PK  |      1 |       1 |          |

|   6 |     NESTED LOOPS                         |                |    106 |      14 |          |

|   7 |      NESTED LOOPS                        |                |    106 |       5 |          |

|   8 |       RECURSIVE WITH PUMP                |                |    107 |       0 |          |

|*  9 |       INDEX RANGE SCAN                   | EMP_MANAGER_IX |    106 |       5 |          |

|  10 |      TABLE ACCESS BY INDEX ROWID         | EMPLOYEE       |    106 |       9 |          |

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

Predicate Information (identified by operation id):

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

   5 - access("EMPLOYEE_ID"=100)

   9 - access("E"."MANAGER_ID"="RECURSIVE"."EMPLOYEE_ID")

 

IsLeaf 기능을 사용하려면 Connect By가 유리하다

Scan한 블럭수는 16으로 동일하다. 하지만 PGA 사용량이 4배나 차이 난다. 하지만 이것은 Connect ByRecursive With의 성능 차이가 아니라 분석함수 lead의 사용 유무에 의한 차이이다. IsLeaf 기능이 필요하다면 기존의 Connect By를 사용하는 것이 유리할 것이다. 하지만 Connect ByRecursive With 자체의 성능만 비교한다면 결과가 달라진다.

 

Connect ByRecursive With에서 IsLeaf 항목을 빼고 다시 실행 해보자.

 

SELECT /*+ GATHER_PLAN_STATISTICS */

        employee_id,

        manager_id,

        LTRIM(SYS_CONNECT_BY_PATH (last_name, '-'),'-') name_tree,

        CONNECT_BY_ROOT last_name root_name,

TO_CHAR(CONNECT_BY_ISCYCLE) iscycle

   FROM employee

  START WITH employee_id = 100

CONNECT BY NOCYCLE PRIOR employee_id = manager_id

ORDER SIBLINGS BY employee_id;

 

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

| Id  | Operation                     | Name           | A-Rows | Buffers | Used-Mem |

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

|   0 | SELECT STATEMENT              |                |    107 |      16 |          |

|*  1 |  CONNECT BY WITH FILTERING    |                |    107 |      16 | 2048  (0)|

|   2 |   TABLE ACCESS BY INDEX ROWID | EMPLOYEE       |      1 |       2 |          |

|*  3 |    INDEX UNIQUE SCAN          | EMP_EMP_ID_PK  |      1 |       1 |          |

|   4 |   NESTED LOOPS                |                |    106 |      14 |          |

|   5 |    CONNECT BY PUMP            |                |    107 |       0 |          |

|   6 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEE       |    106 |      14 |          |

|*  7 |     INDEX RANGE SCAN          | EMP_MANAGER_IX |    106 |       5 |          |

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

Predicate Information (identified by operation id):

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

   1 - access("MANAGER_ID"=PRIOR NULL)

   3 - access("EMPLOYEE_ID"=100)

   7 - access("connect$_by$_pump$_002"."PRIOR employee_id "="MANAGER_ID")

 

WITH recursive(employee_id, manager_id, name_tree) as

( SELECT employee_id, manager_id, last_name

    FROM employee

   WHERE employee_id = 100

  UNION ALL

  SELECT e.employee_id, e.manager_id,

         recursive.name_tree||'-'||e.last_name

    FROM employee e, recursive

   WHERE e.manager_id = recursive.employee_id

)

SEARCH DEPTH FIRST BY employee_id SET IDX

CYCLE manager_id SET iscycle TO "1" DEFAULT "0"

SELECT /*+ GATHER_PLAN_STATISTICS */

       employee_id, manager_id, name_tree,

       regexp_substr(name_tree, '[^-]*' ) root_name,

       iscycle

  FROM recursive;

 

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

| Id  | Operation                               | Name           | A-Rows | Buffers |

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

|   0 | SELECT STATEMENT                        |                |    107 |      16 |

|   1 |  VIEW                                   |                |    107 |      16 |

|   2 |   UNION ALL (RECURSIVE WITH) DEPTH FIRST|                |    107 |      16 |

|   3 |    TABLE ACCESS BY INDEX ROWID          | EMPLOYEE       |      1 |       2 |

|*  4 |     INDEX UNIQUE SCAN                   | EMP_EMP_ID_PK  |      1 |       1 |

|   5 |    NESTED LOOPS                         |                |    106 |      14 |

|   6 |     NESTED LOOPS                        |                |    106 |       5 |

|   7 |      RECURSIVE WITH PUMP                |                |    107 |       0 |

|*  8 |      INDEX RANGE SCAN                   | EMP_MANAGER_IX |    106 |       5 |

|   9 |     TABLE ACCESS BY INDEX ROWID         | EMPLOYEE       |    106 |       9 |

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

Predicate Information (identified by operation id):

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

   4 - access("EMPLOYEE_ID"=100)

   8 - access("E"."MANAGER_ID"="RECURSIVE"."EMPLOYEE_ID")

 

일반적인 경우 Recursive With가 유리함

보았는가? 상황이 역전되어 Recursive With를 사용하면 PGA를 전혀 사용하지 않는다. 따라서 IsLeaf를 사용하지 않는 일반적인 경우 Recursive With가 유리함을 알 수 있다.

 

대용량 배치일 경우 Recursive With를 주의하라

하지만 이것이 끝이 아니다. 대용량 배치일 경우는 상황이 다시 반전된다.

 

WITH recursive(employee_id, name, manager_id, recursive_level) AS

(   SELECT employee_id, first_name, manager_id, 1 recursive_level

      FROM employee

     WHERE  manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.first_name, e.manager_id, recursive_level + 1

      FROM employee e, recursive

     WHERE e.manager_id = recursive.employee_id

)

SELECT /*+ GATHER_PLAN_STATISTICS */  *

  FROM recursive;

 

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

| Id  | Operation                                 | Name           | A-Rows | Buffers |

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

|   0 | SELECT STATEMENT                          |                |    107 |      22 |

|   1 |  VIEW                                     |                |    107 |      22 |

|   2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|                |    107 |      22 |

|*  3 |    TABLE ACCESS FULL                      | EMPLOYEE       |      1 |       8 |

|   4 |    NESTED LOOPS                           |                |    106 |      14 |

|   5 |     NESTED LOOPS                          |                |    106 |       5 |

|   6 |      RECURSIVE WITH PUMP                  |                |    107 |       0 |

|*  7 |      INDEX RANGE SCAN                     | EMP_MANAGER_IX |    106 |       5 |

|   8 |     TABLE ACCESS BY INDEX ROWID           | EMPLOYEE       |    106 |       9 |

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

Predicate Information (identified by operation id):

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

   3 - filter("MANAGER_ID" IS NULL)

   7 - access("E"."MANAGER_ID"="RECURSIVE"."EMPLOYEE_ID")

 

대용량 배치일 경우 Recursive With는 비효율이 발견되었다. manager_id IS NULL 조건 때문에 EMPLOYEE 테이블을 Full Scan 하였지만 Union ALL 아래의 SQL에서 또다시 EMPLOYEE 테이블을 Scan하고 있다. 동일한 블록을 두 번 Scan한 셈이다. 하지만 아래를 보라.

 

SELECT /*+ GATHER_PLAN_STATISTICS */ employee_id, first_name, manager_id, LEVEL

   FROM employee

  START WITH manager_id IS NULL

CONNECT BY PRIOR employee_id = manager_id;

 

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

| Id  | Operation                               | Name     | A-Rows | Buffers |

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

|   0 | SELECT STATEMENT                        |          |    107 |       7 |

|*  1 |  CONNECT BY NO FILTERING WITH START-WITH|          |    107 |       7 |

|   2 |   TABLE ACCESS FULL                     | EMPLOYEE |    107 |       7 |

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

Predicate Information (identified by operation id):

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

   1 - access("MANAGER_ID"=PRIOR NULL)

       filter("MANAGER_ID" IS NULL)

 

Connect By를 사용하면 Union을 사용하지 않으므로 Full Table Scan 한 번으로 끝낼 수 있다. Scan한 블럭수는 세 배 이상 차이가 난다.

 

Connect By VS Recursive With 의 결론

1) 문법적인 측면에서 거의 차이가 없다. Recursive With가 조금 길어지기는 하지만 Union All로 분리되어 있기 때문에 오히려 가독성과 성능 최적화가 쉽게 될 수 있다.

 

2) 기능면에서도 거의 차이가 없다. Connect By의 모든 기능이 구현 가능하다. 다만 Nocycle 기능과 IsCycle 기능의 용도가 서로 다르므로 구분해서 사용하면 된다.

 

3) 성능면에서는 Sort 기능을 사용해도 PGA를 전혀 사용하지 않는 Recursive With가 일반적으로 유리하다. 하지만 IsLeaf 기능을 구현하려면 분석함수를 사용해야 하기 때문에 Connect By가 유리할 수 있다. 또한 Scan할 범위가 넓은 경우 Start With 조건을 Full Table Scan으로 유도하면 같은 블럭을 반복해서 Scan하지 않는 Connect By가 유리하다.

 

결국 각 기능들의 허와 실을 제대로 파악하고 성능 이슈를 최소화 한다면 모두가 웃을 수 있을 것이다.
모두가 웃는 그날까지......  


신고
Posted by extremedb

댓글을 달아 주세요

  1. feelie 2010.02.11 16:16 신고  댓글주소  수정/삭제  댓글쓰기

    잘봤습니다.
    명절 잘보내시고요....
    책이 출판될 날이 기대됩니다...

  2. 혈기린 2010.02.12 10:09 신고  댓글주소  수정/삭제  댓글쓰기

    오호 순환구조를 지원하는 ansi sql이 있었군요 설연휴가 끝나면 책이 나오겠네요 기대만방입니다 ^^
    설연휴 잘보내세요 ~~

  3. baind 2010.02.18 11:14 신고  댓글주소  수정/삭제  댓글쓰기

    정말로 멋진글 잘 보았습니다. 역전의 역전이라...
    오동규님의 책 발간이 얼마 안남으셨다는 글에 실로 두근 거리는 마음에 기다립니다.
    새해 복 많이 받으시고 건강하세요~~
    두근두근~~`

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

      성원에 감사드립니다.
      안타까운 점은 이 블로그의 내용처럼 난위도가 있는 책이라 보는 사람이 별로 없을 것으로 예상됩니다.
      전통적으로 옵티마이져 책은 독자층이 매우 적습니다.
      새해 복 많이 받으세요.

정답의 결과가 틀리다. WHERE 절에 s.sales_cust.prod_cnt > 0 조건이 추가되어야 한다.
자세한 내용은 oraking 님의 댓글을 참조하기 바란다.(2010.09.03)


좋지 않은 상황

한 고객 사에서 전화가 걸려왔는데 성능 문제였다.

특정 SQL이 성능이 좋지 않은데 더욱 상황을 악화시키는 것은 Peak Time에 수십 만 번 실행된다는 것이다. 그런데 전체 건을 조회하는 것이 아니라 고객번호 순으로 10명의 고객에 대한 통계와 실적을 보는 SQL이라고 한다. SQL은 아래와 같으며 환경은 Oracle10g R2 이다. 오라클 Install시 생성되는 SH 스키마를 이용하면 된다.

 

--인덱스 생성

CREATE INDEX ix_cust_birth ON customers (cust_year_of_birth, cust_id);

 

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(c IX_CUST_BIRTH_CUST) LEADING(c) */

       c.cust_id, c.cust_first_name, c.cust_last_name,

       s.prod_cnt, s.channel_cnt, s.tot_amt

  FROM customers c,

       (SELECT s.cust_id,

               COUNT (DISTINCT s.prod_id) AS prod_cnt,

               COUNT (DISTINCT s.channel_id) AS channel_cnt,

               SUM (s.amount_sold) AS tot_amt

          FROM sales s

         GROUP BY s.cust_id) s

 WHERE c.cust_year_of_birth = 1987

   AND s.cust_id = c.cust_id

   AND ROWNUM <= 10;

 

-------------------------------------------------------------------------------------
| Id  | Operation                     | Name          | A-Rows | Buffers | Used-Mem |
-------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                |               |     10 |    5035 |          |
|*  2 |   HASH JOIN                   |               |     10 |    5035 | 1150K (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID| CUSTOMERS     |    151 |     153 |          |
|*  4 |     INDEX RANGE SCAN          | IX_CUST_BIRTH |    151 |       2 |          |
|   5 |    VIEW                       |               |    523 |    4882 |          |
|   6 |     SORT GROUP BY             |               |    523 |    4882 | 8288K (0)|
|   7 |      PARTITION RANGE ALL      |               |    918K|    4882 |          |
|   8 |       TABLE ACCESS FULL       | SALES         |    918K|    4882 |          |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   2 - access("S"."CUST_ID"="C"."CUST_ID")
   4 - access("C"."CUST_YEAR_OF_BIRTH"=1987)


CUSTOMERS 테이블은 10건을 정확히 Scan 하였지만 Salse 테이블을 Full Scan하여 비효율이 발생 하였다. 또한 위의 SQL은 Hash Join을 사용 하였으므로 정렬을 보장 하지 않는다.

좋지 않은 상황 + 악조건

먼저 위의 SQL을 보면 Rownum을 사용하므로 CVM(Complex View Merging)이 발생되지 않는다. 만약 발생된다고 해도 Group By가 전체범위로 처리되므로 TOP SQL에서는 대부분 JPPD(Join Predicate Push Down)의 성능이 우월하다. 또한 Nested Loop Join이 아닌 Hash Join이 수행 되었으므로 고객번호가 작은 것부터 나오지 않는다. Sort 문제도 해결해야 하지만 Oracle10g이므로 JPPD Extension 기능이 수행되지 않는다. JPPD Extension이란 Group By + 집계함수나 Distinct가 존재해도 JPPD가 수행되며 Semi/Anti Join시에도 JPPD가 수행되는 획기적인 기능이다. 이 기능은 Oracle11g부터 사용할 수 있다.

 

정답부터 보지말자 실력이 늘지 않는다
지금부터 여러분이 이 문제를 해결해야 한다. 여러분이 해결사 이다. 퀴즈라고 생각하고 문제를 풀어보라. 하지만 절대 답을 먼저 보아서는 안 된다. 충분히 고민한 후에 답을 풀어보고 정답을 보도록 하자.

힌트가 있다

JPPD
기능을 사용할 수 없다면 JPPD의 흉내를 내면 된다. JPPD의 효과를 만들면 된다.
아래쪽의 실행계획과 결론 부분을 보는것도 힌트가 될 수 있다.

제약사항
Sort 가 되어야 하며 Rownum 조건으로 Customers 테이블에 10건만 Scan되어야 한다. 그 10건에 해당하는 고객만 Sales 테이블에 Access 하는 것이 정답이다.
스칼라 서브쿼리를 세 번 사용하는 것은 정답이 아니다. 그것은 막노동에 가깝다. 필자의 블로그를 꾸준히 구독한 독자라면 어렵지 않게 문제를 풀 수 있다.
 

정답: 아래를 드래그 하면 된다.

 

CREATE OR REPLACE TYPE SALES_CUST_TYPE AS OBJECT

(prod_cnt NUMBER(5),

 channel_cnt NUMBER(2),     

 tot_amt NUMBER(15,2));

/

 

SELECT /*+ GATHER_PLAN_STATISTICS */

       s.cust_id, s.cust_first_name, s.cust_last_name,

       s.sales_cust.prod_cnt,  -- Alias 가 여기에 사용 되었다.

       s.sales_cust.channel_cnt,

       s.sales_cust.tot_amt

  FROM (SELECT /*+ INDEX(c IX_CUST_BIRTH_CUST) */

               c.cust_id, c.cust_first_name, c.cust_last_name,

               (SELECT sales_cust_type -- 타입의 이름을 그대로 사용해야 한다

                          (COUNT (DISTINCT s.prod_id),

                           COUNT (DISTINCT s.channel_id),

                           SUM (s.amount_sold)

                          )

                  FROM sales s

                 WHERE s.cust_id = c.cust_id

) AS sales_cust -- 추후 메인쿼리에서 Alias 가 사용된다.

          FROM customers c

         WHERE c.cust_year_of_birth= 1987

           AND ROWNUM <= 10) s  ;

    

위의 SQL은 JPPD를 수행시킨 효과와 비슷하다. 다른말로 바꾸면 수동으로 Lateral View를 생성한 것이다.
          

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

 

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

| Id  | Operation                     | Name               | A-Rows | Buffers |

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

|   1 |  VIEW                         |                    |     10 |    1104 |

|*  2 |   COUNT STOPKEY               |                    |     10 |      13 |

|   3 |    TABLE ACCESS BY INDEX ROWID| CUSTOMERS          |     10 |      13 |

|*  4 |     INDEX RANGE SCAN          | IX_CUST_BIRTH_CUST |     10 |       3 |

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

 

Predicate Information (identified by operation id):

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

   2 - filter(ROWNUM<=10)

   4 - access("C"."CUST_YEAR_OF_BIRTH"=1987)


읽은 블럭수가 무려 5배 정도 차이가 나며 PGA는 사용 하지도 았았다. 자주 수행될수록 성능의 차이는 급격히 벌어질 것이다.
 

결론:

JPPD 기능을 사용할 수 없으므로 JPPD의 흉내를 낸 것이다. JPPD등이 수행되지 않을 때 Logical Optimizer의 한계를 극복할 수 있는 방법은 여러분의 응용력에 달려있다. 옵티마이져가 한계를 드러낼 때 더 좋은 방법을 적용하는 것이 사람의 할 일이 되었다. 물론 그러기 위해서는 Logical Optimizer의 허와 실을 알아야 할 것이다.

 

PS:

만약 Oracle9i Oracle 8.1.7 버전을 사용하고 있다면 위의 정답이 실행되지 않으므로 http://scidb.tistory.com/69 글을 참조하여 사용하기 바란다.


신고
Posted by extremedb

댓글을 달아 주세요

  1. 혈기린 2010.02.04 09:55 신고  댓글주소  수정/삭제  댓글쓰기

    문제를 보는순간 JPPD를 생각했는데 11g부터 지원한다는걸 잊고 있었네요
    스칼라 서버쿼리를 사용하셨군요 참 배울게 많은 블로그 입니다
    얼릉 책 출판해주세요 ㅎㅎ 책이 고픕니다~~

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

      안녕하세요.
      말씀대로 Type을 이용한 스칼라 서브쿼리 입니다.
      책은 내용이 Freezing 되었고 표지 디자인이 진행 되고 있습니다. 설 대목이 기다리고 있으므로 3월 초나 되어야 시중에 풀리게 될 것입니다.

  2. feelie 2010.02.04 12:27 신고  댓글주소  수정/삭제  댓글쓰기

    스칼라서버쿼리를 사용해야겠구나 생각했습니다.
    정답을 보지않고 해보려고 했는데.....
    type을 이런경우에 사용할수 있군요.
    지난내용중 cast(mutiple 인가 하는 내용을 다시 한번 봐야겠네요.
    내용잘봤습니다..

  3. TeLl2 2010.02.05 17:27 신고  댓글주소  수정/삭제  댓글쓰기

    프로젝트 성격상 type을 선언할 수 없는 경우나,
    권한이 없는 개발자의 경우는 쿼리를 수정해서 할 수도 있을거 같습니다.

    SELECT /*+ USE_NL(C S) INDEX(C IX_CUST_BIRTH) */C.CUST_ID,
    C.CUST_FIRST_NAME,
    C.CUST_LAST_NAME,
    COUNT (DISTINCT S.PROD_ID) PROD_CNT,
    COUNT (DISTINCT S.CHANNEL_ID) CHANNEL_CNT,
    SUM (S.AMOUNT_SOLD) TOT_AMT
    FROM (SELECT CUST_ID,
    CUST_FIRST_NAME,
    CUST_LAST_NAME
    FROM CUSTOMERS
    WHERE CUST_YEAR_OF_BIRTH = 1987
    AND ROWNUM <= 10) C,
    SALES S
    WHERE S.CUST_ID(+) = C.CUST_ID
    GROUP BY C.CUST_ID, C.CUST_FIRST_NAME, C.CUST_LAST_NAME;

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

      JPPD의 흉내를 내는 것이 아니지만 정답입니다.
      말씀 하신 type을 선언할 수 없는 경우나,
      권한이 없는 개발자의 경우에 최적의 해결책 입니다. 제 책에도 이 내용이 있습니다. 좋은 의견 감사합니다.

  4. 백면서생 2010.02.08 08:30 신고  댓글주소  수정/삭제  댓글쓰기

    항상 좋은 글이 올라오네요.^^
    패키지에서 말고 타입 사용을 참으로 오랜만에 보는거 같습니다.

    가장 일반적인 sql이 안올라온거 같아 올려봅니다.^^

    SELECT /*+ GATHER_PLAN_STATISTICS */
    s.cust_id, s.cust_first_name, s.cust_last_name,
    to_number(substr(sales_cust,1,20)) prod_cnt,
    to_number(substr(sales_cust,21,20)) channel_cnt,
    to_number(substr(sales_cust,41,20)) tot_amt
    FROM (SELECT /*+ INDEX(c IX_CUST_BIRTH_CUST) NO_MERGE */
    c.cust_id, c.cust_first_name, c.cust_last_name,
    (
    SELECT lpad(COUNT (DISTINCT s.prod_id),20,'0')||
    lpad(COUNT (DISTINCT s.channel_id),20,'0')||
    lpad(SUM (s.amount_sold),20,'0')
    FROM sales s
    WHERE s.cust_id = c.cust_id
    ) AS sales_cust
    FROM customers c
    WHERE c.cust_year_of_birth= 1987
    AND ROWNUM <= 10) s ;

    돌려보지않고 extremedb님 sql을 수정해서 올려서 실제 쿼리가 될지는 모르겠네요.

    값이 20자리를 넘는다면 아시다시피 자리수를 늘여 주던지 제가 별루 좋아하지 않는 ^^
    COUNT (DISTINCT s.prod_id),20,'0')||','||COUNT (DISTINCT s.channel_id)||','||SUM (s.amount_sold)

    후에 substr,instr조합으로 해야 겠죠.^^;

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

      오랜만 입니다.
      백면서생님 말씀대로 가장 일반적인 방법이지요.
      Tomas kyte가 Effective Oracle By Design에서 소개하기도 했었지요.
      감사 합니다.

  5. 2010.02.10 17:16  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  6. oraking 2010.09.06 15:46 신고  댓글주소  수정/삭제  댓글쓰기

    위 내용을 보면서 궁금한게 생겼습니다.


    맨 위 쿼리에서

    WHERE c.cust_year_of_birth = 1987 AND s.cust_id = c.cust_id AND ROWNUM <= 10;

    의 의미는 조인이 성공한 즉 sale 테이블에 customer가 있는 경우의 10건 인데....

    그 아래 정답이나 다른 글을 보면 sale 테이블에 없는 것이 포함된 10건입니다. ....

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

      그렇군요.
      스칼라 인라인뷰나 스칼라서브쿼리는 아우터조인의 의미를 가지게 되므로 아래처럼 WHERE 절에 s.sales_cust.prod_cnt > 0 조건을 추가해야 합니다.

      SELECT s.cust_id, s.cust_first_name, s.cust_last_name,
      s.sales_cust.prod_cnt, -- Alias 가 여기에 사용 되었다.
      s.sales_cust.channel_cnt,
      s.sales_cust.tot_amt
      FROM (SELECT /*+ INDEX(c ix_cust_birth) */
      c.cust_id, c.cust_first_name, c.cust_last_name,
      (SELECT sales_cust_type -- 타입의 이름을 그대로 사용해야 한다
      (COUNT (DISTINCT s.prod_id),
      COUNT (DISTINCT s.channel_id),
      SUM (s.amount_sold)
      )
      FROM sales s
      WHERE s.cust_id = c.cust_id) AS sales_cust -- 추후 메인쿼리에서 Alias 가 사용된다.
      FROM customers c
      WHERE c.cust_year_of_birth= 1987) s
      WHERE s.sales_cust.prod_cnt > 0 --> 조건추가
      AND ROWNUM <= 10 ;

      좋은 의견에 감사드립니다.

  7. oraking 2010.09.06 18:30 신고  댓글주소  수정/삭제  댓글쓰기

    안녕하세요 LTO책 카테고리에 써야 할지 몰라 이곳에 문의를 드려 봅니다.

    아래 환경은 11gR2 입니다. 힌트 때문에 동일 쿼리의 수행 시간이 다른데 원인을 모르겠습니다.

    ############################
    # 소요 시간 : 00:00:00.29
    ############################
    SELECT /*+ GATHER_PLAN_STATISTICS INDEX(c IX_CUST_BIRTH_CUST) LEADING(c) */
    c.cust_id, c.cust_first_name, c.cust_last_name,
    s.prod_cnt, s.channel_cnt, s.tot_amt
    FROM customers c,
    (SELECT s.cust_id,
    COUNT (DISTINCT s.prod_id) AS prod_cnt,
    COUNT (DISTINCT s.channel_id) AS channel_cnt,
    SUM (s.amount_sold) AS tot_amt
    FROM sales s
    GROUP BY s.cust_id) s
    WHERE c.cust_year_of_birth = 1987
    AND s.cust_id = c.cust_id
    AND ROWNUM <= 10;

    select * from table(dbms_xplan.display_cursor(null,null,'all ALLSTATS LAST -rows'));

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | | 1543 (100)| | | | 10 |00:00:00.20 | 5212 | | | |
    |* 1 | COUNT STOPKEY | | 1 | | | | | | 10 |00:00:00.20 | 5212 | | | |
    |* 2 | HASH JOIN | | 1 | 870 | 1543 (3)| 00:00:19 | | | 10 |00:00:00.20 | 5212 | 836K| 836K| 1232K (0)|
    | 3 | JOIN FILTER CREATE | :BF0000 | 1 | 6493 | 35 (0)| 00:00:01 | | | 151 |00:00:00.01 | 146 | | | |
    | 4 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 6493 | 35 (0)| 00:00:01 | | | 151 |00:00:00.01 | 146 | | | |
    | 5 | BITMAP CONVERSION TO ROWIDS| | 1 | | | | | | 151 |00:00:00.01 | 2 | | | |
    |* 6 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_YOB_BIX | 1 | | | | | | 1 |00:00:00.01 | 2 | | | |
    | 7 | VIEW | | 1 | 303K| 1507 (3)| 00:00:19 | | | 12 |00:00:00.20 | 5066 | | | |
    | 8 | SORT GROUP BY | | 1 | 172K| 1507 (3)| 00:00:19 | | | 12 |00:00:00.20 | 5066 | 99328 | 99328 |88064 (0)|
    | 9 | JOIN FILTER USE | :BF0000 | 1 | 21M| 1478 (1)| 00:00:18 | | | 7979 |00:00:00.24 | 5066 | | | |
    | 10 | PARTITION RANGE ALL | | 1 | 21M| 1478 (1)| 00:00:18 | 1 | 28 | 7979 |00:00:00.21 | 5066 | | | |
    |* 11 | TABLE ACCESS FULL | SALES | 28 | 21M| 1478 (1)| 00:00:18 | 1 | 28 | 7979 |00:00:00.20 | 5066 | | | |
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    1 - filter(ROWNUM<=10)
    2 - access("S"."CUST_ID"="C"."CUST_ID";)
    6 - access("C"."CUST_YEAR_OF_BIRTH"=1987)
    11 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."CUST_ID";))


    Column Projection Information (identified by operation id):
    -----------------------------------------------------------

    1 - "C"."CUST_ID"[NUMBER,22], "C"."CUST_FIRST_NAME"[VARCHAR2,20], "C"."CUST_LAST_NAME"[VARCHAR2,40], "S"."TOT_AMT"[NUMBER,2
    "S"."CHANNEL_CNT"[NUMBER,22]
    2 - (#keys=1) "C"."CUST_ID"[NUMBER,22], "C"."CUST_FIRST_NAME"[VARCHAR2,20], "C"."CUST_LAST_NAME"[VARCHAR2,40], "S"."TOT_AMT
    "S"."CHANNEL_CNT"[NUMBER,22]
    3 - "C"."CUST_ID"[NUMBER,22], "C"."CUST_FIRST_NAME"[VARCHAR2,20], "C"."CUST_LAST_NAME"[VARCHAR2,40]
    4 - "C"."CUST_ID"[NUMBER,22], "C"."CUST_FIRST_NAME"[VARCHAR2,20], "C"."CUST_LAST_NAME"[VARCHAR2,40]
    5 - "C".ROWID[ROWID,10], "C"."CUST_YEAR_OF_BIRTH"[NUMBER,22]
    6 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7924], "C"."CUST_YEAR_OF_BIRTH"[NUMBER,22]
    7 - "S"."CUST_ID"[NUMBER,22], "S"."PROD_CNT"[NUMBER,22], "S"."CHANNEL_CNT"[NUMBER,22], "S"."TOT_AMT"[NUMBER,22]
    8 - (#keys=1) "S"."CUST_ID"[NUMBER,22], COUNT(DISTINCT "S"."CHANNEL_ID";)[22], COUNT(DISTINCT "S"."PROD_ID";)[22], SUM("S"."A
    9 - "S"."PROD_ID"[NUMBER,22], "S"."CUST_ID"[NUMBER,22], "S"."CHANNEL_ID"[NUMBER,22], "S"."AMOUNT_SOLD"[NUMBER,22]
    10 - "S"."PROD_ID"[NUMBER,22], "S"."CUST_ID"[NUMBER,22], "S"."CHANNEL_ID"[NUMBER,22], "S"."AMOUNT_SOLD"[NUMBER,22]
    11 - "S"."PROD_ID"[NUMBER,22], "S"."CUST_ID"[NUMBER,22], "S"."CHANNEL_ID"[NUMBER,22], "S"."AMOUNT_SOLD"[NUMBER,22]


    ############################
    # 힌트 추가 NO_PX_JOIN_FILTER
    # 소요 시간 : 00:00:03.03
    ############################
    SELECT /*+ GATHER_PLAN_STATISTICS INDEX(c IX_CUST_BIRTH_CUST) LEADING(c) NO_PX_JOIN_FILTER(@"SEL$1" "S"@"SEL$1";) */
    c.cust_id, c.cust_first_name, c.cust_last_name,
    s.prod_cnt, s.channel_cnt, s.tot_amt
    FROM customers c,
    (SELECT s.cust_id,
    COUNT (DISTINCT s.prod_id) AS prod_cnt,
    COUNT (DISTINCT s.channel_id) AS channel_cnt,
    SUM (s.amount_sold) AS tot_amt
    FROM sales s
    GROUP BY s.cust_id) s
    WHERE c.cust_year_of_birth = 1987
    AND s.cust_id = c.cust_id
    AND ROWNUM <= 10;

    select * from table(dbms_xplan.display_cursor(null,null,'all ALLSTATS LAST -rows'));

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | | 1543 (100)| | | | 10 |00:00:02.88 | 5212 | | | |
    |* 1 | COUNT STOPKEY | | 1 | | | | | | 10 |00:00:02.88 | 5212 | | | |
    |* 2 | HASH JOIN | | 1 | 870 | 1543 (3)| 00:00:19 | | | 10 |00:00:02.88 | 5212 | 836K| 836K| 1176K (0)|
    | 3 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 6493 | 35 (0)| 00:00:01 | | | 151 |00:00:00.01 | 146 | | | |
    | 4 | BITMAP CONVERSION TO ROWIDS| | 1 | | | | | | 151 |00:00:00.01 | 2 | | | |
    |* 5 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_YOB_BIX | 1 | | | | | | 1 |00:00:00.01 | 2 | | | |
    | 6 | VIEW | | 1 | 303K| 1507 (3)| 00:00:19 | | | 523 |00:00:02.88 | 5066 | | | |
    | 7 | SORT GROUP BY | | 1 | 172K| 1507 (3)| 00:00:19 | | | 523 |00:00:02.88 | 5066 | 10M| 3330K| 9496K (0)|
    | 8 | PARTITION RANGE ALL | | 1 | 21M| 1478 (1)| 00:00:18 | 1 | 28 | 918K|00:00:05.19 | 5066 | | | |
    | 9 | TABLE ACCESS FULL | SALES | 28 | 21M| 1478 (1)| 00:00:18 | 1 | 28 | 918K|00:00:01.82 | 5066 | | | |
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    1 - filter(ROWNUM<=10)
    2 - access("S"."CUST_ID"="C"."CUST_ID";)
    5 - access("C"."CUST_YEAR_OF_BIRTH"=1987)

    Column Projection Information (identified by operation id):
    -----------------------------------------------------------

    1 - "C"."CUST_ID"[NUMBER,22], "C"."CUST_FIRST_NAME"[VARCHAR2,20], "C"."CUST_LAST_NAME"[VARCHAR2,40], "S"."TOT_AMT"[NUMBER,22], "S"."PROD_CNT"[NUMBER,22],
    "S"."CHANNEL_CNT"[NUMBER,22]
    2 - (#keys=1) "C"."CUST_ID"[NUMBER,22], "C"."CUST_FIRST_NAME"[VARCHAR2,20], "C"."CUST_LAST_NAME"[VARCHAR2,40], "S"."TOT_AMT"[NUMBER,22], "S"."PROD_CNT"[NUMBER,22],
    "S"."CHANNEL_CNT"[NUMBER,22]
    3 - "C"."CUST_ID"[NUMBER,22], "C"."CUST_FIRST_NAME"[VARCHAR2,20], "C"."CUST_LAST_NAME"[VARCHAR2,40]
    4 - "C".ROWID[ROWID,10], "C"."CUST_YEAR_OF_BIRTH"[NUMBER,22]
    5 - STRDEF[BM VAR, 10], STRDEF[BM VAR, 10], STRDEF[BM VAR, 7924], "C"."CUST_YEAR_OF_BIRTH"[NUMBER,22]
    6 - "S"."CUST_ID"[NUMBER,22], "S"."PROD_CNT"[NUMBER,22], "S"."CHANNEL_CNT"[NUMBER,22], "S"."TOT_AMT"[NUMBER,22]
    7 - (#keys=1) "S"."CUST_ID"[NUMBER,22], COUNT(DISTINCT "S"."CHANNEL_ID";)[22], COUNT(DISTINCT "S"."PROD_ID";)[22], SUM("S"."AMOUNT_SOLD";)[22]
    8 - "S"."PROD_ID"[NUMBER,22], "S"."CUST_ID"[NUMBER,22], "S"."CHANNEL_ID"[NUMBER,22], "S"."AMOUNT_SOLD"[NUMBER,22]
    9 - "S"."PROD_ID"[NUMBER,22], "S"."CUST_ID"[NUMBER,22], "S"."CHANNEL_ID"[NUMBER,22], "S"."AMOUNT_SOLD"[NUMBER,22]

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

      Bloom Filter 때문입니다. 불필요한 조인을 방지하는 효과가 있습니다. 아래의 링크를 참조하세요.

      http://scidb.tistory.com/entry/Parallel-Query-의-조인시-또다른-튜닝방법pxjoinfilter

  8. oraking 2010.09.07 15:23 신고  댓글주소  수정/삭제  댓글쓰기

    저도 위 글을 읽었습니다만 현재 제가 테스트한 테이블은 파티션테이블이며 parallel은 사용하지 않고 있습니다.

    buffers를 보면 두 쿼리가 동일한 buffer를 엑세스 합니다.

    또한 아래 결과 또한 no rows selected 입니다.

    select filtered, probed, probed - filtered as sent from v$sql_join_filter
    where qc_session_id = sys_context('userenv', 'sid');

    제 개인적인 생각으로는 bloom filter와 FPD의 결합된 형태가 나타난듯합니다.

    FPD: Considering simple filter push in query block SEL$639F1A6F (#2)
    "S"."CUST_ID"=:B1
    try to generate transitive predicate from check constraints for query block SEL$639F1A6F (#2)
    finally: "S"."CUST_ID"=:B1

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

      Parallel을 사용할때만 Bloom Filter가 생성되는것은 아닙니다. 파티션을 사용할 때도 생성됩니다.
      아래글의 pdf 파일을 참조하세요.
      http://scidb.tistory.com/entry/Partition-Access-Pattern

      Bloom Filter가 생성되어 Group By 량이 줄어들어듦으로 해서 PGA 사용량(Used-Mem)이 100배이상 차이가 납니다. 즉 Bloom Filter에 의해서 group by량이 급격히 줄어든것 입니다.

      다만 파티션 Bloom filter인 경우 v$sql_join_filter에 결과가 나타나지 않는 현상이 발견됩니다.