8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Materialized View Support for ANSI Joins in Oracle Database 23c
In Oracle 23c materialized views support query rewrites for SQL statements using ANSI or Oracle style joins. This means your materialized view definitions and SQL statements can use either join syntax and still allow query rewrites. In the examples below we test the various combinations of join syntax for query rewrites.
Related articles.
- Materialized View Enhancements in Oracle Database 23c
- Materialized Views in Oracle
- Real-Time Materialized Views in Oracle Database 12c Release 2 (12.2)
Setup
The examples in this article require the following setup.
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)
);
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;
Support for ANSI Joins
We create a materialized view using an ANSI join and enable it for query rewrites.
drop materialized view if exists order_summary_rtmv;
create materialized view order_summary_rtmv
refresh fast on demand
enable query rewrite
as
select o.created_date,
ol.order_id,
sum(ol.line_qty) as sum_line_qty,
sum(ol.total_value) as sum_total_value,
count(*) as row_count
from orders o
join order_lines ol on ol.order_id = o.id
group by o.created_date,
ol.order_id;
exec dbms_stats.gather_table_stats(null, 'order_summary_rtmv');
We query the base tables using an ANSI join, and we can see in the execution plan the query was rewritten to use the materialized view.
select o.created_date,
ol.order_id,
sum(ol.line_qty) as sum_line_qty,
sum(ol.total_value) as sum_total_value,
count(*) as row_count
from orders o
join order_lines ol on ol.order_id = o.id
where o.id = 1
group by o.created_date,
ol.order_id;
set linesize 100
select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 9gcp0n704gs7g, child number 0
-------------------------------------
select o.created_date, ol.order_id, sum(ol.line_qty) as
sum_line_qty, sum(ol.total_value) as sum_total_value,
count(*) as row_count from orders o join order_lines ol on
ol.order_id = o.id where o.id = 1 group by o.created_date,
ol.order_id
Plan hash value: 1165901663
---------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
|* 1 | MAT_VIEW REWRITE ACCESS FULL| ORDER_SUMMARY_RTMV | 1 | 26 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ORDER_SUMMARY_RTMV"."ORDER_ID"=1)
22 rows selected.
SQL>
This time the query joins the base tables using an Oracle join. Once again, the query is rewritten to use the materialized view.
select o.created_date,
ol.order_id,
sum(ol.line_qty) as sum_line_qty,
sum(ol.total_value) as sum_total_value,
count(*) as row_count
from orders o,
order_lines ol
where ol.order_id = o.id
and o.id = 1
group by o.created_date,
ol.order_id;
set linesize 100
select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 4m9fsqh0n4df3, child number 0
-------------------------------------
select o.created_date, ol.order_id, sum(ol.line_qty) as
sum_line_qty, sum(ol.total_value) as sum_total_value,
count(*) as row_count from orders o, order_lines ol where
ol.order_id = o.id and o.id = 1 group by o.created_date,
ol.order_id
Plan hash value: 1165901663
---------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
|* 1 | MAT_VIEW REWRITE ACCESS FULL| ORDER_SUMMARY_RTMV | 1 | 26 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ORDER_SUMMARY_RTMV"."ORDER_ID"=1)
22 rows selected.
SQL>
Support for Oracle Joins
We recreate the materialized view using an Oracle join.
drop materialized view if exists order_summary_rtmv;
create materialized view order_summary_rtmv
refresh fast on demand
enable query rewrite
as
select o.created_date,
ol.order_id,
sum(ol.line_qty) as sum_line_qty,
sum(ol.total_value) as sum_total_value,
count(*) as row_count
from orders o,
order_lines ol
where ol.order_id = o.id
group by o.created_date,
ol.order_id;
exec dbms_stats.gather_table_stats(null, 'order_summary_rtmv');
We query the base tables using an ANSI join, and we can see in the execution plan the query was rewritten to use the materialized view.
select o.created_date,
ol.order_id,
sum(ol.line_qty) as sum_line_qty,
sum(ol.total_value) as sum_total_value,
count(*) as row_count
from orders o
join order_lines ol on ol.order_id = o.id
where o.id = 1
group by o.created_date,
ol.order_id;
set linesize 100
select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 9gcp0n704gs7g, child number 0
-------------------------------------
select o.created_date, ol.order_id, sum(ol.line_qty) as
sum_line_qty, sum(ol.total_value) as sum_total_value,
count(*) as row_count from orders o join order_lines ol on
ol.order_id = o.id where o.id = 1 group by o.created_date,
ol.order_id
Plan hash value: 1165901663
---------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
|* 1 | MAT_VIEW REWRITE ACCESS FULL| ORDER_SUMMARY_RTMV | 1 | 26 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ORDER_SUMMARY_RTMV"."ORDER_ID"=1)
22 rows selected.
SQL>
This time the query joins the base tables using an Oracle join. Once again, the query is rewritten to use the materialized view.
select o.created_date,
ol.order_id,
sum(ol.line_qty) as sum_line_qty,
sum(ol.total_value) as sum_total_value,
count(*) as row_count
from orders o,
order_lines ol
where ol.order_id = o.id
and o.id = 1
group by o.created_date,
ol.order_id;
set linesize 100
select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 4m9fsqh0n4df3, child number 0
-------------------------------------
select o.created_date, ol.order_id, sum(ol.line_qty) as
sum_line_qty, sum(ol.total_value) as sum_total_value,
count(*) as row_count from orders o, order_lines ol where
ol.order_id = o.id and o.id = 1 group by o.created_date,
ol.order_id
Plan hash value: 1165901663
---------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
|* 1 | MAT_VIEW REWRITE ACCESS FULL| ORDER_SUMMARY_RTMV | 1 | 26 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ORDER_SUMMARY_RTMV"."ORDER_ID"=1)
22 rows selected.
SQL>
For more information see:
- Creating Materialized Views
- Materialized View Enhancements in Oracle Database 23c
- Materialized Views in Oracle
- Real-Time Materialized Views in Oracle Database 12c Release 2 (12.2)
Hope this helps. Regards Tim...