A table of boxes, with a capacity column, and a table of items with columns box and size, and you want to enforce the constraint that an item can't be put in a box of capacity smaller than the item.

AFAIK in oracle, this can be done with a materialised view, or a trigger. Which would perform better, and are there other reasons to use one rather than other?


A materialized view requires extra storage because it is a table.

You want to make your materialized view to be refreshed on commit (for an immediate check) and to be fast refreshable, as a complete refresh may take too long to wait for.

General Restrictions on Fast Refresh

Assuming multiple items can be placed in a single box, the sum of item sizes needs to be accounted for (aggregation), so :

Restrictions on Fast Refresh on Materialized Views with Aggregates

A materialized view requires materialized view logs (which are also tables, more extra storage + administration) on the source tables for fast refreshes.

Maintaining extra tables means generating more redo and undo as well.

A materialized view provides more functionality than you need, which you will never use.

What a trigger would perform on your DML operations (aggregate sizes of inserted/updated/deleted items), a materialized view refresh would perform as well.

When you remove an item, the materialized view needs to be refreshed, but you do not even need to execute the trigger on deletions.

When you update an item, the materialized view needs to be refreshed, but you can skip the aggregation in the trigger if :new.box_id = :old.box_id and :new.size <= :old.size.

Or even better, you can cache the size of boxes in memory (result cache) and access them in the trigger without performing any actual SQL aggregation.

Yes, some of the above seem insignificant, and they usually are, the difference may be unnoticeable. But with such a use case, a trigger can have the same effect as the materialized view combined with a check constraint, but with lower storage/administration overhead and requirements, so I would choose the trigger.

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.