Hey ODC friends ,
I create a test table named "staff" to simulate quickly/fast insert 1e7 rows of random data on oracle 19.3 of my laptop on Windows 10. Here I use the following 2 methods (take a looking at my demo: SQL code and Running process) to respectively compare their spending time.
- for ... loop
- bulk collect ... forall ...
1. for ... loop (code)
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
DROP SEQUENCE seq_sid;
DROP TABLE staff;
CREATE SEQUENCE seq_sid;
CREATE TABLE staff
( id NUMBER DEFAULT seq_sid.NEXTVAL
, name VARCHAR2(6) NOT NULL
, sex NUMBER(1) NOT NULL
, birth_day DATE NOT NULL
, address VARCHAR2(16) NOT NULL
, email VARCHAR2(15) NOT NULL
, qq NUMBER(9) NOT NULL
, CONSTRAINT staff_uq UNIQUE (id)
, CONSTRAINT staff_pk PRIMARY KEY (id, name)
);
ALTER TABLE staff NOLOGGING;
BEGIN
FOR i IN 1 .. 1000
LOOP
INSERT /*+ APPEND */ INTO staff (name, sex, birth_day, address, email, qq)
SELECT DBMS_RANDOM.string('A', 6) AS name
, ROUND(DBMS_RANDOM.value(0, 1)) AS sex
, TO_DATE('1977-06-14', 'YYYY-MM-DD') + TRUNC(DBMS_RANDOM.value(-4713, 9999)) AS birth_day
, DBMS_RANDOM.string('L', 16) AS address
, DBMS_RANDOM.string('L', 6) || '@' || DBMS_RANDOM.string('L', 4) || '.com' AS email
, DBMS_RANDOM.value(10000001, 999999999) AS qq
FROM dual
CONNECT BY level <= 1e4;
COMMIT;
END LOOP;
END;
/
ALTER TABLE staff LOGGING;
SELECT COUNT(*) FROM staff;
SELECT COUNT(DISTINCT (name)) FROM staff;
2. for ... loop (running process)
17:14:49 C##QWZ@ora19c> ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
Session altered.
17:15:04 C##QWZ@ora19c>
17:15:04 C##QWZ@ora19c> DROP SEQUENCE seq_sid;
DROP SEQUENCE seq_sid
*
ERROR at line 1:
ORA-02289: sequence does not exist
17:15:04 C##QWZ@ora19c> DROP TABLE staff;
DROP TABLE staff
*
ERROR at line 1:
ORA-00942: table or view does not exist
17:15:04 C##QWZ@ora19c>
17:15:04 C##QWZ@ora19c> CREATE SEQUENCE seq_sid;
Sequence created.
17:15:04 C##QWZ@ora19c>
17:15:04 C##QWZ@ora19c> CREATE TABLE staff
17:15:04 2 ( id NUMBER DEFAULT seq_sid.NEXTVAL
17:15:04 3 , name VARCHAR2(6) NOT NULL
17:15:04 4 , sex NUMBER(1) NOT NULL
17:15:04 5 , birth_day DATE NOT NULL
17:15:04 6 , address VARCHAR2(16) NOT NULL
17:15:04 7 , email VARCHAR2(15) NOT NULL
17:15:04 8 , qq NUMBER(9) NOT NULL
17:15:04 9 , CONSTRAINT staff_uq UNIQUE (id)
17:15:04 10 , CONSTRAINT staff_pk PRIMARY KEY (id, name)
17:15:04 11 );
Table created.
17:15:04 C##QWZ@ora19c>
17:15:04 C##QWZ@ora19c> ALTER TABLE staff NOLOGGING;
Table altered.
17:15:05 C##QWZ@ora19c> BEGIN
17:15:20 2 FOR i IN 1 .. 1000
17:15:20 3 LOOP
17:15:20 4 INSERT /*+ APPEND */ INTO staff (name, sex, birth_day, address, email, qq)
17:15:20 5 SELECT DBMS_RANDOM.string('A', 6) AS name
17:15:20 6 , ROUND(DBMS_RANDOM.value(0, 1)) AS sex
17:15:20 7 , TO_DATE('1977-06-14', 'YYYY-MM-DD') + TRUNC(DBMS_RANDOM.value(-4713, 9999)) AS birth_day
17:15:20 8 , DBMS_RANDOM.string('L', 16) AS address
17:15:20 9 , DBMS_RANDOM.string('L', 6) || '@' || DBMS_RANDOM.string('L', 4) || '.com' AS email
17:15:20 10 , DBMS_RANDOM.value(10000001, 999999999) AS qq
17:15:20 11 FROM dual
17:15:20 12 CONNECT BY level <= 1e4;
17:15:20 13 COMMIT;
17:15:20 14 END LOOP;
17:15:20 15 END;
17:15:20 16 /
PL/SQL procedure successfully completed.
17:31:42 C##QWZ@ora19c> ALTER TABLE staff LOGGING;
Table altered.
17:37:00 C##QWZ@ora19c>
17:37:00 C##QWZ@ora19c> SELECT COUNT(*) FROM staff;
COUNT(*)
----------
10000000
17:37:00 C##QWZ@ora19c>
17:37:00 C##QWZ@ora19c> SELECT COUNT(DISTINCT (name)) FROM staff;
COUNT(DISTINCT(NAME))
---------------------
9997447
17:37:14 C##QWZ@ora19c>
3. bulk collect ... forall ... (code)
ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
DROP SEQUENCE seq_sid;
DROP TABLE staff;
CREATE SEQUENCE seq_sid;
CREATE TABLE staff
( id NUMBER DEFAULT seq_sid.NEXTVAL
, name VARCHAR2(6) NOT NULL
, sex NUMBER(1) NOT NULL
, birth_day DATE NOT NULL
, address VARCHAR2(16) NOT NULL
, email VARCHAR2(15) NOT NULL
, qq NUMBER(9) NOT NULL
, CONSTRAINT staff_uq UNIQUE (id)
, CONSTRAINT staff_pk PRIMARY KEY (id, name)
);
ALTER TABLE staff NOLOGGING;
INSERT /*+ APPEND */ INTO staff (name, sex, birth_day, address, email, qq)
SELECT DBMS_RANDOM.string('A', 6) AS name
, ROUND(DBMS_RANDOM.value(0, 1)) AS sex
, TO_DATE('1977-06-14', 'YYYY-MM-DD') + TRUNC(DBMS_RANDOM.value(-4713, 9999)) AS birth_day
, DBMS_RANDOM.string('L', 16) AS address
, DBMS_RANDOM.string('L', 6) || '@' || DBMS_RANDOM.string('L', 4) || '.com' AS email
, DBMS_RANDOM.value(10000001, 999999999) AS qq
FROM dual
CONNECT BY level <= 1e4
;
COMMIT;
DECLARE
CURSOR c_staff IS SELECT * FROM staff;
TYPE c_staff_t IS TABLE OF c_staff%ROWTYPE INDEX BY PLS_INTEGER;
cst c_staff_t;
BEGIN
OPEN c_staff;
FETCH c_staff BULK COLLECT INTO cst;
CLOSE c_staff;
FOR i IN 1 .. 999
LOOP
FORALL j IN cst.FIRST .. cst.LAST
INSERT /*+ APPEND */ INTO staff (id, name, sex, birth_day, address, email, qq)
VALUES ( DEFAULT
, cst(j).name
, cst(j).sex
, cst(j).birth_day
, cst(j).address
, cst(j).email
, cst(j).qq
);
COMMIT;
END LOOP;
END;
/
ALTER TABLE staff LOGGING;
SELECT COUNT(*) FROM staff;
SELECT COUNT(DISTINCT (name)) FROM staff;
4. bulk collect ... forall ... (running process)
16:55:39 C##QWZ@ora19c> ALTER SESSION SET nls_date_format = 'YYYY-MM-DD';
Session altered.
17:00:21 C##QWZ@ora19c>
17:00:21 C##QWZ@ora19c> DROP SEQUENCE seq_sid;
DROP SEQUENCE seq_sid
*
ERROR at line 1:
ORA-02289: sequence does not exist
17:00:22 C##QWZ@ora19c> DROP TABLE staff;
DROP TABLE staff
*
ERROR at line 1:
ORA-00942: table or view does not exist
17:00:22 C##QWZ@ora19c>
17:00:22 C##QWZ@ora19c> CREATE SEQUENCE seq_sid;
Sequence created.
17:00:22 C##QWZ@ora19c>
17:00:22 C##QWZ@ora19c> CREATE TABLE staff
17:00:22 2 ( id NUMBER DEFAULT seq_sid.NEXTVAL
17:00:22 3 , name VARCHAR2(6) NOT NULL
17:00:22 4 , sex NUMBER(1) NOT NULL
17:00:22 5 , birth_day DATE NOT NULL
17:00:22 6 , address VARCHAR2(16) NOT NULL
17:00:22 7 , email VARCHAR2(15) NOT NULL
17:00:22 8 , qq NUMBER(9) NOT NULL
17:00:22 9 , CONSTRAINT staff_uq UNIQUE (id)
17:00:22 10 , CONSTRAINT staff_pk PRIMARY KEY (id, name)
17:00:22 11 );
Table created.
17:00:22 C##QWZ@ora19c>
17:00:22 C##QWZ@ora19c> ALTER TABLE staff NOLOGGING;
Table altered.
17:00:24 C##QWZ@ora19c>
17:00:24 C##QWZ@ora19c> INSERT /*+ APPEND */ INTO staff (name, sex, birth_day, address, email, qq)
17:00:24 2 SELECT DBMS_RANDOM.string('A', 6) AS name
17:00:24 3 , ROUND(DBMS_RANDOM.value(0, 1)) AS sex
17:00:24 4 , TO_DATE('1977-06-14', 'YYYY-MM-DD') + TRUNC(DBMS_RANDOM.value(-4713, 9999)) AS birth_day
17:00:24 5 , DBMS_RANDOM.string('L', 16) AS address
17:00:24 6 , DBMS_RANDOM.string('L', 6) || '@' || DBMS_RANDOM.string('L', 4) || '.com' AS email
17:00:24 7 , DBMS_RANDOM.value(10000001, 999999999) AS qq
17:00:24 8 FROM dual
17:00:24 9 CONNECT BY level <= 1e4
17:00:24 10 ;
10000 rows created.
17:00:36 C##QWZ@ora19c> COMMIT;
Commit complete.
17:00:48 C##QWZ@ora19c> DECLARE
17:00:58 2 CURSOR c_staff IS SELECT * FROM staff;
17:00:58 3 TYPE c_staff_t IS TABLE OF c_staff%ROWTYPE INDEX BY PLS_INTEGER;
17:00:58 4 cst c_staff_t;
17:00:58 5 BEGIN
17:00:58 6 OPEN c_staff;
17:00:58 7 FETCH c_staff BULK COLLECT INTO cst;
17:00:58 8 CLOSE c_staff;
17:00:58 9
17:00:58 10 FOR i IN 1 .. 999
17:00:58 11 LOOP
17:00:58 12 FORALL j IN cst.FIRST .. cst.LAST
17:00:58 13 INSERT /*+ APPEND */ INTO staff (id, name, sex, birth_day, address, email, qq)
17:00:58 14 VALUES ( DEFAULT
17:00:58 15 , cst(j).name
17:00:58 16 , cst(j).sex
17:00:58 17 , cst(j).birth_day
17:00:58 18 , cst(j).address
17:00:58 19 , cst(j).email
17:00:58 20 , cst(j).qq
17:00:58 21 );
17:00:58 22 COMMIT;
17:00:58 23 END LOOP;
17:00:58 24 END;
17:00:58 25 /
PL/SQL procedure successfully completed.
17:07:00 C##QWZ@ora19c>
17:07:07 C##QWZ@ora19c> ALTER TABLE staff LOGGING;
Table altered.
17:09:20 C##QWZ@ora19c>
17:09:20 C##QWZ@ora19c> SELECT COUNT(*) FROM staff;
COUNT(*)
----------
10000000
17:11:43 C##QWZ@ora19c> SELECT COUNT(DISTINCT (name)) FROM staff;
COUNT(DISTINCT(NAME))
---------------------
10000
17:11:51 C##QWZ@ora19c>
Unfortunately the
1st method (
for ... loop) has spent about
16 mins creating table "staff" (column ID is
automatically created and other columns are
randomly created), the
2nd method just spent about
6 mins creating table (firstly I only insert
1e4 lines of random data, and secondly
repeatedly insert the previous 1e4 lines of data using
forall on PL/SQL code).
I always think that FORALL must relies on an existing table to create a record type, if so, data being inserted are obviously duplicate (not unique). How to make the inserted data keep unique when using FORALL statement? In other words forall is not specially depending on a temp table?
Or have you other good idea about how to quickly insert 1e7 rows of random data using more short time (based on after creating the structure of test table "staff")?
Best Regards
Quanwen Zhao