6

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.

bitwes
  • 61
  • 1
  • 2
  • What sort of redefinition are you trying to do? Add new columns?change data types? – David Aldridge Aug 24 '13 at 09:16
  • Adding rows mostly. That is the most common case and usually involves adding a UNION ALL to the definition of the view. The view contains a list of access levels and each access level is usually defined by it's own query. We often have to add/remove/redefine an access level. – bitwes Sep 05 '13 at 20:22

1 Answers1

7

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');
haki
  • 9,389
  • 15
  • 62
  • 110
  • How do we go about doing that if the metadata (e.g., number of columns) has to change? Is there a way to keep alive the table of the old MV until the new one is completely built? – precise Jun 04 '20 at 12:35