Thanks for the question, Santhosh.
Asked: May 23, 2017 - 3:39 pm UTC
Last updated: May 24, 2017 - 2:08 am UTC
Version: ORACLE 11G
Viewed 1000+ times
You Asked
Greetings!!!
I am trying to create a materialized view based on a FACT and 3 Dimension tables, all IOT. As per my requirement to handle complex scenarios, I need to create multiple with clauses in the materialized view. My question is:
1, As I am building the materialized view above the dynamic queries made upon the with clauses, Will the Materialized view be refreshed if the FACT (base table) data changes?
I have provided the a sample SQL.
with LiveSQL Test Case:
and Connor said...
Thanks for giving us the SQL scripts.
A materialized view can be kept up to date in several ways
- on demand
- on a schedule
- on each commit
- per query (12.2 onwards)
and the refresh that is required can be either
- fast (apply just deltas from the source tables)
- complete (re-run the entire mv query and re-populate the table).
See this link for an example of how you can get the database to tell you if a mview is a candidate for fast refresh
https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9533445800346654016