16f6c0aecf
Inline adoc files when they are included exactly once. Also fix language tags because this inlining gives us better information on what language the code is written in.
90 lines
1.9 KiB
Plaintext
90 lines
1.9 KiB
Plaintext
== Why is this an issue?
|
|
|
|
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
|
|
|
|
[source,sql]
|
|
----
|
|
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
|
|
|
|
[source,sql]
|
|
----
|
|
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;
|
|
----
|
|
|
|
ifdef::env-github,rspecator-view[]
|
|
|
|
'''
|
|
== Implementation Specification
|
|
(visible only on this page)
|
|
|
|
=== Message
|
|
|
|
Use "FORALL" instead of this DML query in a loop.
|
|
|
|
|
|
endif::env-github,rspecator-view[]
|