- Did you create a MV log for the table?
- Have you set set cursor_sharing=exact?
- Have you set the query rewrite parms?
That's a great question, what exactly constitutes a "complex" query and a "simple" materialized view.
I was taught that any query with aggregation (sum, etc.) or a join was considered complex.
In your case, you are concatenating two columns together (ref_yy||ref_mm) in a Boolean.
Is that complex? Evidently Oracle thinks so. You may want to open an SR.
Also, if 11g is a possibility, the new "virtual columns" may work, as you could reference the concatenation as a single column:http://www.dba-oracle.com/oracle11g/oracle...sed_columns.htm
ORA-12015 cannot create a fast refresh materialized view from a complex query
Cause: Neither ROWIDs nor primary key constraints are supported for complex queries.
Action: Reissue the command with the REFRESH FORCE or REFRESH COMPLETE option or create a simple materialized view.
Here we are, from the docs:
The following are restrictions for fast refresh materialized views with subqueries:
- Materialized views must be primary key materialized views.
- The master's materialized view log must include certain columns referenced in the subquery. For information about which columns must be included, see "Logging Columns in the Materialized View Log".
- If the subquery is many to many or one to many, join columns that are not part of a primary key must be included in the materialized view log of the master. This restriction does not apply to many to one subqueries.
- The subquery must be a positive subquery. For example, you can use the EXISTS condition, but not the NOT EXISTS condition.
- The subquery must use EXISTS to connect each nested level (IN is not allowed).
- Each table can be in only one EXISTS expression.
-The join expression must use exact match or equality comparisons (that is, equi-joins).
- Each table can be joined only once within the subquery.
- A primary key must exist for each table at each nested level.
- Each nested level can only reference the table in the level above it.
- Subqueries can include AND conditions, but each OR condition can only reference columns contained within one row. Multiple OR conditions within a subquery can be connected with an AND condition.