CODEWITHSUNDEEP
oracle-databasematerialized-views
chip
chip

Reputation: 3279

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

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).

Upvotes: 0

Views: 1383

Answers (2)

lescijus
lescijus

Reputation: 76

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;

Upvotes: 1

APC
APC

Reputation: 146239

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.

Upvotes: 1

Related Questions