ORA-12015: Cannot Create A Fast Refresh Materialized View From A Complex Query

I am using below query to build a materialized view. CREATE MATERIALIZED VIEW gcms_business_profile_mview BUILD IMMEDIATE REFRESH FAST WITH PRIMARY KEY STA

Solution 1:

CREATE MATERIALIZED VIEW - Restrictions on FAST Refresh

Restrictions on FAST Refresh

FAST refresh is subject to the following restrictions:

When you specify FAST refresh at create time, Oracle Database verifies that the materialized view you are creating is eligible for fast refresh. When you change the refresh method to FAST in an ALTER MATERIALIZED VIEW statement, Oracle Database does not perform this verification. If the materialized view is not eligible for fast refresh, then Oracle Database returns an error when you attempt to refresh this view.

• Materialized views are not eligible for fast refresh if the defining query contains an analytic function or the XMLTable function.

• Materialized views are not eligible for fast refresh if the defining query references a table on which an XMLIndex index is defined.

• You cannot fast refresh a materialized view if any of its columns is encrypted.

---

Your query contains an analytic finction:

ROW_NUMBER () OVER (PARTITION BY bp_id ORDER BY updtd_dt DESC) 

therefore you cannot use a fast refresh for this query.