Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.5K Security Software
Materialized View - PCTFREE and PCTUSED

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