Create a gist now

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PL/SQL Benchmark Row-by-row Update vs FORALL vs Bulk Update
-- Copyright Data Geekery GmbH
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--
-- This version displays relative execution times (fastest execution = 1)
-- According to our understanding of Oracle licensing, such benchmark results may be published
-- as they cannot be compared to other databases and do not provide absolute time values
SET SERVEROUTPUT ON
DROP TABLE post;
CREATE TABLE post (
id INT NOT NULL PRIMARY KEY,
text VARCHAR2(1000) NOT NULL,
archived NUMBER(1) NOT NULL CHECK (archived IN (0, 1)),
creation_date DATE NOT NULL
);
CREATE INDEX post_creation_date_i ON post (creation_date);
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
CREATE TABLE results (
run NUMBER(2),
stmt NUMBER(2),
elapsed NUMBER
);
DECLARE
v_ts TIMESTAMP WITH TIME ZONE;
PROCEDURE reset_post IS
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE post';
INSERT INTO post
SELECT
level AS id,
lpad('a', 1000, 'a') AS text,
0 AS archived,
DATE '2017-01-01' + (level / 100) AS creation_date
FROM dual
CONNECT BY level <= 10000;
dbms_stats.gather_table_stats('TEST', 'POST');
END reset_post;
BEGIN
-- Repeat the whole benchmark several times to avoid warmup penalty
FOR r IN 1..5 LOOP
reset_post;
v_ts := SYSTIMESTAMP;
UPDATE post
SET archived = 1
WHERE archived = 0 AND creation_date < DATE '2018-01-01';
INSERT INTO results VALUES (r, 1, SYSDATE + ((SYSTIMESTAMP - v_ts) * 86400) - SYSDATE);
reset_post;
v_ts := SYSTIMESTAMP;
DECLARE
TYPE post_ids_t IS TABLE OF post.id%TYPE;
v_post_ids post_ids_t;
BEGIN
SELECT id
BULK COLLECT INTO v_post_ids
FROM post
WHERE archived = 0 AND creation_date < DATE '2018-01-01';
FORALL i IN 1 .. v_post_ids.count
UPDATE post
SET archived = 1
WHERE id = v_post_ids(i);
END;
INSERT INTO results VALUES (r, 2, SYSDATE + ((SYSTIMESTAMP - v_ts) * 86400) - SYSDATE);
reset_post;
v_ts := SYSTIMESTAMP;
FOR rec IN (
SELECT id
FROM post
WHERE archived = 0 AND creation_date < DATE '2018-01-01'
) LOOP
UPDATE post
SET archived = 1
WHERE id = rec.id;
END LOOP;
INSERT INTO results VALUES (r, 3, SYSDATE + ((SYSTIMESTAMP - v_ts) * 86400) - SYSDATE);
END LOOP;
FOR rec IN (
SELECT
run, stmt,
CAST(elapsed AS NUMBER(10, 5)) ratio,
CAST(AVG(elapsed) OVER (PARTITION BY stmt) AS NUMBER(10, 5)) avg_ratio
FROM results
ORDER BY run, stmt
)
LOOP
dbms_output.put_line('Run ' || rec.run ||
', Statement ' || rec.stmt ||
' : ' || rec.ratio || ' (avg : ' || rec.avg_ratio || ')');
END LOOP;
dbms_output.put_line('');
dbms_output.put_line('Copyright Data Geekery GmbH');
dbms_output.put_line('https://www.jooq.org/benchmark');
END;
/
DROP TABLE results;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment