oracle between clause cause ORA-12015: cannot create a fast refresh materialized view from a complex query

1
votes

I successfully create a materialized view with the script:

CREATE MATERIALIZED VIEW orderitem_mvmt
BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
AS
select * from order_item;

Trying to increase performances I change like this

CREATE MATERIALIZED VIEW orderitem_mvmt
BUILD IMMEDIATE
REFRESH FAST
ON DEMAND
AS
select * from order_item
where to_char(last_upd,'yyyy') between to_char(sysdate,'yyyy')-3 and  to_char(sysdate,'yyyy')+3;

Running the script raise the error ORA-12015: cannot create a fast refresh materialized view from a complex query

I don't understant how It match the Fast Refresh Restrictions

Thanks.

1
oraclematerialized-views

1 Answers

4
votes

Have a look at General Restrictions on Fast Refresh

The defining query of the materialized view is restricted as follows:

  • The materialized view must not contain references to non-repeating expressions like SYSDATE and ROWNUM.

  • The materialized view must not contain references to RAW or LONG RAW data types.

  • It cannot contain a SELECT list subquery.

  • It cannot contain analytic functions (for example, RANK) in the SELECT clause.

  • It cannot contain a MODEL clause.

  • It cannot contain a HAVING clause with a subquery.

  • It cannot contain nested queries that have ANY, ALL, or NOT EXISTS.

  • It cannot contain a [START WITH …] CONNECT BY clause.

  • It cannot contain multiple detail tables at different sites.

  • ON COMMIT materialized views cannot have remote detail tables.

  • Nested materialized views must have a join or aggregate.

  • Materialized join views and materialized aggregate views with a GROUP BY clause cannot select from an index-organized table.

Your query contains SYSDATE thus you cannot use it for FAST REFRESH