Does a "union all" materialized view take space?

Does a refresh fast on commit union all materialized view (in Oracle) copy all the underlying data, or does it just have a reference to it?

If this materialized view does copy all the data is there anyway to do the following:

create table3 as ( table1 union all table2 );

So that I can create indexes and materialized view logs on table3, and table 3 is just a reference to table 1 and table 2.

The reason for this is I want put the following in a materialized view:

create materialized view mat1
refresh fast on commit
开发者_Go百科(
  select data, count(*)
  from (table1 union all table2)
  group by data
);

But the above isn't fast refreshable.

But the following sort of thing works:

create materialized view mat1
refresh fast on commit
(
  select data from table1
  union all
  select data from table2
);

create materialized view mat2
refresh fast on commit
(
  select data, count(*) 
  from mat2
  group by data
);

But I'm concerned the first materialized view is unnecessarily replicating all the data.

A materialized view does exactly that - it "materializes" the data by creating a table containing the data defined by the query. You can demonstrate this by dropping the materialized view but preserving the contents as a table:

DROP MATERIALIZED VIEW xxx PRESERVE TABLE;

Which keeps a table named xxx that is no longer refreshed. So, your mat1 view does indeed duplicate all the data in both tables.

Think about it - if it just "referenced" the union, then it would be a regular view, no? You can't have your cake and eat it too in this case.

EDIT:

You cannot have a fast refresh on a complex materialized view. Aggregate functions such as COUNT are one of the things that cause a view to be complex. According to the docs, so does a UNION ALL, so I'm surprised your mat1 view is fast refreshable.

Yes - it duplicates all data, otherwise it would be view with no need for any refreshing...

not sure but you could try:

create materialized view mat1
refresh fast on commit
(
  select data, sum (c) from
  (
  select 1 x, data, count(*) c from table1 group by 1, data
  union
  select 2, data, count(*) from table1 group by 2, data
  ) group by data
);