Home » SQL & PL/SQL » SQL & PL/SQL » Locking issue while MATERIALIZED VIEW REFRESH (PL/SQL Release 12.1.0.2.0)
Locking issue while MATERIALIZED VIEW REFRESH [message #679546] Fri, 06 March 2020 19:21 Go to next message
Manoj.Gupta.91
Messages: 237
Registered: March 2008
Location: Delhi
Senior Member
Hi All,

I need your help in Materialized View refresh. I've tried many options like
ON COMMIT

ON DEMAND START WITH SYSDATE NEXT SYSDATE + 1/4096

ON DEMAND --DBMS_MVIEW.REFRESH('MV_TXN_HIST','F');

but the issue I'm facing is related to blocking sessions. Materialized view never refreshes and creates blocking sessions. Please help me to understand what can be the possible cause and solution to this problem.

Fast refresh doesn't work in any way and creates locking issues but COMPLETE refresh works perfectly fine.

CREATE TABLE TXN_TABLE_1
(
      TXN_ID                              VARCHAR2(36)   
     ,TXN_NOTE                            VARCHAR2(100)  
     ,REF_ID                              VARCHAR2(36)   
     ,REF_URL                             VARCHAR2(500)  
     ,TXN_CREATE_TS                       TIMESTAMP(6)   
     ,TXN_TYPE                            VARCHAR2(20)   
     ,TXN_AMT                             NUMBER(18,3)   
     ,TXN_CURR                            VARCHAR2(4)  
) ;

CREATE TABLE TXN_TABLE_2
(
      TXN_ID                              VARCHAR2(36)   
     ,RES_CODE                            VARCHAR2(100)  
     ,RES_ID                              VARCHAR2(36)   
     ,RES_URL                             VARCHAR2(500)  
     ,RES_CREATE_TS                       TIMESTAMP(6)   
) ;

CREATE MATERIALIZED VIEW LOG ON TXN_TABLE_1 WITH ROWID ;
CREATE MATERIALIZED VIEW LOG ON TXN_TABLE_2 WITH ROWID ;

CREATE MATERIALIZED VIEW MV_TXN_HIST
REFRESH FAST ON COMMIT
WITH ROWID
AS
SELECT
      T.TXN_ID
	 ,T.TXN_AMT
	 ,T.TXN_CURR
	 ,R.RES_CODE
	 ,NVL(R.RES_ID, T.RES_ID) RES_ID
FROM 
     TXN_TABLE_1 T
    ,TXN_TABLE_2 R
WHERE T.TXN_ID = R.TXN_ID(+) ;
Thanks & Regards
Manoj

[Updated on: Sat, 07 March 2020 02:15]

Report message to a moderator

Re: Locking issue while MATERIALIZED VIEW REFRESH [message #679549 is a reply to message #679546] Sat, 07 March 2020 02:18 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 237
Registered: March 2008
Location: Delhi
Senior Member
Hi All,

I got the issue. I was using NVL in Materiliazed View's base query which was creating this issue.

But now I'm facing performance issue with materialized view itself. Time taken for insert and commit on base tables have been increased drastically. Can someone please help me to resolve this issue.

Thanks & Regards
Manoj

Re: Locking issue while MATERIALIZED VIEW REFRESH [message #679550 is a reply to message #679549] Sat, 07 March 2020 03:05 Go to previous messageGo to next message
John Watson
Messages: 8348
Registered: January 2010
Location: Global Village
Senior Member
Primary keys and indexes on the tables and the mview would not hurt. And you need the keys in the logs. Without them, how do you think the refresh can find the correct rows without full scans?
Re: Locking issue while MATERIALIZED VIEW REFRESH [message #679558 is a reply to message #679550] Sun, 08 March 2020 13:34 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 237
Registered: March 2008
Location: Delhi
Senior Member
Hi All,

I'm using global partitioned indexes on materialized view to be used in queries on materialized view. Parallel in materialized view base query.

How can I use ROWID and Key materialized view log please help.

Thanks & Regards
Manoj

[Updated on: Sun, 08 March 2020 13:35]

Report message to a moderator

Re: Locking issue while MATERIALIZED VIEW REFRESH [message #679561 is a reply to message #679558] Mon, 09 March 2020 08:32 Go to previous messageGo to next message
John Watson
Messages: 8348
Registered: January 2010
Location: Global Village
Senior Member
Quote:
I'm using global partitioned indexes on materialized view to be used in queries on materialized view. Parallel in materialized view base query.
So the code you gave in your first post is nothing like reality?
Re: Locking issue while MATERIALIZED VIEW REFRESH [message #679565 is a reply to message #679561] Mon, 09 March 2020 12:00 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 237
Registered: March 2008
Location: Delhi
Senior Member
John Watson wrote on Mon, 09 March 2020 19:02
Quote:
I'm using global partitioned indexes on materialized view to be used in queries on materialized view. Parallel in materialized view base query.
So the code you gave in your first post is nothing like reality?
Correct.

But I'm facing problem with it's fast refresh. Fast is happening sometimes and sometimes not, MV has even stopped refreshing. Whenever MV refreshed it took huge time to refresh.

What could be the possible cause. Please help.

Thanks & Regards
Manoj
Re: Locking issue while MATERIALIZED VIEW REFRESH [message #679628 is a reply to message #679565] Wed, 11 March 2020 07:28 Go to previous message
Bill B
Messages: 1969
Registered: December 2004
Senior Member
To do a fast refresh you must have mview logs defined on the base tables specifying primary key. The base tables must have a primary key defined and not just a unique key. The primary key must be in the MVIEW and it must have a unique index on it
Previous Topic: MATERIALIZED VIEW REFRESH HISTORY
Next Topic: ORA-02069
Goto Forum:
  


Current Time: Mon Sep 28 11:04:56 CDT 2020