I know you're mostly concerned about UPDATE
and mostly about performance, but as a fellow "ORM" maintainer, let me give you another perspective on the problem of distinguishing between "changed", "null", and "default" values, which are three different things in SQL, but possibly only one thing in Java and in most ORMs:
Translating your rationale to INSERT
statements
Your arguments in favour of batchability and statement cacheability hold true in the same way for INSERT
statements as they do for UPDATE
statements. But in the case of INSERT
statements, omitting a column from the statement has a different semantics than in UPDATE
. It means to apply DEFAULT
. The following two are semantically equivalent:
INSERT INTO t (a, b) VALUES (1, 2);
INSERT INTO t (a, b, c) VALUES (1, 2, DEFAULT);
This isn't true for UPDATE
, where the first two are semantically equivalent, and the third one has an entirely different meaning:
-- These are the same
UPDATE t SET a = 1, b = 2;
UPDATE t SET a = 1, b = 2, c = c;
-- This is different!
UPDATE t SET a = 1, b = 2, c = DEFAULT;
Most database client APIs, including JDBC, and by consequence, JPA, don't allow for binding a DEFAULT
expression to a bind variable - mostly because the servers don't allow this either. If you want to re-use the same SQL statement for the aforementioned batchability and statement cacheability reasons, you'd use the following statement in both cases (assuming (a, b, c)
are all the columns in t
):
INSERT INTO t (a, b, c) VALUES (?, ?, ?);
And since c
isn't set, you'd probably bind Java null
to the third bind variable, because many ORMs also cannot distinguish between NULL
and DEFAULT
(jOOQ, for example being an exception here). They only see Java null
and don't know whether this means NULL
(as in the unknown value) or DEFAULT
(as in the uninitialised value).
In many cases, this distinction doesn't matter, but in case your column c is using any of the following features, the statement is simply wrong:
- It has a
DEFAULT
clause
- It might be generated by a trigger
Back to UPDATE
statements
While the above is true for all databases, I can assure you that the trigger issue is true for the Oracle database as well. Consider the following SQL:
CREATE TABLE x (a INT PRIMARY KEY, b INT, c INT, d INT);
INSERT INTO x VALUES (1, 1, 1, 1);
CREATE OR REPLACE TRIGGER t
BEFORE UPDATE OF c, d
ON x
BEGIN
IF updating('c') THEN
dbms_output.put_line('Updating c');
END IF;
IF updating('d') THEN
dbms_output.put_line('Updating d');
END IF;
END;
/
SET SERVEROUTPUT ON
UPDATE x SET b = 1 WHERE a = 1;
UPDATE x SET c = 1 WHERE a = 1;
UPDATE x SET d = 1 WHERE a = 1;
UPDATE x SET b = 1, c = 1, d = 1 WHERE a = 1;
When you run the above, you will see the following output:
table X created.
1 rows inserted.
TRIGGER T compiled
1 rows updated.
1 rows updated.
Updating c
1 rows updated.
Updating d
1 rows updated.
Updating c
Updating d
As you can see, the statement that always updates all the columns will always fire the trigger for all the columns, whereas the statements updating only columns that have changed will fire only those triggers who are listening for such specific changes.
In other words:
The current behaviour of Hibernate that you're describing is incomplete and could even be considered wrong in the presence of triggers (and probably other tools).
I personally think that your query cache optimisation argument is overrated in the case of dynamic SQL. Sure, there will be a few more queries in such a cache, and a bit more parsing work to be done, but this is usually not a problem for dynamic UPDATE
statements, much less than for SELECT
.
Batching is certainly an issue, but in my opinion, a single update shouldn't be normalised to update all the columns just because there is a slight possibility of the statement being batchable. Chances are, the ORM can collect sub-batches of consecutive identical statements and batch those instead of the "whole batch" (in case the ORM is even capable of tracking the difference between "changed", "null", and "default"
UPDATE
is practically equivalent to aDELETE
+INSERT
(because you actually create a new Version of the row). The overhead is high, and grows with the number of indexes, specially if many of the columns that comprise them are actually updated, and the tree (or whatever) used to represent the index needs a significant change. It's not the number of columns that are updated what's relevant, but whether you update a column part of an index. – joanolo Jun 18 '17 at 17:32