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.
Can you create indexes? Are TRANSFORMED_TYPE and/or TRANSFORMED_KEY unique?
The DB hast to compare xxx rows from “where workSet.MARK = 1” with yyy rows from “where source.SELECTABLE = 0 and source.MARK = 1” in subquery(FTS).
So you can calculate yourself: xxx * yyy rows = zzz rows to compare with full table scans.
This is faster but with indexes could perform even better.
update CE_WORKSET_READ_ENTRY workset set workSet.MARK = 0, workSet.SELECTABLE = 0 where workSet.MARK = 1 and exists ( select 1 from CE_WORKSET_READ_ENTRY source where source.TRANSFORMED_TYPE = workSet.TRANSFORMED_TYPE and source.TRANSFORMED_KEY =workSet.TRANSFORMED_KEY and source.SELECTABLE = 0 and source.MARK = 1);
If TRANSFORMED_TYPE and/or TRANSFORMED_KEY are unique then you can remove the subquery.