Materialized Views and indexes
511922Sep 19 2008 — edited Sep 23 2008Hi,
we have a Materialized view created with following SQL:
select * from f4211
Union
select * from f42119;
F4211 has 50K rows
F42119 as 5 Million rows
Its complete refersh as Union is there...
we have two indexes created on two columns of MV as user demanded it.
BEfore indexes, refresh was taking 1h30m to complete.. now its taking 4hrs to complete..
my questions are:
1. Is the time increase due to indexes?
2. does index also rebuilt with MV refresh as i believe MV refresh truncates MV table and inserts all the data based on query?
3. any option to rebuild index after MV refresh is compelete in case they run in parallel?
4. Any option to tune this MV refresh time?
thx