Mansi Raval, June 21, 2017 - 9:02 am UTC
Thank you so much Chris.
Thanks for the examples and links.
updating Materialized view
sankar, July 11, 2017 - 12:03 pm UTC
use below code to update materialized view when base table has having new rows.. this is only for updating materialized view from base table to materialized view.
create materialized view mv
here use on commit is refresh table automatically for mannual use on demand..
refresh fast with primary key for update on commit
as
select * from t;
update mv
set y = 3;
insert into mv values (3, 3);
select * from mv;
X Y
---------- ----------
1 3
2 3
3 3
select * from t;
X Y
---------- ----------
1 1
2 2
July 11, 2017 - 2:31 pm UTC
I'm not following what you're doing, could you clarify please?
The updates and insert can be captured..
Ron, July 24, 2018 - 4:40 pm UTC
create materialized view on pre-built table.
create a trigger on the pre-built table
to capture inserts and updates.
and then the captured data could be updated or inserted
on the base table and then when mview is refreshed it wont be lost.
The inserts could be done thru batch or dbms_Scheduler on base.
July 26, 2018 - 10:41 am UTC
What are you trying to achieve by doing this? What's the end goal here?
And what's the exact code you're running?
A reader, January 20, 2019 - 9:51 am UTC
keep data when updating materialized view
Arnold, January 25, 2019 - 10:37 am UTC
dear Chris,
To keep the data in the underlying table and change them (in 11.2) use:
drop materialized view mv preserve table;
update mv set y = 3;
commit;
You reinstall the materialized view by adding 'on prebuilt table' as follows:
create materialized view mv on prebuilt table refresh fast with primary key as select * from t;
So, the mv is again not updatable.
Below is the whole procedure. Thanks for this great site.
prompt remove previous experiment (dropping the table mv probably is not needed)
drop table t;
drop materialized view mv;
drop table mv;
prompt as before
create table t ( x int primary key, y int );
insert into t values (1, 1);
insert into t values (2, 2);
commit;
create materialized view log on t including new values;
create materialized view mv refresh fast with primary key as select * from t;
prompt there are two database objects for mv:
select object_type from all_objects where object_name='MV';
prompt drop the materialized view, not the underlying table
drop materialized view mv preserve table;
prompt only the table for mv is left:
select object_type from all_objects where object_name='MV';
prompt adapt mv
update mv set y = 3;
commit;
prompt see what is in the table mv now:
select * from mv;
prompt reinstall the mv with exactly the same syntax and 'on prebuilt table' added:
create materialized view mv on prebuilt table refresh fast with primary key as select * from t;
prompt there are two database objects again for mv:
select object_type from all_objects where object_name='MV';
prompt the mv is again not updatable (error)
update mv set y = 4;
prompt remove all: as the materialized view has a prebuilt table, which needs to be removed separately:
drop materialized view mv;
drop table mv;
drop table t;
February 04, 2019 - 1:48 pm UTC
Thanks for sharing - though this is a bit convoluted; it wouldn't be my first choice...
from Create Mview Syntax
Rajeshwaran, Jeyabal, February 14, 2019 - 10:10 am UTC
syntax
Sunny, February 03, 2021 - 6:47 am UTC
why materialized view does not have "replace" option?
any logic behind this?
February 03, 2021 - 2:21 pm UTC
What do you mean exactly? Please clarify!
why materialized view does not have "replace" option?
A reader, February 04, 2021 - 8:18 am UTC
Same reason a table does not create or replace.
Mviews are not simple views but have a table to hold the data as well (replication).
Hope it clarifies.
Cheers.
thank you
A reader, February 07, 2024 - 4:23 am UTC