Home » SQL & PL/SQL » SQL & PL/SQL » Materialized View refresh
Materialized View refresh [message #315499] Mon, 21 April 2008 13:58 Go to next message
Agus211
Messages: 39
Registered: September 2007
Member
Hi, I'm having a problem with a Materialized View with fast refresh. The thing is that when I alter the master table, the Materialized View still get refreshed, but when I delete it and recreate again(giving the schema owner all the same privileges), the materialized view stops refreshing. Am I missing something?. I gave the owner user all the system privileges the documentation says.

Thank you.

This is my master table(which is in a different schema):

CREATE TABLE USER_A.CATALOG
(
ID NUMBER NOT NULL,
NAME VARCHAR2(20 BYTE) NOT NULL,
DESCRIPTION VARCHAR2(20 BYTE)

)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

CREATE UNIQUE INDEX USER_A.CATALOG_PK ON USER_A.CATALOG
(ID)
LOGGING
NOPARALLEL;


ALTER TABLE USER_A.CATALOG ADD (
CONSTRAINT CATALOG_PK
PRIMARY KEY
(ID));

GRANT SELECT, ON COMMIT REFRESH, QUERY REWRITE ON USER_A.CATALOG TO USER_B;

/**************************************************************/

This table has a Materialized view log:

CREATE MATERIALIZED VIEW LOG ON USER_A.CATALOG;

GRANT SELECT, ON COMMIT REFRESH, QUERY REWRITE ON USER_A.MLOG$_CATALOG TO USER_B;

/**************************************************************/

And this is my Materialized view which is created in USERB schema:

CREATE MATERIALIZED VIEW USER_B.CATALOG
TABLESPACE USER_B_DTA
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
USING INDEX
TABLESPACE USER_B_DTA
REFRESH FAST ON COMMIT
WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS
SELECT ID,NAME,DESCRIPTION FROM USER_A.CATALOGO;

CREATE UNIQUE INDEX USER_B.CATALOG_PK ON USER_B.CATALOG
(ID)
LOGGING
TABLESPACE USER_B_DTA
NOPARALLEL;

Re: Materialized View refresh [message #315505 is a reply to message #315499] Mon, 21 April 2008 14:36 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Mviews are not my specialty but I will give this a go.

A materialized view is a query that is "materialized", which means the query it executed and results stored in a table.

The validity of an Mview is in part affected by its "binding" to its underlying data objects.

Some changes to an underlying data object can be made that do not invalidate an Mview, but there are other changes that can be made to an underlying data object that do invalidate a MVIEW.

If you add a column to an underyling table, this does not invalidate the query the Mview is based on, nor does it invalidate the Mview log for the altered table because the mview log does not require the new column, so the Mview remains valid and thus refreshable.

If you drop a column the Mview query does depend upon would not the Mview query and Mview log of the alter table become invalid? Yes it would. Thus the Mview would stop refreshing. How could it refresh without one of its columns?

Similarly, if you drop a table the Mview query depends upon, so too must the Mview become invalid because its query and one of its Mview logs is invalid.

The Mview will continue to refresh as long as it is valid. But if you drop a table then its underlying query, and the associated Mview LOG become invalid. There is no way a FAST REFRESH Mview can continue to refresh after such an invalidation. Even putting the table etc. back is no good. You will have to create the Mview which would mean dropping the thing and recreating it so that it is back in synch with its underling data objects and logs.

how does this sound?

Kevin
Re: Materialized View refresh [message #315506 is a reply to message #315505] Mon, 21 April 2008 15:06 Go to previous messageGo to next message
Agus211
Messages: 39
Registered: September 2007
Member
Hi Kevin, thank's for replying. This is just what I thought!. The thing is that sometimes an aplication I don't own drops and recreates a table that I use to create a Mview, I think I'll have to figure out how to handle this. Thanks!.
Re: Materialized View refresh [message #315507 is a reply to message #315499] Mon, 21 April 2008 15:07 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
yeah I hear you.

Tell those dummies that are dropping tables to get their heads out of the stone ages and start writing database friendly code.

Good luck, Kevin
Re: Materialized View refresh [message #315549 is a reply to message #315507] Mon, 21 April 2008 21:06 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Take a look at the USER_MVIEWS data dictionary view. There is a status column there that tells you if the MV is invalid (unless it is remote). You should be able to recompile it to make it work, however you would need a FULL refresh.

Ross Leishman
Previous Topic: View access
Next Topic: Column to rows
Goto Forum:
  


Current Time: Tue Dec 06 06:09:20 CST 2016

Total time taken to generate the page: 0.08466 seconds