I encountered a problem related to forcing the refresh procedure on the materialized view in a combined manner:
- refresh_method = 'F'
- out_of_place = true
DBMS_MVIEW.REFRESH('FOO_MV', out_of_place => true, atomic_refresh => false, method => 'F');
For the past few days, I have made many different attempts and tests to force a situation in which MV is refreshed using a combination of:
refresh-method = FAST and out-of-place = TRUE
but only succeeded in achieving the combinations:
refresh-method = COMPLETE and out-of-place = TRUE
refresh-method = FAST and out-of-place = FALSE
Therefore, my main question is:
Are there any internal restrictions or conditions that must be met in order to perform FAST out-of-place refresh?Because after reviewing the official documentation:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dwhsg/refreshing-materialized-views.html#GUID-51191C38-D52F-4A4D-B6FF-E631965AD69A I have not found anything that would prevent such a combination from succeeding in my case.
It is even clearly stated that out-of-place should work with any refresh method, with FAST preferred first.
Below I attach a script setting up and demonstrating the problem I am facing. Due to limited privileges in the LiveSQL tool, I recommend using the script on a local database
-- Clean Workspace
DROP TABLE FOO;
DROP MATERIALIZED VIEW FOO_MV;
-- Create the Base Table FOO
CREATE TABLE FOO (
product_id NUMBER PRIMARY KEY,
product_name VARCHAR2(100),
product_price NUMBER(10, 2)
);
-- Insert Sample Data into FOO table
INSERT INTO FOO (product_id, product_name, product_price) VALUES (1, 'Widget A', 19.99);
INSERT INTO FOO (product_id, product_name, product_price) VALUES (2, 'Gizmo B', 29.99);
COMMIT;
-- Create Materialized View Log
CREATE MATERIALIZED VIEW LOG ON FOO
WITH ROWID, PRIMARY KEY, SEQUENCE;
-- Create simple Materialized View
CREATE MATERIALIZED VIEW FOO_MV
BUILD DEFERRED
REFRESH FAST ON DEMAND
AS
SELECT
product_id,
product_name,
product_price
FROM FOO;
-- Drop PK on MV prebuilt table to meet out-of-place refresh requirements
ALTER TABLE FOO_MV DROP PRIMARY KEY;
-- Enable Advanced statistics collection
EXEC DBMS_MVIEW_STATS.SET_MVREF_STATS_PARAMS ('FOO_MV','ADVANCED',30);
-- Intial COMPLETE refresh of the Materialized View (out-of-place)
EXEC DBMS_MVIEW.REFRESH('FOO_MV', out_of_place => true, atomic_refresh => false, method => 'C');
-- Insert incremental sample data into FOO table
INSERT INTO FOO (product_id, product_name, product_price) VALUES (3, 'Gadget X', 49.99);
INSERT INTO FOO (product_id, product_name, product_price) VALUES (4, 'Widget B', 24.99);
COMMIT;
-- Incremental FAST refresh of the Materialized View (HERE IS THE PROBLEM => despite the fact that out-of-place flag is true, the MV is refreshed in-place)
EXEC DBMS_MVIEW.REFRESH('FOO_MV', out_of_place => true, atomic_refresh => false, method => 'F');
-- Here based on the MV_NAME you can see that COMPLETE refresh was performed out-of-place because the name is a recyclebin object name
SELECT * FROM USER_MVREF_STATS ORDER BY refresh_id DESC;
-- This view in my opinion does not reflect the actual state, because it always shows OUT_OF_PLACE = 'N' (I believe that this is another different bug, but not about it now)
SELECT * FROM USER_MVREF_RUN_STATS ORDER BY refresh_id DESC;
-- Here we can again notice the recyclebin object name for COMPLETE refresh
SELECT * FROM USER_MVREF_CHANGE_STATS ORDER BY refresh_id DESC;
-- but finally in this view I found actual confiromation, that the COMPLETE refresh was performed out-of-place, but FAST refresh in-place (even though the out_of_place flag was set to TRUE)
SELECT * FROM SYS.MV_REFRESH_USAGE_STATS$ ORDER BY seq# DESC;
/*
So the final question is:
Are there any internal restrictions or conditions that must be met in order to perform FAST out-of-place refresh?
Because after reviewing the official documentation:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dwhsg/refreshing-materialized-views.html#GUID-51191C38-D52F-4A4D-B6FF-E631965AD69A
I have not found anything that would prevent such a combination from succeeding.
It is even clearly stated that out-of-place should work with any refresh method, with FAST preferred first.
*/
I can't think of a reason why you would ever want to fast + out_of_place.
Fast is about one thing - avoiding the need to refresh the mview completely.
Out of place is by definition refreshing the mview completely.
I agree that we should either block the attempt and/or cleanup the documentation in that regards. I'll log a doc bug for that.