0

I have a couple of MVs in an Oracle 11g database which full-refresh swiftly when not atomic. A new requirement means that they must be.

I understand that when not-atomic DBMS_MVIEW.REFRESH performs a truncate before a series of inserts but when atomic => true all the records are deleted one by one first. Just deleting the records takes over an hour while the non-atomic version only a few minutes.

I need a way to allow queries to this MV to continue while I refresh it. The FAST refresh method is out of the question because the MV query uses counts and mosts records will have changes anyway.

So, the construct I've come up with is this:

LOCK TABLE FOOBAR_MVIEW_LOCK IN EXCLUSIVE MODE;
SELECT * FROM FOOBAR_MVIEW_DATA1,FOOBAR_MVIEW_DATA2;
COMMIT;

and

LOCK TABLE FOOBAR_MVIEW_LOCK IN EXCLUSIVE MODE;
EXECUTE DBMS_MVIEW.REFRESH( 'FOOBAR_MVIEW_DATA1,FOOBAR_MVIEW_DATA2','cc');
COMMIT;

Is there a better way? NB. My team leader gets nervous when I start talking about partition switching. :-)

John Colman
  • 1
  • 1
  • 1
  • I'm not sure that I understand the construct that you've come up with, how that helps solve the problem, what benefit the explicit lock provides, or what role a query that does a Cartesian join between the two tables has. Can you elaborate a bit on the proposed solution? – Justin Cave Nov 21 '12 at 23:05
  • The queries aren't the real queries, just to show the construct. The idea is that there is a process performing queries and another performing refreshes. As each of the sessions is forced to wait for the other to relinquish the lock, only one of them has control at any one time. – John Colman Nov 21 '12 at 23:16
  • So there is only one session that is ever running queries? – Justin Cave Nov 21 '12 at 23:18

1 Answers1

0

You note that your team leader got nervous when you started talking about partition switching. Does that indicate that you have licensed the partitioning option so that this would be a possibility if we can address whatever concerns the team lead has? Or does that indicate that we should discard that option?

If partition switching is not an option, the approach for would be to have a synonym that you flip from pointing to one table (or materialized view) to another. So, for example

  • Rename FOOBAR_MVIEW_DATA1 to FOOBAR_MVIEW_DATA1_BASE1
  • Create a synonym FOOBAR_MVIEW_DATA1 for FOOBAR_MVIEW_DATA1_BASE1
  • Create an empty table FOOBAR_MVIEW_DATA1_BASE2 that is structurally the same as FOOBAR_MVIEW_DATA1_BASE1
  • When you want to refresh the materialized view, insert the data into FOOBAR_MVIEW_DATA1_BASE2 and create the materialized view on the prebuilt table.
  • Once the data is refreshed, point the synonym FOOBAR_MVIEW_DATA1 at FOOBAR_MVIEW_DATA1_BASE2
  • At some future point when the existing queries are all complete, you can truncate FOOBAR_MVIEW_DATA1_BASE1. And you can either choose to rename FOOBAR_MVIEW_DATA1_BASE1 and FOOBAR_MVIEW_DATA1_BASE2 so that you always have the same starting point for a materialized view refresh (_BASE1 has data and _BASE2 is empty) or you can write the refresh code to check where the synonym is pointing and load whatever table isn't referenced by the synonym.
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • I believe that the reason is that we only have partition switching on the production databases, so dev and testing are problematic. Your synonym idea has been considered already but there are 2 MVs and I'm not sure if even a split second of inconsistency between them would be acceptable. I've simplified things in my example above but there are actually two queries retrieving distinct yet related data. The results need to match. If I could convince my TL to use partition exchange, would it be able to solve this? – John Colman Nov 21 '12 at 23:29
  • Correction; It's one query with a join across the two MVs. – John Colman Nov 21 '12 at 23:41