Skip to Main Content
  • Questions
  • FAST out-of-place materialized view refresh problem

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Bartłomiej.

Asked: May 09, 2024 - 1:26 pm UTC

Last updated: May 16, 2024 - 6:36 am UTC

Version: Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production Version 19.17.0.0.0

Viewed 1000+ times

You Asked

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.
*/



with LiveSQL Test Case:

and Connor said...

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.

Rating

  (1 rating)

Comments

Context on why to use FAST + out-of-place

Bartłomiej, May 13, 2024 - 5:24 am UTC

Ok, thank you very much for raising the issue, is there any way I can track the progress of the reported bug?

PS Giving context to the cause - why I wanted to use FAST + out-of-place:
For our data export related solution, after performance testing, we found that the FAST option to refresh the view, does not always prove to be faster than the COMPLETE option. Therefore, wanting to improve the solution, I came across a piece of documentation:
"
7.1.4.1 Types of Out-of-Place Refresh
There are three types of out-of-place refresh:

out-of-place fast refresh

This offers better availability than in-place fast refresh. It also offers better performance when changes affect a large part of the materialized view.
"
~ https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dwhsg/refreshing-materialized-views.html#GUID-51191C38-D52F-4A4D-B6FF-E631965AD69A

Which seems to answer my problem perfectly.

That's why I'm surprised that so much space in the documentation has been dedicated to describing the combination of FAST + out-of-place and you can't actually use it.
Connor McDonald
May 16, 2024 - 6:36 am UTC

Doc bugs typically arent public.


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library