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?
Asked
Active
Viewed 8,317 times
6
-
Which version of Oracle are you using? – David May 06 '09 at 11:56
3 Answers
6
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
- 6,381
- 3
- 34
- 47
-
Tom is great, but he does tend to throw out these "because it does" answers. – Nick Pierpoint May 07 '09 at 22:52
-
Yes he does and they are usually to questions that no one else can answer either. – Leigh Riffel May 08 '09 at 13:21
2
Depending on 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.
Nick Pierpoint
- 17,641
- 9
- 46
- 74
David
- 1,315
- 7
- 8
-
1An implicit commit is done even when atomic_refresh is true, but you are correct about the TRUNCATE/DELETE behavior. – Leigh Riffel May 06 '09 at 18:42
-
Yep - I suppose the implicit commit makes the behaviour consistent regardless of the refresh mechanism. – Nick Pierpoint May 07 '09 at 22:54
0
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
- 7,248
- 27
- 27
-
Not in this case, I wanted to be able to rollback a refresh if the refreshed view itself was screwed up. – Nick Pierpoint May 07 '09 at 22:55