[ORACLE] CONNECT BY

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 테이블을 사용했다면 이런 성능저하는 발생하지 않을 것이다. 아무리 새롭고 좋은것도 오용한다면 결과가 어떻게 되는지 잘 보여주는 예제이다.

출처 : http://scidb.tistory.com/107