We have a query that updates a column for all rows matching a condition which involves a subselect on said column, like the following:
update CE_WORKSET_READ_ENTRY workset set workSet.MARK = 0, workSet.SELECTABLE = 0 where workSet.MARK = 1 and (workSet.TRANSFORMED_TYPE, workSet.TRANSFORMED_KEY) in ( select source.TRANSFORMED_TYPE, source.TRANSFORMED_KEY from CE_WORKSET_READ_ENTRY source where source.SELECTABLE = 0 and source.MARK = 1);
I expect the database to evaluate the condition on the 'old' state, which is indeed the case. However, the statement takes surprisingly long to complete as the number of updated elements grows. Why is this?
Additional infos: there are no indexes, no primary keys, it is used as a temporary store like a queue. The table held ~100'000 entries when the update took extremely long. Unfortunately, I can no longer reproduce the original explain plan since the data is now gone.