conn testuser1/testuser1@//localhost:1521/freepdb1

drop table if exists order_lines purge;
drop table if exists orders purge;


create table orders (
  id            number(10),
  created_date  date,
  constraint orders_pk primary key (id)
);


insert /*+ append */ into orders
select level as id,
       trunc(sysdate - dbms_random.value(0,366)) as created_date
from   dual connect by level <= 1000;
commit;


create table order_lines (
  id            number(10),
  order_id      number(10),
  line_qty      number(5),
  total_value   number(10,2),
  created_date  date,
  constraint order_lines_pk primary key (id),
  constraint ol_o_fk foreign key (order_id) references orders(id)
);

create index ol_o_fk_i on order_lines(order_id);


insert /*+ append */ into order_lines
select level as id,
       trunc(dbms_random.value(1,1000)) as order_id,
       trunc(dbms_random.value(1,20)) as line_qty,
       round(dbms_random.value(1,1000),2) as total_value,
       trunc(sysdate - dbms_random.value(0,366)) as created_date
from   dual connect by level <= 100000;
commit;

exec dbms_stats.gather_table_stats(null, 'orders');
exec dbms_stats.gather_table_stats(null, 'order_lines');

drop materialized view log if exists on orders;

create materialized view log on orders
with rowid, sequence(id, created_date)
including new values;

drop materialized view log if exists on order_lines;

create materialized view log on order_lines
with rowid, sequence(order_id, line_qty, total_value)
including new values;

drop materialized view if exists order_summary_rtmv;

create materialized view order_summary_rtmv
refresh fast on commit
enable concurrent refresh
as
select order_id,
       sum(line_qty) as sum_line_qty,
       sum(total_value) as sum_total_value,
       count(*) as row_count
from   order_lines
group by order_id;

exec dbms_stats.gather_table_stats(null, 'order_summary_rtmv');

column mview_name format a30
column concurrent_refresh_enabled format a30

select mview_name,
       concurrent_refresh_enabled
from   user_mviews;

MVIEW_NAME                     CONCURRENT_REFRESH_ENABLED
------------------------------ ------------------------------
ORDER_SUMMARY_RTMV             Y

SQL>