OraFAQ Forum: SQL & PL/SQL » changing query that builds a materialized view

Home » SQL & PL/SQL » SQL & PL/SQL » changing query that builds a materialized view (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

changing query that builds a materialized view [message #295817]

Wed, 23 January 2008 10:04

msp2481
Messages: 3
Registered: January 2008

Junior Member

Hello,

I recently created a materialized view and it works fine. Now I need to change the query that builds this materialized view. Using Toad, I am unable to change the query that builds the materialized view by going to alter materialized view option. I am unable to modify the query that was initially used to create the materialized view.

Why am I not able to change the query behind the materialized view? Do I need to perform a particular action or have certain privileges before I can modify the query that builds the materialized view. Any help will be appreciated.

Thanks

Report message to a moderator

Re: changing query that builds a materialized view [message #295821 is a reply to message #295817]

Wed, 23 January 2008 10:44

Michel Cadot
Messages: 68773
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You have to recreate the mview.

Regards
Michel

Report message to a moderator

Re: changing query that builds a materialized view [message #295845 is a reply to message #295821]

Wed, 23 January 2008 13:48

msp2481
Messages: 3
Registered: January 2008

Junior Member

Thanks for the reply Michel.

I am trying to figure out what the correct process is for recreating the materialized view with the same name. Will I need to drop the existing snapshot/table (just like a normal table) and the materialized view and then recreate everything? It is important that the materialized view name stays the same. Thanks for your help.

Report message to a moderator

Re: changing query that builds a materialized view [message #295846 is a reply to message #295845]

Wed, 23 January 2008 13:52

Michel Cadot
Messages: 68773
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You drop and recreate the mview.

Regards
Michel

Report message to a moderator

Re: changing query that builds a materialized view [message #295895 is a reply to message #295846]

Wed, 23 January 2008 20:08

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

If you created the MV originally with the ON PREBUILT TABLE option, dropping the MV will not drop the table - you would have to drop it manually. Then you recreate the MV as per usual.

Ross Leishman

Report message to a moderator

Re: changing query that builds a materialized view [message #296704 is a reply to message #295817]

Mon, 28 January 2008 15:44

msp2481
Messages: 3
Registered: January 2008

Junior Member

Thanks for all the replies.

I am currently in the process of testing the materialized view and I need to make minor changes to the query that builds the MV to do the testing. I might have to change the query a few times to cover all my testing. Dropping and recreating the MV every time seems like a lot of work for such a small change. Is there another option or a better way to handle this situation using materialized views?

Is there a reason why MVs are restricted this way?

Thanks..

Report message to a moderator

Re: changing query that builds a materialized view [message #296769 is a reply to message #296704]

Tue, 29 January 2008 00:33

Michel Cadot
Messages: 68773
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No there is no way.

Quote:

Is there a reason why MVs are restricted this way?

How could this be other way?
Mview is something that is built not just a string like view.
Mview is like "create table as select". If you change select you change the whole table.

Regards
Michel

Report message to a moderator

Re: changing query that builds a materialized view [message #296800 is a reply to message #295817]

Tue, 29 January 2008 01:51

MarcS
Messages: 312
Registered: March 2007
Location: Antwerp

Senior Member

If you're only changing the query that would build the MV: why don't you just tune & change the query itself without building a MV?

When the output of the query is what you need, then build the MV for further testing.

If you're experimenting with MV's in how to make them and how they work, then dropping & recreating is your only option.

Report message to a moderator

Previous Topic:	Users of one Schema
Next Topic:	ascii to char conversion....

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 23 23:45:19 CST 2025