Why does DBMS_MVIEW.REFRESH have an implicit commit?

Question

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

Any ideas - except "because it is" - why does this action have implicit commit?

Answer 1

Depending on your version of Oracle and / or the parameters that you supply, dbms_mview.refresh may execute TRUNCATE followed by a direct load. TRUNCATE is a DDL command, and fixing is meant as such problems. Direct loading does not require fixing.

If you are using a newer version of Oracle, I think 10.2+, you can set atomic_refresh to TRUE and update in a single transaction using standard DELETE / INSERT. This method can be quite slow, though.

Answer 2

According to Tom Kit, this is because during development it was decided to consider updating the DDL operation . Since all DDL operations are implicitly committed, it does so. Unfortunately, he does not answer the question of why they decided to do this DDL.

Answer 3

Work arround - call dbms_mview.refresh in an autonomous transaction (create a PL / SQL procedure using pragma autonomous_transaction).