- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 73 Insurance
- 537.3K On-Premises Infrastructure
- 138.6K Analytics Software
- 38.6K Application Development Software
- 6K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.2K Infrastructure Software
- 105.3K Integration
- 41.6K Security Software
Materialized View - PCTFREE and PCTUSED
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.
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 );
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