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

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.

Source: oracle-base.com