2

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.

  • 1
    Did you try an exists query instead of the in? Usually the oracle optimizer gets this right, but sometimes the exists equivalent leads to a faster plan. But without any indexes this will never be really fast. – a_horse_with_no_name Dec 23 '15 at 10:27
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.