Re: ASSM bug: slow INSERT after uncomitted DELETE

>
> I wouldn't agree to that entirely. In general a commit should be used
> along the logical transaction, and there are certainly cases where you
> want to "reload" a table as part of a larger transaction, and only if
> everything else succeeds perform a final commit. You don't want to see
> other sessions an intermediate state of the on-going transaction, that
> might lead to other undesirable side-effects.

Yes of course. But if you abstract special cases you mentioned, you will notice that the vast majority of design advice is against such practices. Of course there are *always* exceptions. Those however do not make up a general case.

To use a general rule of "ASSM is not for DW" is IMHO a bit too general, particularly when the cases supporting it are very specific.

> Also think of materialized view refresh groups that by default use the
> atomic refresh option. In case of a complete refresh these also will
> delete the whole table and re-insert the data in a single transaction
> without any intermediate commits.

Again, a special case.
>
> However I agree that doing so (delete all rows + re-insert within a
> single transaction) with very large tables and/or a large number of
> indexes will potentially pose problems, regardless of ASSM being used
> or not.
>

Exactly.

> One approach that allows to efficiently "replace" table contents
> almost in an "atomic" way is to use partitioning with the exchange
> table feature.

Yes but as you noted, partitioning is not only a special case, but also a paid for option: it doesn't come with standard Oracle nor Enterprise Edition. Not everyone can use it. And partitioning is not always practical to use. Received on Sun Aug 16 2009 - 06:00:43 CDT