'Connect By'에 해당되는 글 4건

  1. 2010.03.05 COPY_T를 대신하여 Connect By 문을 사용하는가? (11)
  2. 2010.02.11 Connect By VS ANSI SQL (6)
  3. 2008.06.23 Hierarchical Queries (3)
  4. 2008.04.30 Recursive SQL (2)

Dummy Table(흔히 Copy_t 라고 불림) 대신에 9i 이후부터는 Connect By level 문을 사용하곤 한다. 하지만 조심하지 않으면 해당 SQL이 종료되지 않는 장애를 만나게 된다. 오늘은 Connect By level 문을 오용하는 사례와 해결책을 제시하고자 한다.

상황
업무팀에서 새로운 SQL을 작성하고 컴파일하여 운영 시스템에 반영되었다. 문제의 SQL이 실행되자 너무 오래걸려서 Time Out이 발생하였다. 아래는 상황을 최대한 간단히 표현하여 테스트를 수행하기 위한 스크립트 이다.

--입사년도 테이블 생성
create table hire as
select '2006' hire_date from dual union all
select '2003' hire_date from dual union all
select '2002' hire_date from dual union all
select '1999' hire_date from dual union all
select '1997' hire_date from dual ;

--현재년도 에서 입사년도를 빼서 차이(gap)를 나타냄
 select hire_date,
        to_char(sysdate, 'YYYY') this_year,
        to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date) gap
   from hire   ;


결과:
HIRE THIS        GAP
---- ---- ----------
2006 2010          4
2003 2010          7
2002 2010          8
1999 2010         11
1997 2010         13

5 rows selected.

업무요건
위의 SQL의 결과에서 나타난 GAP만큼 가상의 ROW를 생성하여야 한다. 즉 입사년도가 2006년인 사람은 ROW가 4개로 되어야 하고 2003년인 사람은 ROW가 7개가 되어야 한다. 전체적으로 43건이 나와야 한다. 아래에 원하는 답이 있다.
 
원하는 답
HIRE THIS        GAP        NUM
---- ---- ---------- ----------
2003 2010          7          7
2003 2010          7          6
2003 2010          7          5
2003 2010          7          4
2003 2010          7          3
2003 2010          7          2
2003 2010          7          1
2006 2010          4          4
2006 2010          4          3
2006 2010          4          2
2006 2010          4          1
....중간생략

문제의 SQL
아래의 SQL은 gap을 Connect By Level 절에 적용시킨 것이다. 아래처럼 SQL을 작성한다면 지옥을 경험할 수 있다.

select hire_date,
       to_char(sysdate, 'YYYY') this_year,
       to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date) gap,
      level
  from hire
connect by level <= to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date) ;

위의 SQL은 전체건을 Fetch하려면 10분이 걸려도 끝나지 않았다. 시간이 너무 오래 걸리므로 아래처럼 COUNT 로 바꿔서 실행해 보았다.

select /*+ gather_plan_statistics */ count(*)
  from  ( select hire_date,
                 to_char(sysdate, 'YYYY') this_year,
                 to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date) gap,
                 level
            from hire
         connect by level <= to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date)
        );

       
결과:
  COUNT(*)
----------
   3773280

버그인가?
무려 370만건 이상의 건수가 나왔다. 이상하지 않은가? 건수의 예측도 할 수 없었다. 건수와 관련해서 일정한 규칙도 존재하지 않았다. 버그인지 아닌지 알 수 없지만 결론적으로 위의 SQL처럼 사용하면 안된다는 것을 알 수 있다. 아래는 Count에 대한 실행통계인데 무려 44초나 걸렸다.

-------------------------------------------------------------------------------
| Id  | Operation                      | Name | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                |      |      1 |00:00:44.39 |       3 |
|   2 |   VIEW                         |      |   3773K|00:00:45.28 |       3 |
|   3 |    CONNECT BY WITHOUT FILTERING|      |   3773K|00:00:41.51 |       3 |
|   4 |     TABLE ACCESS FULL          | HIRE |      5 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------


해결방법

 select /*+ gather_plan_statistics leading(hire) */
        hire_date,
        to_char(sysdate, 'YYYY') this_year,
        to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date) gap,
        num
   from hire,
        (select level as num
           from dual
        connect by level <= 40  --> 충분한 값을 주어야 한다.
        ) b
  where num <= to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date);  



Connect By 절을 인라인뷰로 만들어 Dummy 테이블처럼 사용하였다. 주의 사항은 connect by level <= 40 에서 숫자값을 충분히 주어야 한다. GAP 중에 가장 큰것이 13 이므로 넉넉히 40을 주었다.    
 
결과:
HIRE THIS        GAP        NUM
---- ---- ---------- ----------
1997 2010         13         13
1997 2010         13         12
1997 2010         13         11
.....중간생략
2006 2010          4          4
2006 2010          4          3
2006 2010          4          2
2006 2010          4          1

43 rows selected.  

정상적으로 원하는 결과가 나왔고 성능도 이상적이다.

-------------------------------------------------------------------------------------------
| Id  | Operation                       | Name | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------
|   1 |  MERGE JOIN                     |      |     43 |00:00:00.01 |       3 |          |
|   2 |   SORT JOIN                     |      |      5 |00:00:00.01 |       3 | 2048  (0)|
|   3 |    TABLE ACCESS FULL            | HIRE |      5 |00:00:00.01 |       3 |          |
|*  4 |   SORT JOIN                     |      |     43 |00:00:00.01 |       0 | 2048  (0)|
|   5 |    VIEW                         |      |     40 |00:00:00.01 |       0 |          |
|   6 |     CONNECT BY WITHOUT FILTERING|      |     40 |00:00:00.01 |       0 |          |
|   7 |      FAST DUAL                  |      |      1 |00:00:00.01 |       0 |          |
-------------------------------------------------------------------------------------------
 
주의사항
만약 위의 SQL처럼 인라인뷰를 사용한다고 해도 Nested Loop 조인으로 풀리고 Connect By문을 사용한 인라인뷰가 후행집합이 된다면 선행집합의 건수만큼 반복해서 Connect By문이 수행되므로 조심해야 한다.


결론
Dummy 테이블을 대신하는 Connect By Level을 사용할 때 주의하지 않으면 SQL이 종료되지 않는다. 이를 방지하려면 반드시 DUAL과 함께 사용해야 하며 인라인뷰를 만들어서 사용해야 된다. 만약 예전처럼 Dummy 테이블을 사용했다면 이런 성능저하는 발생하지 않을 것이다. 아무리 새롭고 좋은것도 오용한다면 결과가 어떻게 되는지 잘 보여주는 예제이다.

Posted by extremedb

댓글을 달아 주세요

  1. feelie 2010.03.07 12:34  댓글주소  수정/삭제  댓글쓰기

    잘봤습니다.
    3월이라 책소식이 있을때가 됐는데요. 아직 소식이....^^
    개인적으로 자료를 찾아봤는데 찾을수가 없어 질문합니다.
    요즘에 책을 보니 DMA라는 말을 많이 접하게 됩니다.
    (예전부터 있었는지는 잘 모르겠네요..)
    DMA라는 개념은 알겠는데 오라클에서 사용을 어떻게 할수 있는지 궁금합니다
    DMA라는것이 fixed table을 사용하는지,
    DMA는 쿼리를 사용하지않는다고 하는데,
    그럼 일반 DBA가 DMA을 사용할수는 없는지요?
    사용할수 있다면 어떻게 하는지 궁금합니다.

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

      안녕하세요.
      책은 표지디자인이 끝나고 인쇄소로 넘어갔습니다.
      DMA관련해서는 ASK 엑셈에 질문하시는 것이 좋을것 같습니다. 제가 모니터링 툴 쪽은 잘 알지 못합니다.
      감사합니다.

  2. 김봉호 2010.03.09 12:46  댓글주소  수정/삭제  댓글쓰기

    오 드디어 100번째 글이군요 +_+;;
    요즘 공부한답시고 처음부터 차근차근 보는중입니다 ^^

  3. Favicon of https://jazzjeon.tistory.com BlogIcon JazzJeon 2010.03.09 18:10 신고  댓글주소  수정/삭제  댓글쓰기

    담아가겠습니다. 좋은글 감사합니다.

  4. baind 2010.03.17 11:05  댓글주소  수정/삭제  댓글쓰기

    안녕하세요. 오동규님.
    부족한 제게 오늘도 소중하고 배울점이 많은 글을 볼 수 있도록 블로그에 올려주셔셔 진심으로 감사드립니다.

    다른 이야기이지만 위에 댓글에 인쇄소로 넘어갔다니...출판될 책을 곧 볼 수있다는 사실에 기대 됩니다.^^*
    언제나 건강하시고 귀하의 노고에 진심으로 감사드립니다!

  5. Favicon of http://www.soqool.com BlogIcon 쏘쿨 2010.05.31 15:15  댓글주소  수정/삭제  댓글쓰기

    글 중간 부분에 "건수와 관련해서 일정한 규칙도 존재하지 않았다." 라고 쓰신 것은, 경험한 바에 의해서 쓰신거라고 이해하겠습니다.
    사실 일반적으로 말하면,
    건수와 관련된 규칙이 있고, 위의 건은 정확히 말하면

    3,773,280
    = 5^1 + 5^2 + 5^3 + 5^4
    + 5^4*4^1 + 5^4*4^2 + 5^4*4^3
    + 5^4*4^3*3^1
    + 5^4*4^3*3^1*2^1 + 5^4*4^3*3^1*2^2 + 5^4*4^3*3^1*2^3
    + 5^4*4^3*3^1*2^3*1^1 + 5^4*4^3*3^1*2^3*1^2

    건이 되겠습니다.

  6. 최규정 2010.08.19 14:37  댓글주소  수정/삭제  댓글쓰기

    hire테이블과 connect by level <= to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date)의 결과가 카테시안 곱이 되는거 아닙니까? hire 어떤 테이블일지 모르겠지만 건수가 많으면 느려질것 같은데.. ㅋㅋ

필자는 예전에 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 https://scidb.tistory.com BlogIcon extremedb 2010.02.18 14:46 신고  댓글주소  수정/삭제

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

Hierarchical Queries 는 Recursive Query 혹은 Tree SQL 이라고 부른다.
BOM, 조직도 등을 개발할때 Hierarchical Queries 는 필수이다.
하지만  이상하게도 Hierarchical Queries는 개발자들의 기피대상 1위이다.
Self 조인을 사용한다든지 아니면 Recursive Function 등을 사용하는 등등 어떻게든  Hierarchical Query 를
피해갈려고 애쓴다.
9I 및 10g 에서 Connect By Query 는 이미 피해갈수 없을 정도로 막강해졌다.
즉 예전과는 달리 Connect By 가 워낙 막강해져서 이젠 이기능을 모방하려면 엄청난 노가다(?) 를 해야만 할것이다.
여기서는 기본적인 Connect By 문법은 이야기 하지않고  9i , 10g 에서 추가된 문법과 수정된 Connect By 관련
Plan 에 대해서만 이야기 할 것이다.

9i 신기능

Hierarchical Queries 관련하여 대표적인 미신이 2가지 있다.
1) 조인관련 신기능
첫번째는 조인을 할수가 없다는것이고 두번째는 조인이 포함된 뷰를 사용할수없다는 것이다.
이것은 8i 까지만 사실이고 9i 부터는 자유롭게 from 절을 이용하여 조인할수 있다.

SELECT  LEVEL,
              LPAD('  ',2*(LEVEL - 1)) || lname as ename,
              A.emp_id,
              A.manager_emp_id,
              B.name as deptname
FROM employee A, department B
WHERE A.dept_id = b.dept_id
START WITH A.emp_id = 7839
CONNECT BY PRIOR A.emp_id = A.manager_emp_id;

--결과
사용자 삽입 이미지


















2) 향상된 order by
Hierarchical Queries 의 문제는 order by 를 하면 계층구조가 깨진다는 것이다.
따라서 ORDER BY 를 하려면 인덱스 설계를 잘하는수 밖에 없었다.
하지만 9i 부터 ORDER SIBLINGS BY 절이 나오면서 그런 문제는 사라졌다.
다시말하면 계층구조가 다치지 않는 범위내에서 SORT 하는 기능이 추가 되었다.

SELECT LEVEL, LPAD('  ',2*(LEVEL - 1)) || lname ename,
       emp_id, manager_emp_id
FROM employee
START WITH manager_emp_id IS NULL
CONNECT BY PRIOR emp_id = manager_emp_id
ORDER SIBLINGS BY lname;

--결과

사용자 삽입 이미지


















3) Path 나타내기
계층구조를 한줄로 나타내는 기능이 9i 에서 추가 되었다.

SELECT SYS_CONNECT_BY_PATH(lname, '#')
FROM employee
START WITH manager_emp_id IS NULL
CONNECT BY PRIOR emp_id = manager_emp_id;

--결과

사용자 삽입 이미지


















10g 신기능


1) Root 노드와 Leaf 노드를 구분하는 기능이 추가되었다.
 CONNECT_BY_ROOT 는 반드시 컬럼을 지정하여야 하고
CONNECT_BY_ISLEAF 는 여부(1 이면 Y 라는 뜻이다)이다.

SELECT lname "Employee",
       CONNECT_BY_ROOT lname "Top Manager", 
       CONNECT_BY_ISLEAF
FROM employee
START WITH manager_emp_id = 7839
CONNECT BY PRIOR emp_id = manager_emp_id;

--결과
사용자 삽입 이미지


















2) CYCLE(무한루프) 방지 및 CYCLE 확인기능
먼저 무한루프를 만들기 위하여 데이터를 수정한다.

UPDATE employee
SET manager_emp_id = 7654 --> ROOT 노드(KING)의 매니져를 둠으로써 무한루프 발생
WHERE manager_emp_id IS NULL;

COMMIT;

SELECT LEVEL, LPAD('  ',2*(LEVEL - 1)) || lname "EMPLOYEE",
       emp_id, manager_emp_id
FROM employee
START WITH emp_id = 7839
CONNECT BY PRIOR emp_id = manager_emp_id;

ERROR:
ORA-01436: CONNECT BY loop in user data --> 무한루프가 발생한다.

그럼 해결책은?
아래처럼 하면된다.
아래는 반복을 제거하고 결과를 리턴한다.
 
SELECT LEVEL, LPAD('  ',2*(LEVEL - 1)) || lname "EMPLOYEE",
      
CONNECT_BY_ISCYCLE,               --> CYCLE 에러의 발생지점을 알려준다.
       emp_id, manager_emp_id
FROM employee
START WITH emp_id = 7839
CONNECT BY NOCYCLE PRIOR emp_id = manager_emp_id;    --> 무한루프를 방지한다.

--결과
사용자 삽입 이미지


















3) PLAN 상의 변경
추가적으로 PLAN 에서의 변경사항을 살펴보면
10.2.0.3 버젼까지 튜너들을 괴롭히던 원인을 알수없는 FULL SCAN 이 사라졌다.
--10.2.0.3 PLAN
Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=2 Bytes=22)
   1    0   CONNECT BY (WITH FILTERING)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE)
   3    2       INDEX (UNIQUE SCAN) OF 'EMPLOYEE_PK' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=3)
   4    1     NESTED LOOPS
   5    4       BUFFER (SORT)
   6    5         CONNECT BY PUMP
   7    4       TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE) (Cost=1 Card=2 Bytes=22)
   8    7         INDEX (RANGE SCAN) OF 'IDX_EMPLOYEE_MGR' (INDEX) (Cost=1 Card=2)
   9    1    
TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=1 Card=2 Bytes=22)



위의 FULL SCAN(굵은글씨) 은 있을수 없는 정보(BUG)였는데 이는 10.2.0.4 에서  PLAN 상에서 사라졌다.
--10.2.0.4 PLAN

Execution Plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1 Card=2 Bytes=22)
   1    0   CONNECT BY (WITH FILTERING)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE)
   3    2       INDEX (UNIQUE SCAN) OF 'EMPLOYEE_PK' (INDEX (UNIQUE)) (Cost=1 Card=1 Bytes=3)
   4    1     NESTED LOOPS
   5    4       BUFFER (SORT)
   6    5         CONNECT BY PUMP
   7    4       TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE) (Cost=1 Card=2 Bytes=22)
   8    7         INDEX (RANGE SCAN) OF 'IDX_EMPLOYEE_MGR' (INDEX) (Cost=1 Card=2)

4) 파라미터의 변화
_optimizer_connect_by_cost_based 파라미터가 추가 되었다.
하지만 이파라미터는 버그가 보고되고 있어 사이트에서 수동으로 false 로 바꾸어 놓는 경우가 많다.
false 로 바꾸면 connect by 쿼리에서 조인하는 경우(  9i 의1) 조인관련 신기능 참조 ) FTS 로 PLAN 이 바뀌는 경향이 있으므로 주의 해야한다.
아례 예제를 보자.

SELECT   LEVEL,
              LPAD('  ',2*(LEVEL - 1)) || lname as ename,
              A.emp_id,
              A.manager_emp_id,
              B.name as deptname
FROM employee A, department B
WHERE A.dept_id = b.dept_id
START WITH A.emp_id = 7839
CONNECT BY  PRIOR A.emp_id = A.manager_emp_id;

Execution Plan : --> alter session set "_optimizer_connect_by_cost_based" = true;  적용한 plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=2 Bytes=50)
   1    0   CONNECT BY (WITH FILTERING)
   2    1     NESTED LOOPS (Cost=2 Card=1 Bytes=34)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE) (Cost=1 Card=1 Bytes=22)
   4    3         INDEX (UNIQUE SCAN) OF 'EMPLOYEE_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)
   5    2       TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENT' (TABLE) (Cost=1 Card=4 Bytes=48)
   6    5         INDEX (UNIQUE SCAN) OF 'DEPARTMENT_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)
   7    1     NESTED LOOPS (Cost=2 Card=2 Bytes=50)
   8    7       NESTED LOOPS
   9    8         BUFFER (SORT)
  10    9           CONNECT BY PUMP
  11    8         TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE) (Cost=1 Card=2 Bytes=26)
  12   11           INDEX (RANGE SCAN) OF 'IDX_EMPLOYEE_MGR' (INDEX) (Cost=1 Card=2)
  13    7       TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENT' (TABLE) (Cost=1 Card=1 Bytes=12)
  14   13         INDEX (UNIQUE SCAN) OF 'DEPARTMENT_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)
  15    1     MERGE JOIN (Cost=5 Card=14 Bytes=434)
  16   15       TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENT' (TABLE) (Cost=1 Card=4 Bytes=48)
  17   16         INDEX (FULL SCAN) OF 'DEPARTMENT_PK' (INDEX (UNIQUE)) (Cost=1 Card=4)
  18   15       SORT (JOIN) (Cost=4 Card=14 Bytes=266)
  19   18         TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=3 Card=14 Bytes=266)

 
Execution Plan --> alter session set "_optimizer_connect_by_cost_based" = false; 적용한 plan
--------------------------------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3 Card=1 Bytes=25)
   1    0   CONNECT BY (WITH FILTERING)
   2    1     FILTER
   3    2       COUNT
   4    3         NESTED LOOPS (Cost=3 Card=1 Bytes=25)
   5    4           TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=2 Card=1 Bytes=13)
   6    4           TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENT' (TABLE) (Cost=1 Card=1 Bytes=12)
   7    6             INDEX (UNIQUE SCAN) OF 'DEPARTMENT_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)
   8    1     HASH JOIN
   9    8       CONNECT BY PUMP
  10    8       COUNT
  11   10         NESTED LOOPS (Cost=3 Card=1 Bytes=25)
  12   11           TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=2 Card=1 Bytes=13)
  13   11           TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENT' (TABLE) (Cost=1 Card=1 Bytes=12)
  14   13             INDEX (UNIQUE SCAN) OF 'DEPARTMENT_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)
  15    1     COUNT
  16   15       NESTED LOOPS (Cost=3 Card=1 Bytes=25)
  17   16         TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=2 Card=1 Bytes=13)
  18   16         TABLE ACCESS (BY INDEX ROWID) OF 'DEPARTMENT' (TABLE) (Cost=1 Card=1 Bytes=12)
  19   18           INDEX (UNIQUE SCAN) OF 'DEPARTMENT_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)

결론:
Hierarchical Queries는 조직도나 tree 구조의 BOM 업무의 거의 모든 기능을 커버한다.
위의 문법은 아주 기초적인 것들이니 반드시 이해하고 업무에 활용하기 바란다.

'Oracle > SQL Pattern' 카테고리의 다른 글

응용력 발휘하기  (2) 2009.03.20
Hypothetical Functions 은 어떨때 써야하나?  (3) 2008.10.25
Hierarchical Queries  (3) 2008.06.23
가로를 세로로 바꾸기  (0) 2008.05.27
Jonathan Lewis 의 퀴즈 접근법  (0) 2008.05.15
Recursive SQL  (2) 2008.04.30
Posted by extremedb

댓글을 달아 주세요

  1. Favicon of https://ukja.tistory.com BlogIcon 욱짜 2008.06.23 22:16 신고  댓글주소  수정/삭제  댓글쓰기

    위에서 3)번에 해당하는 문제가 메타링크 번호 5065418에 Filtering Connect By에 의한 성능 저하 현상으로 보고되어 있네요.

  2. Favicon of https://scidb.tistory.com BlogIcon extremedb 2008.06.24 10:04 신고  댓글주소  수정/삭제  댓글쓰기

    메타링크에 좋은 정보가 있네요.
    감사합니다.

  3. 2014.05.22 10:57  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

Recursive SQL

Oracle/SQL Pattern 2008. 4. 30. 11:30

Recursive SQL

개념 :
SQL 이 좋은 언이이긴 하지만 집합처리 언어이기 때문에 불편한점이 많은 것이 사실이다.
특히 아래와 같은 구조에서 세로를 가로로 바꿀려고 하면 힘들다.

SELECT DEPTNO, ENAME
FROM EMP;

DEPTNO ENAME
------ ----------
10 CLARK
10 KING
10 MILLER
20 SMITH
20 ADAMS
20 FORD
20 SCOTT
20 JONES
30 ALLEN
30 BLAKE
30 MARTIN
30 JAMES
30 TURNER
30 WARD

위의 데이터를 아래처럼 부서번호별로 가로로 바꾸어야 하는것이 미션이다.

DEPTNO ENAME
------- ------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,SCOTT,ADAMS,FORD
30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

오라클의 경우 조금 복잡한 Recursive SQL 을 작성해야 한다.

select deptno,
ltrim(sys_connect_by_path(ename,','),',') ename
from (
select deptno,
ename,
row_number() over (partition by deptno order by empno) rn,
count(*) over (partition by deptno) cnt
from emp
)
where level = cnt
start with rn = 1
connect by prior deptno = deptno and prior rn = rn-1 ;

그럼 mySQL은 어떨까?
놀랍게도 mySQL 은 group_concat 한방으로 해결하고 있다.

select deptno,
group_concat(ename order by empno separator, ',') as ename
from emp
group by deptno;

사실 필자는 오라클 9i 이전버젼부터 mySQL 의 정규식을 부러워 했었다.
mySQL 은 정말 그렇고 그런? DBMS 일까? 다시생각해야 될필요가 있다.

그럼 DB2 나 SQL SERVER 는 Recursive SQL 이 사용가능할까?

현재 많은 사람들이 Recursive 쿼리가 안된다고 판단하고 Self Join 을 사용하고 있다.
그러나 DB2나 SQL 서버의 최신버젼은 아래와 같이 Recursive With 를 사용하면 된다.
with x (deptno, cnt, list, empno, len)
as (
select deptno, count(*) over (partition by deptno),
cast(ename as varchar(100)), empno, 1
from emp
union all
select x.deptno, x.cnt, x.list ',' e.ename, e.empno, x.len+1
from emp e, x --> recursive call
where e.deptno = x.deptno
and e.empno > x. empno
)
select deptno,list
from x
where len = cnt ;

참고로 오라클은 Recursive With 절 이나 group_concat 은 11g 버젼에서도 지원하지 않고 있다.
오라클에서는 Recursive function 은 예전부터 지원되어 왔다.

참조서적 : SQL Cookbook By Anthony Molinaro(O'Reilly)

'Oracle > SQL Pattern' 카테고리의 다른 글

응용력 발휘하기  (2) 2009.03.20
Hypothetical Functions 은 어떨때 써야하나?  (3) 2008.10.25
Hierarchical Queries  (3) 2008.06.23
가로를 세로로 바꾸기  (0) 2008.05.27
Jonathan Lewis 의 퀴즈 접근법  (0) 2008.05.15
Recursive SQL  (2) 2008.04.30
Posted by extremedb

댓글을 달아 주세요

  1. 행인 2009.04.02 15:10  댓글주소  수정/삭제  댓글쓰기

    10g부터 collect 라는 함수가 추가되어서 비슷한 흉내를 낼 수 있습니다.
    (ordering등 손을 따로 봐야 하지만요...)

    SQL> create or replace type tov as table of varchar2(255);
    /

    Type created.

    SQL> select deptno, cast(collect(ename) as tov) enames
    from emp group by deptno;

    DEPTNO ENAMES
    ---------- ------------------------------------------------------------
    10 TOV('CLARK', 'KING', 'MILLER')
    20 TOV('SMITH', 'FORD', 'ADAMS', 'SCOTT', 'JONES')
    30 TOV('ALLEN', 'BLAKE', 'MARTIN', 'TURNER', 'JAMES', 'WARD')

  2. Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.04.02 19:03 신고  댓글주소  수정/삭제  댓글쓰기

    좋은 정보 감사합니다.
    아래의 X-Query 도 참고 하시기 바랍니다.

    select deptno, XMLAgg(XMLElement("nm", ename||',')).EXTRACT('//text()').GetStringVal() ename
    from emp
    group by deptno
    ;