8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Materialized View Concurrent Refreshes in Oracle Database 23c

In previous releases on-commit materialized view refreshes were serialized. If multiple session triggered an on-commit refresh of the same materialized view, they were performed one at a time. This could impact performance on busy tables. In Oracle 23c materialized view refreshes can be performed concurrently for on-commit fast refreshes by adding the ENABLE CONCURRENT REFRESH clause.

Setup
Concurrent Refreshes

Setup

The example in this article requires the following setup.

Create and populate two tables with orders and order lines data.

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');

Create materialized logs on the two tables to enable fast refreshes.

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;

Concurrent Refreshes

In Oracle 23c materialized view refreshes can be performed concurrently for on-commit fast refreshes by adding the ENABLE CONCURRENT REFRESH clause.

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');

We can see concurrent refreshes are enabled by displaying the CONCURRENT_REFRESH_ENABLED column in the USER_MVIEWS view.

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>

Concurrent refreshes will only happen if there are no overlaps in the materialized view rows updated by each session.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.