[hibernate-dev] 6.0 - multi-table mutations

[Steve Ebersole]

https://hibernate.zulipchat.com/#narrow/stream/132094-hibernate-orm-dev/topic/6.2E0.20-.20multi-table.20mutation

On Tue, Mar 26, 2019 at 8:25 AM Steve Ebersole <steve at hibernate.org> wrote:

> While working on 6 I discovered that we maybe do not do the best job in
> regards to "bulk update/delete" queries against multi-table entities
> (secondary tables, joined inheritance, etc).  This short-coming exists in
> previous versions.  Essentially, when a strategy is not explicitly
> specified we fallback to using an "id table" and performing the SQL updates
> or deletes using the id table as a sub-query.  The problem is that for some
> databases this breaks when the ids are composite due to the database not
> having complete support for tuples.  For example, H2 does not allow the
> sub-query for an in-predicate to return more than one column: so a query
> like `... where (id1, id2) in (select val1, val2 from ...)` will not work.
> This tuple concern is something I had not considered when I first wrote
> this feature.
>
> Obviously with 6 we have a chance to improve this.  So I have been
> thinking about some ways this might be improved.  The guiding principle
> here was to make this as implicit as possible...
>
> Because certain strategies will not work when the ids are composite, I
> think the first consideration is whether we want to allow the strategy to
> be definable per-entity.  The idea would be to allow for the most efficient
> strategy to be used generally (whatever that might be for the given
> app/database) but to pick an alternative strategy in cases where that
> "fallback" one will not work.  The Dialect should certainly play a part in
> this strategy selection.
>
> And lastly, we should consider whether it is beneficial to leverage these
> strategies when performing normal mutations (merge, persist, etc).  I think
> really this comes down to whether we think there is a benefit to handling
> these via CTE if the database supports that as opposed to sending
> individual updates or deletes against each table.
>
> Thoughts?
>