Home » SQL & PL/SQL » SQL & PL/SQL » MATERIALIZED VIEW (TNS for 64-bit Windows: Version 11.2.0.1.0 - Production)
MATERIALIZED VIEW [message #666484] |
Wed, 08 November 2017 06:42 |
|
shawaj
Messages: 89 Registered: January 2016
|
Member |
|
|
Hi
I have created materialized view first time and when i query from materialized view than i got an error ORA-01410: invalid ROWID.I do not have any idea why this happening..
Please help me.
CREATE MATERIALIZED VIEW MV_TRPS_MCHNT_FRNT
TABLESPACE SAKET
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0)
NOLOGGING
BUILD IMMEDIATE
REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE+1/1440
AS
SELECT PM.SHCODE,PM.LOCNO,PM.B1CODE,B3FULL,MNAME M3FULL,POCODE PO,PM.SCODE,
PM.ACTIVE,PM.SHIP_FLAG,PM.POSTRING,PM.MANAGER,PM.UCODE,PM.STYLENO,
STYLEBUY,DESCRIPTION,FAB1NAME FABRIC,VNAME,SUM(QTY) QTY
FROM PO_MCHNT_BYR PM,FABRIC F,DELIVERY D,VENDOR V,FAB1 F1
WHERE PM.STYLENO=F.STYLENO(+)
AND PM.STYLENO=D.STYLENO(+)
AND D.VCODE=V.VCODE(+)
AND F.FAB1NO=F1.FAB1NO(+)
AND F.MAINALLIED(+)='M'
AND NVL(D.FLAG(+),'S')<>'C'
AND PM.POSTRING<>'Z'
GROUP BY PM.SHCODE,PM.LOCNO,PM.B1CODE,B3FULL,MNAME,POCODE,PM.SCODE,PM.ACTIVE,PM.SHIP_FLAG,PM.POSTRING,PM.MANAGER,PM.UCODE,PM.STYLENO,STYLEBUY,DESCRIPTION,FAB1NAME,VNAME;
My query was simple
select * from MV_TRPS_MCHNT_FRNT;
|
|
|
|
Re: MATERIALIZED VIEW [message #666490 is a reply to message #666488] |
Wed, 08 November 2017 07:57 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
>GROUP BY > PM.SHCODE,PM.LOCNO,PM.B1CODE,B3FULL,MNAME,POCODE,PM.SCODE,PM.ACTIVE,PM.SHIP_FLAG,PM.POSTRING,PM.MANAGER,PM.UCODE,PM.STYLENO,STYLEBUY, DESCRIPTION,FAB1NAME,VNAME;
IMO, every column should be prefixed with table alias to avoid confusion where they originate (but I doubt this will change the problem).
|
|
|
Re: MATERIALIZED VIEW [message #666491 is a reply to message #666490] |
Wed, 08 November 2017 23:12 |
|
shawaj
Messages: 89 Registered: January 2016
|
Member |
|
|
Good morning to all
Thanks for your reply.
I have created materialized view using following syntax
CREATE MATERIALIZED VIEW MV_TRPS_MCHNT_FRNT
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT PM.SHCODE,PM.LOCNO,PM.B1CODE,B3FULL,MNAME M3FULL,POCODE PO,PM.SCODE,
PM.ACTIVE,PM.SHIP_FLAG,PM.POSTRING,PM.MANAGER,PM.UCODE,PM.STYLENO,
STYLEBUY,DESCRIPTION,FAB1NAME FABRIC,VNAME,SUM(QTY) QTY
FROM
PO_MCHNT_BYR PM,FABRIC F,DELIVERY D,VENDOR V,FAB1 F1
WHERE
PM.STYLENO=F.STYLENO(+)
AND PM.STYLENO=D.STYLENO(+)
AND D.VCODE=V.VCODE(+)
AND F.FAB1NO=F1.FAB1NO(+)
AND F.MAINALLIED(+)='M'
AND NVL(D.FLAG(+),'S')<>'C'
AND PM.POSTRING<>'Z'
GROUP BY
PM.SHCODE,PM.LOCNO,PM.B1CODE,B3FULL,MNAME,POCODE,PM.SCODE,PM.ACTIVE,PM.SHIP_FLAG,PM.POSTRING,PM.MANAGER,PM.UCODE,PM.STYLENO,STYLEBUY,DESCRIPTION,F
AB1NAME,VNAME;
But i don't know when it will refresh implicitly or explicitly by using
EXEC DBMS_MVIEW.refresh('MV_TRPS_MCHNT_FRNT');
[Updated on: Wed, 08 November 2017 23:25] Report message to a moderator
|
|
|
Re: MATERIALIZED VIEW [message #666494 is a reply to message #666491] |
Thu, 09 November 2017 02:03 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Quote:But i don't know when it will refresh implicitly or explicitly by using
You have provided two view definitions: one with a scheduled refresh, one with refresh on demand. Which do you want? Automatic refresh or only when you run the procedure?
|
|
|
Re: MATERIALIZED VIEW [message #666507 is a reply to message #666491] |
Thu, 09 November 2017 06:28 |
|
shawaj
Messages: 89 Registered: January 2016
|
Member |
|
|
shawaj wrote on Wed, 08 November 2017 23:12Good morning to all
Thanks for your reply.
I have created materialized view using following syntax
CREATE MATERIALIZED VIEW MV_TRPS_MCHNT_FRNT
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT PM.SHCODE,PM.LOCNO,PM.B1CODE,B3FULL,MNAME M3FULL,POCODE PO,PM.SCODE,
PM.ACTIVE,PM.SHIP_FLAG,PM.POSTRING,PM.MANAGER,PM.UCODE,PM.STYLENO,
STYLEBUY,DESCRIPTION,FAB1NAME FABRIC,VNAME,SUM(QTY) QTY
FROM
PO_MCHNT_BYR PM,FABRIC F,DELIVERY D,VENDOR V,FAB1 F1
WHERE
PM.STYLENO=F.STYLENO(+)
AND PM.STYLENO=D.STYLENO(+)
AND D.VCODE=V.VCODE(+)
AND F.FAB1NO=F1.FAB1NO(+)
AND F.MAINALLIED(+)='M'
AND NVL(D.FLAG(+),'S')<>'C'
AND PM.POSTRING<>'Z'
GROUP BY
PM.SHCODE,PM.LOCNO,PM.B1CODE,B3FULL,MNAME,POCODE,PM.SCODE,PM.ACTIVE,PM.SHIP_FLAG,PM.POSTRING,PM.MANAGER,PM.UCODE,PM.STYLENO,STYLEBUY,DESCRIPTION,F
AB1NAME,VNAME;
But i don't know when it will refresh implicitly or explicitly by using
EXEC DBMS_MVIEW.refresh('MV_TRPS_MCHNT_FRNT');
I am talking about this definition.
I want to refresh automatically
|
|
|
Re: MATERIALIZED VIEW [message #666508 is a reply to message #666507] |
Thu, 09 November 2017 06:50 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
This looks as though you are trolling: being deliberately stupid in order to make people angry. However....
If you create the MV as REFRESH FORCE ON DEMAND, it is not going to refresh automatically. If you create it with REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE+1/1440 then a refresh job will run every day. Automatically.
--correction: refresh every minute, not every day.
[Updated on: Thu, 09 November 2017 06:51] Report message to a moderator
|
|
|
Re: MATERIALIZED VIEW [message #666509 is a reply to message #666484] |
Thu, 09 November 2017 07:10 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
Please, for the sanity of those trying to read you code, learn to format it:
REATE MATERIALIZED VIEW MV_TRPS_MCHNT_FRNT TABLESPACE SAKET STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0) NOLOGGING BUILD IMMEDIATE REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE+1/1440
AS
SELECT PM.SHCODE,
PM.LOCNO,
PM.B1CODE,
B3FULL,
MNAME M3FULL,
POCODE PO,
PM.SCODE,
PM.ACTIVE,
PM.SHIP_FLAG,
PM.POSTRING,
PM.MANAGER,
PM.UCODE,
PM.STYLENO,
STYLEBUY,
DESCRIPTION,
FAB1NAME FABRIC,
VNAME,
SUM(QTY) QTY
FROM PO_MCHNT_BYR PM,
FABRIC F,
DELIVERY D,
VENDOR V,
FAB1 F1
WHERE PM.STYLENO =F.STYLENO(+)
AND PM.STYLENO =D.STYLENO(+)
AND D.VCODE =V.VCODE(+)
AND F.FAB1NO =F1.FAB1NO(+)
AND F.MAINALLIED(+) ='M'
AND NVL(D.FLAG(+),'S')<>'C'
AND PM.POSTRING <>'Z'
GROUP BY PM.SHCODE,
PM.LOCNO,
PM.B1CODE,
B3FULL,
MNAME,
POCODE,
PM.SCODE,
PM.ACTIVE,
PM.SHIP_FLAG,
PM.POSTRING,
PM.MANAGER,
PM.UCODE,
PM.STYLENO,
STYLEBUY,
DESCRIPTION,
FAB1NAME,
VNAME;
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:42:05 CDT 2024
|