Home » SQL & PL/SQL » SQL & PL/SQL » Materialized view creation with complex queries (11.2.0.4)
Materialized view creation with complex queries [message #657029] Wed, 26 October 2016 08:01 Go to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
I would like to create the materialized view with refresh fast as commit with the below query. I am getting the error. Please suggest me. MV has to refresh as an when commit occurs on any of the table. I have created the MV logs on every table.
CREATE MATERIALIZED VIEW STATUS_REP_MV
refresh
 with rowid
 fast
 on commit
   AS 
WITH ALLSR AS (
SELECT  
	V.PROJECT_ID
	,SR.REPORT_DATE STATUS_REPORT_DATE
	,SR.ID STATUS_REPORT_ID
	,SR.NAME
	,SR.CREATED_BY USER_CREATED_SR
	,SR.CREATED_DATE 
	,V.ROLE_ID
	,V.DEL_START
	,V.DEL_FINISH
	,V.SIGNUM
	,V.ROLE_TYPE
	,SR.ERI_EXEC_STAT_SUMRY
	,SR.EGCT_ST_STAT_NEW
	,SR.ERI_ACCOMPLISHMENTS
	,SR.ERI_NEXT_STEPS
	,SR.SCHEDULE_STATUS
	,SR.SCOPE_STATUS
	,SR.COST_EFFORT_STATUS
	,SR.EGCT_SR_QUALITY
	,SR.LAST_UPDATED_DATE
	,SR.SCOPE_CHANGE SCOPE_CHANGE_REQUIRED
	,SR.COST_EFFORT_EXTERNAL PRJ_AFF_BY_EXT_FAC
	,SR.ERI_KPI_SUM_RVW PRJ_GOV_KPI_SUMMARY_REVIEW
	,SR.SCOPE_OBJECTIVE PROJECT_OBJECTIVE_CHANGED
	,SR.SCHEDULE_MILESTONE NEXT_MILESTONE_ON_TRACK
	,SR.ERI_PROJ_PHASE PROJECT_PHASE
	,SR.PHASE CURRENT_PHASE
	,SR.ERI_STEER_DATE
	,SR.SCOPE_DELIVERABLE DELIVERABLE_SCOPE_CHANGED
	,SR.CODE SRID
	,SR.REPORT_UPDATE REPORT_UPDATE
	,SR.EGCT_ST_STAT_NEW OVERALL_STATUS
	,SR.ERI_CONFIRM_STATUS
	,SR.ERI_READY_CONFIRM ERI_READY_CONFIRM
	,SR.ERI_ACCOMPLISH_SH
	,SR.COST_EFF_EXPLAN_ADD
	,SR.EGCT_ESCALATION_SH
	,SR.ERI_EXEC_STAT_SUM_SH
	,SR.ERI_NEXT_STEPS_SH
	,SR.SCHEDULE_EXPLAN_SH
  --,MAX(SR.REPORT_DATE) OVER(PARTITION BY SR.CREATED_DATE ORDER BY SR.CREATED_DATE  DESC)
FROM 
	EGCT_PRJ_ROLES_VIEW V INNER JOIN ODF_CA_CATSPRJSTATUSREP SR ON (V.PROJECT_ID=SR.ODF_PARENT_ID 
	AND V.ROLE_ID=SR.CREATED_BY AND V.ROLE_TYPE IN ('CPM_DELEGATE','PREV_CPM','CPM')
  AND ( TRUNC(SR.CREATED_DATE) >= (
										CASE
										  WHEN V.ROLE_TYPE IN ('CPM_DELEGATE','PREV_CPM')
										  THEN TRUNC(V.DEL_START)
										  ELSE TRUNC(SR.CREATED_DATE)
										END
									)
			AND TRUNC(SR.CREATED_DATE) <= (
											CASE
											  WHEN V.ROLE_TYPE IN ('CPM_DELEGATE','PREV_CPM')
											  THEN TRUNC(V.DEL_FINISH)
											  ELSE TRUNC(SR.CREATED_DATE)
											END)
	)
  )
 --WHERE V.PROJECT_ID=5721143
)
, MAXRP AS
(
SELECT O.PROJECT_ID,MAX(O.STATUS_REPORT_ID) ID FROM ALLSR O WHERE O.STATUS_REPORT_DATE= (SELECT  MAX (I.STATUS_REPORT_DATE) FROM ALLSR I WHERE I.PROJECT_ID= O.PROJECT_ID)
GROUP BY O.PROJECT_ID)
SELECT 
B."PROJECT_ID",B."STATUS_REPORT_DATE",B."STATUS_REPORT_ID",B."NAME",B."USER_CREATED_SR",B."CREATED_DATE",B."ROLE_ID",B."DEL_START",B."DEL_FINISH",B."SIGNUM",B."ROLE_TYPE",B."ERI_EXEC_STAT_SUMRY",B."EGCT_ST_STAT_NEW",B."ERI_ACCOMPLISHMENTS",B."ERI_NEXT_STEPS",B."SCHEDULE_STATUS",B."SCOPE_STATUS",B."COST_EFFORT_STATUS",B."EGCT_SR_QUALITY",B."LAST_UPDATED_DATE",B."SCOPE_CHANGE_REQUIRED",B."PRJ_AFF_BY_EXT_FAC",B."PRJ_GOV_KPI_SUMMARY_REVIEW",B."PROJECT_OBJECTIVE_CHANGED",B."NEXT_MILESTONE_ON_TRACK",B."PROJECT_PHASE",B."CURRENT_PHASE",B."ERI_STEER_DATE",B."DELIVERABLE_SCOPE_CHANGED",B."SRID",B."REPORT_UPDATE",B."OVERALL_STATUS",B."ERI_CONFIRM_STATUS",B."ERI_READY_CONFIRM",B."ERI_ACCOMPLISH_SH",B."COST_EFF_EXPLAN_ADD",B."EGCT_ESCALATION_SH",B."ERI_EXEC_STAT_SUM_SH",B."ERI_NEXT_STEPS_SH",B."SCHEDULE_EXPLAN_SH" 
FROM ALLSR B JOIN MAXRP M ON (B.PROJECT_ID=M.PROJECT_ID AND  B.STATUS_REPORT_ID=M.ID); 
SQL Error: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
12054. 00000 -  "cannot set the ON COMMIT refresh attribute for the materialized view"
*Cause:    The materialized view did not satisfy conditions for refresh at
           commit time.
*Action:   Specify only valid options.
Re: Materialized view creation with complex queries [message #657033 is a reply to message #657029] Wed, 26 October 2016 08:14 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
You would need to provide the materialized view log creation statements before anyone can comment.
However, I would think it highly unlikely that an MV with that many joins and aggregations can
be fast refreshed.

Furthermore:
There are a couple of bugs regarding ANSI join syntax and MVs, you'll need to rewrite to use the old Oracle join syntax.
Better get rid of the WITH clause, too. Keep the SQL as simple as you can.
You'll need to include the rowids in the view.
Re: Materialized view creation with complex queries [message #657050 is a reply to message #657033] Thu, 27 October 2016 00:21 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
I have created the below MV logs.
create materialized view log on ODF_CA_CATSPRJSTATUSREP with rowid; 
create materialized view log on INV_INVESTMENTS with rowid; 
create materialized view log on INV_PROJECTS with rowid; 
create materialized view log on SRM_RESOURCES  with rowid; 
create materialized view log on ODF_CA_EGCT_DYN_RIGHT_AUDIT with rowid; 
create materialized view log on ODF_CA_ERI_PSC_FORM with rowid; 

Also I am trying to create with scheduler job to run with every minute. Am I doing proper way or it is wrong way. Please suggest me.
BEGIN
DBMS_SCHEDULER.CREATE_JOB 
(
JOB_NAME            => 'REFRESH_MVIEW',
JOB_TYPE            => 'PLSQL_BLOCK',
JOB_ACTION          => 'BEGIN DBMS_MVIEW.REFRESH("STATUS_REP_MV",''F''); END;',
NUMBER_OF_ARGUMENTS => 0,
START_DATE          => SYSTIMESTAMP,
REPEAT_INTERVAL     => 'FREQ=DAILY; INTERVAL=1',
END_DATE            => NULL,
ENABLED             => TRUE,
AUTO_DROP           => FALSE,
COMMENTS            => 'JOB TO REFRESH'
);
END;
Re: Materialized view creation with complex queries [message #657052 is a reply to message #657050] Thu, 27 October 2016 00:51 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
Your job will run once a day.
Re: Materialized view creation with complex queries [message #657053 is a reply to message #657052] Thu, 27 October 2016 01:24 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
I have created the job as below.
BEGIN
DBMS_SCHEDULER.CREATE_JOB 
(
JOB_NAME            => 'REFRESH_MVIEW',
JOB_TYPE            => 'PLSQL_BLOCK',
JOB_ACTION          => 'BEGIN DBMS_MVIEW.REFRESH("STATUS_REP_MV",''F''); END;',
NUMBER_OF_ARGUMENTS => 0,
REPEAT_INTERVAL     => 'FREQ=MINUTELY',
START_DATE          => SYSTIMESTAMP + interval '10' second,
END_DATE            => NULL,
ENABLED             => TRUE,
AUTO_DROP           => FALSE,
COMMENTS            => 'JOB TO REFRESH'
);
END;
Re: Materialized view creation with complex queries [message #657133 is a reply to message #657053] Fri, 28 October 2016 07:37 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Please provide the proper solution..Not able to solve it.
Re: Materialized view creation with complex queries [message #657134 is a reply to message #657133] Fri, 28 October 2016 07:38 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
I already gave you quite a lot of advice. Have you tried any of it?
Re: Materialized view creation with complex queries [message #657136 is a reply to message #657133] Fri, 28 October 2016 07:40 Go to previous messageGo to next message
pablolee
Messages: 2847
Registered: May 2007
Location: Scotland
Senior Member
Word to the wise. Have a think about the environment in which you are demanding help. Nobody here is under ANY obligation to help you, so when you ask for help, do it politely, don't demand full solutions and pay attention to what people tell you.
Re: Materialized view creation with complex queries [message #657137 is a reply to message #657133] Fri, 28 October 2016 08:05 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2697
Registered: January 2010
Location: Connecticut, USA
Senior Member
grpatwari wrote on Fri, 28 October 2016 08:37
Please provide the proper solution..Not able to solve it.
Chance you will be able to create MV with FAST REFRESH ON COMMIT is next to none. Create it with refresh on demand then use
DBMS_MVIEW.EXPLAIN_MVIEW & DBMS_MVIEW.EXPLAIN_REWRITE to see what prevents your MV from supporting FAST REFRESH ON COMMIT.

SY.
Re: Materialized view creation with complex queries [message #657138 is a reply to message #657050] Fri, 28 October 2016 08:18 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2697
Registered: January 2010
Location: Connecticut, USA
Senior Member
grpatwari wrote on Thu, 27 October 2016 01:21

Also I am trying to create with scheduler job to run with every minute. Am I doing proper way or it is wrong way. Please suggest me.
It appears you don't understand what "ON COMMIT" is, otherwise you wouldn't be creating refresh job and running it every minute which will take a strain on your
database. Why do you need a job if MV is automatically refreshed when changes on any MV base tables are committed.

SY.
Re: Materialized view creation with complex queries [message #657139 is a reply to message #657138] Fri, 28 October 2016 08:29 Go to previous messageGo to next message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Please let me know without job and MV. How to rewrite the query.
Re: Materialized view creation with complex queries [message #657157 is a reply to message #657139] Fri, 28 October 2016 09:57 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2697
Registered: January 2010
Location: Connecticut, USA
Senior Member
I already told you - create MV with refresh on demand, run DBMS_MVIEW.EXPLAIN_MVIEW & DBMS_MVIEW.EXPLAIN_REWRITE
to see what prevents your MV from supporting FAST REFRESH ON COMMIT. Then try rewriting MV query to address
the issues. And keep in mind fast refresh and refresh on commit are not possible for many complex queries.

SY.
Re: Materialized view creation with complex queries [message #657191 is a reply to message #657157] Mon, 31 October 2016 04:18 Go to previous message
grpatwari
Messages: 288
Registered: June 2008
Location: Hyderabad
Senior Member
Thank you. I will try to rewrite the query.
Previous Topic: Bug in Oracle External Table Not display Decimal Values
Next Topic: low value & high value
Goto Forum:
  


Current Time: Sat Jan 20 06:03:01 CST 2018

Total time taken to generate the page: 0.01384 seconds