Hello,
I have some questions about optimizing materialized views (MVs):
In the following tutorial it is recommended to set PCTFREE and PCTUSED to 0 or 99:
Note: If a materialized view is complete refreshed, then set it's PCTFREE to 0 and PCTUSED to 99 for maximum efficiency.
--> https://satya-dba.blogspot.com/2009/07/materialized-views-oracle.html
First question:
Is this recommendation correct? Should the two parameters PCTFREE 0 and PCTUSED 99 be set accordingly?
How is that exact procedure when the MV is updated?
The MV is created ( PCTFREE 0 and PCTUSED 99 are set accordingly):
CREATE MATERIALIZED VIEW "SYSK85"."CMVF_01_SK85_BESTAND_2019" ("BRNR", "LEAT", "TLRTAL", "LEATSO", "STAND_DATUM", "VOAT", "AQEQ", "ELAT", "ELDT", "VSKN", "VTKZ", "ANZAHL")
PCTFREE 0 PCTUSED 99
NOCOMPRESS LOGGING
NO INMEMORY
BUILD IMMEDIATE
USING INDEX
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE
AS (
select BRNR, LEAT, TLRTAL, LEATSO, STAND_DATUM, VOAT, AQEQ, ELAT, ELDT, VSKN, VTKZ, count(*) as ANZAHL
from CVF_SK85_BESTAND_2019 GROUP BY BRNR, LEAT, TLRTAL, LEATSO, STAND_DATUM, VOAT, AQEQ, ELAT, ELDT, VSKN, VTKZ
);
Second question:
Should the Create of the MV be set the two parameters PCTFREE 0 and PCTUSED 99?
But what if the MV is updated?
EXEC DBMS_MVIEW.REFRESH('CMVF_01_SK85_BESTAND_2019', 'C', atomic_refresh=>FALSE);
Should the two parameters PCTFREE and PCTUSED be reset again explicitly after the update (so that the database can reorganize the data - if necessary)? Or is not that necessary?
Thank you very much
Kind regards
George