Materialized View won't get created if I use refresh fast clause

0
votes

I use this clause to create a materialized view

CREATE MATERIALIZED VIEW mvName
REFRESH FAST
START WITH TO_DATE('01-APR-2016 12:19:00','dd-mon-yyyy hh24:mi:ss')
NEXT SYSDATE+1/1440 as ....;

Using this script, this won't create the materialized view. But if I use REFRESH COMPLETE, then it will create the materialized view.

There is also a note in the oracle doc that says If you specify REFRESH FAST, then the CREATE statement will fail unless materialized view logs already exist for the materialized view master tables. Oracle Database creates the direct loader log automatically when a direct-path INSERT takes place. No user intervention is needed.

And I'm not sure if the issue that I'm having is because of this comment which I don't fully understand (this one specifically: then the CREATE statement will fail unless materialized view logs already exist for the materialized view master tables).

2
oraclematerialized-views
Really, the only important part of the script is the as ... part...which you left out. - Gerrat
wut? why? that's a select * stmt. is there something I need to take note of specifically when I use a REFRESH FAST clause? - chip
Well, because there are a ton of restrictions on your select that may or may not make it eligible for a fast refresh. Without knowing what your sql was, it's impossible to know if it is even available for fast refresh. - Gerrat

2 Answers

1
votes

The FAST REFRESH option allows us to synchronize a materialized view with its underlying table(s) by applying just the delta since the previous refresh. The trick is that Oracle needs to know what the delta is.

So we need to create materialized view log on the underlying source table. And, as the error message makes clear, we cannot create a materialized view with the FAST REFRESH option unless all the tables in the SELECT clause have such logs. Find out more.

We don't need the logs for a materialized view with the COMPLETE REFRESH option because that queries the whole table each time.

1
votes
create materialized view log on xx_test_tab;

CREATE MATERIALIZED VIEW mvName
REFRESH FAST
START WITH TO_DATE('01-APR-2016 12:19:00','dd-mon-yyyy hh24:mi:ss')
NEXT SYSDATE+1/1440 as select * from xx_test_tab;