Home » SQL & PL/SQL » SQL & PL/SQL » changing query that builds a materialized view (Oracle 10g)
changing query that builds a materialized view [message #295817] Wed, 23 January 2008 10:04 Go to next message
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
Re: changing query that builds a materialized view [message #295821 is a reply to message #295817] Wed, 23 January 2008 10:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You have to recreate the mview.

Regards
Michel
Re: changing query that builds a materialized view [message #295845 is a reply to message #295821] Wed, 23 January 2008 13:48 Go to previous messageGo to next message
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.
Re: changing query that builds a materialized view [message #295846 is a reply to message #295845] Wed, 23 January 2008 13:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You drop and recreate the mview.

Regards
Michel
Re: changing query that builds a materialized view [message #295895 is a reply to message #295846] Wed, 23 January 2008 20:08 Go to previous messageGo to next message
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
Re: changing query that builds a materialized view [message #296704 is a reply to message #295817] Mon, 28 January 2008 15:44 Go to previous messageGo to next message
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..
Re: changing query that builds a materialized view [message #296769 is a reply to message #296704] Tue, 29 January 2008 00:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
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
Re: changing query that builds a materialized view [message #296800 is a reply to message #295817] Tue, 29 January 2008 01:51 Go to previous message
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.

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


Current Time: Fri Feb 14 16:42:30 CST 2025