2テーブル結合の集計マテビューで確認
マテビューで参照されている場合と参照されていない場合でinsert10万件の時間を比較する
結論：
高速リフレッシュが有効の場合、更新性能は大幅に劣化する
(8.0.27)
前提:
「マテビュー」記載手順でマテビュー環境構築済
既存ルートユーザの認証方法変更済
alter user 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY password;
-- 1. 参照元テーブル作成
drop table test.order_header;
drop table test.order_detail;
create table test.order_header(
orderid int not null,
orderdate datetime ,
customerid int
);
alter table test.order_header add constraint order_header_pk primary key (orderid);
create table test.order_detail(
orderid int not null,
renban int not null,
productid int ,
unitprice int,
orderqty int
);
alter table test.order_detail add constraint order_detail_pk primary key (orderid,renban);
-- 2. マテビューログ作成
CALL flexviews.create_mvlog('test','order_header');
CALL flexviews.create_mvlog('test','order_detail');
select * from flexviews.mvlogs where table_name='order_header';
select * from flexviews.mvlogs where table_name='order_detail';
select * from flexviews.mvlog_02080293ff8f9f6d311b69e86f92cecd;
select * from flexviews.mvlog_70fbcd3aea64ec543ad200372d6621dc;
insert into test.order_header values(1,'2021-01-01',100);
insert into test.order_header values(2,'2021-01-02',200);
insert into test.order_detail values(1,1,9901,30,1);
insert into test.order_detail values(1,2,9902,50,10);
insert into test.order_detail values(1,3,9903,10,15);
insert into test.order_detail values(2,1,9901,30,2);
-- 3. マテビュー作成
call flexviews.create('test','mview1','INCREMENTAL');
set @mv_id = flexviews.get_id('test', 'mview1');
select @mv_id;
select * from flexviews.mview;
call flexviews.add_table(@mv_id, 'test', 'order_header', 't1', NULL);
call flexviews.add_table(@mv_id, 'test', 'order_detail', 't2', 'on t1.orderid = t2.orderid');
select * from flexviews.mview_table;
call flexviews.add_expr(@mv_id, 'SUM', 't2.unitprice * t2.orderqty', 'total_amount');
call flexviews.add_expr(@mv_id, 'GROUP', 't1.orderdate', 'group_on_orderdate');
call flexviews.add_expr(@mv_id, 'GROUP', 't2.productid', 'group_on_productid');
select * from flexviews.mview_expression;
call flexviews.enable(@mv_id);
select * from test.order_header;
select * from test.order_detail;
select * from test.mview1;
insert into test.order_header values(3,'2021-01-02',100);
insert into test.order_detail values(3,1,9901,30,10);
insert into test.order_detail values(3,2,9902,50,20);
-- 4. リフレッシュ動作確認
set @mv_id = flexviews.get_id('test', 'mview1');
select @mv_id;
call flexviews.refresh(@mv_id,'BOTH',NULL);
-- call flexviews.refresh(@mv_id,'COMPUTE',NULL);
-- call flexviews.refresh(@mv_id,'APPLY',NULL);
※うまく動かなくなった場合は下記を実施してリセットする
drop database flexviews;
testスキーマにあるマテビューの実表を削除
CDCサービスkill
CDCセットアップ
CDCサービス起動
install.sql実行
-- 5. データ投入の時間計測
truncate table test.order_header;
truncate table test.order_detail;
truncate table test.mview1;
drop procedure test.proc1;
delimiter //
create procedure test.proc1(in x int)
begin
declare i int;
set i = 0;
while i < x do
set i = i + 1;
insert into test.order_header values(
i
,date_add('2020-01-01', interval floor(100 * rand()) day)
,floor(rand() * 100)+1
);
insert into test.order_detail values(
i
,1
,floor(rand() * 100)+1
,(floor(rand() * 10)+1) * 100
,floor(rand() * 10)+1
);
insert into test.order_detail values(
i
,2
,floor(rand() * 100)+1
,(floor(rand() * 10)+1) * 100
,floor(rand() * 10)+1
);
end while;
end
//
delimiter ;
call test.proc1(100000);
-- 5.1. マテビューの参照テーブルとなっている場合
10万件
(3 min 19.14 sec)
-- 5.2. マテビューの参照テーブルとなっていない場合
10万件
(2 min 41.19 sec)
(19c)
https://seisenudoku.seesaa.net/article/472425809.html
-- 1. 参照元テーブル作成
drop table order_header purge;
drop table order_detail purge;
create table order_header(
orderid int not null,
orderdate timestamp,
customerid int
);
alter table order_header add constraint order_header_pk primary key (orderid);
create table order_detail(
orderid int not null,
renban int not null,
productid int ,
unitprice int,
orderqty int
);
alter table order_detail add constraint order_detail_pk primary key (orderid,renban);
insert into order_header values(1,'2021-01-01',100);
insert into order_header values(2,'2021-01-02',200);
insert into order_detail values(1,1,9901,30,1);
insert into order_detail values(1,2,9902,50,10);
insert into order_detail values(1,3,9903,10,15);
insert into order_detail values(2,1,9901,30,2);
commit;
-- 2. マテビューログ作成
create materialized view log on order_header with sequence, rowid
(orderid, orderdate, customerid)
including new values;
create materialized view log on order_detail with sequence, rowid
(orderid, renban, productid, unitprice, orderqty)
including new values;
-- 3. マテビュー作成
create materialized view mview1
build immediate
refresh fast on commit
enable query rewrite as
select
t1.orderdate
,t2.productid
,sum(t2.unitprice * t2.orderqty) total_amount
,COUNT(T2.UNITPRICE * T2.ORDERQTY) TOTAL_AMOUNT_CNT
,COUNT(*) CNT
from order_header t1
inner join order_detail t2
on t1.orderid = t2.orderid
group by t1.orderdate,t2.productid
;
-- 4. リフレッシュ動作確認
select * from order_header order by orderid;
select * from order_detail order by orderid,renban;
select * from mview1 order by orderdate,productid;
insert into order_header values(3,'2021-01-02',100);
insert into order_detail values(3,1,9901,30,10);
insert into order_detail values(3,2,9902,50,20);
commit;
※マテビューの元表に対してTRUNCATEした後、高速リフレッシュをするとエラーになる。
ORA-32321: "TEST"."MVIEW1"のREFRESH FASTはディテール表のTRUNCATE後はサポートされていません
完全リフレッシュなら実行可能
exec DBMS_MVIEW.REFRESH( 'MVIEW1', 'f');
exec DBMS_MVIEW.REFRESH( 'MVIEW1', 'c');
-- 5. データ投入の時間計測
truncate table order_header;
truncate table order_detail;
exec DBMS_MVIEW.REFRESH( 'MVIEW1', 'c');
set time on
set timing on
declare
begin
for i in 1..100000 loop
insert into order_header values
(i
,to_date('20200101','yyyymmdd') + floor(dbms_random.value(0, 100))
,floor(dbms_random.value(1, 101))
);
insert into order_detail values
(i
,1
,floor(dbms_random.value(1, 101))
,floor(dbms_random.value(1, 11)) * 100
,floor(dbms_random.value(1, 101))
);
insert into order_detail values
(i
,2
,floor(dbms_random.value(1, 101))
,floor(dbms_random.value(1, 11)) * 100
,floor(dbms_random.value(1, 101))
);
commit;
end loop;
end;
/
select * from order_header order by orderid fetch first 20 rows only;
select * from order_detail order by orderid,renban fetch first 20 rows only;
select * from mview1 order by orderdate,productid fetch first 20 rows only;
-- 5.1. マテビューの参照テーブルとなっている場合
10万件
経過: 00:44:28.62
★8万件まですすんだところで下記エラー
ORA-04036: インスタンスにより使用されるPGAメモリーがPGA_AGGREGATE_LIMITを超えて います ORA-06512: 行26
alter system set pga_aggregate_limit=3G scope=both;
で2G=>3Gに増やして再実行
マテビュー削除
select * from user_mviews;
select * from user_mview_logs;
DROP MATERIALIZED VIEW LOG ON ORDER_DETAIL;
DROP MATERIALIZED VIEW LOG ON ORDER_HEADER;
DROP MATERIALIZED VIEW MVIEW1;
-- 5.2. マテビューの参照テーブルとなっていない場合
10万件
経過: 00:00:20.19
(14)
https://qiita.com/nuko_yokohama/items/0ae59f5d797bc79dcc06
-- 1. pg_ivm(IMMV)のインストール
git clone https://github.com/sraoss/pg_ivm
cd pg_ivm
make USE_PGXS=1 PG_CONFIG=/usr/pgsql-14/bin/pg_config
sudo make install USE_PGXS=1 PG_CONFIG=/usr/pgsql-14/bin/pg_config
CREATE EXTENSION pg_ivm;
\dx
※
現状集約関数に対応していない
truncate未対応
-- 2. 参照元テーブル作成
drop table order_header;
drop table order_detail;
create table order_header(
orderid int not null,
orderdate timestamp,
customerid int
);
alter table order_header add constraint order_header_pk primary key (orderid);
create table order_detail(
orderid int not null,
renban int not null,
productid int ,
unitprice int,
orderqty int
);
alter table order_detail add constraint order_detail_pk primary key (orderid,renban);
insert into order_header values(1,'2021-01-01',100);
insert into order_header values(2,'2021-01-02',200);
insert into order_detail values(1,1,9901,30,1);
insert into order_detail values(1,2,9902,50,10);
insert into order_detail values(1,3,9903,10,15);
insert into order_detail values(2,1,9901,30,2);
-- 3. マテビュー作成
select create_immv('mview1', $$
select
t1.orderdate
,t2.productid
,t2.unitprice * t2.orderqty total_amount
from order_header t1
inner join order_detail t2
on t1.orderid = t2.orderid
$$
);
\dt mview1
select * from mview1;
-- 4. リフレッシュ動作確認
select * from order_header order by orderid;
select * from order_detail order by orderid,renban;
select * from mview1 order by orderdate,productid;
insert into order_header values(3,'2021-01-02',100);
insert into order_detail values(3,1,9901,30,10);
insert into order_detail values(3,2,9902,50,20);
-- 5. データ投入の時間計測
truncate table order_header;
truncate table order_detail;
\timing 1
do $$
declare
begin
for i in 1..100000 loop
insert into order_header values
(i
,'2020-01-01'::date + CAST( floor(100 * random()) || 'days' AS interval)
,floor(random() * 100)+1
);
insert into order_detail values
(i
,1
,floor(random() * 100)+1
,( floor(random() * 10)+1 ) * 100
,floor(random() * 100)+1
);
insert into order_detail values
(i
,2
,floor(random() * 100)+1
,( floor(random() * 10)+1 ) * 100
,floor(random() * 100)+1
);
end loop;
end
$$
;
select * from order_header order by orderid fetch first 20 rows only;
select * from order_detail order by orderid,renban fetch first 20 rows only;
select * from mview1 order by orderdate,productid fetch first 20 rows only;
-- 5.1. マテビューの参照テーブルとなっている場合
10万件
時間: 67658.302 ミリ秒(01:07.658)
-- 5.2. マテビューの参照テーブルとなっていない場合
10万件
時間: 2545.425 ミリ秒(00:02.545)
多数のインデックス付きビュー、または少数ではあるものの非常に複雑なインデックス付きビューで参照されるテーブルに対して
DML (UPDATE、DELETE、INSERT など) を実行する場合、DML 実行時にこれらのインデックス付きビューを更新する必要もあります。
その結果、DML クエリのパフォーマンスが大幅に低下する場合があります。
-- 1. 参照元テーブル作成
drop table order_header;
drop table order_detail;
create table order_header(
orderid int not null,
orderdate datetime2,
customerid int
);
alter table order_header add constraint order_header_pk primary key (orderid);
create table order_detail(
orderid int not null,
renban int not null,
productid int ,
unitprice int NOT NULL,
orderqty int NOT NULL
);
alter table order_detail add constraint order_detail_pk primary key (orderid,renban);
insert into order_header values(1,'2021-01-01',100);
insert into order_header values(2,'2021-01-02',200);
insert into order_detail values(1,1,9901,30,1);
insert into order_detail values(1,2,9902,50,10);
insert into order_detail values(1,3,9903,10,15);
insert into order_detail values(2,1,9901,30,2);
-- 2. インデックス付きビュー作成
-- set the options to support indexed views.
set numeric_roundabort off;
set ansi_padding, ansi_warnings, concat_null_yields_null, arithabort,
quoted_identifier, ansi_nulls on;
-- create view with schemabinding.
if object_id ('mview1', 'view') is not null
drop view mview1 ;
go
create view mview1
with schemabinding as
select
t1.orderdate
,t2.productid
,sum(t2.unitprice * t2.orderqty) total_amount
,COUNT_BIG(*) CNT
from dbo.order_header t1
inner join dbo.order_detail t2
on t1.orderid = t2.orderid
group by t1.orderdate,t2.productid
;
go
-- create an index on the view.
create unique clustered index mview1_idx01
on mview1 (orderdate, productid);
go
-- 3. リフレッシュ動作確認
select * from order_header order by orderid;
select * from order_detail order by orderid,renban;
select * from mview1 order by orderdate,productid;
insert into order_header values(3,'2021-01-02',100);
insert into order_detail values(3,1,9901,30,10);
insert into order_detail values(3,2,9902,50,20);
-- 4. データ投入の時間計測
delete from order_header;
delete from order_detail;
※truncateは実行不可
TRUNCATE TABLE 'order_header' できません。オブジェクト 'mview1' によって参照されています。
set nocount on
declare @i int;
set @i = 1;
begin transaction;
while @i <= 100000
begin
insert into order_header values(
@i
,dateadd(day,floor(100 * rand()),'2020-01-01')
,floor(rand() * 100)+1
);
insert into order_detail values(
@i
,1
,floor(rand() * 100)+1
,(floor(rand() * 10)+1) * 100
,floor(rand() * 100)+1
);
insert into order_detail values(
@i
,2
,floor(rand() * 100)+1
,(floor(rand() * 10)+1) * 100
,floor(rand() * 100)+1
);
set @i = @i + 1;
end
select top 20 * from order_header order by orderid;
select top 20 * from order_detail order by orderid,renban;
select top 20 * from mview1 order by orderdate,productid;
-- 4.1. インデックス付きビューの参照テーブルとなっている場合
10万件
4分12秒
-- 4.2. インデックス付きビューの参照テーブルとなっていない場合
10万件
2秒
