Having fast refresh on materialized views having virtual columns

Question

I wanted a Materialized View with fast refresh (update on the contained tables update). The materialized view query is something like:

SELECT T1.CODE, T2.NAME
FROM T1

UNION ALL

SELECT T2.CODE, T3.NAME
FROM T2, T3
WHERE T2.ID = T3.ID

It appears that in case of using UNION ALL I should add ROWID to achieve fast refresh.

Adding ROWID is ok for the first part, but for the second part it can not be added as it has a join.

It seems there are 2 options to overcome this:

Adding a redundant column for NAME on T2 and remove the T3 from query.
Adding a virtual column containing a function that selects name from T3 like

NAME NVARCHAR2(4000) GENERATED ALWAYS AS ("SCHEMA"."GET_NAME_FROM_OTHER_TABLE"("ID"))

According to this link: http://www.oracle-base.com/articles/11g/virtual-columns-11gr1.php it says:

Materialized views that access the virtual column must be fully refreshed.

and

If it refers to a deterministic user-defined function, it cannot be used as a partitioning key column.

Could I still have a Fast Refresh option on the materialized view using Solution 2?

function that uses data from another table cannot be deterministic - then no. — haki, Jan 28 '14 at 08:40
http://stackoverflow.com/questions/7505020/oracle-how-to-create-a-materialized-view-with-fast-refresh-and-joins might help. — Frank Schmitt, Jan 28 '14 at 11:49

score 0 · Answer 1 · answered Jan 28 '14 at 09:21

Does this work?

SELECT T1.CODE, T2.NAME, T1.ROWID as ROWID_T1, NULL as ROWID_T2, NULL as ROWID_T3 
FROM T1

UNION ALL

SELECT T2.CODE, T3.NAME, NULL as ROWID_T1, T2.ROWID as ROWID_T2, T3.ROWID as ROWID_T3 
FROM T2, T3
WHERE T2.ID = T3.ID

Maybe you have to use CAST(NULL AS ROWID) AS ROWID_T2 in order to get proper data type in first query.

Having fast refresh on materialized views having virtual columns

1 Answers1