오라클 샘플 데이터 만들기, 테스트 데이터 벌크인서트 + dbms_random

 

OS환경 : Oracle Linux 6.8 (64bit)

 

DB 환경 : Oracle Database 11.2.0.4

 

쿼리 : 오라클 샘플 데이터 만들기, 테스트 데이터 벌크인서트 + dbms_random

벌크인서트와 dbms_random을 더해서 빠르게 대량 샘플데이터를 insert 시킬 수 있음

샘플 테이블 및 인덱스 생성(COLB)

 

CREATE TABLE RANDOM(COLA VARCHAR2(20), COLB NUMBER, COLC NUMBER, 
COLD VARCHAR2(30), COLE VARCHAR2(30), COLF VARCHAR2(30), 
COLG NUMBER, COLH VARCHAR2(30), COLI VARCHAR2(30));
CREATE INDEX IDX_COLB ON RANDOM(COLB); 

 

샘플 데이터 삽입

SQL>
DECLARE
TYPE tbl_ins IS TABLE OF RANDOM%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..14000 LOOP 
   w_ins(i).COLA :=i;
   w_ins(i).COLB :=300000;
   w_ins(i).COLC :=99;
   w_ins(i).COLD :='ABC'||dbms_random.string('x',10);
   w_ins(i).COLE :='EEEEEEEEEEEEEEEE';
   w_ins(i).COLF :='FFFFFFFFFFFFFFFF';
   w_ins(i).COLG :=9999999;
   w_ins(i).COLH :='HHHHHHHHHHHHHHHHHHHHHHHHHH';
   w_ins(i).COLI :='IIIIIIIIIIIIIIIIIIIIIIIIII';
END LOOP;
   FORALL i in 1..14000 INSERT INTO RANDOM VALUES w_ins(i);
   COMMIT;
END;
/

 

빨간색 칠한부분이 dbms_random 으로 랜덤한 값넣는 부분

 

테이블 건수 및 데이터 확인

SQL> select count(*) from random;
 
  COUNT(*)
----------
     14000
 
SQL> 
select * from random
where rownum <= 3;
 
COLA               COLB       COLC COLD               COLE                 COLF              COLG COLH
-------------------- ---------- ---------- ------------------------------ ------------------------------ ------------------------------ ---------- ------------------------------
COLI
------------------------------
237             300000     99 ABCU9FOR9TA8Q          EEEEEEEEEEEEEEEE         FFFFFFFFFFFFFFFF           9999999 HHHHHHHHHHHHHHHHHHHHHHHHHH
IIIIIIIIIIIIIIIIIIIIIIIIII
 
238             300000     99 ABCF14T1UZP4S          EEEEEEEEEEEEEEEE         FFFFFFFFFFFFFFFF           9999999 HHHHHHHHHHHHHHHHHHHHHHHHHH
IIIIIIIIIIIIIIIIIIIIIIIIII
 
239             300000     99 ABCSMVU846XDX          EEEEEEEEEEEEEEEE         FFFFFFFFFFFFFFFF           9999999 HHHHHHHHHHHHHHHHHHHHHHHHHH
IIIIIIIIIIIIIIIIIIIIIIIIII
 
 
3 rows selected.

 

용량확인

SQL>
set lines 200
col owner for a10
col segment_name for a20
select owner,segment_name,segment_type,sum(bytes)/1024/1024 as MB
from dba_segments
where owner=UPPER('&OWNER')
GROUP BY owner,segment_name,segment_type;
 
OWNER       SEGMENT_NAME     SEGMENT_TYPE           MB
---------- -------------------- ------------------ ----------
JSH       IDX_COLB        INDEX            .4375
JSH       RANDOM        TABLE                2
 
2 rows selected.

 

이상태에서 데이터를 더 빠르게 넣고 싶다면 table에 nologging 옵션을 걸어준 뒤 insert 작업을 해주면됨

테이블 nologging

SQL> alter table random nologging;
 
Table altered.

 

데이터 중복 삽입

SQL> insert into random select * from random;
 
2294000 rows created.
 
SQL> /
 
4588000 rows created.

 

 

참조 : https://positivemh.tistory.com/183

대량 데이터 삽입 insert 빠르게 실행 벌크 insert