MV Refresh on Partitioned Materialized views [message #673524] |
Thu, 22 November 2018 08:24 |
|
saipradyumn
Messages: 419 Registered: October 2011 Location: Hyderabad
|
Senior Member |
|
|
Hi All,
I have Partitioned materialized view on Branch column.
Here my requirement is to get the refresh for each partition not for the entire materialized view.
But from the below code its always going for total materialized view refresh
DROP TABLE MYOBJECTS;
CREATE TABLE MYOBJECTS
PARTITION BY LIST (BRANCH)
(PARTITION LOC1 VALUES ('HYD'),
PARTITION LOC2 VALUES ('DEL'),
PARTITION LOC3 VALUES ('MUM') )
AS
SELECT 'HYD' AS BRANCH, ALL_OBJECTS.*
FROM ALL_OBJECTS WHERE 1=2;
INSERT INTO MYOBJECTS SELECT 'HYD', ALL_OBJECTS.* FROM ALL_OBJECTS WHERE ROWNUM<10;
INSERT INTO MYOBJECTS SELECT 'DEL', ALL_OBJECTS.* FROM ALL_OBJECTS WHERE ROWNUM<10;
INSERT INTO MYOBJECTS SELECT 'MUM', ALL_OBJECTS.* FROM ALL_OBJECTS WHERE ROWNUM<10;
SELECT * FROM MYOBJECTS ;
ALTER TABLE MYOBJECTS ADD CONSTRAINT XPK_MYOBJECTS PRIMARY KEY(BRANCH, OBJECT_ID);
--DROP MATERIALIZED VIEW LOG ON MYOBJECTS;
CREATE MATERIALIZED VIEW LOG ON MYOBJECTS
PARALLEL
WITH ROWID, SEQUENCE
(BRANCH, OBJECT_TYPE, OBJECT_ID)
INCLUDING NEW VALUES;
DROP MATERIALIZED VIEW MV_OBJ_SUMMARY;
CREATE MATERIALIZED VIEW MV_OBJ_SUMMARY
PARTITION BY LIST(BRANCH)
(PARTITION LOC1 VALUES ('HYD'),
PARTITION LOC2 VALUES ('DEL'),
PARTITION LOC3 VALUES ('MUM') )
BUILD DEFERRED
USING INDEX
REFRESH FORCE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
AS
SELECT BRANCH, OBJECT_TYPE, COUNT(*) CNT
FROM MYOBJECTS
GROUP BY BRANCH, OBJECT_TYPE;
select * from MV_OBJ_SUMMARY; -- Here the count is ZERO
BEGIN
DBMS_MVIEW.REFRESH('MV_OBJ_SUMMARY.LOC1','C',ATOMIC_REFRESH => FALSE);
END;
but here after the refresh for one partition also its fetching the other partition data also.
Please help me to find out is there any way to refresh the data only for one specific partition
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: MV Refresh on Partitioned Materialized views [message #673541 is a reply to message #673540] |
Fri, 23 November 2018 02:16 |
|
saipradyumn
Messages: 419 Registered: October 2011 Location: Hyderabad
|
Senior Member |
|
|
Yes,I tried it Michel. Even the execution is also very fast with out loosing the other branches data.
I have 30 MV and 30 such kind of procedure.Every procedure have branch as parameter.As now we have 5 branches.
Just to confirm are there any challenges/Issues that i need to take care if I apply the partition on MV
Thanks
SaiPradyumn
|
|
|