پشتیبانی از Materialized View برای ANSI JOINS در پایگاه داده Oracle 23c
در پایگاه داده MV Oracle 23c ها(Materialized View) قابلیت Query Rewrites را دستورات SQL که در آنها از مدل های ANSI join و Oracle style join باشد را پشتیبانی می کند. این بدان معناست که تعاریف MV و عبارات SQL شما می توانند از هر یک از دستورات join استفاده کنند و همچنان اجازه Query Rewrites را بدهند. در مثال‌های زیر، ترکیب‌های مختلف نحو پیوستن را برای Query Rewrites آزمایش می‌کنیم.
محیط تست :
در ابتدا محیط تست را با دستورات زیر آماده می کنیم.
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;
پشتیبانی از ANSI Joins:
ما با استفاده از ANSI join یک MV ایجاد می کنیم و آن را برای 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’);
ما جداول پایه را با استفاده از یک ANSI JOIN پرس و جو می کنیم و در طرح اجرا می بینیم که پرس و جو برای استفاده از MV شده بازنویسی شده است.
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 ۹gcp0n704gs7g, 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 |
—————————————————————————————————
| ۰ | SELECT STATEMENT | | | | ۴ (۱۰۰)| |
|* ۱ | MAT_VIEW REWRITE ACCESS FULL| ORDER_SUMMARY_RTMV | ۱ | ۲۶ | ۴ (۰)| ۰۰:۰۰:۰۱ |
—————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
۱ – filter(“ORDER_SUMMARY_RTMV”.”ORDER_ID”=1)
۲۲ rows selected.
SQL>
این بار پرس و جو با استفاده از یک Oracle Join نوشته می شود. یک بار دیگر، پرس و جو برای استفاده از MV بازنویسی می شود.
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 ۴m9fsqh0n4df3, 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 |
—————————————————————————————————
| ۰ | SELECT STATEMENT | | | | ۴ (۱۰۰)| |
|* ۱ | MAT_VIEW REWRITE ACCESS FULL| ORDER_SUMMARY_RTMV | ۱ | ۲۶ | ۴ (۰)| ۰۰:۰۰:۰۱ |
—————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
۱ – filter(“ORDER_SUMMARY_RTMV”.”ORDER_ID”=1)
۲۲ rows selected.
SQL>
پشتیبانی از Oracle Joins:
در این مرحله MV را با استفاده از 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’);
ما جداول پایه را با استفاده از یک ANSI JOIN پرس و جو می کنیم و در طرح اجرا می بینیم که پرس و جو برای استفاده از MV شده بازنویسی شده است.
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 ۹gcp0n704gs7g, 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 |
—————————————————————————————————
| ۰ | SELECT STATEMENT | | | | ۴ (۱۰۰)| |
|* ۱ | MAT_VIEW REWRITE ACCESS FULL| ORDER_SUMMARY_RTMV | ۱ | ۲۶ | ۴ (۰)| ۰۰:۰۰:۰۱ |
—————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
۱ – filter(“ORDER_SUMMARY_RTMV”.”ORDER_ID”=1)
۲۲ rows selected.
SQL>
این بار پرس و جو با استفاده از یک Oracle Join نوشته می شود. یک بار دیگر، پرس و جو برای استفاده از MV بازنویسی می شود.
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 ۴m9fsqh0n4df3, 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 |
—————————————————————————————————
| ۰ | SELECT STATEMENT | | | | ۴ (۱۰۰)| |
|* ۱ | MAT_VIEW REWRITE ACCESS FULL| ORDER_SUMMARY_RTMV | ۱ | ۲۶ | ۴ (۰)| ۰۰:۰۰:۰۱ |
—————————————————————————————————
Predicate Information (identified by operation id):
—————————————————
۱ – filter(“ORDER_SUMMARY_RTMV”.”ORDER_ID”=1)
۲۲ rows selected.
SQL>
دیدگاه خود را ثبت کنید
تمایل دارید در گفتگوها شرکت کنید؟در گفتگو ها شرکت کنید.