During the 11g Data Warehouse Administration course that I delivered this week in Munich, I have shown this demonstration about Partition Change Tracking (PCT) that I’d like to share with the Oracle Community. You may have a look here for an Introduction into Materialized Views if that topic is new for you. Often, the Base Tables of Materialized Views are partitioned, which gives use additional options for the Refresh as well as for the Rewrite:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> grant dba to adam identified by adam;
Grant succeeded.
SQL> connect adam/adam
Connected.
SQL> create table sales
(product varchar2(50),
channel_id number,
cust_id number,
amount_sold number(10,2),
time_id date)
partition by list (channel_id)
(partition c0 values (0),
partition c1 values (1),
partition c2 values (2),
partition c3 values (3),
partition c4 values (4)
);
Table created.
SQL> alter table sales nologging;
Table altered.
SQL> insert /*+ append */ into sales
select
'Oracle Enterprise Edition' as product,
mod(rownum,5) as channel_id,
mod(rownum,1000) as cust_id ,
5000 as amount_sold,
to_date
('01.' || lpad(to_char(mod(rownum,12)+1),2,'0') || '.2010' ,'dd.mm.yyyy')
as time_id
from dual connect by level<=1e6;
1000000 rows created.
SQL> commit;
Commit complete.
The above was my demo setup, done with 11.2.0.1 Most of the shown should work with lower versions also, though. We will now see a Materialized View that contains the Partition Key of the Base Table, which makes it easily possible for the system to associate the rows of the Materialized View with the Partitions. Otherwise, we would need to explain that with a Partition Marker.
SQL> create materialized view mv_pct enable query rewrite as select channel_id,sum(amount_sold) from sales group by channel_id; Materialized view created.
The MV is now fresh and may get used for Query Rewrite:
SQL> select mview_name,last_refresh_type,staleness from user_mviews; MVIEW_NAME LAST_REF STALENESS ------------------------------ -------- ------------------- MV_PCT COMPLETE FRESH SQL> set autotrace on explain SQL> select channel_id,sum(amount_sold) from sales group by channel_id; CHANNEL_ID SUM(AMOUNT_SOLD) ---------- ---------------- 0 1000000000 1 1000000000 2 1000000000 3 1000000000 4 1000000000 Execution Plan ---------------------------------------------------------- Plan hash value: 470332451 --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 130 | 3 (0)| 00:00:01 | | 1 | MAT_VIEW REWRITE ACCESS FULL| MV_PCT | 5 | 130 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2)
Now we modify one row:
SQL> set autotrace off SQL> update sales set amount_sold=1 where rownum<2; 1 row updated. SQL> commit; Commit complete.:
This makes the MV stale right? Well actually not all of it:
SQL> select mview_name,last_refresh_type,staleness from user_mviews; MVIEW_NAME LAST_REF STALENESS ------------------------------ -------- ------------------- MV_PCT COMPLETE NEEDS_COMPILE SQL> select detail_partition_name, freshness from user_mview_detail_partition; DETAIL_PARTITION_NAME FRESH ------------------------------ ----- C4 FRESH C3 FRESH C2 FRESH C1 FRESH C0 STALE
The second Dictionary View is new in 11g, not the behavior of the next query. Only Partition C0 is stale, which is why queries that do not need channel_id 0 can still use the MV:
SQL> set autotrace on explain
SQL> select sum(amount_sold) from sales where channel_id=1;
SUM(AMOUNT_SOLD)
----------------
1000000000
Execution Plan
----------------------------------------------------------
Plan hash value: 1805549181
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | MAT_VIEW REWRITE ACCESS FULL| MV_PCT | 1 | 26 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MV_PCT"."CHANNEL_ID"=1)
Note
-----
- dynamic sampling used for this statement (level=2)
The Rewrite is still possible although part of the MV is stale. We call that PCT Rewrite. Furthermore, we can now do a PCT Fast Refresh, although there is no MV Log on the Base Table sales. The PCT Refresh does not need to scan the whole Base Table but only the stale partitions, which is in this case about 5 times faster than a Complete Refresh, because we have 5 equally sized partitions. The value ‚P‚ is new in 11g, but it also works with ‚F‘ or ‚?‘ in earlier versions.
SQL> set autotrace off
SQL> exec dbms_mview.refresh('MV_PCT','P')
PL/SQL procedure successfully completed.
SQL> select mview_name,last_refresh_type,staleness from user_mviews;
MVIEW_NAME LAST_REF STALENESS
------------------------------ -------- -------------------
MV_PCT FAST_PCT FRESH
Conclusion: Partitioning delivers some extra benefits when used together with Materialized Views. Especially, Rewrite & Refresh can be done on the Partition layer then. As always: „Don’t believe it, test it!“ 🙂
Pickleball spielen 
#1 von Manoj am Januar 5, 2013 - 12:32
Hi,
Your tutorial really helps to understand PCT well, we have issue with materialized view logs when truncating base table partition that contains 200million rows every time, we do truncate on master table partition daily. so planning to use PCT to find whether its stale and do a complete refresh of the partition alone.
I tried your example it works perfectly but in my case base table partition key is year field having the same field in materialized view also but i am not using group by so not able to create materialized view.
It would be great if you tell how to associate Partition Key of the Base Table with the rows of the Materialized View.
Thanks in advance!
#2 von Uwe Hesse am Januar 6, 2013 - 10:14
Manoj, if the partition key is not (obviously) part of the MV query, you can provide the information how tow rows of the MV container table relate to the base table via a partition marker. See here for an example: http://docs.oracle.com/cd/E11882_01/server.112/e25554/advmv.htm#DWHSG8232
#3 von akhil am Februar 15, 2013 - 21:17
Hi Manoj, in your case you should try partitioning the materialized view based on the detail table partition key, oracle can use truncate partition on materialized during a pct refresh please use below article Oracle can use TRUNCATE PARTITION on a materialized view if it satisfies the conditions in „Benefits of Partitioning a Materialized View“ and hence, make the PCT refresh process more efficient.
http://docs.oracle.com/cd/E14072_01/server.112/e10810/advmv.htm#i1009128
#4 von Sami Ullah am Juni 13, 2013 - 03:37
Hi,
I am also facing some issue with MV PCT refresh. I have 2 queries,
1: I have 2 base tables and both of them are partitioned.I am creating Materialized View using DBMS_MVIEW.PMARKER on the base tables rowid in my Select Statement.My Materialized View gets created but when I try to PCT refresh it using BMS_SNAPSHOT.REFRESH(‚MY_mv‘,’P‘),it gives error saying it can not fast refresh. Could you please help me with this.
2: My 2nd query is that how do we create partitioned Materialized View?
Thank you.
#5 von Uwe Hesse am Juni 13, 2013 - 10:23
Sami Ullah,
1. you can use EXPLAIN_MVIEW as explained here http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_redefi.htm#i999656 to see why your PCT refresh is not done.
2. see here for an example http://docs.oracle.com/cd/E11882_01/server.112/e25554/advmv.htm#i1006729
#6 von Raju am Juli 20, 2013 - 13:16
another great explanation. thank you so much. the examples are great.
#7 von Aditya D am Februar 13, 2014 - 06:59
How does PCT tracks when partition exchange happens on any underline Fact table? It does not mark as stale in user_mview_detail_partition table.
#8 von Iudith Mentzel am Februar 17, 2014 - 14:28
Hello Uwe,
I am trying to understand the internal workings of the PCT refresh.
I have the following simple scenario, in 11gR2.
1. I have a table with 6 partitions
2. A materialized view log with primary key exists on the table
3. A simple materialized view, that selects all the columns of the table,
defined with REFRESH FAST ON DEMAND.
My purpose was to check whether the fast refresh works after a DROP PARTITION
operation and it does indeed, I see the last refresh type as FAST_PCT.
Then I performed the following:
1. Added a new row in the last partition.
At this point, the MLOG$_mytable contains 1 row.
2. Dropped the first partition.
After the drop, the MLOG$_mytable contains 0 rows.
3. Refreshed the materialized view.
After the refresh, the last refresh type is again shown as FAST_PCT,
and the materialized view contains ALL the rows of the table,
including the single row that I added manually and that was NOT in the MLOG$_mytable
before the refresh.
Does this mean that the refresh performed did in fact execute a „complete refresh“
for both partitions, the one that I dropped and the one that contains the newly added row ?
In other words, if I want to be able to perform a PCT refresh after a partition maintenance
operation (like DROP PARTITION), does this mean that such a refresh will perform the same type of PCT refresh for ALL the partitions that happened to contain data changes,
and not just for the partitions for which the DDL was performed ?
I found that Oracle logs the partition maintenance operations in table sys.SUMPARTLOG$
but after the DROP PARTITION only one rows was logged, for the partition that was dropped,
so I wonder where was the data logged in MLOG$_mytable (for the other partition)
„moved“ after the DROP PARTITION, so that Oracle still „knew“ to refresh the materialized view correctly.
Thanks a lot in advance for any enlightenment on this issue 🙂
Best Regards,
Iudith Mentzel
#9 von Uwe Hesse am März 5, 2014 - 10:50
Iudit, the point is that you don’t need a MV log for PCT refresh. A PCT refresh will scan through the whole partition of the base table, though. It is therefore not as fast as a fast refresh, using the MV log. That is if only few rows have been modified and the partitions are large.
#10 von Iudith Mentzel am März 5, 2014 - 20:38
Hello Uwe,
Thanks a lot for your answer.
After some more research, I found that when a DML is committed, any partitions affected
by that DML appear marked as STALE in the USER_MVIEW_DETAIL_PARTITION dictionary view.
If the base table does have an MLOG$, then as long as there are only DML operations performed, the MLOG$ will log the changes and a FAST refresh is possible, as usual,
and after the refresh the partitions become FRESH in the above view.
When a DLL is performed (like a DROP PARTITION), then, on the next refresh, a FAST_PCT refresh will be chosen, or kind of „preferred“ upon a FAST refresh, and this is why Oracle wipes out all the logged changes from the MLOG$
and performs a FAST_PCT which means (probably) a complete refresh for all the partitions marked as STALE, including those partitions for which only DML was performed, but no DDL.
So, I think it still can be useful to have an MV log, because most of the activity is DML, which can always use a FAST refresh, while DDL is performed much seldom, and then a FAST_PCT refresh is „the second best“ thing available.
Our scenario is anyway more complicated, because the „real“ materialized view is in fact located at a remote site, and FAST_PCT is not supported for replication mviews across db-links.
So, I thought that a local MV that does support the FAST_PCT, and having its own MV log,
could serve as a „middleware master“ for the remote MV …
And this seems to be able to work indeed, but, however, it looks like the developers changed their mind and renounced (at least for the moment) the idea of performing
partition maintenance DDL-s as part of the application …
I am glad anyway for „having given it a try“ and having gained a better understanding
of the FAST_PCT mechanism :):)
Thanks a lot & Best Regards,
Iudith
#11 von Aditya D am März 12, 2014 - 14:15
Hello Uwe,
How does PCT tracks when partition exchange happens on any underline Fact table? It does not mark as stale in user_mview_detail_partition table.
Thanks & Regards,
Aditya D