CODEWITHSUNDEEP
javaspringoracle-databasejdbc
Michael M
Michael M

Reputation: 11

Access (Oracle) Materialized View from Java while MView is beeing refreshed

I have a MView that, since it does involve many joins and is pretty complex, gets refreshed on demand by calling DBMS_MVIEW.REFRESH (asynchronously) from a (Spring-/Hibernate-based) Java Web-App like this:

this.getEntityManager().createNativeQuery("{call DBMS_MVIEW.REFRESH('AccountManagerView', method => 'C', PARALLELISM => 4)}");

Up to here everything is working fine.

The only issue that I have is that while the MView is beeing refreshed other callers trying to read from the MView are blocked, thus waiting until the refresh is done.

The funny thing is, that I can access the MView from SQL developer. If I do a select from there, the call does NOT wait for the refresh to be finished.

I made sure that the (read) call is done in a fresh Transaction with Oracle's default isolation level und read-only = true. Unfortunately it still blocks...

Since I can access the data from SQL Developer there should be a way to achieve the same from the Java-code....

Upvotes: 0

Views: 1480

Answers (1)

Michael M
Michael M

Reputation: 11

Oracle 12g introduced a new refresh option for materialized views

out_of_place=>true

Using this option oracle builds a complete new table with new data in the background and when it's done switches the "pointer" of the MView to this table and drops the old one.

If I use this option when refreshing the view my Java code has access to the (possibly old) data in the old view which is the desired behaviour.

So this cool new parameter (which was new for me) solved my problem!

Upvotes: 1

Related Questions