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)
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;
/*
C
----------
107
*/
create or replace view vw as
select count(*) c from hr.employees
where 1 = 2;
exec dbms_mview.refresh ( 'mv' );
select * from mv;
/*
C
----------
0
*/Finally - what exactly is the point of adding the always false condition?!