① 샘플 데이터 생성

오라클 튜닝 서적을 보면 예제 데이터를 생성하는데 CONNECT BY 구문을 자주 사용하는 것을 볼 수 있다. 튜닝 효과를 보려면 테이블에 데이터가 아주 많이 담겨 있어야 하는데 다량의 데이터를 만들어 내는 작업이 그리 쉽지만은 않다. 하지만 계층형 쿼리와 오라클에서 제공하는 DBMS_RANDOM이란 패키지(난수를 생성하는 패키지로 패키지에 대해선 PL/SQL 부분에서 상세히 설명할 것이다)를 사용하면 몇 만 건의 데이터도 쉽게 생성할 수 있다. 예제 테이블을 만들어 데이터를 생성해 보자.

입력

    CREATE TABLE ex7_1 AS
    SELECT ROWNUM seq,
           '2014' || LPAD(CEIL(ROWNUM/1000) , 2, '0' ) month,
           ROUND(DBMS_RANDOM.VALUE (100, 1000)) amt
      FROM DUAL
    CONNECT BY LEVEL <= 12000;

결과

    table EX7_1이(가) 생성되었습니다.

입력

    SELECT *
      FROM ex7_1;

결과

    SEQ MONTH   AMT
    --- ------- ------------
    1   201401  748
    2   201401  972
    3   201401  627
    4   201401  609
    5   201401  211
    ...

입력

    SELECT month, SUM(amt)
      FROM ex7_1
     GROUP BY month
     ORDER BY month;

결과

    MONTH        SUM(AMT)
    -------- -----------
    201401        538702
    201402        535529
    201403        550541
    201404        550948
    201405        555712
    201406        555138
    201407        549140
    201408        540903
    201409        561034
    201410        556503
    201411        559535
    201412        552660

    12개의 행이 선택됨.

위 쿼리에서 사용된 주요 항목을 설명하면 다음과 같다.

CONNECT BY LEVEL <= 12000

“CONNECT BY LEVEL <= 숫자”를 사용하면 명시한 숫자만큼의 로우를 반환하는데, 내부적으로 보면 등비수열의 합만큼 로우를 생성한다. 예를 들어, DUAL 테이블은 기본 로우 개수는 1개인데 “SELECT … FROM DUAL CONNECT BY LEVEL <= 3”이라고 명시할 때, 이는 첫째 항(a = 1), 공비(r =1), 항의 수(n = 3)인 등비수열에 해당된다.

등비수열 합(S) 공식

• 공비 r = 1이면, S = a\*n

• 공비 r ≠ 1이면, S = ![](https://lh3.googleusercontent.com/-vFAheZdc5hs/VcbRQEzRTmI/AAAAAAAABFM/jPoFPVc7Yl8/s0/num_221.jpg)

따라서 “LEVEL <=3”일 때는 총합이 3이 되어 3개의 로우가 생성 및 반환된다. 그리고 다음과 같이 서브 쿼리로 DUAL 테이블을 조회하는 쿼리를 UNION ALL로 연결하면, 맨 바깥에 있는 쿼리의 기본 로우 수는 1이 아닌 2가 된다.

입력

    SELECT ROWNUM
    FROM (
           SELECT 1 AS row_num
             FROM DUAL
            UNION ALL
           SELECT 1 AS row_num
             FROM DUAL
    )
    CONNECT BY LEVEL <= 4;

결과

    ROWNUM
    ----------
    1
    2
    3
    4
    5
    ...
    30개의 행이 선택됨.

따라서 이때는 a = 2, r = 2, n = 4가 되어, 등비수열 공식에 대입하면(r 값이 1이 아닐 때) 30이 나오고 쿼리 결과도 30개의 로우가 생성된다.

LPAD(CEIL(ROWNUM/1000) , 2, ‘0’ )

“CONNECT BY LEVEL <= 12000”로 인해 총 12,000개의 로우가 반환되며 ROWNUM도 1부터 12,000이고, 이 수를 1,000으로 나눈 뒤 CEIL 함수로 정수 형태로 변환해 두 자리 숫자 형태로 만들면 ROWNUM이 999까지는 01을, 1000부터 2000까지는 02, 이런 식으로 01~12까지 값을 반환한다.

DBMS_RANDOM.VALUE (100, 1000)

이 구문은 100에서 1000 사이의 난수를 발생시킨다.

따라서 month 컬럼 값으로 201401 ~ 201412, amt컬럼은 100에서 1000 사이 값으로 총 12,000건의 데이터가 ex7_1 테이블에 생성된 것이다.