物化视图：汇总查询涉及partitionn操作
问题描述：
撞上了墙，试图插入TABLEB与满足一定的条件后插入到表A物化视图：汇总查询涉及partitionn操作
drop materialized view mv ;
drop materialized view log on tablea ;
create materialized view log on tablea
with rowid, sequence (tino, price)
including new values;
create materialized view mv
refresh fast on commit
enable query rewrite
as
SELECT tino,sum(price)
FROM tablea PARTITION(PART_201609)
group by tino;
以上将返回ORA-12054行：不能设置ON COMMIT刷新属性。 这是一个限制吗？聚合查询中没有分区运算符？ 表过大，我想我的观点仅具有特定于某个特定时期/月 当我删除PARTITION(PART_201609)跑如下我能够成功创建视图的数据：
create materialized view mv
refresh fast on commit
enable query rewrite
as
SELECT tino,sum(price)
FROM tablea
group by tino;
- 编辑 - 包括表A的DDL
- 创建表
create table TABLEA
(
tino NUMBER not null,
price VARCHAR2(200),
dated DATE
)
partition by range (DATED)
(
partition PART_201608 values less than (TO_DATE(' 2016-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition PART_201609 values less than (TO_DATE(' 2016-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition PART_201610 values less than (TO_DATE(' 2016-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
答
您可以使用the dbms_mview.explain_mview procedure明白为什么你提出的查询，不能用于快速刷新上提交MV：
begin
dbms_mview.explain_mview(q'[SELECT tino,sum(price)
FROM tablea PARTITION(PART_201609)
group by tino]');
end;
/
select capability_name, possible, msgno, msgtxt from mv_capabilities_table;
CAPABILITY_NAME P MSGNO MSGTXT
------------------------------ - ---------- ------------------------------------------------------------------------------------------
PCT N
REFRESH_COMPLETE Y
REFRESH_FAST N
REWRITE Y
PCT_TABLE N 2067 no partition key or PMARKER or join dependent expression in select list
REFRESH_FAST_AFTER_INSERT N 2169 the materialized view contains partition extended table name
REFRESH_FAST_AFTER_ONETAB_DML N 2143 SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ONETAB_DML N 2146 see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML N 2161 see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N 2157 PCT is not possible on any of the detail tables in the materialized view
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL N 2169 the materialized view contains partition extended table name
REWRITE_PCT N 2158 general rewrite is not possible or PCT is not possible on any of the detail tables
PCT_TABLE_REWRITE N 2185 no partition key or PMARKER in select list
据我所知没有周围的2169错误的任何方式：指定分区
02169, 00000, "the materialized view contains partition extended table name"
// *Cause: Fast refresh of materialized aggregate views and/or materialized
// join views are not supported if they were defined using partition
// extended table names.
// *Action: Create the fast refreshable materialized view without using
// partition extended table names or create the materialized view as
// a complete refresh materialized view.
的名字是有点不寻常无妨;你可以通过指定日期范围来实现同样的事情，Oracle会将查询限制在相关的分区上。你从相同的执行计划：
explain plan for
select tino, sum(price)
from tablea partition(part_201609)
group by tino;
和
explain plan for
select tino, sum(price)
from tablea
where dated >= date '2016-09-01'
and dated < date '2016-10-01'
group by tino;
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 115 | 15 (7)| 00:00:01 | | |
| 1 | HASH GROUP BY | | 1 | 115 | 15 (7)| 00:00:01 | | |
| 2 | PARTITION RANGE SINGLE| | 1 | 115 | 14 (0)| 00:00:01 | 2 | 2 |
| 3 | TABLE ACCESS FULL | TABLEA | 1 | 115 | 14 (0)| 00:00:01 | 2 | 2 |
--------------------------------------------------------------------------------------------------
你会看到更高的行数，比我从我的虚拟表搞定，但要注意PSTART和PSTOP列。
使用您的MV还是不太够，但：
begin
dbms_mview.explain_mview(q'[select tino, sum(price)
from tablea
where dated >= date '2016-09-01'
and dated < date '2016-10-01'
group by tino]');
end;
/
select capability_name, possible, msgno, msgtxt from mv_capabilities_table;
CAPABILITY_NAME P MSGNO MSGTXT
------------------------------ - ---------- ------------------------------------------------------------------------------------------
PCT N
REFRESH_COMPLETE Y
REFRESH_FAST N
REWRITE Y
PCT_TABLE N 2067 no partition key or PMARKER or join dependent expression in select list
REFRESH_FAST_AFTER_INSERT N 2081 mv log does not have all necessary columns
REFRESH_FAST_AFTER_ONETAB_DML N 2143 SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ONETAB_DML N 2146 see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ONETAB_DML N 2142 COUNT(*) is not present in the select list
REFRESH_FAST_AFTER_ONETAB_DML N 2143 SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ANY_DML N 2161 see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT N 2157 PCT is not possible on any of the detail tables in the materialized view
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT N 2158 general rewrite is not possible or PCT is not possible on any of the detail tables
PCT_TABLE_REWRITE N 2185 no partition key or PMARKER in select list
您需要解决2067错误：
02067, 00000, "no partition key or PMARKER or join dependent expression in select list"
// *Cause: The capability in question is not supported when the materialized
// view unless the select list (and group by list if a GROUP BY
// clause is present) includes the partition key or
// PMARKER function reference to the table in question or an expression
// join dependent on the partitioning column of the table in question.
// *Action: Add the partition key or a PMARKER function reference or a join dependent
// expression to the select list (and the GROUP BY clause, if present).
...这是关系到partition change tracking。您可以添加a partition marker的选择列表和组通过，这也得到了相同的执行计划（PSTART/PSTOP），但现在允许快速刷新：
explain plan for
select dbms_mview.pmarker(rowid), tino, sum(price)
from tablea
where dated >= date '2016-09-01'
and dated < date '2016-10-01'
group by dbms_mview.pmarker(rowid), tino;
select * from table(dbms_xplan.display);
begin
dbms_mview.explain_mview(q'[select dbms_mview.pmarker(rowid), tino, sum(price)
from tablea
where dated >= date '2016-09-01'
and dated < date '2016-10-01'
group by dbms_mview.pmarker(rowid), tino]');
end;
/
CAPABILITY_NAME P MSGNO MSGTXT
------------------------------ - ---------- ------------------------------------------------------------------------------------------
PCT Y
REFRESH_COMPLETE Y
REFRESH_FAST Y
REWRITE Y
PCT_TABLE Y
REFRESH_FAST_AFTER_INSERT N 2081 mv log does not have all necessary columns
REFRESH_FAST_AFTER_ONETAB_DML N 2143 SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ONETAB_DML N 2146 see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ONETAB_DML N 2142 COUNT(*) is not present in the select list
REFRESH_FAST_AFTER_ONETAB_DML N 2143 SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ANY_DML N 2161 see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT Y
REWRITE_FULL_TEXT_MATCH Y
REWRITE_PARTIAL_TEXT_MATCH Y
REWRITE_GENERAL Y
REWRITE_PCT Y
PCT_TABLE_REWRITE Y
你的确可以使用查询来创建你的MV：
create materialized view mv
refresh fast on commit
enable query rewrite
as
select dbms_mview.pmarker(rowid), tino, sum(price)
from tablea
where dated >= date '2016-09-01'
and dated < date '2016-10-01'
group by dbms_mview.pmarker(rowid), tino;
Materialized view MV created.
如果要启用在MV的所有功能，您可以将dated列添加到您的MV日志：
create materialized view log on tablea
with rowid, sequence (dated, tino, price)
including new values;
，包括您在MV查询丢失的集合体：
select dbms_mview.pmarker(rowid), tino, sum(price), count(price), count(*)
from tablea a
where dated >= date '2016-09-01'
and dated < date '2016-10-01'
group by dbms_mview.pmarker(rowid), tino
不相关，还要注意就可以了，如果它是有益的，分区过的MV查看日志：
create materialized view log on tablea
partition by range (dated)
(
partition PART_201608 values less than (TO_DATE(' 2016-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition PART_201609 values less than (TO_DATE(' 2016-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition PART_201610 values less than (TO_DATE(' 2016-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)
with rowid, sequence (dated, tino, price)
including new values;
你可以添加tablea表的DDL - 特别是分区键？看起来关键不在分组中，这是[PCT]的要求（http://docs.oracle.com/cd/E11882_01/server.112/e25554/advmv.htm#DWHSG00324）。您是否检查了查询的[功能]（https://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm#DWHSG8218）？ –
在编辑中添加了DDL – Wasky
不熟悉分区表，但是......不是，你不是在MV定义中包含PARTITION规范，而是使用WHERE过滤器吗？ '日期在...和...之间' – mathguy