Recently i faced a issue that has wasted my 2 days of work on Materialized View.
I have below script that i have created for the MV
CREATE MATERIALIZED VIEW WI_REASON_COUNT_MV
BUILD IMMEDIATE
REFRESH COMPLETE
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS
SELECT
T.day_name,
T.WEEK_END_DATE,
T.month_desc,
T.year_id,
T.CAL_QUARTER_DESC,
Ro.RESOURCE_ID,
WRO.PROCESS_ID,
WRO.ORGANIZATION_ID,
Ro.SHIFT_ID,
Ro.CREW_LEAD,
Ro.CREW_MEMBERS,
Ro.CREW_COUNT,
Ro.CHANGEOVER_REASON_ID,
SUM(Ro.NUMBER_OF_CHANGE_OVER) NUMBER_OF_CHANGE_OVER,
SUM(Ro.TOTAL_CHANGE_OVER_TIME ) TOTAL_CHANGE_OVER_TIME ,
SUM(Ro.NUMBER_OF_SETUP) NUMBER_OF_SETUP ,
SUM(Ro.TOTAL_SETUP_TIME) TOTAL_SETUP_TIME,
SUM(Ro.TRGT_CHANGE_OVER_PLANT) TRGT_CHANGE_OVER_PLANT,
SUM(Ro.TRGT_CHANGE_OVER_LINE) TRGT_CHANGE_OVER_LINE ,
SUM(Ro.TRGT_CHANGE_OVER_PROCESS) TRGT_CHANGE_OVER_PROCESS,
SUM(Ro.TRGT_SETUP_PLANT) TRGT_SETUP_PLANT ,
SUM(Ro.TRGT_SETUP_LINE)TRGT_SETUP_LINE,
SUM(Ro.TRGT_SETUP_PROCESS) TRGT_SETUP_PROCESS,
SUM(Ro.SCHEDULED_HRS) SCHEDULED_HOURS,
SUM(Ro.MAN_HOURS) MAN_HOURS,
COUNT(Ro.NUMBER_OF_CHANGE_OVER),
COUNT(Ro.NUMBER_OF_SETUP) ,
COUNT(Ro.TRGT_CHANGE_OVER_PLANT),
COUNT(Ro.TRGT_CHANGE_OVER_LINE),
COUNT(Ro.TRGT_CHANGE_OVER_PROCESS),
COUNT(Ro.TRGT_SETUP_PLANT),
COUNT(Ro.TRGT_SETUP_LINE),
COUNT(Ro.TRGT_SETUP_PROCESS),
COUNT(Ro.SCHEDULED_HRS),
COUNT(Ro.MAN_HOURS),
count(*),
GRoUPING_ID (
Ro.RESOURCE_ID,
Ro.SHIFT_ID,
Ro.CREW_LEAD,
Ro.CREW_MEMBERS,
Ro.CREW_COUNT,
Ro.CHANGEOVER_REASON_ID)
FRoM
WI_REASON_COUNT Ro,
WI_SHIFT_CALENDAR_MV T,
WI_RESOURCE_MASTER WRO
WHERE
Ro.SHIFT_ID = T.SHIFT_ID AND
WRO.RESOURCE_ID=Ro.RESOURCE_ID AND
WRO.RESOURCE_ID=T.RESOURCE_ID
GRoUP BY GROUPING SETS ((
Ro.RESOURCE_ID,
Ro.SHIFT_ID,
Ro.CREW_LEAD,
Ro.CREW_MEMBERS,
Ro.CREW_COUNT,
WRO.PROCESS_ID,
WRO.ORGANIZATION_ID,
Ro.CHANGEOVER_REASON_ID,
T.day_name,
T.WEEK_END_DATE,
T.month_desc,
T.year_id,
T.CAL_QUARTER_DESC)
I go the duplicate data in MV for single entry in my base table WI_REASON_COUNT.
Later i found that this was due to the null value for one of the CHANGEOVER_REASON_ID in my base table
So always keep in mind that what ever columns we have group by in MV that cannot have null value.
GRoUP BY GROUPING SETS ((
Ro.RESOURCE_ID,
Ro.SHIFT_ID,
Ro.CREW_LEAD,
Ro.CREW_MEMBERS,
Ro.CREW_COUNT,
WRO.PROCESS_ID,
WRO.ORGANIZATION_ID,
Ro.CHANGEOVER_REASON_ID,
T.day_name,
T.WEEK_END_DATE,
T.month_desc,
T.year_id,
T.CAL_QUARTER_DESC)
