Home » SQL & PL/SQL » SQL & PL/SQL » MV Refresh on Partitioned Materialized views (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
MV Refresh on Partitioned Materialized views [message #673524] Thu, 22 November 2018 08:24 Go to next message
saipradyumn
Messages: 400
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 #673530 is a reply to message #673524] Thu, 22 November 2018 10:44 Go to previous messageGo to next message
John Watson
Messages: 7953
Registered: January 2010
Location: Global Village
Senior Member
For an MV with an aggregation to be fast refreshable, if I remember correctly you must include a count of each GROUP BY column as well as a count(*). This will be in the docs somewhere.
Re: MV Refresh on Partitioned Materialized views [message #673531 is a reply to message #673524] Thu, 22 November 2018 11:10 Go to previous messageGo to next message
Michel Cadot
Messages: 66469
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Is there a specific reason you don't want Oracle refreshes all partitions when it nevertheless has to do the work for all partitions?

Try adding an index on MYOBJECTS (BRANCH, OBJECT_TYPE), maybe it will not refresh all partitions as it has not to scan the whole table as it currently does.

Also, refreshing a single partition, is this not a 12c stuff?

Re: MV Refresh on Partitioned Materialized views [message #673532 is a reply to message #673524] Thu, 22 November 2018 11:16 Go to previous messageGo to next message
John Watson
Messages: 7953
Registered: January 2010
Location: Global Village
Senior Member
Oh, I've just seen: your refresh is method C, it should be P for partition change tracking.
Re: MV Refresh on Partitioned Materialized views [message #673534 is a reply to message #673532] Thu, 22 November 2018 12:16 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2863
Registered: January 2010
Location: Connecticut, USA
Senior Member
Partition change tracking doesn't mean refresh just one partition. MV is always refreshed as a whole, as far as I know. PCT (partition change tracking) is just a finer grain of tracking freshness + ability to refresh in parallel.

SY.
Re: MV Refresh on Partitioned Materialized views [message #673535 is a reply to message #673534] Fri, 23 November 2018 00:46 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All,

Thanks for your responses.Here my problem is we need to give the support for the multiple procedures execution parallel for the different branches.

In one procedure we are going for the refresh of MV for one branch. In another procedure we are copying the data from the MV for different branch to some other table.

Earlier all these MV are not partitioned.Hence because of the parallel execution of these two jobs,another procedure which is copying the data from MV to thet another temp table is becomimg ZERO.

As MV refresh is table level when the refresh is happening for one MV at the same time one more procedure is reading the data for another branch which is leading to ZERO results

I want to avoid this scenario, by applying the partitions at MV also and I want to go for Partition refresh

Thanks
SaiPradyumn





Re: MV Refresh on Partitioned Materialized views [message #673536 is a reply to message #673535] Fri, 23 November 2018 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 66469
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Create several mview (one per branch) and a view upon them.

Re: MV Refresh on Partitioned Materialized views [message #673537 is a reply to message #673536] Fri, 23 November 2018 00:59 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
As per the business there is possibility to get the more and more branches frequently.when the new branch is adding to the application, I can not go for adding one more new objects in the database.

If this MV refresh at partition level is not possible, I need to go for the sequential execution internally .
Even though the job was triggered externally one branch procedure will be idle until the next branch same procedure was completed.


Apart from these two let me know is there any optimistic way to achieve the same .

Re: MV Refresh on Partitioned Materialized views [message #673538 is a reply to message #673537] Fri, 23 November 2018 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 66469
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I can not go for adding one more new objects in the database.

Why? You have to add a new partition which is a new object, table or partition this is (about) the same thing.

[Updated on: Fri, 23 November 2018 01:11]

Report message to a moderator

Re: MV Refresh on Partitioned Materialized views [message #673539 is a reply to message #673538] Fri, 23 November 2018 01:45 Go to previous messageGo to next message
saipradyumn
Messages: 400
Registered: October 2011
Location: Hyderabad
Senior Member
Here all procedures code is generic for all branches.If I am creating the new MV for each branch, it required a lot of code changes as we don't branch specific procedures.


If am able to achieve the refresh at the partition level, its very simple and less code changes by appending the
parameter branch to the existing code.

Can I pass the parameter as p as suggested in the thread

BEGIN
DBMS_MVIEW.REFRESH('MV_OBJ_SUMMARY.LOC1','P',ATOMIC_REFRESH => FALSE);
END;

Thanks
SaiPradyumn
Re: MV Refresh on Partitioned Materialized views [message #673540 is a reply to message #673539] Fri, 23 November 2018 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 66469
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Here all procedures code is generic for all branches.If I am creating the new MV for each branch, it required a lot of code changes as we don't branch specific procedures.
You pass the partition name to your procedures (otherwise there are not generic and you have a procedure per branch) so it is the same thing except that the partition name is replaced by the table name.
Define "a lot"; how many procedures have you?

Quote:
Can I pass the parameter as p as suggested in the thread

Why don't you try it?

Re: MV Refresh on Partitioned Materialized views [message #673541 is a reply to message #673540] Fri, 23 November 2018 02:16 Go to previous message
saipradyumn
Messages: 400
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

Previous Topic: SQL Query assistance
Next Topic: General Question on altering table without locking it
Goto Forum:
  


Current Time: Wed Jul 17 19:44:22 CDT 2019