① 샘플 데이터 생성

    오라클 튜닝 서적을 보면 예제 데이터를 생성하는데 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 테이블에 생성된 것이다.

    신간 소식 구독하기
    뉴스레터에 가입하시고 이메일로 신간 소식을 받아 보세요.