Bulk updating multiple columns in oracle
The difference is much more drastic if each call to the SQL engine has to be done over the network from another process.
Again, the benchmark code is available from a gist, and I will paste it to the end of this blog post as well.
With larger data sets being updated, the two results will converge. While we're running individual row-by-row updates, we're sending all the update statements in one batch to the SQL engine.A requirement arises in many systems to update multiple SQL database rows.For small numbers of rows requiring updates, it can be adequate to use an UPDATE statement for each row that requires an update.But it often cannot, or it is too difficult to make it do so, so resorting to SQL is the way to go. 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 import