Materialized View and Partitioning "Gotchas", Part II
If you follow the blog, then you know that Mark recently posted about materialized views for aggregation, located here.
One of the issues he encountered was how to refresh materialized views based on tables loaded with partition exchanges... a common approach to loading fact tables while minimizing the downtime for the reporting schema. Once the appropriate indexes are built on a staging table, that table can then become part of the partitioned table with little more than a dictionary update on Oracle's part... which is a near instantaneous process.
Mark attempted to refresh the materialized view with two different approaches. First, he demonstrated a "FAST" refresh... which uses materialized view logs to maintain recent updates to the table. But this seemed to cause the following error:
"ORA-32313: REFRESH FAST of "SH"."PRODUCT_SALES_MV" unsupported after PMOPs".
PMOP stands for "partition maintenance operation", and includes the standard fare of partition-based DDL operations: EXCHANGES, SPLITS, TRUNCATES, DROPS, etc. Mark's deduction: a fast refresh cannot occur using materialized view logs on a table that just accepted a partition exchange. If you think about it... this makes perfect sense. How is a materialized view log, which stores incremental data via a trigger on the base table, supposed to handle a whole new partition of data that is brought in through a DDL operation? The short answer is... it can't.
So in his usual fashion, Mark presented the preferred alternative. He shows us how one might refresh the aggregate using Partition Change Tracking (PCT). This is a really cool feature that uses the inherent metadata that Oracle stores when the base table or tables are partitioned. The database knows which partitions contain modified, or "new" data, and how the rows in the materialized view map to that underlying table. Refreshing the materialized view is then easy... just refresh what needs refreshing. Simple enough.
So we have the complete picture about which method to use and when, right? Well... maybe not. In reworking the original examples for a new article he's writing, Mark encountered something rather unusual: he was unable to recreate the ORA-32313 that he received in the original test case. He conferred with me and Peter Scott, a materialized view expert working with us at Rittman Mead, to shed light on what now seemed like a flawed test case. No matter which database version we tried, or which combination of controls we mashed together, none of us could recreate the original error. Personally, I tried all the PMOP's: EXCHANGES, TRUNCATES, SPLITS, DROPS, etc. I also tried all the different permutations of the EXCHANGE command: WITHOUT VALIDATION, INCLUDING INDEXES... everything. I tried exchanging full partitions and empty ones. No matter what... I couldn't generate the original error:
SQL> alter table sales exchange partition SALES_Q1_1998 with table sales_stg;
Table altered.
Elapsed: 00:00:01.19
SQL> EXEC DBMS_MVIEW.REFRESH('product_sales_mv', 'F');
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.20
SQL>
But then it occurred to me... what if PCT was playing a part in this? As Mark demonstrated in the original posting... the main drawback to using PCT in a standard star schema is the inability to maintain dimensional attributes in the stored aggregate. In other words... whenever the materialized view contains attributes derived from non-partitioned dimension tables... and those dimension tables themselves have been modified since the last refresh... PCT can no longer use partition metadata to keep track of which rows have changed. The actual error the database gives us is rather non-descriptive:
"ORA-12052: cannot fast refresh materialized view SH.PRODUCT_SALES_MV".
Oh... thanks for that Oracle. But Mark works through what's needed to reconcile this error. Instead, we should maintain only fact table attributes in the aggregate... and then Oracle doesn't care whether the dimension tables have been updated or not. The database will simply do a "join back" to the dimension tables during the Query Rewrite phase, while still avoiding a join to the largest table at play: the fact table.
So what part was PCT playing in the FAST refresh portion of the original test case... the one that wasn't considering PCT at all? Well, as it turns out (and as the documentation points out), a FAST refresh does not necessarily utilize the materialized view logs, as we initially expected. Actually, in cases where there have been PMOP's on one of the base tables, a FAST refresh will actually use PCT if available. So when Mark, Peter and myself were all trying to generate the PMOP error, we were unable to because the database was fooling us by using PCT to refresh the aggregate. It wasn't until I tried the FAST refresh after modifying data in the PRODUCTS dimension table that I finally generated the error:
SQL> alter table sales exchange partition SALES_Q1_1998 with table sales_stg;
Table altered.
Elapsed: 00:00:00.16
SQL>
SQL> insert into products values
2 ( 10001,
3 'test product',
4 'test product',
5 'Test Subcategory',
6 10001,
7 'Test Subcategory',
8 'Test Category',
9 10001,
10 'Test Category',
11 1,
12 'U',
13 'P',
14 1,
15 'STATUS',
16 1.99,
17 1.99,
18 'TOTAL',
19 1,
20 NULL,
21 SYSDATE,
22 NULL,
23 'A');
1 row created.
Elapsed: 00:00:00.00
SQL> EXEC DBMS_MVIEW.REFRESH('product_sales_mv', 'F');
BEGIN DBMS_MVIEW.REFRESH('product_sales_mv', 'F'); END;
*
ERROR at line 1:
ORA-32313: REFRESH FAST of "SH"."PRODUCT_SALES_MV" unsupported after PMOPs
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2537
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2743
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2712
ORA-06512: at line 1
Elapsed: 00:00:00.09
SQL>
So what's the moral of this story? I guess it depends on your requirements. You might be able to sum it up by saying "The database is really fond of using PCT"... and perhaps the database is right this time.