クエリリライト

MySQLとPostgreSQLはリライトされない
OracleとSQL Serverは基本的パターンは動作確認できた

※ただし、Oracleはマテビュー定義のテーブル結合方式にANSI準拠を使用できない。

• MySQL

(8.0.27)

マテビューのクエリリライトはサポートしていない模様
念のため確認

前提:
「マテビュー」記載手順でマテビュー環境構築済
既存ルートユーザの認証方法変更済

-- 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);

-- 5. クエリリライト動作確認

explain
select
 t1.orderdate
,t2.productid
,sum(t2.unitprice * t2.orderqty) total_amount
from test.order_header t1
inner join test.order_detail t2
on t1.orderid = t2.orderid
group by t1.orderdate,t2.productid
;

→リライトされていない

• Oracle

(19c)

https://blogs.oracle.com/otnjp/post/tsushima-hakushi-70

ANSI準拠の結合文を使用したマテビューは、テキスト一致のクエリーリライトしか行われない。
Oracle固有の結合文を使用する必要がある。
問合せについては、ANSI準拠の結合文を使用してもテキスト一致以外のクエリー・リライトも行われる。

-- 1. 参照元テーブル作成

drop table order_header purge;
drop table order_detail purge;
drop table customer 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);

create table customer(
customerid int not null,
customername varchar2(100)
);

alter table customer add constraint customer_pk primary key (customerid);

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);

insert into customer values(100,'Taro');
insert into customer values(200,'Jiro');

commit;

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);

insert into order_header values(4,'2021-01-02',100);
insert into order_detail values(4,1,9901,30,10);
insert into order_detail values(4,2,9902,50,20);

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. マテビュー作成

-- 3.1 ANSI準拠の結合文を用いた場合

drop materialized view mview1;

create materialized view mview1
build immediate  
refresh fast on commit
enable query rewrite as
select
 t1.orderdate
,t2.productid
,t1.customerid
,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,t1.customerid
;

-- 3.2 Oracle固有の結合文を用いた場合

drop materialized view mview2;

create materialized view mview2
build immediate  
refresh fast on commit
enable query rewrite as
select
 t1.orderdate
,t2.productid
,t1.customerid
,sum(t2.unitprice * t2.orderqty) total_amount
,COUNT(T2.UNITPRICE * T2.ORDERQTY) TOTAL_AMOUNT_CNT
,COUNT(*) CNT
from order_header t1,order_detail t2
where t1.orderid = t2.orderid
group by t1.orderdate,t2.productid,t1.customerid
;

select * from order_header order by orderid;
select * from order_detail order by orderid,renban;
select * from customer order by customerid;
select * from mview1 order by orderdate,productid,customerid;
select * from mview2 order by orderdate,productid,customerid;

-- 4. クエリリライト動作確認

show parameter QUERY_REWRITE_ENABLED
show parameter OPTIMIZER_MODE

exec dbms_stats.gather_table_stats(user,'ORDER_HEADER');
exec dbms_stats.gather_table_stats(user,'ORDER_DETAIL');
exec dbms_stats.gather_table_stats(user,'CUSTOMER');
exec dbms_stats.gather_table_stats(user,'MVIEW1');
exec dbms_stats.gather_table_stats(user,'MVIEW2');

-- 4.1 マテビュー定義そのもの

explain plan for
select
 t1.orderdate
,t2.productid
,t1.customerid
,sum(t2.unitprice * t2.orderqty) total_amount
from order_header t1
inner join order_detail t2
on t1.orderid = t2.orderid
group by t1.orderdate,t2.productid,t1.customerid
;

select * from table(dbms_xplan.display() );

→クエリリライトされている(ANSI準拠の結合文を用いたマテビュー)
→クエリリライトされている(Oracle固有の結合文を用いたマテビュー)

explain plan for
select
 t1.orderdate
,t2.productid
,t1.customerid
,sum(t2.unitprice * t2.orderqty) total_amount
from order_header t1,order_detail t2
where t1.orderid = t2.orderid
group by t1.orderdate,t2.productid,t1.customerid
;

select * from table(dbms_xplan.display() );

→クエリリライトされていない(ANSI準拠の結合文を用いたマテビュー)★
→クエリリライトされている(Oracle固有の結合文を用いたマテビュー)

-- 4.2 where条件追加

explain plan for
select
 t1.orderdate
,t2.productid
,t1.customerid
,sum(t2.unitprice * t2.orderqty) total_amount
from order_header t1
inner join order_detail t2
on t1.orderid = t2.orderid
where t2.productid = 9901
group by t1.orderdate,t2.productid,t1.customerid
;

select * from table(dbms_xplan.display() );

→クエリリライトされていない(ANSI準拠の結合文を用いたマテビュー)★
→クエリリライトされている(Oracle固有の結合文を用いたマテビュー)

explain plan for
select
 t1.orderdate
,t2.productid
,t1.customerid
,sum(t2.unitprice * t2.orderqty) total_amount
from order_header t1,order_detail t2
where t1.orderid = t2.orderid
and t2.productid = 9901
group by t1.orderdate,t2.productid,t1.customerid
;

select * from table(dbms_xplan.display() );

→クエリリライトされていない(ANSI準拠の結合文を用いたマテビュー)★
→クエリリライトされている(Oracle固有の結合文を用いたマテビュー)

-- 4.3 グループキーのロールアップ
explain plan for
select
 t1.orderdate
,t2.productid
,sum(t2.unitprice * t2.orderqty) total_amount
from order_header t1
inner join order_detail t2
on t1.orderid = t2.orderid
group by t1.orderdate,t2.productid
;

select * from table(dbms_xplan.display() );

→クエリリライトされていない(ANSI準拠の結合文を用いたマテビュー)★
→クエリリライトされている(Oracle固有の結合文を用いたマテビュー)

explain plan for
select
 t1.orderdate
,t2.productid
,sum(t2.unitprice * t2.orderqty) total_amount
from order_header t1. order_detail t2
where t1.orderid = t2.orderid
group by t1.orderdate,t2.productid
;

select * from table(dbms_xplan.display() );

→クエリリライトされていない(ANSI準拠の結合文を用いたマテビュー)★
→クエリリライトされている(Oracle固有の結合文を用いたマテビュー)

-- 4.4 マテビューにテーブル結合追加

explain plan for
select
 t1.orderdate
,t2.productid
,t3.customername
,sum(t2.unitprice * t2.orderqty) total_amount
from order_header t1
inner join order_detail t2
on t1.orderid = t2.orderid
inner join customer t3
on t1.customerid = t3.customerid
group by t1.orderdate,t2.productid,t3.customername
;

select * from table(dbms_xplan.display() );

→クエリリライトされていない(ANSI準拠の結合文を用いたマテビュー)★
→クエリリライトされている(Oracle固有の結合文を用いたマテビュー)

explain plan for
select
 t1.orderdate
,t2.productid
,t3.customername
,sum(t2.unitprice * t2.orderqty) total_amount
from order_header t1, order_detail t2, customer t3
where t1.orderid = t2.orderid
and t1.customerid = t3.customerid
group by t1.orderdate,t2.productid,t3.customername
;

select * from table(dbms_xplan.display() );

→クエリリライトされていない(ANSI準拠の結合文を用いたマテビュー)★
→クエリリライトされている(Oracle固有の結合文を用いたマテビュー)

※クエリリライトされない理由の調査

@?/rdbms/admin/utlxrw.sql

truncate table REWRITE_TABLE;

DECLARE
  qrytext VARCHAR2(500)  :='
select
 t1.orderdate
,t2.productid
,sum(t2.unitprice * t2.orderqty) total_amount
from order_header t1
inner join order_detail t2
on t1.orderid = t2.orderid
group by t1.orderdate,t2.productid
';
 
  idno    VARCHAR2(30) :='ID1';
BEGIN
  DBMS_MVIEW.EXPLAIN_REWRITE(qrytext, 'TEST.MVIEW1', idno);
END;
/

SELECT message FROM rewrite_table ORDER BY sequence;

※リライトできないときにエラー発生させるヒント句
/*+ REWRITE_OR_ERROR */ 

• PostgreSQL

(14)
マテビューのクエリリライトはサポートしていない模様
念のため確認

-- 1. 参照元テーブル作成

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);

-- 2. マテビュー作成

create materialized view mview1
as
select
 t1.orderdate
,t2.productid
,sum(t2.unitprice * t2.orderqty) total_amount
from order_header t1
inner join order_detail t2
on t1.orderid = t2.orderid
group by t1.orderdate,t2.productid
;

select * from order_header;
select * from order_detail;
select * from mview1;

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);

-- 3. リフレッシュ動作確認
REFRESH MATERIALIZED VIEW   mview1;
REFRESH MATERIALIZED VIEW CONCURRENTLY  mview1;

※CONCURRENTLYの場合、マテビューにユニークインデックスが必要
ERROR:  cannot refresh materialized view "public.mview1" concurrently
HINT:  Create a unique index with no WHERE clause on one or more columns of the materialized view.

create unique index mview1_idx01 on mview1(orderdate,productid);

-- 4. クエリリライト動作確認

explain analyze
select
 t1.orderdate
,t2.productid
,sum(t2.unitprice * t2.orderqty) total_amount
from order_header t1
inner join order_detail t2
on t1.orderid = t2.orderid
group by t1.orderdate,t2.productid
;

→リライトされていない

explain analyze
select
 t1.orderdate
,t1.productid
,t1.total_amount
from mview1 t1
;

• SQL Server

(2019)

https://docs.microsoft.com/ja-jp/sql/relational-databases/views/create-indexed-views?view=sql-server-ver15

-- 1. 参照元テーブル作成

drop table order_header;
drop table order_detail;
drop table customer;

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);

create table customer(
customerid int not null,
customername varchar(100)
);

alter table customer add constraint customer_pk primary key (customerid);

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);

insert into customer values(100,'Taro');
insert into customer values(200,'Jiro');

-- 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
,t1.customerid
,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,t1.customerid
;
go

-- create an index on the view.
create unique clustered index mview1_idx01
on mview1 (orderdate, productid, customerid);
go

-- 3. リフレッシュ動作確認

select * from order_header order by orderid;
select * from order_detail order by orderid,renban;
select * from customer order by customerid;
select * from mview1 order by orderdate,productid,customerid;

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);

insert into order_header values(4,'2021-01-02',100);
insert into order_detail values(4,1,9901,30,10);
insert into order_detail values(4,2,9902,50,20);

-- 4. クエリリライト動作確認

-- 4.1 インデックス付きビュー定義そのもの
set statistics profile on
set statistics profile off

select
 t1.orderdate
,t2.productid
,t1.customerid
,sum(t2.unitprice * t2.orderqty) total_amount
from order_header t1
inner join order_detail t2
on t1.orderid = t2.orderid
group by t1.orderdate,t2.productid,t1.customerid
;

→クエリリライトされている

-- 4.2 where条件追加

select
 t1.orderdate
,t2.productid
,t1.customerid
,sum(t2.unitprice * t2.orderqty) total_amount
from order_header t1
inner join order_detail t2
on t1.orderid = t2.orderid
where t2.productid = 9901
group by t1.orderdate,t2.productid,t1.customerid
;

→クエリリライトされている

-- 4.3 グループキーのロールアップ
select
 t1.orderdate
,t2.productid
,sum(t2.unitprice * t2.orderqty) total_amount
from order_header t1
inner join order_detail t2
on t1.orderid = t2.orderid
group by t1.orderdate,t2.productid
;

→クエリリライトされている

-- 4.4 インデックス付きビューにテーブル結合追加

select
 t1.orderdate
,t2.productid
,t3.customername
,sum(t2.unitprice * t2.orderqty) total_amount
from order_header t1
inner join order_detail t2
on t1.orderid = t2.orderid
inner join customer t3
on t1.customerid = t3.customerid
group by t1.orderdate,t2.productid,t3.customername
;

→クエリリライトされていない