Using a Materialized View with the Master table empty

Question

I was executing some tests with the concept of Materialized Views in Oracle and I wasn't able to answer this question, would a Materialized View fetch rows when the master table it was originally created from becomes empty? I have listed a sequence of events below to visualize what I have in mind,

Create a valid Materialized View based on a huge Master table

Here's an example

The Master table

CREATE TABLE master_tab
(                    
col1 varchar2(10),         
col2 varchar2(10), 
col3 varchar2(10),
tr_val1 number,
tr_val2 number,
tr_val3 number
)

The Materialized View definition

CREATE MATERIALIZED VIEW mview_test
BUILD IMMEDIATE 
REFRESH COMPLETE
ON DEMAND 
ENABLE QUERY REWRITE
AS SELECT col1, col2
SUM(tr_val1), SUM(tr_val2)
FROM master_tab
GROUP BY col1, col2

At a later point in time, for some reason, the master table master_tab gets truncated
So, now, only the Materialized View has any data relevant to any query on the Master table master_tab

I wasn't very conversant with what Oracle does behind the scenes to fetch the correct data. Now, what happens when a query gets executed from an application looking for data from the Master table? For example, what happens when an application queries,

SELECT col1, col2
SUM(tr_val1), SUM(tr_val2)
FROM master_tab
GROUP BY col1, col2

What kind of decisions does Oracle take internally to fetch data from Materialized Views?
Would Oracle still retrieve the correct data from the Materialized View even if the Master table is empty?
If the answer to #2 is yes, does it mean that as long as the Materialized View isn't refreshed again, Oracle will still be able to fetch data for all queries looking for data from the Master?

Thanks for your help

What sort of tests were executing that you couldn't run a test for this case? — APC
@APC - I have posted my question here after executing quite a number of tests. First, I wasn't sure if there was a way to validate if my application is indeed using the Materialized View for all relevant queries on the Master table. Second, to test the scenario in question, I truncated the Master only to see all my queries return no values at all. I couldn't make out if this is due to the application still using the Master table for all its data (in-spite of there being a Materialized View for a specific combination of columns) or due to a probable standard Materialized View behavior — Sofia

BA. BA. · Accepted Answer · 2017-09-11T06:38:45

To try to answer your questions:

To use query rewrite and have the data fetched from MV, the following is checked: a) Query re-write enabled (Session) b) Query re-write enabled (MV) and c) Re-write integrity check (I think this is where you need to control)

The above in addition to the check on the SQL itself if it can be rewritten using a MV (you can use DBMS_MVIEW.EXPLAIN_REWRITE on the SQL to advise if rewrite is possible and which MV are used

The integrity level QUERY_RESRITE_INTEGRITYis used to see if it is allowed to use query rewrite or not. The default is ENFORCED, and can be TRUSTED or STALE_TOLERATED. In your case, if it is TRUSTED or STALE_TOLERATED, the query would still be rewritten and the data will be retrieved from MV
I believe Yes, given 2

Using a Materialized View with the Master table empty

1 Answers