views:

1285

answers:

3

I noticed recently that calling dbms_mview.refresh(...), which refreshes materialized views in Oracle, has an implicit commit.

Any ideas - other than "because it does" - why this action has an implicit commit?

+2  A: 

According to Tom Kyte it is because a decision was made at design time to consider refreshing to be a DDL operation. Since all DDL operations implicitly commit, so does this one. Unfortunatly he doesn't answer the resulting question of why they choose to make it DDL.

Leigh Riffel
Tom is great, but he does tend to throw out these "because it does" answers.
Nick Pierpoint
Yes he does and they are usually to questions that no one else can answer either.
Leigh Riffel
A: 

Depending no your Oracle version and/or the parameters you supply dbms_mview.refresh may be doing a TRUNCATE followed by a direct load. TRUNCATE is a DDL command and as such issues an implicit commit. Direct load does not require a commit.

If you are using a more recent version of Oracle, I think 10.2+, you can set the atomic_refresh parameter to TRUE and it will refresh within a single transaction, using standard DELETE / INSERTs. This method could be quite a bit slower though.

David
An implicit commit is done even when atomic_refresh is true, but you are correct about the TRUNCATE/DELETE behavior.
Leigh Riffel
Thanks for the clarification.
David
Yep - I suppose the implicit commit makes the behaviour consistent regardless of the refresh mechanism.
Nick Pierpoint
A: 

A work arround is to do the call to dbms_mview.refresh in an autonomous transaction (create a PL/SQL procedure with pragma autonomous_transaction).

tuinstoel
Not in this case, I wanted to be able to rollback a refresh if the refreshed view itself was screwed up.
Nick Pierpoint