I have been trying to create a materialized view...

LOG

CREATE MATERIALIZED VIEW LOG
ON cdb.reporter
TABLESPACE cdb_views
WITH PRIMARY KEY
INCLUDING NEW VALUES;

VIEW

create materialized view
mviews.mv_reporter
USING NO INDEX
REFRESH FAST
as (select
state_code,
rept_num,
ref_yy,
ref_mm,
phase,
version,
payroll_indicator,
com_code1,
com_code2,
pro_factor
from cdb.reporter
where payroll_indicator = 0
and ref_yy||ref_mm >= to_char((sysdate-790.955),'YYYYMM'));

When I do I get the error ...

from cdb.reporter
*
ERROR at line 16:
ORA-12015: cannot create a fast refresh materialized view from a complex query

When I take out the last line "and ref_yy||ref_mm >= to_char((sysdate-790.955),'YYYYMM')" it is created fine.

I supposed that the materalized view just couldn't handle that last line. But a developer ran the following on his oracle instance and he says it worked ...

CREATE MATERIALIZED VIEW LOG ON CDB.REPORTER;

create materialized view
cdb.reporter_est_view
USING NO INDEX
REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 1/96
as (select
state_code,
rept_num,
ref_yy,
ref_mm,
phase,
version,
payroll_indicator,
com_code1,
com_code2,
pro_factor
from cdb.reporter
where payroll_indicator = 0
and ref_yy||ref_mm >= to_char((sysdate-182.625),'YYYYMM'));

So from what he's told me the last line is valid in a materialized view. From what I read on line, some functions are now allowed with the refresh fast option. I figured the sysdate was one.

The only other difference I see is that we're version 10.2.0.2 and he's 10.2.0.3.

Can someone explain this one to me?

THanks
JJ

Hi JJ,

First:

- 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





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

JJ,

See Metalink Note:179466.1: