Oracle - Materialized View, can this MV be fast refreshable?

Question

I have a materialized view that looks somewhat like the following and I'm wondering if there is anyway to have this materialized view 'fast' refreshable? Basically, I'm asking the following:

1. Can a materialized view contain oracle functions such as COALESCE, NVL, NVL2, etc and still be fast refreshable

2. Can a materialized view contain functions that I have made and still be fast refreshable.

3. Can a materialized view contain joins to derived tables and still be fast refreshable?

I checked the Oracle documentation about this, and it did not list these restrictions, however after testing the case below on my own system, I don't believe it is possible.

Oracle version: 10g

SELECT COALESCE (col1, col2),
       myOracleFunction(col3, col4)
  FROM tableA a 
       LEFT OUTER JOIN
       (SELECT   id, MAX (sample_key) prim_sam_key
            FROM table_sample
        GROUP BY id
          HAVING COUNT (1) = 1) b ON a.id = b.id;

Answer 1

Requirements from the link you provided that you're missing:

• COUNT(*) must be specified.
• The SELECT list must contain all GROUP BY columns.

Also, the following requirement indicates that, for your query, a fast refresh will only be possible if table_sample has been updated, but tableA has not:

• Materialized aggregate views with outer joins are fast refreshable after conventional DML and direct loads, provided only the outer table has been modified. Also, unique constraints must exist on the join columns of the inner join table. If there are outer joins, all the joins must be connected by ANDs and must use the equality (=) operator.

Finally, when asking about materialized views, it is always a good idea to state exactly what materialized view logs you have created.