Hi all,
Can I create a materialize view using union all and from other mat views?
Example:
DROP MATERIALIZED VIEW mat_vw_test;
CREATE MATERIALIZED VIEW mat_vw_test REFRESH FAST ON COMMIT WITH PRIMARY KEY DISABLE QUERY REWRITE AS
SELECT a.rowID AS arowID,
b.rowID AS browID,
CHARTOROWID('AAALktAAGAAAABSAAU') AS crowID,
1 AS marker,
a.field_A, b.field_B, b.field_BC
from tableA a, tableB b
where tableA.myID = tableB.myID
and a.cond = 'B'
union all
SELECT a.rowID AS arowID,
b.rowID AS browID,
c.rowID AS crowID,
2 AS marker,
a.field_A, b.field_B, c.field_C
from tableAB a, mat_vw_tableBB b, tableCB c
where tableA.myID = tableB.myID
and b.req_id(+) = c.req_id
and a.cond = 'A'
union all
SELECT a.rowID AS arowID,
b.rowID AS browID,
c.rowID AS crowID,
3 AS marker,
a.field_A, b.field_B, c.field_C
from tableAC a, tableBC b, mat_vw_tableCC c
where tableA.myID = tableB.myID
and b.req_id(+) = c.req_id
and a.cond = 'D';
I don't get any error at creation but it takes a lot at the time of run. The mat_vw_tableCC run before inserting new records.
Any ideas?
Thanks!
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0