In this case, a view can be created that hides some nastier syntax like a "union".
A materialized view can contain the view with a simple query. So if the end query just uses the view, it should rewrite to the materialized view.
Furthermore, an exception is thrown when it contains the "union" while creating the view. At a minimum, we should print a friendlier message when the rewrite fails.
A script is attached.
An example of this:
create view logical_complex0 as
with t as
(select c1 as a, c2 as b from tab1 where c2 in (select f from logical_simple where g > 0)
select tab3.c1 as c, tab4.c2 as d from tab3, tab4 where tab3.c2 = tab4.c2)
select a, b
– query separator
create materialized view aview_complex0 stored as orc as
select a as x, b as y, count
group by 1, 2;