MV complete refresh transactions
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 9 Sep 2008 15:16:55 -0700
Just ran into something unexpected.
When doing a complete refresh on a materialized view, the deletion of the
rows
appears to be a different transaction than the insertion of new rows.
This is not an issue that is affected by ATOMIC_REFRESH => [TRUE|FALSE]
If I do a complete refresh on an MV in one session, a query against the MV
from
another session returns 0 rows for a period of time while the data is being
refreshed.
This is on 9.2.0.6 on Linux.
Until and unless I find a workaround for MV, this is being built as a
regular table,
with a procedure using a transaction to delete and insert rows every 15
minutes.
The manual method works as expected, no interruptions from user queries.
The refresh must be complete BTW, no way around it.
Anyone know a way around the MV issue?
The manual method works, it just is somewhat of a kludge.
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Other related posts:
