SQL>
SQL> create table my_all_objects
2 nologging
3 as
4 select * from all_objects
5 union all
6 select * from all_objects
7 union all
8 select * from all_objects
9 /
Table created.
SQL>
SQL>
SQL> alter session set query_rewrite_enabled=true;
Session altered.
SQL>
SQL> alter session set query_rewrite_integrity=enforced;
Session altered.
SQL>
SQL> create materialized view my_all_objects_aggs
2 build immediate
3 refresh on commit
4 enable query rewrite
5 as
6 select owner, count(*)
7 from my_all_objects
8 group by owner
9 /
SQL>
SQL> analyze table my_all_objects_aggs compute statistics;
SQL>
SQL> drop table my_all_objects;
Table dropped.
SQL>
SQL>