Oracle 에서 connect by 구문으로 테스트데이터(샘플테이블) 만들기

오라클에서 특정 기능을 테스트하거나 성능측정 등을 하기 위해서 데이터를 갑자기 크게 많이 만들고 싶은 경우가 있습니다.

이때 주로 많이들 사용하는 방법이 자기복제 입니다.

테이블을 먼저 만들고, 데이터를 몇개 집어넣고,

 

      SQL> Insert into TTT1 select * from TTT1;

 

과 같은 자기복제 구문을 n 번 실행시키는 거죠... 그러면 테이블이 매우 빠르게 커지죠~

물론 테이블에 데이터 중복이 너무 많다는 단점이 있습니다. 같은 데이터가 계속 반복되니까...

 

그래서 이번에는 connect by 문과 level 키워드를 사용해서 데이터를 만들어 보겠습니다.

 

먼저, 아래와 같이 간단히 connect by level 문장이 수행되는 것을 확인해봅니다.

 

 

level <= 20 의 조건으로 connect by 를 실행하면 위와같이 20개의 데이터를 얻을 수 있습니다.

level <= 200000 이라고 조건을 주면 20만건의 Row 가 만들어지겠죠?

 

이것을 이용합니다.

아래의 SQL문은 위의 connect by level 문장에서 조회되는 level 값에 몇가지 변화를 주어 다양한 컬럼을 만들어봤습니다.

 

 

mod() 함수는 값을 나눠서 나머지를 구하는 함수 입니다.  mod(level,20) 은 level 값을 20으로 나눈 나머지 값이 나옵니다.

chr() 함수는 주어진 숫자에 해당하는 Ascii Character 값을 출력합니다.

97번은 영어 알파벳 소문자 'a' 이고, 65번은 영어 알파벳 대문자 'A' 입니다.

즉, big_ename 은 영어 소문자 'a' ~ 'z' 가 되고,

big_addr 은 영어 대문자 'A' ~ 'Z' 가 되겠습니다.

 

그럼, 이제 이 SQL 문을 Inline View 로 from 절에 넣고, 조회되는 값을 CTAS (Create Table As Select) 구문을 이용해서 테이블을 생성할 수 있겠습니다.

 

SQL>
create table TEST1
as
select empno, salary, deptno,
       lpad(big_ename, 3000, big_ename) as big_ename,
       lpad(big_addr, 3000, big_addr) as big_addr
from ( select level + 10000000 as empno,
              mod(level,1000) + 10000 as salary,
              mod(level,20) as deptno,
              chr(97+mod(level,26)) as big_ename, 
              chr(65+mod(level,26)) as big_addr
       from dual
       connect by level <= 20000
     )
;

 

from 절에서 조회되는 값중에 big_ename, big_addr 값을 lpad() 함수를 이용해서 늘렸습니다.

같은 값이 3000번 반복해서 출력됩니다. 'a' 는 'aaaaaa...' 와 같이~

 

 

 

▶ 참고 : Oracle Connect by 구문, Start with 구문 예제