The performance of DML queries in loops can be improved by placing them in a FORALL statement. This way, queries will be sent in bulk,
minimizing the number of context switches between PL/SQL and SQL.
Noncompliant code example
SET SERVEROUTPUT ON
CREATE TABLE largeTable(
foo VARCHAR2(42)
);
BEGIN
FOR i IN 1 .. 100000 LOOP
INSERT INTO largeTable VALUES('bar' || i); -- Non-compliant
END LOOP;
END;
/
SET TIMING ON
DECLARE
TYPE largeTableRowArrayType IS TABLE OF largeTable%ROWTYPE;
largeTableRowArray largeTableRowArrayType;
BEGIN
SELECT * BULK COLLECT INTO largeTableRowArray FROM largeTable;
EXECUTE IMMEDIATE 'TRUNCATE TABLE largeTable';
FOR i IN largeTableRowArray.FIRST .. largeTableRowArray.LAST LOOP
INSERT INTO largeTable (foo) VALUES (largeTableRowArray(i).foo); -- Non-compliant
END LOOP;
END;
/
SET TIMING OFF
DROP TABLE largeTable;
Compliant solution
SET SERVEROUTPUT ON
CREATE TABLE largeTable(
foo VARCHAR2(42)
);
BEGIN
FOR i IN 1 .. 100000 LOOP
INSERT INTO largeTable VALUES('bar' || i); -- Non-compliant
END LOOP;
END;
/
SET TIMING ON
DECLARE
TYPE largeTableRowArrayType IS TABLE OF largeTable%ROWTYPE;
largeTableRowArray largeTableRowArrayType;
BEGIN
SELECT * BULK COLLECT INTO largeTableRowArray FROM largeTable;
EXECUTE IMMEDIATE 'TRUNCATE TABLE largeTable';
FORALL i IN largeTableRowArray.FIRST .. largeTableRowArray.LAST
INSERT INTO largeTable (foo) VALUES (largeTableRowArray(i).foo); -- Compliant
INSERT INTO largeTable (foo) VALUES ('baz'); -- Compliant, not in a loop
END;
/
SET TIMING OFF
DROP TABLE largeTable;
