Redefine materialized view with no downtime

Question

I have a materialized view that I need to redefine the SQL for. We have an external system that hits the view over a db link, and the monster view takes 5 minutes to refresh the data in the view. The only way I know how to redefine the SQL for a view is to drop it and recreate it, but it would be very bad if the external system couldn't find the table, or it didn't have a complete data set. I need to have as little downtime as possible.

Is there any way to do this natively or more elegantly than:

1. Create public synonym for materialized view and make everything that uses the view use the synonym instead.
2. Create new materialized view with new SQL
3. Change the synonym to point to the new view
4. Drop the old view.

I've got code to do this dynamically but it is getting really ugly. It seems like there should be a better way to handle this.

Answer 1

Oracle has a build in solution for that. Keep in mind that the mview declaration is separate from that of the table.

The original mview

create materialized view mv1 as select dept , count(*) as cnt from scott.emp;

we want to change the declaration so that only dept over 5 will be calculated

drop materialized view mv1 preserve table;

notice the PRESERVE TABLE clause - the table mv1 is not droped - only the mview layer.

desc mv1

now we create the mview with a different query on top of the existing table

create materialized view mv1 on prebuilt table as 
  select dept , count(*) as cnt from scott.emp where dept > 5;

notice the on prebuilt table clause. the mview is using the existing object.

exec dbms_mview.refresh_mview('mv1');