OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.10.0.0
방법 : 대량 샘플데이터 생성용 쿼리 벌크인서트
벌크인서트를 이용해 대량 데이터를 만들 때
회원번호, 전화번호, 주소, 금액, 일자, 일시 등의 샘플 데이터를 어떻게 만들면 좋을까 생각하다가 만들어본 쿼리
아래 벌크 인서트문의 데이터 형식 중 필요한 부분만 잘라내서 사용하면됨
벌크 인서트(Bulk Insert)란?
대량의 데이터를 효율적으로 한 번에 삽입하는 방법임
PL/SQL 과 FORALL 문을 사용하여 여러 행을 반복적으로 insert 함
일반 단일 행 insert 보다 성능이 좋고, 일반적으로 대량의 데이터를 빠르게 처리하는 데 사용됨
테이블 생성
1
2
3
4
5
6
7
8
9
|
SQL>
drop table sample_t purge;
create table sample_t
(id1 number, id2 number, name varchar2(10),
date1 varchar2(8), date2 varchar2(8), date3 date,
phone varchar2(13), price number, qty number,
test1 number, test2 varchar2(5), test3 varchar2(4)
)
nologging;
|
컬럼 설명
id1 : i 값을 넣는 컬럼
id2 : i 값과 랜덤값을 같이 넣어서 중복이 발생하지 않게 하는 컬럼
name : data 생성시 이름넣어야 할 때 사용하는 컬럼(랜덤이라 사람이름처럼은 나오지 않음)
date1 : 2010년부터 2021년까지 '20210303' 와 같은 랜덤 문자열 날짜를 넣어주는 컬럼
date2 : 2021년으로 고정하고 월일만 랜덤 문자열로 날짜를 넣어주는 컬럼
date3 : 2010년부터 2021년까지 '20210303 12:30:00' 과 같은 랜덤 date 타입 날짜일시를 넣어주는 컬럼
phone : 전화번호를 랜덤으로 넣어주는 컬럼
price : 상품 가격 등 가격을 랜덤으로 넣어주는 컬럼(1000원~10000원까지 1천원 단위로 생성)
qty : 상품 수량 등 수량을 랜덤으로 넣어주는 컬럼
test1 : 일반 숫자 컬럼
test2 : 일반 문자 컬럼
test3 : 일반 문자와 랜덤 숫자를 합친 컬럼
데이터 삽입(10만건)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
SQL>
DECLARE
TYPE tbl_ins IS TABLE OF sample_t%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR i IN 1..100000 LOOP
w_ins(i).id1 := i;
w_ins(i).id2 := i||ceil(dbms_random.value(1, 10000000));
w_ins(i).name := dbms_random.string('x',5);
w_ins(i).date1 := round(dbms_random.value(2010,2021))||to_char(round(dbms_random.value(1,12)), 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date3 := to_date(round(dbms_random.value(2010,2021))||'-'||round(dbms_random.value(1,12))||'-'||round(dbms_random.value(1,28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');
w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
w_ins(i).price := ceil(dbms_random.value(1, 10))*1000;
w_ins(i).qty := ceil(dbms_random.value(1, 10));
w_ins(i).test1 := 1234;
w_ins(i).test2 := 'SQLP';
w_ins(i).test3 := 'A'||ceil(dbms_random.value(100, 999));
END LOOP;
FORALL i in 1..100000 INSERT INTO sample_t VALUES w_ins(i);
COMMIT;
END;
/
PL/SQL procedure successfully completed.
|
데이터 조회
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL> select * from sample_t where rownum <= 10;
ID1 ID2 NAME DATE1 DATE2 DATE3 PHONE PRICE QTY TEST1 TEST2 TEST
---------- ---------- ---------- -------- -------- ------------ ------------- ---------- ---------- ---------- ----- ----
354 3545727252 5VAX5 20191010 20210104 05-FEB-21 010-1672-1245 7000 7 1234 SQLP A410
355 3554538073 N78TP 20110615 20210604 07-OCT-19 010-7208-2070 3000 4 1234 SQLP A912
356 3564218134 N0901 20210318 20210622 20-MAY-21 010-3538-2899 5000 3 1234 SQLP A920
357 3579618053 1ONJ3 20130416 20210201 07-NOV-20 010-7276-9253 4000 4 1234 SQLP A547
358 3588664664 S8KSP 20110403 20210426 16-APR-19 010-5829-2943 7000 10 1234 SQLP A745
359 359330829 OIVPJ 20160626 20210220 14-AUG-19 010-3925-5858 6000 3 1234 SQLP A385
360 36038031 AHNC3 20141001 20210406 26-AUG-20 010-1471-4127 9000 5 1234 SQLP A597
361 3615691138 N7QLW 20100527 20211101 25-MAY-20 010-6096-7330 2000 5 1234 SQLP A435
362 3621206521 PUGQQ 20111202 20210927 03-JAN-19 010-3480-7358 2000 9 1234 SQLP A558
363 3632691153 MZGXU 20121005 20210708 26-FEB-20 010-1634-1662 10000 3 1234 SQLP A151
10 rows selected.
|
date 조회 포맷 변경 후 데이터 조회
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select * from sample_t where rownum <= 10;
ID1 ID2 NAME DATE1 DATE2 DATE3 PHONE PRICE QTY TEST1 TEST2 TEST
---------- ---------- ---------- -------- -------- ------------------- ------------- ---------- ---------- ---------- ----- ----
354 3545727252 5VAX5 20191010 20210104 2021-02-05 04:51:57 010-1672-1245 7000 7 1234 SQLP A410
355 3554538073 N78TP 20110615 20210604 2019-10-07 22:30:29 010-7208-2070 3000 4 1234 SQLP A912
356 3564218134 N0901 20210318 20210622 2021-05-20 23:01:37 010-3538-2899 5000 3 1234 SQLP A920
357 3579618053 1ONJ3 20130416 20210201 2020-11-07 07:48:52 010-7276-9253 4000 4 1234 SQLP A547
358 3588664664 S8KSP 20110403 20210426 2019-04-16 09:15:21 010-5829-2943 7000 10 1234 SQLP A745
359 359330829 OIVPJ 20160626 20210220 2019-08-14 08:40:23 010-3925-5858 6000 3 1234 SQLP A385
360 36038031 AHNC3 20141001 20210406 2020-08-26 03:21:08 010-1471-4127 9000 5 1234 SQLP A597
361 3615691138 N7QLW 20100527 20211101 2020-05-25 22:02:21 010-6096-7330 2000 5 1234 SQLP A435
362 3621206521 PUGQQ 20111202 20210927 2019-01-03 13:23:27 010-3480-7358 2000 9 1234 SQLP A558
363 3632691153 MZGXU 20121005 20210708 2020-02-26 11:41:51 010-1634-1662 10000 3 1234 SQLP A151
10 rows selected.
|
이렇게 샘플 데이터를 간편하게 대량으로 만들 수 있음
*추가
대량 데이터 삽입시 undo 에러 발생시(1백만건 생성)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
SQL>
DECLARE
TYPE tbl_ins IS TABLE OF sample_t%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR d IN 1..10 LOOP
FOR i IN 1..100000 LOOP
w_ins(i).id1 := (d - 1) * 100000 + i;
w_ins(i).id2 := i||ceil(dbms_random.value(1, 10000000));
w_ins(i).name := dbms_random.string('x',5);
w_ins(i).date1 := round(dbms_random.value(2010,2021))||to_char(round(dbms_random.value(1,12)), 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date3 := to_date(round(dbms_random.value(2019,2021))||'-'||round(dbms_random.value(1,12))||'-'||round(dbms_random.value(1,28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');
w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
w_ins(i).price := ceil(dbms_random.value(1, 10))*1000;
w_ins(i).qty := ceil(dbms_random.value(1, 10));
w_ins(i).test1 := 1234;
w_ins(i).test2 := 'SQLP';
w_ins(i).test3 := 'A'||ceil(dbms_random.value(100, 999));
END LOOP;
FORALL i in 1..100000 INSERT INTO sample_t VALUES w_ins(i);
COMMIT;
END LOOP;
END;
/
|
for문으로 한번 더 감싸서 벌크인서트가 10번 반복 실행되게끔 하면됨
참조 : https://positivemh.tistory.com/242
https://positivemh.tistory.com/183
'ORACLE > Sql' 카테고리의 다른 글
오라클 19c undo 상세 정보 html 로 확인 쿼리 (0) | 2022.06.28 |
---|---|
오라클 19c 테이블별 카운트 확인 쿼리(마이그레이션 검증용) (0) | 2022.03.14 |
오라클 19c 쉘 못붙는 경우 쿼리로 awr 리포트 html 형식으로 저장 (0) | 2022.02.10 |
오라클 19c controlfile 에 저장하고 있는 정보 확인 (0) | 2021.01.29 |
오라클 상세 모니터링 스크립트 (0) | 2020.12.28 |