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 |
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 |
John Watson
Messages: 8938 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 |
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 #657053 is a reply to message #657052] |
Thu, 27 October 2016 01:24 |
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 #657136 is a reply to message #657133] |
Fri, 28 October 2016 07:40 |
pablolee
Messages: 2882 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 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
grpatwari wrote on Fri, 28 October 2016 08:37Please 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 |
Solomon Yakobson
Messages: 3275 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 #657157 is a reply to message #657139] |
Fri, 28 October 2016 09:57 |
Solomon Yakobson
Messages: 3275 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.
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 06:49:36 CDT 2024
|