DBA Hub

📋Steps in this guide1/3

Materialized View Support for ANSI Joins in Oracle Database 23ai/26ai

In Oracle 23ai/26ai materialized views support query rewrites for SQL statements using ANSI or Oracle style joins.

oracle 23configurationintermediate
by OracleDba
14 views
1

Setup

The examples in this article require the following setup.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
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;
2

Support for ANSI Joins

We create a materialized view using an ANSI join and enable it for query rewrites. 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. This time the query joins the base tables using an Oracle join. Once again, the query is rewritten to use the materialized view.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
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');

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>

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>
3

Support for Oracle Joins

We recreate the materialized view using an Oracle join. 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. This time the query joins the base tables using an Oracle join. Once again, the query is rewritten to use the materialized view. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
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');

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>

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!