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!“ 🙂