do we need to recreate a materialized view if the underlying table structure changes
I have changed the order of columns in my base table, but after that materialized of this table in showing as invalid, even after refreshing it.
Do I need to recreate the materialized view or is there something else I can implement.
Is it happening because I had dropped and recreated the underlying base table.
Please help. Thanks!
Answer
You do not need to rebuild it. However, if you change an object that the materialized view depends on you will need to recompile the view to validate and compute the staleness of it:
ALTER MATERIALIZED VIEW mview_name COMPILE;
You can check whether your view needs to be recompiled by checking the STALENESS column in USER_MVIEWS for your MV in question. If the column shows "NEEDS_COMPILE", you need to execute above statement.
Have also a look at Invalidating Materialized Views in the Database Data Warehousing Guide and the ALL_MVIEWS Reference in the documentation.