Forum Stats

  • 3,873,041 Users
  • 2,266,499 Discussions
  • 7,911,412 Comments

Discussions

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