Materialized Views: Refreshing same MV by 2 calls to DBMS_MVIEW.REFRESH at the same time.

Current State:
 Currently we have a system that inserts records directly into a staging table directly after any of several tables are inserted, updated or deleted to/from. This is done by invoking the update functions in every trigger attached to these tables that force changes to the staging table. This staging table is part of a larger search.

Research:
My first response when seeing the code that creates the staging table was to as if there was a more elegant way to accomplish the same result. The existing code works but lacks documentation and the person who built it left the company without imparting knowledge of this to some else.

I have explored several different ways to deal with this issue. Some are unworkable due to how the application layer works. Currently I have two possibilities, leave it as it is or design a materialized view that will replace the staging table in question.

I've gone through the process of using the DBMS packages to determine if the Materialized view is possible and what type of refresh it could have.

Because I am on an OLTP database the materialized view must be refreshed prior to the user has accessing the search to account to any recent changes they have made. I know that I can do an ON_DEMAND refresh by calling DBMS_MV.REFRESH for the materialized view. I also know that I have to allow for 2 users going doing a search at the same time.

Question:
Does the DBMS Materialized view functionality has any issues with being called at the same time, by 2 different users?

References to the articles/documentation I have read available upon request.
Thanks for your time!
A.E. Harris Received on Wed May 18 2005 - 12:16:32 CDT