UPDATE on column referenced in sub-query

Posted on

Question :

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.

Answer :

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.

Leave a Reply

Your email address will not be published. Required fields are marked *