Redefine materialized view with no downtime

materialized-views, oracle

Solution

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');

Problem

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: - Create public synonym for materialized view and make everything that uses the view use the synonym instead. - Create new materialized view with new SQL - Change the synonym to point to the new view - 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.

Original source