CREATE TABLE XXAA_INSERT_TEST (
id NUMBER(10),
code VARCHAR2(10),
description VARCHAR2(50));
ALTER TABLE XXAA_INSERT_TEST ADD (
CONSTRAINT XXAA_INSERT_TEST_pk PRIMARY KEY (id));
ALTER TABLE XXAA_INSERT_TEST ADD (
CONSTRAINT XXAA_INSERT_TEST_uk UNIQUE (code));
SET SERVEROUTPUT ON;
--------------------------------------------------------------------------------
DECLARE
TYPE t_XXAA_INSERT_TEST_tab IS TABLE OF XXAA_INSERT_TEST%ROWTYPE;
l_tab t_XXAA_INSERT_TEST_tab := t_XXAA_INSERT_TEST_tab();
l_start NUMBER;
l_size NUMBER := 10000;
BEGIN
-- Populate collection.
FOR i IN 1 .. l_size
LOOP
l_tab.extend;
l_tab(l_tab.last).id :=
i;
l_tab(l_tab.last).code :=
TO_CHAR(i);
l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
END LOOP;
EXECUTE IMMEDIATE 'TRUNCATE TABLE
XXAA_INSERT_TEST';
-- Time regular inserts.
l_start := DBMS_UTILITY.get_time;
FOR i IN l_tab.first .. l_tab.last LOOP
INSERT INTO XXAA_INSERT_TEST (id, code, description)
VALUES (l_tab(i).id, l_tab(i).code, l_tab(i).description);
END LOOP;
DBMS_OUTPUT.put_line('Normal Inserts: ' || (DBMS_UTILITY.get_time - l_start));
EXECUTE IMMEDIATE 'TRUNCATE TABLE
XXAA_INSERT_TEST';
-- Time bulk inserts.
l_start := DBMS_UTILITY.get_time;
FORALL i IN l_tab.first .. l_tab.last
INSERT INTO XXAA_INSERT_TEST VALUES l_tab(i);
DBMS_OUTPUT.put_line('Bulk Inserts : ' || (DBMS_UTILITY.get_time - l_start));
COMMIT;
END;
No comments:
Post a Comment