Bulk Collect speeds

Quick example

--------------------------------------------------------
--  Quick test on insert rates Oracle DB
--------------------------------------------------------

SET TIMING ON;
SET ECHO ON;
SET LINES 200;

--------------------------------------------------------
--  DDL for Table LOG_DATA
--------------------------------------------------------

  CREATE TABLE "LOG_DATA" 
   (	
	"ID" NUMBER, 
	"DATA1" VARCHAR2(255 ), 
	"DATA2" VARCHAR2(255 ), 
	"SENSOR_NAME" VARCHAR2(50 ), 
	"ANGLE_INFO" VARCHAR2(50 ), 
	"INDATE" DATE
   )
;
--------------------------------------------------------
--  Create the SOME_DATA Table with a CTAS
--------------------------------------------------------
CREATE TABLE SOME_DATA AS SELECT * FROM LOG_DATA WHERE 1 = 2;


--------------------------------------------------------
--   Generate some random data into the SOME_DATA table 
--------------------------------------------------------
BEGIN 
FOR I IN 1..50000 LOOP
INSERT INTO SOME_DATA 
	(ID,DATA1,DATA2,SENSOR_NAME,ANGLE_INFO,INDATE)
VALUES
	((SELECT DBMS_RANDOM.VALUE(50000000, 999999999) FROM DUAL),(SELECT DBMS_RANDOM.VALUE(50000000, 999999999) FROM DUAL),(SELECT DBMS_RANDOM.VALUE(50000000, 999999999) FROM DUAL),(SELECT DBMS_RANDOM.VALUE(50000000, 999999999) FROM DUAL),(SELECT DBMS_RANDOM.VALUE(50000000, 999999999) FROM DUAL),SYSDATE)
;
COMMIT;
END LOOP;
END;
/



--------------------------------------------------------
-- Do a quick insert...no primary key constraint
-- I average about .35 seconds on my VirtualBox Linux image
--------------------------------------------------------
DECLARE
 CURSOR C_SOME_DATA IS
	SELECT /*+ PARALLEL */ * FROM SOME_DATA;
	
TYPE FETCH_ARRAY IS TABLE OF C_SOME_DATA%ROWTYPE;
S_ARRAY FETCH_ARRAY;

BEGIN
	OPEN C_SOME_DATA;
	LOOP
		FETCH C_SOME_DATA BULK COLLECT INTO S_ARRAY LIMIT 50000;
		
		FORALL I IN 1..S_ARRAY.COUNT 
		INSERT INTO LOG_DATA VALUES S_ARRAY(I);
			
		EXIT WHEN C_SOME_DATA%NOTFOUND;
	END LOOP;
	CLOSE C_SOME_DATA;
	COMMIT;
END;
/


--------------------------------------------------------
-- Create a simple primary key constraint on log_data 
--------------------------------------------------------
TRUNCATE TABLE LOG_DATA;
ALTER TABLE "LOG_DATA" ADD CONSTRAINT SINGLE_PK PRIMARY KEY("ID") ;

--------------------------------------------------------
-- Do a quick insert again...
-- I average about .80 seconds on my VirtualBox Linux image
--------------------------------------------------------
DECLARE
 CURSOR C_SOME_DATA IS
	SELECT /*+ PARALLEL */ * FROM SOME_DATA;
	
TYPE FETCH_ARRAY IS TABLE OF C_SOME_DATA%ROWTYPE;
S_ARRAY FETCH_ARRAY;

BEGIN
	OPEN C_SOME_DATA;
	LOOP
		FETCH C_SOME_DATA BULK COLLECT INTO S_ARRAY LIMIT 50000;
		
		FORALL I IN 1..S_ARRAY.COUNT 
		INSERT INTO LOG_DATA VALUES S_ARRAY(I);
			
		EXIT WHEN C_SOME_DATA%NOTFOUND;
	END LOOP;
	CLOSE C_SOME_DATA;
	COMMIT;
END;
/


--------------------------------------------------------
--  Create a n log_data 
--------------------------------------------------------
DROP TABLE LOG_DATA PURGE;
CREATE TABLE "LOG_DATA" 
   (	"ID" NUMBER, 
	"DATA1" VARCHAR2(255 ), 
	"DATA2" VARCHAR2(255 ), 
	"SENSOR_NAME" VARCHAR2(50 ), 
	"ANGLE_INFO" VARCHAR2(50 ), 
	"INDATE" DATE,
	CONSTRAINT MULTI_PK PRIMARY KEY (ID, DATA1,DATA2,SENSOR_NAME));
  )
;

--------------------------------------------------------
-- Do a quick insert 
-- I average about 1.5 seconds on my VirtualBox Linux image 
--------------------------------------------------------
TRUNCATE TABLE LOG_DATA;
DECLARE
 CURSOR C_SOME_DATA IS
	SELECT /*+ PARALLEL */ * FROM SOME_DATA;
	
TYPE FETCH_ARRAY IS TABLE OF C_SOME_DATA%ROWTYPE;
S_ARRAY FETCH_ARRAY;

BEGIN
	OPEN C_SOME_DATA;
	LOOP
		FETCH C_SOME_DATA BULK COLLECT INTO S_ARRAY LIMIT 50000;
		
		FORALL I IN 1..S_ARRAY.COUNT 
		INSERT INTO LOG_DATA VALUES S_ARRAY(I);
			
		EXIT WHEN C_SOME_DATA%NOTFOUND;
	END LOOP;
	CLOSE C_SOME_DATA;
	COMMIT;
END;
/

--------------------------------------------------------
-- Let's try it again with the PK Constraint disabled 
-- I average about .45 seconds on my VirtualBox Linux image 
--------------------------------------------------------
TRUNCATE TABLE LOG_DATA;

ALTER TABLE LOG_DATA DISABLE CONSTRAINT MULTI_PK;

DECLARE
 CURSOR C_SOME_DATA IS
	SELECT /*+ PARALLEL */ * FROM SOME_DATA;
	
TYPE FETCH_ARRAY IS TABLE OF C_SOME_DATA%ROWTYPE;
S_ARRAY FETCH_ARRAY;

BEGIN
	OPEN C_SOME_DATA;
	LOOP
		FETCH C_SOME_DATA BULK COLLECT INTO S_ARRAY LIMIT 50000;
		
		FORALL I IN 1..S_ARRAY.COUNT 
		INSERT INTO LOG_DATA VALUES S_ARRAY(I);
			
		EXIT WHEN C_SOME_DATA%NOTFOUND;
	END LOOP;
	CLOSE C_SOME_DATA;
	COMMIT;
END;
/