Skip to Main Content
  • Questions
  • Oracle Materialized View - adding a Where clause


Question and Answer

Chris Saxon

Thanks for the question.

Asked: January 25, 2023 - 12:28 pm UTC

Last updated: January 25, 2023 - 1:34 pm UTC

Version: 1.5.5

Viewed 100+ times

You Asked

I have a materialised view in my own schema which based on a table in another schema. However I want to modify the matrealise view to add a where clause which will never becomes true e.g. Where 1=2
Can i alter an existing materialised view to add this where clause in the MView query . Will alter command works or I have to drop and recreate the Mview (Which i am rluctatant to do actually)

and Chris said...

You can't use ALTER MATERIALIZED VIEW to change the query. So you'll (probably) have to drop & recreate it.

That said, there is a trick!

If you create the materialized view over a (regular) view, you can change the underlying view definition. The MV will pick up the new query on the next complete refresh.

create or replace view vw as 
  select count(*) c from hr.employees;
create materialized view mv as 
  select * from vw;

select * from mv;

create or replace view vw as 
  select count(*) c from hr.employees
  where  1 = 2;
exec dbms_mview.refresh ( 'mv' );

select * from mv;

Finally - what exactly is the point of adding the always false condition?!

More to Explore


New to good database design? Check out Chris Saxon's full fundamentals class.