The /*+Go-Faster*/ Oracle & PeopleSoft Blog: Partitioning

Showing posts with label Partitioning. Show all posts

Tuesday, September 02, 2025

Partition Pruning/Elimination on Reference Partitioned Tables

I discussed locally partitioning the unique index on a reference partitioned table in a previous blog. Having implemented it, I wanted to confirm what happens when I execute a single-table query on the reference partitioned table.

It is not possible to specify predicates on the partitioning key columns, because they are on the foreign key table. However, provided that a query specifies predicates on all the foreign key columns, the database can still prune/eliminate partitions, and it does not probe every partition.

Single Table Query

This example uses the same example tables as the previous blog. The journal header table, PS_JRNL_HEADER, is the parent and therefore foreign key of the journal line table, PS_JRNL_LN.

CREATE TABLE PS_JRNL_HEADER 
(BUSINESS_UNIT VARCHAR2(5 CHAR) NOT NULL
,JOURNAL_ID VARCHAR2(10 CHAR) NOT NULL
,JOURNAL_DATE DATE NOT NULL
,UNPOST_SEQ NUMBER NOT NULL
…
,CONSTRAINT PS_JRNL_HEADER PRIMARY KEY (BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, UNPOST_SEQ)
) 
PARTITION BY RANGE (fiscal_year) INTERVAL (1)
(PARTITION FISCAL_YEAR_2016 VALUES LESS THAN (2017))
/

CREATE TABLE PS_JRNL_LN 
(BUSINESS_UNIT VARCHAR2(5 CHAR) NOT NULL
,JOURNAL_ID VARCHAR2(10 CHAR) NOT NULL
,JOURNAL_DATE DATE NOT NULL
,UNPOST_SEQ NUMBER NOT NULL 
,JOURNAL_LINE NUMBER(9,0) NOT NULL
,LEDGER VARCHAR2(10 CHAR) NOT NULL
…
,CONSTRAINT PS_JRNL_LN PRIMARY KEY (BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, UNPOST_SEQ, JOURNAL_LINE, LEDGER)
,CONSTRAINT PS_JRNL_LN_FK FOREIGN KEY (BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, UNPOST_SEQ) REFERENCES PS_JRNL_HEADER 
)
PARTITION BY REFERENCE(PS_JRNL_LN_FK)
…
/

My single-table query has literal equality predicates on each of the foreign key columns.

select *
from ps_jrnl_ln
where business_unit = '12345'
and journal_id = 'XX12345678'
and journal_date = TO_DATE('25/05/2021','DD/MM/YYYY')
and unpost_seq = 0
/

This is the resulting execution plan

Plan hash value: 2773029334
 
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |            |     1 |   277 |     5   (0)| 00:00:01 |       |       |
|   1 |  PARTITION REFERENCE SINGLE                |            |     1 |   277 |     5   (0)| 00:00:01 |   KEY |   KEY |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PS_JRNL_LN |     1 |   277 |     5   (0)| 00:00:01 |   KEY |   KEY |
|   3 |    SORT CLUSTER BY ROWID BATCHED           |            |     1 |       |     4   (0)| 00:00:01 |       |       |
|*  4 |     INDEX RANGE SCAN                       | PS_JRNL_LN |     1 |       |     4   (0)| 00:00:01 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("BUSINESS_UNIT"='12345' AND "JOURNAL_ID"='XX12345678' AND "JOURNAL_DATE"=TO_DATE(' 2021-05-25 
              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "UNPOST_SEQ"=0)

The execution plan only mentions PS_JRNL_LN. There is no mention of visiting PS_JRNL_HEADER, but this doesn’t mean it didn’t happen.
At line 1, PARTITION REFERENCE SINGLE indicates that the database accessed a single partition; it did not scan multiple partitions.
The partition start/stop values for table and index accesses are all ‘KEY’. This indicates that the partition was only determined during execution rather than earlier parsing. Usually, when we specify literal values in a SQL statement, we expect to see literal partition start/stop values. Here, the partition is looked up for the foreign key values provided, so we get key values during execution.

What is going on here? Oracle visits the foreign key table PS_JRNL_HEADER, looking up the foreign key, which is also its primary key, and is the subject of a unique index. It determines the partition in the foreign key table from the ROWID in the index (even though the index cannot be locally partitioned because it does not contain the partitioning key column). There is a 1:1 relationship of partitions between the reference partitioned table and its foreign key table. Thus, Oracle also determines which partition to query in the reference partitioned table, and hence the partition start/stop values mentioned in the execution plan are ‘KEY’ because they are determined at execution time.

Trace Test

To confirm this, I traced the query. The buffer cache was flushed before the test, so that I would see the physical I/O for each block accessed.

ALTER SYSTEM FLUSH BUFFER_CACHE; 
ALTER SESSION SET tracefile_identifier=DMK1_JRNL_LN_LOOKUP; 
exec dbms_monitor.session_trace_enable(waits => true, binds => true);

select *
from ps_jrnl_ln
where business_unit = '12345'
and journal_id = 'XX12345678'
and journal_date = TO_DATE('25/05/2021','DD/MM/YYYY')
and unpost_seq = 0
/

exec dbms_monitor.session_trace_disable;

Specifying TRACEFILE_IDENTIFIER makes it easy to correctly identify the trace file in v$diag_trace_file.

SELECT * FROM v$DIAG_TRACE_FILE
WHERE trace_filename like '%DMK%'
ORDER BY modify_time desc
/

Then it can be queried from v$diag_trace_file_contents and spooled to a local file (see also Obtaining Trace Files without Access to the Database Server).

clear screen
set pages 0 lines 200 echo off
spool DMK_JRNL_LN_LOOKUP.trc
SELECT payload FROM v$diag_trace_file_contents
WHERE trace_filename = 'xxxxarcx2_ora_235305_DMK1_JRNL_LN_LOOKUP.trc'
ORDER BY line_number
/
spool off

Database Objects and IDs

The trace mentions three object IDs. I have looked them up in the DBA_OBJECTS view for convenience.

SELECT object_id, object_type, object_name, subobject_name 
FROM dba_objects WHERE objecT_id IN(574371,600163, 574522)
/

OBJECT_ID OBJECT_TYPE          OBJECT_NAME        SUBOBJECT_NAME
---------- -------------------- ------------------ ------------------------------------------------
    574371 INDEX                PS_JRNL_HEADER
    574522 TABLE PARTITION      PS_JRNL_LN         FISCAL_YEAR_2021_ACCOUNTING_PERIOD_07
    600163 INDEX PARTITION      PS_JRNL_LN         FISCAL_YEAR_2021_ACCOUNTING_PERIOD_07

Trace File

3 blocks are read from object 574371. This is the primary key index PS_JRNL_HEADER, on the table of the same name. Oracle is looking up the partition on the foreign key on JRNL_LN to get the partition in the reference table. Only the table is partitioned; this index is not, but Oracle can get the partition from the row ID in the index. Curiously, Oracle still performs this lookup if the index on the reference partitioned table is not partitioned. Thus, this is an overhead of reference partitioning, not of whether the index is partitioned, but the foreign key is only looked up once for each foreign key, and then the blocks will be in the buffer cache.
There is one multi-block and two single-block reads from the index PS_JRNL_LN, but only from one index partition; FISCAL_YEAR_2021_ACCOUNTING_PERIOD_07.
Finally, Oracle looks up table rows by ROWID. They are all in the table partition that has the same name as the index partition, and this required two single block reads.

Trace file /u01/app/oracle/diag/rdbms/xxxxarcx/xxxxarcx2/trace/xxxxarcx2_ora_235305_DMK1_JRNL_LN_LOOKUP.trc

…

*** TRACE CONTINUED FROM FILE 
/u01/app/oracle/diag/rdbms/xxxxarcx/xxxxarcx2/trace/xxxxarcx2_ora_235305_DMK0_JRNL_LN_LOOKUP.trc ***

…

=====================

PARSING IN CURSOR #140550623318264 len=169 dep=0 uid=130 oct=3 lid=130 tim=1987367209651 hv=686856243 ad='61aa57d40' sqlid='db2cyj8ng161m'

select *

from ps_jrnl_ln

where business_unit = '12345'

and journal_id = 'XX12345678'

and journal_date = TO_DATE('25/05/2021','DD/MM/YYYY')

and unpost_seq = 0



END OF STMT

PARSE #140550623318264:c=123,e=124,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2773029334,tim=1987367209651

WAIT #140550623318264: nam='gc cr grant 2-way' ela= 237 p1=97 p2=459395 p3=1 obj#=574371 tim=1987367210207

WAIT #140550623318264: nam='cell single block physical read: flash cache' ela= 358 cellhash#=4239709683 diskhash#=0 bytes=8192 obj#=574371 tim=1987367210612^[1]

WAIT #140550623318264: nam='gc cr grant 2-way' ela= 110 p1=39 p2=474285 p3=1 obj#=574371 tim=1987367210865

WAIT #140550623318264: nam='cell single block physical read: flash cache' ela= 335 cellhash#=3429896051 diskhash#=0 bytes=8192 obj#=574371 tim=1987367211225

WAIT #140550623318264: nam='cell single block physical read: flash cache' ela= 330 cellhash#=4156894774 diskhash#=0 bytes=8192 obj#=574371 tim=1987367211636

EXEC #140550623318264:c=1481,e=2000,p=3,cr=3,cu=0,mis=0,r=0,dep=0,og=1,plh=2773029334,tim=1987367211709

WAIT #140550623318264: nam='SQL*Net message to client' ela= 3 driver id=1413697536 #bytes=1 p3=0 obj#=574371 tim=1987367211783

WAIT #140550623318264: nam='gc cr multi block grant' ela= 234 p1=69 p2=647975 p3=14 obj#=600163 tim=1987367212195

WAIT #140550623318264: nam='cell multiblock physical read' ela= 205 cellhash#=4156894774 diskhash#=0 bytes=32768 obj#=600163 tim=1987367212436^[2]

WAIT #140550623318264: nam='cell single block physical read: xrmem cache' ela= 169 cellhash#=3429896051 diskhash#=0 bytes=8192 obj#=600163 tim=1987367212706

WAIT #140550623318264: nam='cell single block physical read: RDMA' ela= 44 cellhash#=4239709683 diskhash#=0 bytes=8192 obj#=600163 tim=1987367212817

WAIT #140550623318264: nam='gc cr grant 2-way' ela= 71 p1=39 p2=481918 p3=1 obj#=600163 tim=1987367212976

WAIT #140550623318264: nam='cell single block physical read: xrmem cache' ela= 137 cellhash#=4239709683 diskhash#=0 bytes=8192 obj#=600163 tim=1987367213151

WAIT #140550623318264: nam='cell single block physical read: RDMA' ela= 38 cellhash#=3429896051 diskhash#=0 bytes=8192 obj#=574522 tim=1987367213303^[3]

WAIT #140550623318264: nam='cell single block physical read: RDMA' ela= 29 cellhash#=3429896051 diskhash#=0 bytes=8192 obj#=574522 tim=1987367213406

WAIT #140550623318264: nam='cell single block physical read: RDMA' ela= 28 cellhash#=3429896051 diskhash#=0 bytes=8192 obj#=574522 tim=1987367213502

WAIT #140550623318264: nam='cell single block physical read: RDMA' ela= 25 cellhash#=3429896051 diskhash#=0 bytes=8192 obj#=574522 tim=1987367213563

WAIT #140550623318264: nam='cell single block physical read: RDMA' ela= 26 cellhash#=3429896051 diskhash#=0 bytes=8192 obj#=574522 tim=1987367213620

WAIT #140550623318264: nam='cell single block physical read: flash cache' ela= 649 cellhash#=3429896051 diskhash#=0 bytes=8192 obj#=574522 tim=1987367214312

FETCH #140550623318264:c=1397,e=3255,p=13,cr=11,cu=0,mis=0,r=4,dep=0,og=1,plh=2773029334,tim=1987367215128

STAT #140550623318264 id=1 cnt=4 pid=0 pos=1 obj=0 op='PARTITION REFERENCE SINGLE PARTITION: KEY KEY (cr=14 pr=16 pw=0 str=1 time=5142 us cost=5 size=277 card=1)'

STAT #140550623318264 id=2 cnt=4 pid=1 pos=1 obj=574372 op='TABLE ACCESS BY LOCAL INDEX ROWID BATCHED PS_JRNL_LN PARTITION: KEY KEY (cr=11 pr=13 pw=0 str=1 time=3240 us cost=5 size=277 card=1)'

STAT #140550623318264 id=3 cnt=4 pid=2 pos=1 obj=0 op='SORT CLUSTER BY ROWID BATCHED (cr=4 pr=7 pw=0 str=1 time=1366 us cost=4 size=0 card=1)'

STAT #140550623318264 id=4 cnt=4 pid=3 pos=1 obj=600104 op='INDEX RANGE SCAN PS_JRNL_LN PARTITION: KEY KEY (cr=4 pr=7 pw=0 str=1 time=1331 us cost=4 size=0 card=1)'

WAIT #140550623318264: nam='SQL*Net message from client' ela= 155007 driver id=1413697536 #bytes=1 p3=0 obj#=574522 tim=1987367370548

CLOSE #140550623318264:c=14,e=13,dep=0,type=0,tim=1987367370652

=====================

…

Locally Partitioned Index

In my scenario, I found that the number of levels in B-tree index in the local partitions was generally only 2 or 3, rather than 4 in the global non-partitioned version of that index (of course, this will vary from case to case). So local partitioning saved one or two I/Os per index probe. This quickly outweighs the lookup of the journal header table because there are many journal lines per journal header.

SELECT index_owner, partition_name, index_name, num_rows, distinct_keys, blevel, leaf_blocks, status
FROM dba_ind_partitions
WHERE index_name = 'PS_JRNL_LN'
ORDER by partition_position
/

INDEX_OWNE PARTITION_NAME                         INDEX_NAME           NUM_ROWS DISTINCT_KEYS     BLEVEL LEAF_BLOCKS STATUS  
---------- -------------------------------------- ------------------ ---------- ------------- ---------- ----------- --------
…
XXXXXXXXXX FISCAL_YEAR_2018_ACCOUNTING_PERIOD_01  PS_JRNL_LN           10189190      10189190          2       32008 USABLE
XXXXXXXXXX FISCAL_YEAR_2018_ACCOUNTING_PERIOD_02  PS_JRNL_LN            5368231       5368231          2       16970 USABLE
XXXXXXXXXX FISCAL_YEAR_2018_ACCOUNTING_PERIOD_03  PS_JRNL_LN            6713612       6713612          2       21132 USABLE
XXXXXXXXXX FISCAL_YEAR_2018_ACCOUNTING_PERIOD_04  PS_JRNL_LN            8500469       8500469          2       27128 USABLE
XXXXXXXXXX FISCAL_YEAR_2018_ACCOUNTING_PERIOD_05  PS_JRNL_LN            7901118       7901118          2       24862 USABLE
XXXXXXXXXX FISCAL_YEAR_2018_ACCOUNTING_PERIOD_06  PS_JRNL_LN           29785888      29785888          3       95734 USABLE
XXXXXXXXXX FISCAL_YEAR_2018_ACCOUNTING_PERIOD_07  PS_JRNL_LN           29978325      29978325          3       96377 USABLE
XXXXXXXXXX FISCAL_YEAR_2018_ACCOUNTING_PERIOD_08  PS_JRNL_LN            8470092       8470092          2       26743 USABLE
XXXXXXXXXX FISCAL_YEAR_2018_ACCOUNTING_PERIOD_09  PS_JRNL_LN           30393756      30393756          3       97669 USABLE
XXXXXXXXXX FISCAL_YEAR_2018_ACCOUNTING_PERIOD_10  PS_JRNL_LN           30649060      30649060          3       98537 USABLE
XXXXXXXXXX FISCAL_YEAR_2018_ACCOUNTING_PERIOD_11  PS_JRNL_LN            9340460       9340460          2       29597 USABLE
XXXXXXXXXX FISCAL_YEAR_2018_ACCOUNTING_PERIOD_12  PS_JRNL_LN           55420790      55420790          3      177193 USABLE  
…

TL;DR

The single-table lookup of the reference partitioned table does benefit from partition pruning/elimination, although there is no predicate on the partitioning key, but only if there are predicates on each of the foreign key columns. The foreign key becomes a proxy for the predicate on the partitioning key.

Thus, locally partitioning the primary key index on a reference partitioned table can be effective even if you do not additionally join the foreign key table.

Thursday, June 26, 2025

Optimising Journal Line Queries: 3. Partitioning

This is the third of a series of five articles that examine the challenges posed by typical queries on the journal line table (PS_JRNL_LN).

PeopleSoft does not partition tables by default. Application Designer does not support partitioning, mainly because different databases implement partitioning differently. Thus, it is always left to the customer to implement as a customisation. In this article, I am only going to discuss partitioning on Oracle.

Interval Partitioning

This is the sample query that I started with.

SELECT A.FISCAL_YEAR, A.ACCOUNTING_PERIOD, B.BUSINESS_UNIT, B.JOURNAL_ID, TO_CHAR(B.JOURNAL_DATE,'YYYY-MM-DD'), B.LEDGER, B.ACCOUNT
, B.PRODUCT, B.PROJECT_ID, B.CHARTFIELD1, B.CHARTFIELD2, B.CURRENCY_CD, B.AFFILIATE, SUM( B.MONETARY_AMOUNT), B.FOREIGN_CURRENCY
, SUM( B.FOREIGN_AMOUNT), A.REVERSAL_CD, A.REVERSAL_ADJ_PER, A.JRNL_HDR_STATUS, TO_CHAR(A.POSTED_DATE,'YYYY-MM-DD'), A.OPRID, A.DESCR254
, B.DEPTID, A.SOURCE, B.ALTACCT, TO_CHAR(CAST((A.DTTM_STAMP_SEC) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), B.LINE_DESCR 
FROM PS_JRNL_HEADER A, PS_JRNL_LN B 
WHERE (A.BUSINESS_UNIT = B.BUSINESS_UNIT 
AND A.JOURNAL_ID = B.JOURNAL_ID 
AND A.JOURNAL_DATE = B.JOURNAL_DATE 
AND A.UNPOST_SEQ = B.UNPOST_SEQ 
AND A.JRNL_HDR_STATUS IN('P','V','U') 
AND A.FISCAL_YEAR IN (2024) 
AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 12 
AND B.CHARTFIELD1 IN ('1234567','1234568','1234569')
AND B.LEDGER IN ('LEDGER')) 
GROUP BY A.FISCAL_YEAR, A.ACCOUNTING_PERIOD, B.BUSINESS_UNIT, B.JOURNAL_ID, B.JOURNAL_DATE, B.LEDGER, B.ACCOUNT, B.PRODUCT
, B.PROJECT_ID, B.CHARTFIELD1, B.CHARTFIELD2, B.CURRENCY_CD, B.AFFILIATE, B.FOREIGN_CURRENCY, A.REVERSAL_CD, A.REVERSAL_ADJ_PER
, A.JRNL_HDR_STATUS, A.POSTED_DATE, A.OPRID, A.DESCR254, B.DEPTID, A.SOURCE, B.ALTACCT, A.DTTM_STAMP_SEC, B.LINE_DESCR

It would have been desirable to have been able to partition PS_JRNL_LN by FISCAL_YEAR. However, that column is only present on the parent table, PS_JRNL_HEADER. Oracle can do referential partitioning, where the child table is partitioned by an attribute of a column in the parent table. The parent table must also be partitioned similarly, thus producing a 1:1 mapping of partitions between the parent and child tables. However, this feature also requires the presence of an enforced foreign key constraint between parent and child tables.

PeopleSoft has never implemented or supported database-enforced referential integrity constraints (again, mainly because it was done differently on different databases). Although it is tempting to add a foreign key constraint between these tables, that would be a customisation to PeopleSoft that Oracle would not support. The application would then have to insert parent rows before child rows and delete child rows before deleting parent rows. It has never been tested against these constraints.

Therefore, it is only possible to consider partitioning by a column on PS_JRNL_LN. A column in the unique key is an obvious choice.

Depending on how BUSINESS_UNIT is set up and used, you might be able to list sub-partition by this column, and split journal lines down into several subpartitions. However, it is almost inevitable that the volumes will be heavily skewed.
It is tempting to range partition on JOURNAL_ID. Although this column usually contains an entirely numeric value, it is in fact defined as a character (VARCHAR2) data type. Therefore, it is not possible to interval partition upon it. Periodically, it would be necessary to add partitions manually.
The alternative is to interval range partition on JOURNAL_DATE. I chose to define a monthly interval. I specified the first few partitions for whole years because at this customer, these partitions contained less data after archiving. Thereafter, Oracle automatically creates monthly partitions as data is inserted.

CREATE TABLE PS_JRNL_LN 
(BUSINESS_UNIT VARCHAR2(5 CHAR) NOT NULL
,JOURNAL_ID VARCHAR2(10 CHAR) NOT NULL 
,JOURNAL_DATE DATE NOT NULL 
,UNPOST_SEQ NUMBER(*,0) NOT NULL 
,JOURNAL_LINE NUMBER(9,0) NOT NULL 
,LEDGER VARCHAR2(10 CHAR) NOT NULL 
…
) PARTITION BY RANGE (JOURNAL_DATE) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) 
(PARTITION JRNL_LN_2016 VALUES LESS THAN (TO_DATE('2017-01-01', 'YYYY-MM-DD'))
,PARTITION JRNL_LN_2017 VALUES LESS THAN (TO_DATE('2018-01-01', 'YYYY-MM-DD'))
,PARTITION JRNL_LN_2018 VALUES LESS THAN (TO_DATE('2019-01-01', 'YYYY-MM-DD'))
,PARTITION JRNL_LN_2019 VALUES LESS THAN (TO_DATE('2020-01-01', 'YYYY-MM-DD')) 
) 
/

Partitioning on JOURNAL_DATE still arranges the journal line data into current and historical partitions. We find that the various financial processes only create journal lines in the current and previous fiscal years. Therefore, earlier fiscal years are effectively static. This presents an opportunity to compress these partitions because nothing will subsequently update them that would decompress compressed rows. Thus, partitioning and compression go together.

Renaming Partitions

By default, interval partitions are given system-generated names. I find it convenient to rename them to something more meaningful. This has no bearing on performance. In this case, I used something based on the date to which the partition relates. There are 2 implicit cursors in the following PL/SQL block. The first renames table partitions, and the second renames index partitions. This script should be run periodically as new partitions are added. It is available on GitHub.

rem rename_jrnl_ln_partitions.sql    
rem requires https://github.com/davidkurtz/psscripts/blob/master/psftapi.sql
spool rename_jrnl_ln_partitions.lst
set serveroutput on
DECLARE
  l_high_value DATE;
  l_sql CLOB;
  l_new_partition_name VARCHAR2(30);
BEGIN
  psft_ddl_lock.set_ddl_permitted(TRUE);
  FOR i IN (
    select /*+LEADING(r upt upkc utc)*/ r.recname, upt.table_name, utp.partition_name, utp.high_value, upt.interval interval_size
    from sysadm.psrecdefn r 
      INNER JOIN user_part_tables upt ON upt.table_name = DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) 
        AND upt.partitioning_type = 'RANGE' and upt.interval IS NOT NULL
      INNER JOIN user_part_key_columns upkc ON upkc.name = upt.table_name AND upkc.object_Type = 'TABLE' and upkc.column_position = 1
      INNER JOIN user_tab_columns utc ON utc.table_name = upkc.name AND utc.column_name = upkc.column_name
      INNER JOIN user_tab_partitions utp ON utp.table_name = upt.table_name AND utp.partition_name like 'SYS_P%'
    WHERE r.recname = 'JRNL_LN' AND r.rectype = 0
    AND (utc.data_type = 'DATE' OR utc.data_type like 'TIMESTAMP%')
  ) LOOP
    l_sql := 'SELECT '||i.high_value||'-'||i.interval_size||' FROM DUAL';
    EXECUTE IMMEDIATE l_sql INTO l_high_value;
    l_new_partition_name := i.recname||'_'||TO_CHAR(l_high_value,'YYYYMM');
    l_sql := 'ALTER TABLE '||i.table_name||' RENAME PARTITION '||i.partition_name||' TO '||l_new_partition_name;
    IF i.partition_name != l_new_partition_name THEN
      dbms_output.put_line(l_sql);
      EXECUTE IMMEDIATE l_sql;
    END IF;
  END LOOP;

  FOR i IN (
    select /*+LEADING(r upi upkc utc)*/ r.recname, upi.index_name, uip.partition_name, uip.high_value, upi.interval interval_size
    from sysadm.psrecdefn r 
      INNER JOIN user_part_indexes upi ON upi.table_name = DECODE(r.sqltablename,' ','PS_'||r.recname,r.sqltablename) 
        AND upi.partitioning_type = 'RANGE' and upi.interval IS NOT NULL
      INNER JOIN user_part_key_columns upkc ON upkc.name = upi.index_name AND upkc.object_Type = 'INDEX' and upkc.column_position = 1
      INNER JOIN user_tab_columns utc ON utc.table_name = upi.table_name AND utc.column_name = upkc.column_name
      INNER JOIN user_ind_partitions uip ON uip.index_name = upi.index_name 
        AND (uip.partition_name like 'SYS_P%' OR SUBSTR(uip.partition_name,1+LENGTH(r.recname),1) != SUBSTR(upi.index_name,3,1))
    WHERE r.recname = 'JRNL_LN' AND r.rectype = 0
    AND (utc.data_type = 'DATE' OR utc.data_type like 'TIMESTAMP%')
  ) LOOP
    l_sql := 'SELECT '||i.high_value||'-'||i.interval_size||' FROM DUAL';
    EXECUTE IMMEDIATE l_sql INTO l_high_value;
    l_new_partition_name := i.recname||SUBSTR(i.index_name,3,1)||TO_CHAR(l_high_value,'YYYYMM');
    l_sql := 'ALTER INDEX '||i.index_name||' RENAME PARTITION '||i.partition_name||' TO '||l_new_partition_name;
    IF i.partition_name != l_new_partition_name THEN
      dbms_output.put_line(l_sql);
      EXECUTE IMMEDIATE l_sql;
    END IF;
  END LOOP;
  psft_ddl_lock.set_ddl_permitted(FALSE);
END;
/
spool off

Tuesday, March 18, 2025

Locally Partitioned Unique Indexes on Reference Partitioned Tables

Normally, if you want to locally partition a unique index, you must include the partitioning key in the index key. Otherwise, you get will error ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index.

CREATE TABLE PS_JRNL_HEADER 
(BUSINESS_UNIT VARCHAR2(5 CHAR) NOT NULL
,JOURNAL_ID VARCHAR2(10 CHAR) NOT NULL
,JOURNAL_DATE DATE NOT NULL
,UNPOST_SEQ NUMBER NOT NULL
…
) 
PARTITION BY RANGE (fiscal_year) INTERVAL (1)
(PARTITION FISCAL_YEAR_2016 VALUES LESS THAN (2017))
…
/

CREATE UNIQUE INDEX PS_JRNL_HEADER 
ON PS_JRNL_HEADER (BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, UNPOST_SEQ)
LOCAL
/

ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

This rule also applies to indexes on reference partitioned tables but in a slightly different way. The unique key on the child table cannot contain the partitioning key because those columns are only on the parent table. However, it can still be locally partitioned if it includes the parent table's primary key.

Here is an example taken from PeopleSoft General Ledger. We can't add foreign keys to the PeopleSoft database, but we can add them to an archive database to support queries of archived data.

CREATE TABLE PS_JRNL_HEADER 
(BUSINESS_UNIT VARCHAR2(5 CHAR) NOT NULL
,JOURNAL_ID VARCHAR2(10 CHAR) NOT NULL
,JOURNAL_DATE DATE NOT NULL
,UNPOST_SEQ NUMBER NOT NULL
…
,CONSTRAINT PS_JRNL_HEADER PRIMARY KEY (BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, UNPOST_SEQ)
) 
PARTITION BY RANGE (fiscal_year) INTERVAL (1)
SUBPARTITION BY RANGE (accounting_period) 
SUBPARTITION TEMPLATE 
(SUBPARTITION accounting_period_00 VALUES LESS THAN (1)
…
,SUBPARTITION accounting_period_12 VALUES LESS THAN (13)
,SUBPARTITION accounting_period_max VALUES LESS THAN (maxvalue)
)
(PARTITION FISCAL_YEAR_2016 VALUES LESS THAN (2017))
COMPRESS FOR QUERY LOW 
/

CREATE TABLE PS_JRNL_LN 
(BUSINESS_UNIT VARCHAR2(5 CHAR) NOT NULL
,JOURNAL_ID VARCHAR2(10 CHAR) NOT NULL
,JOURNAL_DATE DATE NOT NULL
,UNPOST_SEQ NUMBER NOT NULL 
,JOURNAL_LINE NUMBER(9,0) NOT NULL
,LEDGER VARCHAR2(10 CHAR) NOT NULL
…
,CONSTRAINT PS_JRNL_LN PRIMARY KEY (BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, UNPOST_SEQ, JOURNAL_LINE, LEDGER)
,CONSTRAINT PS_JRNL_LN_FK FOREIGN KEY (BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, UNPOST_SEQ) REFERENCES PS_JRNL_HEADER 
)
PARTITION BY REFERENCE(PS_JRNL_LN_FK)
COMPRESS FOR ARCHIVE LOW 
/

If I try to locally partition a unique index without one of the parent table's unique key columns, I get ORA-14039, which is exactly as I might expect.

CREATE UNIQUE INDEX PS_JRNL_LN2 
ON PS_JRNL_LN (BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, /*UNPOST_SEQ,*/ JOURNAL_LINE, LEDGER) 
LOCAL COMPRESS 3
/

ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
14039. 00000 -  "partitioning columns must form a subset of key columns of a UNIQUE index"
*Cause:    User attempted to create a UNIQUE partitioned index whose
           partitioning columns do not form a subset of its key columns
           which is illegal
*Action:   If the user, indeed, desired to create an index whose
           partitioning columns do not form a subset of its key columns,
           it must be created as non-UNIQUE; otherwise, correct the
           list of key and/or partitioning columns to ensure that the index'
           partitioning columns form a subset of its key columns

What is going on here?

JRNL_HEADER is partitioned on FISCAL_YEAR and sub-partitioned on ACCOUNTING_PERIOD.
JRNL_LN is reference-partitioned. Reference partitioning requires an enforced foreign key constraint. JRNL_LN has a foreign key constraint that references JRNL_HEADER. Thus, there is a 1:1 relationship of partitions on JRNL_LN (the child table), to partitions or in this case, sub-partitions on JRNL_HEADER (the parent table).

SELECT table_name, composite, partition_name, subpartition_count, partition_position, high_value
FROM   user_tab_partitions
WHERE  table_name LIKE 'PS_JRNL%' ORDER BY 1 DESC
/

                                                                 SubP Part
TABLE_NAME         COM PARTITION_NAME                           Count  Pos HIGH_VALUE
------------------ --- ---------------------------------------- ----- ---- --------------------
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_00        0    1
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_01        0    2
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_02        0    3
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_03        0    4
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_04        0    5
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_05        0    6
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_06        0    7
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_07        0    8
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_08        0    9
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_09        0   10
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_10        0   11
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_11        0   12
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_12        0   13
PS_JRNL_LN         NO  FISCAL_YEAR_2016_ACCOUNTING_PERIOD_MAX       0   14
PS_JRNL_HEADER     YES FISCAL_YEAR_2016                            14    1 2017

15 rows selected.

The partitioning keys recorded for the reference partitioned table JRNL_LN are the primary key columns on the parent table, although it is partitioned and sub-partitioned by different columns (FISCAL_YEAR and ACCOUNTING_PERIOD).

SELECT * FROM user_part_key_columns WHERE name LIKE 'PS_JRNL%' ORDER BY 1,2 desc, 4
/

NAME                 OBJEC COLUMN_NAME          COLUMN_POSITION COLLATED_COLUMN_ID
-------------------- ----- -------------------- --------------- ------------------
PS_JRNL_HEADER       TABLE FISCAL_YEAR                        1                   
PS_JRNL_LN           TABLE BUSINESS_UNIT                      1                   
PS_JRNL_LN           TABLE JOURNAL_ID                         2                   
PS_JRNL_LN           TABLE JOURNAL_DATE                       3                   
PS_JRNL_LN           TABLE UNPOST_SEQ                         4

The parent table in a foreign key relationship must have a primary key, and the child table must reference it. The primary key of the parent table is a proxy for the partitioning key. The two tables effectively share the partitioning key through the 1:1 relationship of partitions. Each primary key on the parent table can only appear in a single sub-partition, and therefore, corresponding child rows can only appear in the corresponding partition in the child table. Therefore, the uniqueness of the locally partitioned index on the child table can be assured by inspecting just the local partition.

Natural -v- Surrogate Keys

The example above uses natural keys. The key on the child table leads with the key columns of the parent table, followed by one or more additional key columns. Thus it is possible to locally partition the primary or unique key index on the child table.

However, if a data model uses surrogate keys then the key on the child table is a completely different column to the key on the parent table, and it would not be possible to locally partition an index unless it also contained the parent surrogate key, in which case it could not be used to enforce uniqueness.

see also Ask Tom: Surrogate -v- Natural Keys

TL;DR

It appears that the rule that partitioning columns must form a subset of key columns of a unique index has been relaxed. It hasn't really. Instead, the reference partition uses the primary key on the parent of the foreign key as a proxy for the partitioning key.

Tuesday, November 24, 2020

PeopleSoft Financials Ledger Partitioning Recommendations

I have written previously about partitioning the PS_LEDGER table in Financials for the benefit of General Ledger (GL) reporting. I have always recommended top-level range partitioning on the combination of FISCAL_YEAR and ACCOUNTING_PERIOD with separate partitions for each accounting period. It also leaves sub-partitioning available to use another column, usually LEDGER.

PeopleSoft DBA blog: nVision Performance Tuning: 4. Partitioning of Ledger, Ledger Budget, and Summary Ledger Tables
Go-Faster blog: Retrofitting Partitioning into Existing Applications: Example 1. General Ledger

However, recent research into partition change tracking (PCT) and materialized views has made me question that advice. The decision is not as clear cut.

Go-Faster blog: Partition Change Tracking During Materialized View Refresh and Query Rewrite

Summary Ledgers or Materialized Views

You can create summary ledger tables in PeopleSoft Financials that are incrementally maintained by batch processes, and then nVision reports can reference the summary ledgers instead. If the summary ledgers are not up to date, then the reports will produce out of date results.

Similarly, materialized views can be used in an Oracle database to create pre-generated reporting tables. An application can reference a materialized view directly, or the database can dynamically rewrite submitted SQL queries to use the materialized view if they are sufficiently similar to the query that created the materialized view and if the optimizer judges that it is cheaper to query the materialized view. By default, the database will check that the materialized view is up to date, that is no change has been made to the underlying tables since the last refresh commenced, before it can rewrite the SQL query. So the query will always return the same data, but if the materialized view is out of date you don't get the performance improvement.

You can optionally choose to configure the database to write SQL queries to use stale materialized views by setting QUERY_REWRITE_INTEGRITY=stale_tolerated at either database or session-level.

Materialized views can be created for the nVision queries that you wish to optimise, and no further code change is required because the database will rewrite the SQL. You can see a typical example of this in my blog about PCT.

Partition Change Tracking is a mechanism the Oracle database uses to 'track freshness to a finer grain than the entire materialized view'. It can identify which partitions and subpartitions are fresh and can be used for query rewrite, and to refresh just the partitions that are stale or that contain stale sub-partitions.

Alternatives for Partitioning PS_LEDGER

If you wish to create materialized views on the main ledger table, and rely upon query rewrite, and keep the materialized views up to date with respect to the ledger table, and only use them when they are up to date, then you probably want PCT to help with both rewrite and refresh.

1. Multi-column composite partitioning

I usually like to range partition PS_LEDGER on the combination of FISCAL_YEAR and ACCOUNTING_PERIOD to produce a separate range partition for each accounting period. Partition pruning works very effectively with the SQL generated by nVision. It also allows sub-partitioning on another column, usually LEDGER. This is desirable when a system has multiple actuals ledgers, and especially since 9.2 where the budget data is also stored in PS_LEDGER rather than PS_LEDGER_BUDG.

However, amongst the documented restrictions of PCT is that partitioned tables must use either range, or list partitioning, or composite partitioning with range or list as the top-level partitioning strategy. Also, the top-level partition key must consist of only a single column (see Database Data Warehousing Guide -> Advanced Materialized Views -> About Partition Change Tracking)

If I want to use query rewrite to materialized views for queries on LEDGER table then I have a few choices.

If I stick with multi-column range partitioning, then I cannot use PCT. I must either keep the materialized views fresh, or the queries remain on the ledger table. Any update to any partition in the ledger table will render the entire materialized view stale and prevent query rewrite. Many customers run a suite of nVision reportbooks overnight. I could set QUERY_REWRITE_INTEGRITY=stale_tolerated at session-level for the report books processes using a trigger on the process scheduler request table (PSPRCSRQST) - see Setting Oracle Session Parameters for Specific Process Scheduler Processes. In this case, I would have to take responsibility for refreshing the materialized views prior to running, say, a suite of report processes. This is effectively the same situation as using summary ledgers, but without code change to the reports.

I have created materialized views on summary ledger tables in order to provide compressed copies of the summary ledger. Again, in this case, the materialized views had to be refreshed after the summary ledger maintenance process.

Or, I have to produce a simpler partitioning strategy for the ledger table that is still compatible with PCT.

2. Composite Range-Range Partitioning on FISCAL_YEAR and ACCOUNTING_PERIOD

I could composite partition both the LEDGER table and the materialized views by FISCAL_YEAR and ACCOUNTING_PERIOD, but then I cannot further subpartition by other columns. This would degrade queries on smaller ledgers that could not be rewritten to dedicated materialized views.

CREATE TABLE ps_ledger
(business_unit VARCHAR2(5) NOT NULL
…
) PCTFREE 10 PCTUSED 80
PARTITION BY RANGE (FISCAL_YEAR) 
SUBPARTITION BY RANGE (ACCOUNTING_PERIOD)
SUBPARTITION TEMPLATE
(SUBPARTITION ap_bf VALUES LESS THAN (1) 
,SUBPARTITION ap_01 VALUES LESS THAN (2) 
,SUBPARTITION ap_02 VALUES LESS THAN (3) 
,SUBPARTITION ap_03 VALUES LESS THAN (4) 
,SUBPARTITION ap_04 VALUES LESS THAN (5) 
,SUBPARTITION ap_05 VALUES LESS THAN (6) 
,SUBPARTITION ap_06 VALUES LESS THAN (7) 
,SUBPARTITION ap_07 VALUES LESS THAN (8) 
,SUBPARTITION ap_08 VALUES LESS THAN (9) 
,SUBPARTITION ap_09 VALUES LESS THAN (10) 
,SUBPARTITION ap_10 VALUES LESS THAN (11) 
,SUBPARTITION ap_11 VALUES LESS THAN (12) 
,SUBPARTITION ap_12 VALUES LESS THAN (13) 
,SUBPARTITION ap_cf VALUES LESS THAN (MAXVALUE))
 (PARTITION ledger_2018 VALUES LESS THAN (2019) PCTFREE 0 COMPRESS
,PARTITION ledger_2019 VALUES LESS THAN (2020) PCTFREE 0 COMPRESS
,PARTITION ledger_2020 VALUES LESS THAN (2021) 
,PARTITION ledger_2021 VALUES LESS THAN (2022) 
)
ENABLE ROW MOVEMENT NOLOGGING
/

The materialized view will be similarly composite partitioned. Note that I have created the materialized view for specific fiscal years and for a specific ledger. I would create materialized views for each combination of ledger and each distinct set of analysis columns that are regularly reported upon.

CREATE MATERIALIZED VIEW mv_ledger_2020
PARTITION BY RANGE (FISCAL_YEAR)
SUBPARTITION BY RANGE (ACCOUNTING_PERIOD)
SUBPARTITION TEMPLATE
(SUBPARTITION ap_bf VALUES LESS THAN (1) 
,SUBPARTITION ap_01 VALUES LESS THAN (2) 
,SUBPARTITION ap_02 VALUES LESS THAN (3) 
,SUBPARTITION ap_03 VALUES LESS THAN (4) 
,SUBPARTITION ap_04 VALUES LESS THAN (5) 
,SUBPARTITION ap_05 VALUES LESS THAN (6) 
,SUBPARTITION ap_06 VALUES LESS THAN (7) 
,SUBPARTITION ap_07 VALUES LESS THAN (8) 
,SUBPARTITION ap_08 VALUES LESS THAN (9) 
,SUBPARTITION ap_09 VALUES LESS THAN (10) 
,SUBPARTITION ap_10 VALUES LESS THAN (11) 
,SUBPARTITION ap_11 VALUES LESS THAN (12) 
,SUBPARTITION ap_12 VALUES LESS THAN (13) 
,SUBPARTITION ap_cf VALUES LESS THAN (MAXVALUE))
(PARTITION ledger_2019 VALUES LESS THAN (2020)
,PARTITION ledger_2020 VALUES LESS THAN (2021)
) PCTFREE 0 COMPRESS PARALLEL
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT business_unit, account, chartfield1, fiscal_year, accounting_period,
sum(posted_total_amt) posted_total_amt
FROM ps_ledger
WHERE fiscal_year >= 2019
AND   ledger = 'ACTUALS'
AND   currency_cd = 'GBP'
GROUP BY business_unit, account, chartfield1, fiscal_year, accounting_period
/

Note that I have equality criteria on LEDGER and CURRENCY_CD in the materialized view, but I have not included those columns in the select clause, and so they are not in the view. Oracle can still rewrite queries to use this materialized view and that specify the same criteria on PS_LEDGER

PCT will determine whether any unpruned partitions or subpartitions are stale and if so prevent query rewrite. It is documented behaviour that the materialized view refresh will truncate and rebuild the whole top-level partitions, in this case each fiscal year, where the partition or any constituent sub-partitions are stale. So even if just one subpartition, for one accounting period is stale, the whole fiscal year is refreshed.

3. Composite Range-Range Partitioning on ACCOUNTING_PERIOD and FISCAL_YEAR

I investigated making ACCOUNTING_PERIOD the top-level partitioning key, and sub-partitioning on FISCAL_YEAR.

Partitioning pruning works correctly, so the query performance will be unaffected,
PCT refresh processed all years for the single accounting period, rather than all accounting periods for the single year. That is less work if you have fewer fiscal years than accounting periods. Generally, this is the case. I usually see systems that contain 3 to 6 fiscal years of data.

However, it has a number of problems.

I can't specify storage options or compression attributes on sub-partitions in the create table DDL command, so I have to come along afterwards with PL/SQL to alter the sub-partitions.
It is not possible to interval range sub-partition an object, so I can't automatically add partitions for future fiscal years on demand. Instead, I am going to have to add new fiscal year subpartitions to each of the 14 range partitions.

On balance, I don't think I would choose to implement this.

Conclusion

There is no single clear recommendation for partitioning and PCT. It will depend on the circumstances.

If I don't need to introduce materialized views on PS_LEDGER then I would usually stick with the multi-column composite partitioning with the top-level range partition on FISCAL_YEAR and ACCOUNTING_PERIOD.

PeopleSoft provides summary ledgers to improve the performance of the ledger queries and compressed materialized views can be built on these.

If you only have a single actuals ledger then composite range-range partition on FISCAL_YEAR and ACCOUNTING_PERIOD is attractive.

I do not recommend interval partitioning on FISCAL_YEAR because this affects the scope of the refresh process. It processes a second top-level range partition.

If the budget data is stored in the PS_LEDGER table rather than LEDGER_BUDG then consider building separate materialized views for each value of LEDGER.

If you can manage to build materialized views to support rewrite of most the queries on the smaller ledgers, then the lack of sub-partitioning by LEDGER is not going to be a problem unless the query doesn't get rewritten because the materialized views are stale. Keeping the materialized views up to date is going to be a bigger challenge.

Monday, November 23, 2020

Partition Change Tracking During Materialized View Refresh and Query Rewrite

This article discusses the interplay of Partitioning, Partition Change Tracking and Query Rewrite in relation to Materialized Views.

Introduction

Documented Preconditions and Limitations

Demonstrations

1: Multi-column composite partitioning
2: Simple 1-Dimensional Range Partitioning
3: Interval Partitioning
4: Composite (Range-List) Partitioning
5: Composite (Range-Range) Partitioning
6: Mismatching Partitioning
7: Partition on Accounting Period, Subpartition on Fiscal Year

Conclusion

Introduction

In the Oracle database, Materialized Views can be used to create pre-generated reporting tables. A view of the data based on a SQL query is materialized into a table. That query may restrict the rows and columns and may aggregate the data. An application can reference the materialized view directly, or the Oracle database can 'rewrite' SQL queries on the original tables that are similar to query in a materialized view to use that materialized view instead.

By default, QUERY_REWRITE_INTEGRITY is enforced, which means Query rewrite works only with materialized views that are up to date (i.e. the underlying data hasn't changed since the materialized view was last refreshed). This note deals with that scenario. Optionally, rewrite integrity can be configured to allow rewrite to occur on stale materialized views (this is called 'stale tolerated'). It can be set at system or session-level.

Partition Change Tracking (PCT) is 'a way of tracking the staleness of a materialized view on the partition and subpartition level'. If both the materialized view and at least one underlying table in the view are similarly partitioned, then Oracle can determine the relationship between partitions and subpartitions in the underlying table and those in the materialized view. The database can track not just whether any partition in the underlying tables has been updated since the last refresh of the materialized view, but which ones. During SQL parse, if after partition pruning of the query on the underlying tables, none of the remaining partitions are stale then the query can still be rewritten. Also, it is possible to refresh just the stale partitions in the materialized view, those that correspond to the underlying partitions that have been updated since the last refresh.

Query rewrite is a cost-based SQL transformation. Therefore, it will only occur if the optimizer calculates that the rewritten query has a lower cost. If I refresh the materialized view in non-atomic mode, then the materialized view will be truncated and populated in direct-path mode, thus the data can be compressed (either with basic compression, or Hybrid-Columnar Compression if on an engineered platform) without the need of the Advanced Compression Licence. This will further reduce the size and cost of accessing the materialized view and increase the likelihood of query rewrite.

I have written a series of blogs about retrofitting partitioning into existing applications. One of my examples was based on PeopleSoft General Ledger reporting in which I discussed options for partitioning the ledger such that there is a different partition for each accounting period. Once an accounting period is closed the application generally doesn't usually change it further. It should be possible to create partitioned materialized views on the ledger table to support GL reporting using query rewrite. As the application continues to insert data into the partition for the current accounting period, that partition will quickly become stale and queries on that partition won't be rewritten. However, it is common for customers to run suites of reports overnight, and those could be run after a materialized view refresh and make good use of query rewrite.

However, as I modelled this, I ran into a few problems that reveal some of the behaviour of PCT, query rewrite and materialized view refresh. I have created a number of test scripts that illustrate various scenarios that I will describe below. The full scripts are available on Github.

Documented Preconditions and Limitations

Oracle's documentation sets out a number of preconditions for PCT.

Partitioned tables must use either range, list or composite partitioning with range or list as the top-level partitioning strategy. - Therefore, hash partitioning is not supported. What about interval partitioning? See demonstration 3.
The top-level partition key must consist of only a single column. - If, as I proposed, the ledger table is range partitioned on the combination FISCAL_YEAR and ACCOUNTING_PERIOD then PCT will not work (see demonstration 1: Multi-column composite partitioning). So, are other partitioning strategies viable?
The materialized view must contain either the partition key column or a partition marker or ROWID or join dependent expression of the detail table.
If you use a GROUP BY clause, the partition key column or the partition marker or ROWID or join dependent expression must be present in the GROUP BY clause.

Note that, while partition change tracking tracks the staleness on a partition and subpartition level (for composite partitioned tables), the level of granularity for PCT refresh is only the top-level partitioning strategy. Consequently, any change to data in one of the subpartitions of a composite partitioned-table will only mark the single impacted subpartition as stale and have the rest of the table available for rewrite, but the PCT refresh will refresh the whole partition that contains the impacted subpartition.

Demonstrations

In each of the following demonstrations, I will create a copy of the PeopleSoft Financials General Ledger table PS_LEDGER, populate it with random data to simulate 2½ years of actuals and 4 years of budget data. The table will be partitioned differently in each demonstration. I will also create one or two materialized views that will also be partitioned. Then I will add data for another accounting period and look at how the materialized view refresh and query rewrite behave when one partition is stale.

The tests have been run on Oracle 19.9. Query rewrite is enabled, and rewrite integrity is enforced.

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      enforced

Demonstration 1: Multi-column composite partitioning

I will start with my usual composite partitioning of the ledger table on the combination of FISCAL_YEAR and ACCOUNTING_PERIOD to permit sub-partitioning on LEDGER.

CREATE TABLE ps_ledger
(business_unit VARCHAR2(5) NOT NULL
,ledger VARCHAR2(10) NOT NULL
,account VARCHAR2(10) NOT NULL
…
) PCTFREE 10 PCTUSED 80
PARTITION BY RANGE (FISCAL_YEAR,ACCOUNTING_PERIOD) 
SUBPARTITION BY LIST (LEDGER)
SUBPARTITION TEMPLATE
(SUBPARTITION actuals  VALUES ('ACTUALS')
,SUBPARTITION budget   VALUES ('BUDGET'))
(PARTITION ledger_2018 VALUES LESS THAN (2019,0) PCTFREE 0 COMPRESS
--
,PARTITION ledger_2019_bf VALUES LESS THAN (2019,1) PCTFREE 0 COMPRESS 
,PARTITION ledger_2019_01 VALUES LESS THAN (2019,2) PCTFREE 0 COMPRESS 
…
,PARTITION ledger_2019_12 VALUES LESS THAN (2019,13) PCTFREE 0 COMPRESS
,PARTITION ledger_2019_cf VALUES LESS THAN (2020,0) PCTFREE 0 COMPRESS
--
,PARTITION ledger_2020_bf VALUES LESS THAN (2020,1)
,PARTITION ledger_2020_01 VALUES LESS THAN (2020,2) 
…
,PARTITION ledger_2020_12 VALUES LESS THAN (2020,13)
,PARTITION ledger_2020_cf VALUES LESS THAN (2021,0)
--
,PARTITION ledger_2021_bf VALUES LESS THAN (2021,1)
,PARTITION ledger_2021_01 VALUES LESS THAN (2021,2)
…
,PARTITION ledger_2021_12 VALUES LESS THAN (2021,13)
,PARTITION ledger_2021_cf VALUES LESS THAN (2022,0)
)
ENABLE ROW MOVEMENT 
NOPARALLEL NOLOGGING
/
@treeselectors
@popledger

I will also create the tree selector tables used as dimension tables in the nVision General Ledger Reports

REM treeselectors.sql 
CREATE TABLE PSTREESELECT05
(SELECTOR_NUM INTEGER NOT NULL,
 TREE_NODE_NUM INTEGER NOT NULL,
 RANGE_FROM_05 VARCHAR2(05) NOT NULL,
 RANGE_TO_05   VARCHAR2(05) NOT NULL)
 PARTITION BY RANGE (SELECTOR_NUM) INTERVAL (1)
 (PARTITION pstreeselector VALUES LESS THAN (2))
 NOPARALLEL NOLOGGING;
CREATE UNIQUE INDEX PS_PSTREESELECT05 ON PSTREESELECT05 (SELECTOR_NUM, TREE_NODE_NUM, RANGE_FROM_05);

CREATE TABLE PSTREESELECT10 
(SELECTOR_NUM INTEGER NOT NULL,
 TREE_NODE_NUM INTEGER NOT NULL,
 RANGE_FROM_10 VARCHAR2(10) NOT NULL,
 RANGE_TO_10   VARCHAR2(10) NOT NULL)
 PARTITION BY RANGE (SELECTOR_NUM) INTERVAL (1)
 (PARTITION pstreeselector VALUES LESS THAN (2))
 NOPARALLEL NOLOGGING;
CREATE UNIQUE INDEX PS_PSTREESELECT10 ON PSTREESELECT10 (SELECTOR_NUM, TREE_NODE_NUM, RANGE_FROM_10);

exec dbms_stats.set_table_prefs('SCOTT','PSTREESELECT05','GRANULARITY','ALL');
exec dbms_stats.set_table_prefs('SCOTT','PSTREESELECT10','GRANULARITY','ALL');
exec dbms_stats.set_table_prefs('SCOTT','PSTREESELECT05','METHOD_OPT'-
    ,'FOR ALL COLUMNS SIZE 1, FOR COLUMNS SELECTOR_NUM, (SELECTOR_NUM, TREE_NODE_NUM) SIZE 254');
exec dbms_stats.set_table_prefs('SCOTT','PSTREESELECT10','METHOD_OPT'-
    ,'FOR ALL COLUMNS SIZE 1, FOR COLUMNS SELECTOR_NUM, (SELECTOR_NUM, TREE_NODE_NUM) SIZE 254');

And then I will populate and collect statistics on the ledger with randomised, but skewed, data to simulate

actuals data from fiscal year 2018 to period 6 of 2020
budget data from fiscal year 2018 to 2021 that is 10% of the size of the actuals data.

Some typical indexes will be built on the ledger table.

The tree selector tables will be populated with data corresponding to the ledger data:

the business unit tree will have both business units,
the account tree will have 25% of the 999 accounts,
the chartfield tree will have 10% of the 999 chartfields.

Statistics preferences will be defined so that statistics will be collected at all table, partition and subpartition levels on all these tables. There will only be histograms on a few low cardinality columns.

REM popledger.sql
set autotrace off echo on pages 99 lines 200 trimspool on
truncate table ps_ledger;
exec dbms_stats.set_table_prefs('SCOTT','PS_LEDGER','METHOD_OPT'-
    ,'FOR ALL COLUMNS SIZE 1, FOR COLUMNS FISCAL_YEAR, ACCOUNTING_PERIOD, LEDGER, BUSINESS_UNIT SIZE 254');
exec dbms_stats.set_table_prefs('SCOTT','PS_LEDGER','GRANULARITY','ALL');
ALTER TABLE PS_LEDGER PARALLEL 8 NOLOGGING;

CREATE /*UNIQUE*/ INDEX ps_ledger ON ps_ledger
(business_unit, ledger, account, deptid
,product, fund_code, class_fld, affiliate
,chartfield2, project_id, book_code, gl_adjust_type
,currency_cd, statistics_code, fiscal_year, accounting_period
) COMPRESS 2 PARALLEL
/
INSERT /*+APPEND PARALLEL ENABLE_PARALLEL_DML NO_GATHER_OPTIMIZER_STATISTICS*//*IGNORE_ROW_ON_DUPKEY_INDEX(PS_LEDGER)*/ 
INTO ps_ledger
with n as (
SELECT rownum n from dual connect by level <= 1e2
), fy as (
SELECT 2017+rownum fiscal_year FROM dual CONNECT BY level <= 4
), ap as (
SELECT FLOOR(dbms_random.value(0,13)) accounting_period FROM dual connect by level <= 998
UNION ALL SELECT 998 FROM DUAL CONNECT BY LEVEL <= 1
UNION ALL SELECT 999 FROM DUAL CONNECT BY LEVEL <= 1
), l as (
SELECT 'ACTUALS' ledger FROM DUAL CONNECT BY LEVEL <= 10
UNION ALL SELECT 'BUDGET' FROM DUAL
)
select 'BU'||LTRIM(TO_CHAR(CASE WHEN dbms_random.value <= .9 THEN 1 ELSE 2 END,'000')) business_unit 
,      l.ledger
,      'ACC'||LTRIM(TO_CHAR(999*SQRT(dbms_random.value),'000')) account 
,      'ALTACCT'||LTRIM(TO_CHAR(999*dbms_random.value,'000')) altacct
,      'DEPT'||LTRIM(TO_CHAR(9999*dbms_random.value,'0000')) deptid
,      'OPUNIT'||LTRIM(TO_CHAR(99*dbms_random.value,'00')) operating_unit
,      'P'||LTRIM(TO_CHAR(99999*dbms_random.value,'00000')) product 
,      'FUND'||LTRIM(TO_CHAR(9*dbms_random.value,'0')) fund_code
,      'CLAS'||LTRIM(TO_CHAR(9*dbms_random.value,'0')) class_fld
,      'PROD'||LTRIM(TO_CHAR(9*dbms_random.value,'0')) program_code
,      ' ' budget_ref
,      'AF'||LTRIM(TO_CHAR(999*dbms_random.value,'000')) affiliate 
,      'AFI'||LTRIM(TO_CHAR(99999*dbms_random.value,'00000')) affiliate_intra1
,      'AFI'||LTRIM(TO_CHAR( 9999*dbms_random.value,'0000')) affiliate_intra2
,      'CF'||LTRIM(TO_CHAR(  999*SQRT(dbms_random.value),'000')) chartfield1
,      'CF'||LTRIM(TO_CHAR(99999*dbms_random.value,'00000')) chartfield2
,      'CF'||LTRIM(TO_CHAR( 9999*dbms_random.value,'0000')) chartfield3
,      'PRJ'||LTRIM(TO_CHAR(9999*dbms_random.value,'0000')) project_id
,      'BK'||LTRIM(TO_CHAR(99*dbms_random.value,'00')) book_code
,      'GL'||LTRIM(TO_CHAR(99*dbms_random.value,'00')) gl_adjust_type
,      'GBP' currency_cd 
,      ' ' statistics_code 
,      fy.fiscal_year
,      ap.accounting_period
,      dbms_random.value(0,1e6) posted_total_amt 
,      0 posted_base_amt 
,      0 posted_tran_amt 
,      'GBP' base_currency 
,      SYSDATE dttm_stamp_sec
,      0 process_instance 
FROM   fy,ap, l, n
WHERE  l.ledger = 'BUDGET' or (fy.fiscal_year < 2020 or (fy.fiscal_year = 2020 AND ap.accounting_period <= 6))
/
commit;
exec dbms_stats.gather_table_stats('SCOTT','PS_LEDGER');

CREATE INDEX psxledger ON ps_ledger
(ledger, fiscal_year, accounting_period, business_unit, account, chartfield1
) LOCAL COMPRESS 4 PARALLEL
/
CREATE INDEX psyledger ON ps_ledger
(ledger, fiscal_year, business_unit, account, chartfield1, accounting_period
) LOCAL COMPRESS 3 PARALLEL
/
ALTER INDEX ps_ledger NOPARALLEL;
ALTER INDEX psxledger NOPARALLEL;
ALTER INDEX psyledger NOPARALLEL;

TRUNCATE TABLE PSTREESELECT05;
TRUNCATE TABLE PSTREESELECT10;
INSERT INTO PSTREESELECT05
WITH x as (SELECT DISTINCT business_unit FROM ps_ledger)
, y as (SELECT 30982, FLOOR(DBMS_RANDOM.value(1,1e10)) tree_node_num, business_unit FROM x)
select y.*, business_unit FROM y
/
INSERT INTO PSTREESELECT10
WITH x as (SELECT DISTINCT account FROM ps_ledger)
, y as (SELECT 30984, FLOOR(DBMS_RANDOM.value(1,1e10)) tree_node_num, account FROM x)
select y.*, account FROM y
where mod(tree_node_num,100)<25
/
INSERT INTO PSTREESELECT10
WITH x as (SELECT DISTINCT chartfield1 FROM ps_ledger)
, y as (SELECT 30985, FLOOR(DBMS_RANDOM.value(1,1e10)) tree_node_num, chartfield1 FROM x)
select y.*, chartfield1 FROM y
where mod(tree_node_num,100)<10
/

Per complete fiscal year, there are 1,000,000 actuals rows and 100,000 budget rows

LEDGER     FISCAL_YEAR   COUNT(*) MAX(ACCOUNTING_PERIOD)
---------- ----------- ---------- ----------------------
ACTUALS           2018    1000000                    999
                  2019    1000000                    999
                  2020     538408                      6

BUDGET            2018     100000                    999
                  2019     100000                    999
                  2020     100000                    999
                  2021     100000                    999

**********             ----------
sum                       2938408

There are about 77K rows per accounting period with just 1000 rows in periods 998 (adjustments), 999 (carry forward)

LEDGER     FISCAL_YEAR ACCOUNTING_PERIOD   COUNT(*)
---------- ----------- ----------------- ----------
…
ACTUALS           2019                 0      76841
                                       1      76410
                                       2      76867
                                       3      77088
                                       4      77740
                                       5      77010
                                       6      76650
                                       7      76553
                                       8      76923
                                       9      76586
                                      10      76276
                                      11      76943
                                      12      76113
                                     998       1000
                                     999       1000

********** ***********                   ----------
           sum                              1000000

ACTUALS           2020                 0      77308
                                       1      76696
                                       2      76944
                                       3      77227
                                       4      76944
                                       5      76524
                                       6      76765

********** ***********                   ----------
           sum                               538408
…

I will create two MVs each containing data for a single fiscal year; one for 2019 and one for 2020 I will only range partition the MV on accounting period. We don't need to partition it on FISCAL_YEAR since it only contains a single year.

CREATE MATERIALIZED VIEW mv_ledger_2019
PARTITION BY RANGE (ACCOUNTING_PERIOD)
(PARTITION ap_bf VALUES LESS THAN (1) 
,PARTITION ap_01 VALUES LESS THAN (2) 
,PARTITION ap_02 VALUES LESS THAN (3) 
,PARTITION ap_03 VALUES LESS THAN (4) 
,PARTITION ap_04 VALUES LESS THAN (5) 
,PARTITION ap_05 VALUES LESS THAN (6) 
,PARTITION ap_06 VALUES LESS THAN (7) 
,PARTITION ap_07 VALUES LESS THAN (8) 
,PARTITION ap_08 VALUES LESS THAN (9) 
,PARTITION ap_09 VALUES LESS THAN (10) 
,PARTITION ap_10 VALUES LESS THAN (11) 
,PARTITION ap_11 VALUES LESS THAN (12) 
,PARTITION ap_12 VALUES LESS THAN (13) 
,PARTITION ap_cf VALUES LESS THAN (MAXVALUE)
) PCTFREE 0 COMPRESS 
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT business_unit, account, chartfield1, fiscal_year, accounting_period,
sum(posted_total_amt) posted_total_amt
FROM ps_ledger
WHERE fiscal_year = 2019
AND   ledger = 'ACTUALS'
AND   currency_cd = 'GBP'
GROUP BY business_unit, account, chartfield1, fiscal_year, accounting_period
/
CREATE MATERIALIZED VIEW mv_ledger_2020
PARTITION BY RANGE (ACCOUNTING_PERIOD)
(PARTITION ap_bf VALUES LESS THAN (1) 
,PARTITION ap_01 VALUES LESS THAN (2) 
,PARTITION ap_02 VALUES LESS THAN (3) 
,PARTITION ap_03 VALUES LESS THAN (4) 
,PARTITION ap_04 VALUES LESS THAN (5) 
,PARTITION ap_05 VALUES LESS THAN (6) 
,PARTITION ap_06 VALUES LESS THAN (7) 
,PARTITION ap_07 VALUES LESS THAN (8) 
,PARTITION ap_08 VALUES LESS THAN (9) 
,PARTITION ap_09 VALUES LESS THAN (10) 
,PARTITION ap_10 VALUES LESS THAN (11) 
,PARTITION ap_11 VALUES LESS THAN (12) 
,PARTITION ap_12 VALUES LESS THAN (13) 
,PARTITION ap_cf VALUES LESS THAN (MAXVALUE)
) PCTFREE 0 COMPRESS 
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT business_unit, account, chartfield1, fiscal_year, accounting_period,
sum(posted_total_amt) posted_total_amt
FROM ps_ledger
WHERE fiscal_year = 2020
AND   ledger = 'ACTUALS'
AND   currency_cd = 'GBP'
GROUP BY business_unit, account, chartfield1, fiscal_year, accounting_period
/
@mvpop
@@mvpop
@@mvsql
@@pop2020m7
@@mvsql
@@mvtrc
@@mvvol
@@mvsql
@@mvcap

The materialized views are populated on creation, but I will explicitly collect statistics on them.

SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME;

ALTER MATERIALIZED VIEW mv_ledger_2019 NOPARALLEL;
exec dbms_stats.set_table_prefs('SCOTT','MV_LEDGER_2019','METHOD_OPT',-
    'FOR ALL COLUMNS SIZE 1, FOR COLUMNS FISCAL_YEAR, ACCOUNTING_PERIOD, BUSINESS_UNIT SIZE 254');
exec dbms_stats.set_table_prefs('SCOTT','MV_LEDGER_2019','GRANULARITY','ALL');

ALTER MATERIALIZED VIEW mv_ledger_2020 NOPARALLEL;
exec dbms_stats.set_table_prefs('SCOTT','MV_LEDGER_2020','METHOD_OPT',-
    'FOR ALL COLUMNS SIZE 1, FOR COLUMNS FISCAL_YEAR, ACCOUNTING_PERIOD, BUSINESS_UNIT SIZE 254');
exec dbms_stats.set_table_prefs('SCOTT','MV_LEDGER_2020','GRANULARITY','ALL');

exec dbms_stats.gather_table_stats('SCOTT','MV_LEDGER_2019');
exec dbms_stats.gather_table_stats('SCOTT','MV_LEDGER_2020');

Although I can do a full refresh of the MV, I cannot do a PCT refresh.

BEGIN dbms_mview.refresh(list=>'MV_LEDGER_2020',method=>'P',atomic_refresh=>FALSE); END;

*
ERROR at line 1:
ORA-12047: PCT FAST REFRESH cannot be used for materialized view "SCOTT"."MV_LEDGER_2020"
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3020
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2432
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 88
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 253
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2413
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2976
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3263
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3295
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 16
ORA-06512: at line 1

I can use EXPLAIN_MVIEW to check the status of the MV

REM mvcap.sql
create table MV_CAPABILITIES_TABLE
(
  statement_id      varchar(30) ,
  mvowner           varchar(30) ,
  mvname            varchar(30) ,
  capability_name   varchar(30) ,
  possible          character(1) ,
  related_text      varchar(2000) ,
  related_num       number ,
  msgno             integer ,
  msgtxt            varchar(2000) ,
  seq               number
) ;
 
truncate table MV_CAPABILITIES_TABLE;
EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW ('SCOTT.MV_LEDGER_2019');
EXECUTE DBMS_MVIEW.EXPLAIN_MVIEW ('SCOTT.MV_LEDGER_2020');
break on mvname skip 1
column rel_text format a20
column msgtxt format a60
SELECT mvname, capability_name,  possible, SUBSTR(related_text,1,20) AS rel_text, SUBSTR(msgtxt,1,60) AS msgtxt
FROM MV_CAPABILITIES_TABLE
WHERE mvname like 'MV_LEDGER_20%'
ORDER BY mvname, seq;

EXPLAIN_MVIEW reports that general query rewrite is available but PCT and PCT query rewrite are not. Per the manual, Oracle simply cannot do a PCT refresh if the table has multi-column partitioning.

CAPABILITY_NAME                P REL_TEXT             MSGTXT
------------------------------ - -------------------- ------------------------------------------------------------
PCT                            N
REFRESH_COMPLETE               Y
REFRESH_FAST                   N
REWRITE                        Y
PCT_TABLE                      N PS_LEDGER            PCT not supported with multi-column partition key
REFRESH_FAST_AFTER_INSERT      N SCOTT.PS_LEDGER      the detail table does not have a materialized view log
REFRESH_FAST_AFTER_ONETAB_DML  N POSTED_TOTAL_AMT     SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ONETAB_DML  N                      see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ONETAB_DML  N                      COUNT(*) is not present in the select list
REFRESH_FAST_AFTER_ONETAB_DML  N                      SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ANY_DML     N                      see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
REFRESH_FAST_PCT               N                      PCT is not possible on any of the detail tables in the mater
REWRITE_FULL_TEXT_MATCH        Y
REWRITE_PARTIAL_TEXT_MATCH     Y
REWRITE_GENERAL                Y
REWRITE_PCT                    N                      general rewrite is not possible or PCT is not possible on an
PCT_TABLE_REWRITE              N PS_LEDGER            PCT not supported with multi-column partition key

At the moment, the materialized views are up to date.

SELECT L.TREE_NODE_NUM,L2.TREE_NODE_NUM,SUM(A.POSTED_TOTAL_AMT)
FROM   PS_LEDGER A
,      PSTREESELECT05 L1
,      PSTREESELECT10 L
,      PSTREESELECT10 L2
WHERE  A.LEDGER='ACTUALS'
AND    A.FISCAL_YEAR=2020
AND    (A.ACCOUNTING_PERIOD BETWEEN 1 AND 6)
AND    L1.SELECTOR_NUM=30982 AND A.BUSINESS_UNIT=L1.RANGE_FROM_05
AND    L.SELECTOR_NUM=30985 AND A.CHARTFIELD1=L.RANGE_FROM_10
AND    L2.SELECTOR_NUM=30984 AND A.ACCOUNT=L2.RANGE_FROM_10
AND    A.CURRENCY_CD='GBP'
GROUP BY L.TREE_NODE_NUM,L2.TREE_NODE_NUM
/

And I get MV rewrite because the MV is up to date. Note that Oracle only probed partitions 2 to 7, so it correctly pruned partitions.

Plan hash value: 3290858815
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |  5573 |   239K|   276   (3)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY                       |                   |  5573 |   239K|   276   (3)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                          |                   |  5573 |   239K|   275   (3)| 00:00:01 |       |       |
|   3 |    JOIN FILTER CREATE                | :BF0000           |     2 |    22 |     1   (0)| 00:00:01 |       |       |
|*  4 |     INDEX RANGE SCAN                 | PS_PSTREESELECT05 |     2 |    22 |     1   (0)| 00:00:01 |       |       |
|   5 |    VIEW                              | VW_GBC_17         |  5573 |   179K|   274   (3)| 00:00:01 |       |       |
|   6 |     HASH GROUP BY                    |                   |  5573 |   364K|   274   (3)| 00:00:01 |       |       |
|   7 |      JOIN FILTER USE                 | :BF0000           |  5573 |   364K|   273   (2)| 00:00:01 |       |       |
|*  8 |       HASH JOIN                      |                   |  5573 |   364K|   273   (2)| 00:00:01 |       |       |
|*  9 |        INDEX RANGE SCAN              | PS_PSTREESELECT10 |   239 |  4541 |     2   (0)| 00:00:01 |       |       |
|* 10 |        HASH JOIN                     |                   | 23295 |  1091K|   270   (2)| 00:00:01 |       |       |
|* 11 |         INDEX RANGE SCAN             | PS_PSTREESELECT10 |    77 |  1386 |     2   (0)| 00:00:01 |       |       |
|  12 |         PARTITION RANGE ITERATOR     |                   |   301K|  8827K|   267   (2)| 00:00:01 |     2 |     7 |
|* 13 |          MAT_VIEW REWRITE ACCESS FULL| MV_LEDGER_2020    |   301K|  8827K|   267   (2)| 00:00:01 |     2 |     7 |
--------------------------------------------------------------------------------------------------------------------------

Now I will add more random data for the financial year 2020, accounting period 7. So there have been changes to just one partition.

REM pop2020m7.sql
insert into ps_ledger
with n as (
SELECT rownum n from dual connect by level <= 1e6/13
)
select 'BU'||LTRIM(TO_CHAR(CASE WHEN dbms_random.value <= .9 THEN 1 ELSE 2 END,'000')) business_unit 
,      'ACTUALS' ledger
,      'ACC'||LTRIM(TO_CHAR(999*SQRT(dbms_random.value),'000')) account 
,      'ALTACCT'||LTRIM(TO_CHAR(999*dbms_random.value,'000')) altacct
,      'DEPT'||LTRIM(TO_CHAR(9999*dbms_random.value,'0000')) deptid
,      'OPUNIT'||LTRIM(TO_CHAR(99*dbms_random.value,'00')) operating_unit
,      'P'||LTRIM(TO_CHAR(99999*dbms_random.value,'00000')) product 
,      'FUND'||LTRIM(TO_CHAR(9*dbms_random.value,'0')) fund_code
,      'CLAS'||LTRIM(TO_CHAR(9*dbms_random.value,'0')) class_fld
,      'PROD'||LTRIM(TO_CHAR(9*dbms_random.value,'0')) program_code
,      ' ' budget_ref
,      'AF'||LTRIM(TO_CHAR(999*dbms_random.value,'000')) affiliate 
,      'AFI'||LTRIM(TO_CHAR(99999*dbms_random.value,'00000')) affiliate_intra1
,      'AFI'||LTRIM(TO_CHAR( 9999*dbms_random.value,'0000')) affiliate_intra2
,      'CF'||LTRIM(TO_CHAR(  999*SQRT(dbms_random.value),'000')) chartfield1
,      'CF'||LTRIM(TO_CHAR(99999*dbms_random.value,'00000')) chartfield2
,      'CF'||LTRIM(TO_CHAR( 9999*dbms_random.value,'0000')) chartfield3
,      'PRJ'||LTRIM(TO_CHAR(9999*dbms_random.value,'0000')) project_id
,      'BK'||LTRIM(TO_CHAR(99*dbms_random.value,'00')) book_code
,      'GL'||LTRIM(TO_CHAR(99*dbms_random.value,'00')) gl_adjust_type
,      'GBP' currency_cd 
,      ' ' statistics_code 
,      2020 fiscal_year
,      7 accounting_period
,      dbms_random.value(0,1e6) posted_total_amt 
,      0 posted_base_amt 
,      0 posted_tran_amt 
,      'GBP' base_currency 
,      SYSDATE dttm_stamp_sec
,      0 process_instance 
FROM   n
/
set lines 200 pages 999 autotrace off
commit;
column owner format a10
column table_name format a15
column mview_name format a15
column detailobj_owner format a10 heading 'Detailobj|Owner'
column detailobj_name  format a15
column detailobj_alias format a20
column detail_partition_name format a20
column detail_subpartition_name format a20
column parent_table_partition format a20
select * from user_mview_detail_relations;
select * from user_mview_detail_partition;
select * from user_mview_detail_subpartition where freshness != 'FRESH';
SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME;
/

As soon as I have committed the insert, both the MVs need to be refreshed, even though none of the data queried by MV_LEDGER_2019 was changed. USER_MVIEW_DETAIL_RELATIONS reports PCT not applicable. No individual partitions are listed as stale.

MVIEW_NAME      STALENESS           LAST_REF COMPILE_STATE
--------------- ------------------- -------- -------------------
MV_LEDGER_2019  NEEDS_COMPILE       COMPLETE NEEDS_COMPILE
MV_LEDGER_2020  NEEDS_COMPILE       COMPLETE NEEDS_COMPILE

                           Detailobj
OWNER      MVIEW_NAME      Owner      DETAILOBJ_NAME  DETAILOBJ DETAILOBJ_ALIAS      D NUM_FRESH_PCT_PARTITIONS NUM_STALE_PCT_PARTITIONS
---------- --------------- ---------- --------------- --------- -------------------- - ------------------------ ------------------------
SCOTT      MV_LEDGER_2019  SCOTT      PS_LEDGER       TABLE     PS_LEDGER            N                       86                        0
SCOTT      MV_LEDGER_2020  SCOTT      PS_LEDGER       TABLE     PS_LEDGER            N                       86                        0

I no longer get Query Rewrite for either fiscal year.

SELECT L.TREE_NODE_NUM,L2.TREE_NODE_NUM,SUM(A.POSTED_TOTAL_AMT)
FROM   PS_LEDGER A
,      PSTREESELECT05 L1
,      PSTREESELECT10 L
,      PSTREESELECT10 L2
WHERE  A.LEDGER='ACTUALS'
AND    A.FISCAL_YEAR=2019
AND    A.ACCOUNTING_PERIOD BETWEEN 1 AND 6
AND    L1.SELECTOR_NUM=30982 AND A.BUSINESS_UNIT=L1.RANGE_FROM_05
AND    L.SELECTOR_NUM=30985 AND A.CHARTFIELD1=L.RANGE_FROM_10
AND    L2.SELECTOR_NUM=30984 AND A.ACCOUNT=L2.RANGE_FROM_10
AND    A.CURRENCY_CD='GBP'
GROUP BY L.TREE_NODE_NUM,L2.TREE_NODE_NUM
/ 

Plan hash value: 346876754
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |   492 | 45756 |  2036   (1)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY              |                   |   492 | 45756 |  2036   (1)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                 |                   |   492 | 45756 |  2035   (1)| 00:00:01 |       |       |
|*  3 |    INDEX RANGE SCAN         | PS_PSTREESELECT10 |   239 |  4541 |     2   (0)| 00:00:01 |       |       |
|*  4 |    HASH JOIN                |                   |  2055 |   148K|  2033   (1)| 00:00:01 |       |       |
|   5 |     MERGE JOIN CARTESIAN    |                   |   154 |  4466 |     3   (0)| 00:00:01 |       |       |
|*  6 |      INDEX RANGE SCAN       | PS_PSTREESELECT05 |     2 |    22 |     1   (0)| 00:00:01 |       |       |
|   7 |      BUFFER SORT            |                   |    77 |  1386 |     2   (0)| 00:00:01 |       |       |
|*  8 |       INDEX RANGE SCAN      | PS_PSTREESELECT10 |    77 |  1386 |     1   (0)| 00:00:01 |       |       |
|   9 |     PARTITION RANGE ITERATOR|                   | 26686 |  1172K|  2030   (1)| 00:00:01 |     3 |     8 |
|  10 |      PARTITION LIST SINGLE  |                   | 26686 |  1172K|  2030   (1)| 00:00:01 |     1 |     1 |
|* 11 |       TABLE ACCESS FULL     | PS_LEDGER         | 26686 |  1172K|  2030   (1)| 00:00:01 |   KEY |   KEY |
-----------------------------------------------------------------------------------------------------------------

Without PCT, I cannot do a partial refresh of a partitioned materialized view, and will not get query rewrite if just a single partition in the underlying table has changed, whether I need it for this query or not.

So is there a different partitioning strategy that will permit PCT to work effectively?

Demonstration 2: Simple 1-Dimensional Range Partitioning

Let's start with a simple range partitioned example; one partition per fiscal year.

CREATE TABLE ps_ledger
(business_unit VARCHAR2(5) NOT NULL
…
) PCTFREE 10 PCTUSED 80
PARTITION BY RANGE (FISCAL_YEAR) 
(PARTITION ledger_2018 VALUES LESS THAN (2019) PCTFREE 0 COMPRESS
,PARTITION ledger_2019 VALUES LESS THAN (2020) PCTFREE 0 COMPRESS
,PARTITION ledger_2020 VALUES LESS THAN (2021) PCTFREE 10 NOCOMPRESS
,PARTITION ledger_2021 VALUES LESS THAN (2022) PCTFREE 10 NOCOMPRESS)
ENABLE ROW MOVEMENT 
NOPARALLEL NOLOGGING
/
@treeselectors
@popledger

Now I am going to build a materialized view to summarise the ledger data by BUSINESS_UNIT, ACCOUNT and CHARTFIELD1, and of course by FISCAL_YEAR and ACCOUNTING_PERIOD.

CREATE MATERIALIZED VIEW mv_ledger_2020
PARTITION BY RANGE (FISCAL_YEAR)
(PARTITION ledger_2019 VALUES LESS THAN (2020) 
,PARTITION ledger_2020 VALUES LESS THAN (2021) 
) PCTFREE 0 COMPRESS NOPARALLEL
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS 
SELECT business_unit, ledger, account, chartfield1, fiscal_year, accounting_period,
sum(posted_total_amt) posted_total_amt
FROM ps_ledger
WHERE fiscal_year >= 2019
AND   ledger = 'ACTUALS'
AND   currency_cd = 'GBP'
GROUP BY business_unit, ledger, account, chartfield1, fiscal_year, accounting_period
/
@mvpop

I can see the MV has partitions for 2019 and 2020 populated, and they contain fewer rows than the original.

                                          Sub-                                             Rows
                Part                      Part                                              per
TABLE_NAME       Pos PARTITION_NAME        Pos SUBPARTITION_NAME         NUM_ROWS BLOCKS  Block COMPRESS COMPRESS_FOR
--------------- ---- -------------------- ---- ------------------------- -------- ------ ------ -------- -------------------
MV_LEDGER_2020     1 LEDGER_2019                                                                ENABLED  BASIC
                   2 LEDGER_2020                                                                ENABLED  BASIC
                                                                          1456077   4864  299.4

PS_LEDGER          1 LEDGER_2018                                          1100000  17893   61.5 ENABLED  BASIC
                   2 LEDGER_2019                                          1100000  17892   61.5 ENABLED  BASIC
                   3 LEDGER_2020                                           637915  16456   38.8 DISABLED
                   4 LEDGER_2021                                           100000   2559   39.1 DISABLED
                                                                          2937915  54800   53.6

When I query 2018 ledger data, for which there is no materialized view, the execution plan shows that Oracle full scanned only the first partition of the PS_LEDGER table that contains the 2018 data. It eliminated the other partitions.

Plan hash value: 1780139226
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                   |   822 | 76446 |  4883   (1)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY            |                   |   822 | 76446 |  4883   (1)| 00:00:01 |       |       |
|*  2 |   HASH JOIN               |                   |   822 | 76446 |  4882   (1)| 00:00:01 |       |       |
|*  3 |    INDEX RANGE SCAN       | PS_PSTREESELECT10 |   228 |  4332 |     2   (0)| 00:00:01 |       |       |
|*  4 |    HASH JOIN              |                   |  3601 |   260K|  4880   (1)| 00:00:01 |       |       |
|   5 |     MERGE JOIN CARTESIAN  |                   |   180 |  5220 |     3   (0)| 00:00:01 |       |       |
|*  6 |      INDEX RANGE SCAN     | PS_PSTREESELECT05 |     2 |    22 |     1   (0)| 00:00:01 |       |       |
|   7 |      BUFFER SORT          |                   |    90 |  1620 |     2   (0)| 00:00:01 |       |       |
|*  8 |       INDEX RANGE SCAN    | PS_PSTREESELECT10 |    90 |  1620 |     1   (0)| 00:00:01 |       |       |
|   9 |     PARTITION RANGE SINGLE|                   | 39970 |  1756K|  4877   (1)| 00:00:01 |     1 |     1 |
|* 10 |      TABLE ACCESS FULL    | PS_LEDGER         | 39970 |  1756K|  4877   (1)| 00:00:01 |     1 |     1 |
---------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."ACCOUNT"="L2"."RANGE_FROM_10")
   3 - access("L2"."SELECTOR_NUM"=30984)
   4 - access("A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05" AND "A"."CHARTFIELD1"="L"."RANGE_FROM_10")
   6 - access("L1"."SELECTOR_NUM"=30982)
   8 - access("L"."SELECTOR_NUM"=30985)
  10 - filter("A"."ACCOUNTING_PERIOD"<=6 AND "A"."LEDGER"='ACTUALS' AND "A"."FISCAL_YEAR"=2018 AND
              "A"."ACCOUNTING_PERIOD">=1 AND "A"."CURRENCY_CD"='GBP')

When I query the 2020 data, Oracle has rewritten the query to use the second partition of the materialised view. Again it only queried a single partition.

Plan hash value: 4006930814
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   |  1088 | 88128 |   674   (2)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY                   |                   |  1088 | 88128 |   674   (2)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                      |                   |  1088 | 88128 |   673   (2)| 00:00:01 |       |       |
|*  3 |    INDEX RANGE SCAN              | PS_PSTREESELECT10 |   228 |  4332 |     2   (0)| 00:00:01 |       |       |
|*  4 |    HASH JOIN                     |                   |  4767 |   288K|   671   (2)| 00:00:01 |       |       |
|   5 |     MERGE JOIN CARTESIAN         |                   |   180 |  5220 |     3   (0)| 00:00:01 |       |       |
|*  6 |      INDEX RANGE SCAN            | PS_PSTREESELECT05 |     2 |    22 |     1   (0)| 00:00:01 |       |       |
|   7 |      BUFFER SORT                 |                   |    90 |  1620 |     2   (0)| 00:00:01 |       |       |
|*  8 |       INDEX RANGE SCAN           | PS_PSTREESELECT10 |    90 |  1620 |     1   (0)| 00:00:01 |       |       |
|   9 |     PARTITION RANGE SINGLE       |                   | 52909 |  1705K|   668   (2)| 00:00:01 |     2 |     2 |
|* 10 |      MAT_VIEW REWRITE ACCESS FULL| MV_LEDGER_2020    | 52909 |  1705K|   668   (2)| 00:00:01 |     2 |     2 |
----------------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("MV_LEDGER_2020"."ACCOUNT"="L2"."RANGE_FROM_10")
   3 - access("L2"."SELECTOR_NUM"=30984)
   4 - access("MV_LEDGER_2020"."BUSINESS_UNIT"="L1"."RANGE_FROM_05" AND
              "MV_LEDGER_2020"."CHARTFIELD1"="L"."RANGE_FROM_10")
   6 - access("L1"."SELECTOR_NUM"=30982)
   8 - access("L"."SELECTOR_NUM"=30985)
  10 - filter("MV_LEDGER_2020"."ACCOUNTING_PERIOD"<=6 AND "MV_LEDGER_2020"."FISCAL_YEAR"=2020 AND
              "MV_LEDGER_2020"."ACCOUNTING_PERIOD">=1)

Now I am going to simulate running financial processing for period 7 in fiscal year 2020, by inserting data into PS_LEDGER for that period.

@pop2020m7.sql

The materialised view status and staleness on USER_MVIEWS changes to NEEDS_COMPILE when the insert into PS_LEDGER is committed.

USER_MVIEW_DETAIL_RELATIONS shows that 1 tracked partition is stale but three are still fresh.
USER_MVIEW_DETAIL_PARTITION shows the tracking status of each source partition. We can see that the LEDGER_2020 partition on PS_LEDGER is stale but the others are still fresh.

22:00:01 SQL> SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME;

MVIEW_NAME      STALENESS           LAST_REF COMPILE_STATE
--------------- ------------------- -------- -------------------
MV_LEDGER_2020  NEEDS_COMPILE       COMPLETE NEEDS_COMPILE

22:00:01 SQL> select * from user_mview_detail_relations;

                           Detailobj
OWNER      MVIEW_NAME      Owner      DETAILOBJ_NAME  DETAILOBJ DETAILOBJ_ALIAS      D NUM_FRESH_PCT_PARTITIONS NUM_STALE_PCT_PARTITIONS
---------- --------------- ---------- --------------- --------- -------------------- - ------------------------ ------------------------
SCOTT      MV_LEDGER_2020  SCOTT      PS_LEDGER       TABLE     PS_LEDGER            Y                        3                        1

22:00:01 SQL> select * from user_mview_detail_partition;

                           Detailobj
OWNER      MVIEW_NAME      Owner      DETAILOBJ_NAME  DETAIL_PARTITION_NAM DETAIL_PARTITION_POSITION FRESHNE LAST_REFRESH_TIME
---------- --------------- ---------- --------------- -------------------- ------------------------- ------- -------------------
SCOTT      MV_LEDGER_2020  SCOTT      PS_LEDGER       LEDGER_2018                                  1 FRESH   21:59:41 15/11/2020
SCOTT      MV_LEDGER_2020  SCOTT      PS_LEDGER       LEDGER_2019                                  2 FRESH   21:59:41 15/11/2020
SCOTT      MV_LEDGER_2020  SCOTT      PS_LEDGER       LEDGER_2020                                  3 STALE   21:59:41 15/11/2020
SCOTT      MV_LEDGER_2020  SCOTT      PS_LEDGER       LEDGER_2021                                  4 FRESH   21:59:41 15/11/2020

The query on 2019 still rewrites because the 2019 partition is fresh

Plan hash value: 4006930814
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   |  1088 | 88128 |   674   (2)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY                   |                   |  1088 | 88128 |   674   (2)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                      |                   |  1088 | 88128 |   673   (2)| 00:00:01 |       |       |
|*  3 |    INDEX RANGE SCAN              | PS_PSTREESELECT10 |   228 |  4332 |     2   (0)| 00:00:01 |       |       |
|*  4 |    HASH JOIN                     |                   |  4767 |   288K|   671   (2)| 00:00:01 |       |       |
|   5 |     MERGE JOIN CARTESIAN         |                   |   180 |  5220 |     3   (0)| 00:00:01 |       |       |
|*  6 |      INDEX RANGE SCAN            | PS_PSTREESELECT05 |     2 |    22 |     1   (0)| 00:00:01 |       |       |
|   7 |      BUFFER SORT                 |                   |    90 |  1620 |     2   (0)| 00:00:01 |       |       |
|*  8 |       INDEX RANGE SCAN           | PS_PSTREESELECT10 |    90 |  1620 |     1   (0)| 00:00:01 |       |       |
|   9 |     PARTITION RANGE SINGLE       |                   | 52909 |  1705K|   668   (2)| 00:00:01 |     1 |     1 |
|* 10 |      MAT_VIEW REWRITE ACCESS FULL| MV_LEDGER_2020    | 52909 |  1705K|   668   (2)| 00:00:01 |     1 |     1 |
----------------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("MV_LEDGER_2020"."ACCOUNT"="L2"."RANGE_FROM_10")
   3 - access("L2"."SELECTOR_NUM"=30984)
   4 - access("MV_LEDGER_2020"."BUSINESS_UNIT"="L1"."RANGE_FROM_05" AND
              "MV_LEDGER_2020"."CHARTFIELD1"="L"."RANGE_FROM_10")
   6 - access("L1"."SELECTOR_NUM"=30982)
   8 - access("L"."SELECTOR_NUM"=30985)
  10 - filter("MV_LEDGER_2020"."ACCOUNTING_PERIOD"<=6 AND "MV_LEDGER_2020"."FISCAL_YEAR"=2019 AND
              "MV_LEDGER_2020"."ACCOUNTING_PERIOD">=1)

But we no longer get rewrite on the 2020 partition because it is stale. The query stays on PS_LEDGER.

Plan hash value: 1780139226

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                   |   477 | 44361 |  4483   (1)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY            |                   |   477 | 44361 |  4483   (1)| 00:00:01 |       |       |
|*  2 |   HASH JOIN               |                   |   477 | 44361 |  4482   (1)| 00:00:01 |       |       |
|*  3 |    INDEX RANGE SCAN       | PS_PSTREESELECT10 |   228 |  4332 |     2   (0)| 00:00:01 |       |       |
|*  4 |    HASH JOIN              |                   |  2090 |   151K|  4479   (1)| 00:00:01 |       |       |
|   5 |     MERGE JOIN CARTESIAN  |                   |   180 |  5220 |     3   (0)| 00:00:01 |       |       |
|*  6 |      INDEX RANGE SCAN     | PS_PSTREESELECT05 |     2 |    22 |     1   (0)| 00:00:01 |       |       |
|   7 |      BUFFER SORT          |                   |    90 |  1620 |     2   (0)| 00:00:01 |       |       |
|*  8 |       INDEX RANGE SCAN    | PS_PSTREESELECT10 |    90 |  1620 |     1   (0)| 00:00:01 |       |       |
|   9 |     PARTITION RANGE SINGLE|                   | 23179 |  1018K|  4476   (1)| 00:00:01 |     3 |     3 |
|* 10 |      TABLE ACCESS FULL    | PS_LEDGER         | 23179 |  1018K|  4476   (1)| 00:00:01 |     3 |     3 |
---------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."ACCOUNT"="L2"."RANGE_FROM_10")
   3 - access("L2"."SELECTOR_NUM"=30984)
   4 - access("A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05" AND "A"."CHARTFIELD1"="L"."RANGE_FROM_10")
   6 - access("L1"."SELECTOR_NUM"=30982)
   8 - access("L"."SELECTOR_NUM"=30985)
  10 - filter("A"."ACCOUNTING_PERIOD"<=6 AND "A"."LEDGER"='ACTUALS' AND "A"."FISCAL_YEAR"=2020 AND
              "A"."ACCOUNTING_PERIOD">=1 AND "A"."CURRENCY_CD"='GBP')

So now I have to refresh the view. I am going to use

method P to indicate that it should use PCT,
atomic refresh is set to false because I want Oracle to truncate the partition and repopulate it in direct path mode so that the data is compressed (because I am not licenced for advanced compression).

I am also going to trace the refresh process so I can see what actually happened. I will give the trace file an identifying suffix to make it easier to find. I can query the trace file name from v$diag_info.

I need to collect statistics myself, or they won't be updated.

REM mvtrc.sql
disconnect
connect scott/tiger@oracle_pdb

column name format a20
column value format a70
alter session set tracefile_identifier=PCT;
select * from v$diag_info where name like '%Trace%';

alter session set sql_trace = true;
exec dbms_mview.refresh(list=>'MV_LEDGER_2019',method=>'P',atomic_refresh=>FALSE);
exec dbms_mview.refresh(list=>'MV_LEDGER_2020',method=>'P',atomic_refresh=>FALSE);
alter session set sql_trace = false;
exec dbms_stats.gather_Table_stats(user,'MV_LEDGER_2019');
exec dbms_stats.gather_Table_stats(user,'MV_LEDGER_2020');
v$diag_info indicates the trace file
   INST_ID NAME                 VALUE                                                                      CON_ID
---------- -------------------- ---------------------------------------------------------------------- ----------
         1 Diag Trace           /u01/app/oracle/diag/rdbms/oracle/oracle/trace                                  0
         1 Default Trace File   /u01/app/oracle/diag/rdbms/oracle/oracle/trace/oracle_ora_7802_PCT.trc          0

I can see the total number of rows in MV_LEDGER_2020 has gone up from 1455085 to 1528980, reflecting the rows I inserted.

                                          Sub-                                             Rows
                Part                      Part                                              per
TABLE_NAME       Pos PARTITION_NAME        Pos SUBPARTITION_NAME         NUM_ROWS BLOCKS  Block COMPRESS COMPRESS_FOR
--------------- ---- -------------------- ---- ------------------------- -------- ------ ------ -------- ------------------------------
MV_LEDGER_2020     1 LEDGER_2019                                           946825   3173  298.4 ENABLED  BASIC
                   2 LEDGER_2020                                           582155   1926  302.3 ENABLED  BASIC
                                                                          1528980   5099  299.9

PS_LEDGER          1 LEDGER_2018                                          1100000  17893   61.5 ENABLED  BASIC
                   2 LEDGER_2019                                          1100000  17892   61.5 ENABLED  BASIC
                   3 LEDGER_2020                                           637915  16456   38.8 DISABLED
                   4 LEDGER_2021                                           100000   2559   39.1 DISABLED
                                                                          2937915  54800   53.6

I am just going to pick out the statements from the trace that alter the materialized view. I can see the LEDGER_2020 partition was truncated and then the data for the stale ledger partition is reinserted in direct path mode, so it will have been compressed. Statistics confirm this as I can calculate that the number of rows per block is still around 300.

…
/* MV_REFRESH (ATB) */ ALTER TABLE "SCOTT"."MV_LEDGER_2020" TRUNCATE  PARTITION LEDGER_2020
…
/* MV_REFRESH (INS) */ INSERT /*+ APPEND BYPASS_RECURSIVE_CHECK */  INTO "SCOTT"."MV_LEDGER_2020" PARTITION ( LEDGER_2020 ) ("BUSINESS_UNIT"
,"LEDGER", "ACCOUNT", "CHARTFIELD1", "FISCAL_YEAR", "ACCOUNTING_PERIOD", "POSTED_TOTAL_AMT") SELECT  /*+ X_DYN_PRUNE */ 
"PS_LEDGER"."BUSINESS_UNIT", "PS_LEDGER"."LEDGER" , "PS_LEDGER"."ACCOUNT" , "PS_LEDGER"."CHARTFIELD1" , "PS_LEDGER"."FISCAL_YEAR" P0,
"PS_LEDGER"."ACCOUNTING_PERIOD" ,SUM("PS_LEDGER"."POSTED_TOTAL_AMT")  FROM "PS_LEDGER" "PS_LEDGER" WHERE ("PS_LEDGER"."FISCAL_YEAR">=2019 
AND "PS_LEDGER"."LEDGER"='ACTUALS' AND "PS_LEDGER"."CURRENCY_CD"='GBP') AND  ( ( (  (  (  ( "PS_LEDGER"."FISCAL_YEAR" >= 2020 )  )  )  
AND  (  (  ( "PS_LEDGER"."FISCAL_YEAR" < 2021 )  )  ) )  ) ) GROUP BY "PS_LEDGER"."BUSINESS_UNIT","PS_LEDGER"."LEDGER"
,"PS_LEDGER"."ACCOUNT","PS_LEDGER"."CHARTFIELD1","PS_LEDGER"."FISCAL_YEAR","PS_LEDGER"."ACCOUNTING_PERIOD"
…

I can use EXPLAIN_MVIEW to check the status of MV_LEDGER_2020. PCT is enabled for refresh and rewrite.

CAPABILITY_NAME                P REL_TEXT             MSGTXT
------------------------------ - -------------------- ------------------------------------------------------------
PCT                            Y
REFRESH_COMPLETE               Y
REFRESH_FAST                   Y
REWRITE                        Y
PCT_TABLE                      Y PS_LEDGER
REFRESH_FAST_AFTER_INSERT      N SCOTT.PS_LEDGER      the detail table does not have a materialized view log
REFRESH_FAST_AFTER_ONETAB_DML  N POSTED_TOTAL_AMT     SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ONETAB_DML  N                      see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ONETAB_DML  N                      COUNT(*) is not present in the select list
REFRESH_FAST_AFTER_ONETAB_DML  N                      SUM(expr) without COUNT(expr)
REFRESH_FAST_AFTER_ANY_DML     N                      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 PS_LEDGER

I can see PCT has worked.

I still get query rewrite for the partitions that are still fresh rather than stale.
The refresh process refreshes only the stale partitions.

However, I have to regenerate the materialized view for the whole fiscal year, when I have only changed one accounting period. Could I organise it to refresh just a single accounting period?

Demonstration 3: Interval Partitioning

This time I am going to use interval partitioning. I have explicitly specified the partitions for previous years because I don't want to allow any free space in the blocks, but the current and future partitions will be created automatically.

CREATE TABLE ps_ledger
(business_unit VARCHAR2(5) NOT NULL
…
) PCTFREE 10 PCTUSED 80
PARTITION BY RANGE (FISCAL_YEAR) INTERVAL (1)
(PARTITION ledger_2018 VALUES LESS THAN (2019) PCTFREE 0 COMPRESS
,PARTITION ledger_2019 VALUES LESS THAN (2020) PCTFREE 0 COMPRESS)
ENABLE ROW MOVEMENT NOLOGGING
/
@treeselectors
@popledger

I will similarly create a single materialized view with interval partitioning per fiscal year and populate it for 2019 onwards.

CREATE MATERIALIZED VIEW mv_ledger_2020
PARTITION BY RANGE (FISCAL_YEAR) INTERVAL (1)
(PARTITION ledger_2019 VALUES LESS THAN (2020)
) PCTFREE 0 COMPRESS
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT business_unit, account, chartfield1, fiscal_year, accounting_period,
sum(posted_total_amt) posted_total_amt
FROM ps_ledger
WHERE fiscal_year >= 2019
AND   ledger = 'ACTUALS'
AND   currency_cd = 'GBP'
GROUP BY business_unit, account, chartfield1, fiscal_year, accounting_period
/
@@mvpop
@@mvvol
@@mvsql

I get exactly the same behaviour as the previous demonstration. The only difference is that the new partitions have system generated names, but as before just one of them is identified as stale.

@pop2020m7.sql
23:25:42 SQL> SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME;

MVIEW_NAME      STALENESS           LAST_REF COMPILE_STATE
--------------- ------------------- -------- -------------------
MV_LEDGER_2020  NEEDS_COMPILE       COMPLETE NEEDS_COMPILE

23:25:42 SQL> select * from user_mview_detail_relations;

                           Detailobj
OWNER      MVIEW_NAME      Owner      DETAILOBJ_NAME  DETAILOBJ DETAILOBJ_ALIAS      D NUM_FRESH_PCT_PARTITIONS NUM_STALE_PCT_PARTITIONS
---------- --------------- ---------- --------------- --------- -------------------- - ------------------------ ------------------------
SCOTT      MV_LEDGER_2020  SCOTT      PS_LEDGER       TABLE     PS_LEDGER            Y                        3                        1

23:25:42 SQL> select * from user_mview_detail_partition;

                           Detailobj
OWNER      MVIEW_NAME      Owner      DETAILOBJ_NAME  DETAIL_PARTITION_NAM DETAIL_PARTITION_POSITION FRESHNE LAST_REFRESH_TIME
---------- --------------- ---------- --------------- -------------------- ------------------------- ------- -------------------
SCOTT      MV_LEDGER_2020  SCOTT      PS_LEDGER       LEDGER_2018                                  1 FRESH   23:25:21 15/11/2020
SCOTT      MV_LEDGER_2020  SCOTT      PS_LEDGER       LEDGER_2019                                  2 FRESH   23:25:21 15/11/2020
SCOTT      MV_LEDGER_2020  SCOTT      PS_LEDGER       SYS_P981                                     3 STALE   23:25:21 15/11/2020
SCOTT      MV_LEDGER_2020  SCOTT      PS_LEDGER       SYS_P982                                     4 FRESH   23:25:21 15/11/2020

However, when I look in the trace of the refresh, I see that it has truncated and repopulated the partitions for both 2020 and 2021 even though I didn't change any of the data in the 2021 partition, and it is listed as fresh.

…
/* MV_REFRESH (ATB) */ ALTER TABLE "SCOTT"."MV_LEDGER_2020" TRUNCATE  PARTITION SYS_P987
…
/* MV_REFRESH (ATB) */ ALTER TABLE "SCOTT"."MV_LEDGER_2020" TRUNCATE  PARTITION SYS_P986
…
/* MV_REFRESH (INS) */ INSERT /*+ APPEND BYPASS_RECURSIVE_CHECK */  FIRST  WHEN  (  (  (  ( "P0" >= 2020 )  )  )  AND  (  (  ( "P0" < 2021 )
)  )  )  THEN  INTO "SCOTT"."MV_LEDGER_2020" PARTITION (SYS_P986)("BUSINESS_UNIT", "ACCOUNT", "CHARTFIELD1", "FISCAL_YEAR", 
"ACCOUNTING_PERIOD", "POSTED_TOTAL_AMT")  WHEN  (  (  (  ( "P0" >= 2021 )  )  )  AND  (  (  ( "P0" < 2022 )  )  )  )  THEN  INTO 
"SCOTT"."MV_LEDGER_2020" PARTITION (SYS_P987)("BUSINESS_UNIT", "ACCOUNT", "CHARTFIELD1", "FISCAL_YEAR", "ACCOUNTING_PERIOD", 
"POSTED_TOTAL_AMT") SELECT  /*+ X_DYN_PRUNE */ "PS_LEDGER"."BUSINESS_UNIT" , "PS_LEDGER"."ACCOUNT" , "PS_LEDGER"."CHARTFIELD1" , 
"PS_LEDGER"."FISCAL_YEAR" P0, "PS_LEDGER"."ACCOUNTING_PERIOD" ,SUM("PS_LEDGER"."POSTED_TOTAL_AMT")  FROM "PS_LEDGER" "PS_LEDGER" WHERE
("PS_LEDGER"."FISCAL_YEAR">=2019 AND "PS_LEDGER"."LEDGER"='ACTUALS' AND "PS_LEDGER"."CURRENCY_CD"='GBP') AND  ( ( (  (  (  ( 
"PS_LEDGER"."FISCAL_YEAR" >= 2020 )  )  )  AND  (  (  ( "PS_LEDGER"."FISCAL_YEAR" < 2022 )  )  )  )  ) ) GROUP BY
"PS_LEDGER"."BUSINESS_UNIT","PS_LEDGER"."ACCOUNT","PS_LEDGER"."CHARTFIELD1","PS_LEDGER"."FISCAL_YEAR","PS_LEDGER"."ACCOUNTING_PERIOD"
…

In practice, in this particular case, it won't make a huge difference because there is no actuals data in 2021. The partition for 2021 has been created in the data dictionary, but due to deferred segment creation, it has not been physically created because there is no data in it. However, if I had updated data in 2019, then it would have truncated and repopulated two partitions (2019 and 2020).

Interval partitioning is a form of range partitioning, so it is expected that PCT still works. However, I have no explanation as to why the partition following the stale partition was also refreshed. This might be a bug.

Demonstration 4: Composite (Range-List) Partitioning

This time I am going to create a composite partitioned table. It will have the same range partitioning on FISCAL_YEAR, but then I will list subpartition it by ACCOUTING_PERIOD with 14 periods per fiscal year. I will use a template so that each partition will have the same subpartitions.

CREATE TABLE ps_ledger
(business_unit VARCHAR2(5) NOT NULL
…
) PCTFREE 10 PCTUSED 80
PARTITION BY RANGE (FISCAL_YEAR) 
SUBPARTITION BY LIST (ACCOUNTING_PERIOD)
SUBPARTITION TEMPLATE
(SUBPARTITION ap_bf VALUES (0) 
,SUBPARTITION ap_01 VALUES (1) 
,SUBPARTITION ap_02 VALUES (2) 
,SUBPARTITION ap_03 VALUES (3) 
,SUBPARTITION ap_04 VALUES (4) 
,SUBPARTITION ap_05 VALUES (5) 
,SUBPARTITION ap_06 VALUES (6) 
,SUBPARTITION ap_07 VALUES (7) 
,SUBPARTITION ap_08 VALUES (8) 
,SUBPARTITION ap_09 VALUES (9) 
,SUBPARTITION ap_10 VALUES (10) 
,SUBPARTITION ap_11 VALUES (11) 
,SUBPARTITION ap_12 VALUES (12) 
,SUBPARTITION ap_cf VALUES (DEFAULT))
 (PARTITION ledger_2018 VALUES LESS THAN (2019) PCTFREE 0 COMPRESS
,PARTITION ledger_2019 VALUES LESS THAN (2020) PCTFREE 0 COMPRESS
,PARTITION ledger_2020 VALUES LESS THAN (2021) 
,PARTITION ledger_2021 VALUES LESS THAN (2022) 
) ENABLE ROW MOVEMENT NOPARALLEL NOLOGGING
/
@treeselectors
@popledger

I will similarly partition the materialized view

CREATE MATERIALIZED VIEW mv_ledger_2020
PARTITION BY RANGE (FISCAL_YEAR) 
SUBPARTITION BY LIST (ACCOUNTING_PERIOD)
SUBPARTITION TEMPLATE
(SUBPARTITION ap_bf VALUES (0) 
,SUBPARTITION ap_01 VALUES (1) 
,SUBPARTITION ap_02 VALUES (2) 
,SUBPARTITION ap_03 VALUES (3) 
,SUBPARTITION ap_04 VALUES (4) 
,SUBPARTITION ap_05 VALUES (5) 
,SUBPARTITION ap_06 VALUES (6) 
,SUBPARTITION ap_07 VALUES (7) 
,SUBPARTITION ap_08 VALUES (8) 
,SUBPARTITION ap_09 VALUES (9) 
,SUBPARTITION ap_10 VALUES (10) 
,SUBPARTITION ap_11 VALUES (11) 
,SUBPARTITION ap_12 VALUES (12) 
,SUBPARTITION ap_cf VALUES (DEFAULT))
(PARTITION ledger_2019 VALUES LESS THAN (2020)
,PARTITION ledger_2020 VALUES LESS THAN (2021)
) PCTFREE 0 COMPRESS PARALLEL
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT business_unit, account, chartfield1, fiscal_year, accounting_period,
sum(posted_total_amt) posted_total_amt
FROM ps_ledger
WHERE fiscal_year >= 2019
AND   ledger = 'ACTUALS'
AND   currency_cd = 'GBP'
GROUP BY business_unit, account, chartfield1, fiscal_year, accounting_period
/
@mvpop

PCT does work properly. USER_MVIEW_DETAIL_PARTITION reports that one partition is stale USER_MVIEW_DETAIL_SUBPARTITION correctly identified that it is a stale sub-partition, but as expected, the materialized view refresh truncates the partition not the sub-partition and repopulates it. So we are still processing a whole fiscal year.

@pop2020m7.sql
17:40:03 SQL> SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME;

MVIEW_NAME      STALENESS           LAST_REF COMPILE_STATE
--------------- ------------------- -------- -------------------
MV_LEDGER_2020  NEEDS_COMPILE       COMPLETE NEEDS_COMPILE

17:40:03 SQL> select * from user_mview_detail_relations;

                           Detailobj
OWNER      MVIEW_NAME      Owner      DETAILOBJ_NAME  DETAILOBJ DETAILOBJ_ALIAS      D NUM_FRESH_PCT_PARTITIONS NUM_STALE_PCT_PARTITIONS
---------- --------------- ---------- --------------- --------- -------------------- - ------------------------ ------------------------
SCOTT      MV_LEDGER_2020  SCOTT      PS_LEDGER       TABLE     PS_LEDGER            Y                       55                        1

17:40:10 SQL> select * from user_mview_detail_partition;

no rows selected

17:40:10 SQL> select * from user_mview_detail_subpartition where freshness != 'FRESH';

                           Detailobj
OWNER      MVIEW_NAME      Owner      DETAILOBJ_NAME  DETAIL_PARTITION_NAM DETAIL_SUBPARTITION_ DETAIL_SUBPARTITION_POSITION FRESH
---------- --------------- ---------- --------------- -------------------- -------------------- ---------------------------- -----
SCOTT      MV_LEDGER_2020  SCOTT      PS_LEDGER       LEDGER_2020          LEDGER_2020_AP_07                               8 STALE

If I query periods 1 to 6 in 2020 using a BETWEEN, this is then expanded to two inequalities that I can see in the predicate section. These subpartitions are up to date, and Oracle performs query rewrite.

Plan hash value: 1400212726
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                   | 12260 |   969K|       |   664   (1)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY                    |                   | 12260 |   969K|  1128K|   664   (1)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                       |                   | 12260 |   969K|       |   428   (2)| 00:00:01 |       |       |
|*  3 |    INDEX RANGE SCAN               | PS_PSTREESELECT10 |   270 |  5130 |       |     2   (0)| 00:00:01 |       |       |
|*  4 |    HASH JOIN                      |                   | 45363 |  2746K|       |   425   (1)| 00:00:01 |       |       |
|   5 |     MERGE JOIN CARTESIAN          |                   |   182 |  5278 |       |     3   (0)| 00:00:01 |       |       |
|*  6 |      INDEX RANGE SCAN             | PS_PSTREESELECT05 |     2 |    22 |       |     1   (0)| 00:00:01 |       |       |
|   7 |      BUFFER SORT                  |                   |    91 |  1638 |       |     2   (0)| 00:00:01 |       |       |
|*  8 |       INDEX RANGE SCAN            | PS_PSTREESELECT10 |    91 |  1638 |       |     1   (0)| 00:00:01 |       |       |
|   9 |     PARTITION RANGE SINGLE        |                   |   497K|    15M|       |   421   (1)| 00:00:01 |     2 |     2 |
|  10 |      PARTITION LIST ITERATOR      |                   |   497K|    15M|       |   421   (1)| 00:00:01 |   KEY |   KEY |
|* 11 |       MAT_VIEW REWRITE ACCESS FULL| MV_LEDGER_2020    |   497K|    15M|       |   421   (1)| 00:00:01 |    15 |    28 |
-------------------------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("MV_LEDGER_2020"."ACCOUNT"="L2"."RANGE_FROM_10")
   3 - access("L2"."SELECTOR_NUM"=30984)
   4 - access("MV_LEDGER_2020"."BUSINESS_UNIT"="L1"."RANGE_FROM_05" AND
              "MV_LEDGER_2020"."CHARTFIELD1"="L"."RANGE_FROM_10")
   6 - access("L1"."SELECTOR_NUM"=30982)
   8 - access("L"."SELECTOR_NUM"=30985)
  11 - filter("MV_LEDGER_2020"."ACCOUNTING_PERIOD">=1 AND "MV_LEDGER_2020"."ACCOUNTING_PERIOD"<=6 AND
              "MV_LEDGER_2020"."FISCAL_YEAR"=2020)

But if I create period 7 in fiscal year 2020, then that subpartition is stale and Oracle leaves the query against that period as submitted to run against PS_LEDGER.

Plan hash value: 3964652976
---------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                                             | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                                      |                   |     1 |    92 |     7  (15)| 00:00:01 |       |       |
|     1 |  HASH GROUP BY                                        |                   |     1 |    92 |     7  (15)| 00:00:01 |       |       |
|- *  2 |   HASH JOIN                                           |                   |     1 |    92 |     6   (0)| 00:00:01 |       |       |
|     3 |    NESTED LOOPS                                       |                   |     1 |    92 |     6   (0)| 00:00:01 |       |       |
|-    4 |     STATISTICS COLLECTOR                              |                   |       |       |            |          |       |       |
|- *  5 |      HASH JOIN                                        |                   |     1 |    73 |     5   (0)| 00:00:01 |       |       |
|     6 |       NESTED LOOPS                                    |                   |     1 |    73 |     5   (0)| 00:00:01 |       |       |
|-    7 |        STATISTICS COLLECTOR                           |                   |       |       |            |          |       |       |
|- *  8 |         HASH JOIN                                     |                   |     1 |    55 |     4   (0)| 00:00:01 |       |       |
|     9 |          NESTED LOOPS                                 |                   |     1 |    55 |     4   (0)| 00:00:01 |       |       |
|-   10 |           STATISTICS COLLECTOR                        |                   |       |       |            |          |       |       |
|    11 |            PARTITION RANGE SINGLE                     |                   |     1 |    44 |     3   (0)| 00:00:01 |     3 |     3 |
|    12 |             PARTITION LIST SINGLE                     |                   |     1 |    44 |     3   (0)| 00:00:01 |   KEY |   KEY |
|  * 13 |              TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PS_LEDGER         |     1 |    44 |     3   (0)| 00:00:01 |    36 |    36 |
|  * 14 |               INDEX RANGE SCAN                        | PSXLEDGER         |     1 |       |     2   (0)| 00:00:01 |    36 |    36 |
|  * 15 |           INDEX RANGE SCAN                            | PS_PSTREESELECT05 |     1 |    11 |     1   (0)| 00:00:01 |       |       |
|- * 16 |          INDEX RANGE SCAN                             | PS_PSTREESELECT05 |     1 |    11 |     1   (0)| 00:00:01 |       |       |
|  * 17 |        INDEX RANGE SCAN                               | PS_PSTREESELECT10 |     1 |    18 |     1   (0)| 00:00:01 |       |       |
|- * 18 |       INDEX RANGE SCAN                                | PS_PSTREESELECT10 |     1 |    18 |     1   (0)| 00:00:01 |       |       |
|  * 19 |     INDEX RANGE SCAN                                  | PS_PSTREESELECT10 |     1 |    19 |     1   (0)| 00:00:01 |       |       |
|- * 20 |    INDEX RANGE SCAN                                   | PS_PSTREESELECT10 |     1 |    19 |     1   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------------------------------------
…

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."ACCOUNT"="L2"."RANGE_FROM_10")
   5 - access("A"."CHARTFIELD1"="L"."RANGE_FROM_10")
   8 - access("A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05")
  13 - filter("A"."CURRENCY_CD"='GBP')
  14 - access("A"."LEDGER"='ACTUALS' AND "A"."FISCAL_YEAR"=2020 AND "A"."ACCOUNTING_PERIOD"=7)
  15 - access("L1"."SELECTOR_NUM"=30982 AND "A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05")
       filter("A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05")
  16 - access("L1"."SELECTOR_NUM"=30982)
  17 - access("L"."SELECTOR_NUM"=30985 AND "A"."CHARTFIELD1"="L"."RANGE_FROM_10")
       filter("A"."CHARTFIELD1"="L"."RANGE_FROM_10")
  18 - access("L"."SELECTOR_NUM"=30985)
  19 - access("L2"."SELECTOR_NUM"=30984 AND "A"."ACCOUNT"="L2"."RANGE_FROM_10")
       filter("A"."ACCOUNT"="L2"."RANGE_FROM_10")
  20 - access("L2"."SELECTOR_NUM"=30984)

So PCT also controls query rewrite correctly on list partitioning. Again, when I look at the trace of the stale partition refresh, the entire 2020 partition was truncated and refreshed in direct-path mode. There is no accounting period criterion on the insert statement.

…
/* MV_REFRESH (ATB) */ ALTER TABLE "SCOTT"."MV_LEDGER_2020" TRUNCATE  PARTITION LEDGER_2020
…
/* MV_REFRESH (INS) */ INSERT /*+ APPEND BYPASS_RECURSIVE_CHECK */  INTO "SCOTT"."MV_LEDGER_2020" PARTITION ( LEDGER_2020 ) ("BUSINESS_UNIT",
"ACCOUNT", "CHARTFIELD1", "FISCAL_YEAR", "ACCOUNTING_PERIOD", "POSTED_TOTAL_AMT") SELECT  /*+ X_DYN_PRUNE */ "PS_LEDGER"."BUSINESS_UNIT" ,
"PS_LEDGER"."ACCOUNT" , "PS_LEDGER"."CHARTFIELD1" , "PS_LEDGER"."FISCAL_YEAR" P0, "PS_LEDGER"."ACCOUNTING_PERIOD" , 
SUM("PS_LEDGER"."POSTED_TOTAL_AMT")  FROM "PS_LEDGER" "PS_LEDGER" WHERE ("PS_LEDGER"."FISCAL_YEAR"=2020 AND "PS_LEDGER"."LEDGER"='ACTUALS' 
AND "PS_LEDGER"."CURRENCY_CD"='GBP') AND  ( ( (  (  (  ( "PS_LEDGER"."FISCAL_YEAR" < 2021 )  )  )  )  ) )GROUP BY
"PS_LEDGER"."BUSINESS_UNIT","PS_LEDGER"."ACCOUNT","PS_LEDGER"."CHARTFIELD1","PS_LEDGER"."FISCAL_YEAR","PS_LEDGER"."ACCOUNTING_PERIOD"
…

Demonstration 5: Composite (Range-Range) Partitioning

I am still composite partitioning the ledger table and materialized view in this test. It will have the same range partitioning on FISCAL_YEAR, but this time I will range subpartition it by ACCOUTING_PERIOD with 14 periods per fiscal year. I will use a template so that each partition will have the same subpartitions.

CREATE TABLE ps_ledger
(business_unit VARCHAR2(5) NOT NULL
…
) PCTFREE 10 PCTUSED 80
PARTITION BY RANGE (FISCAL_YEAR) 
SUBPARTITION BY RANGE (ACCOUNTING_PERIOD)
SUBPARTITION TEMPLATE
(SUBPARTITION ap_bf VALUES LESS THAN (1) 
,SUBPARTITION ap_01 VALUES LESS THAN (2) 
,SUBPARTITION ap_02 VALUES LESS THAN (3) 
,SUBPARTITION ap_03 VALUES LESS THAN (4) 
,SUBPARTITION ap_04 VALUES LESS THAN (5) 
,SUBPARTITION ap_05 VALUES LESS THAN (6) 
,SUBPARTITION ap_06 VALUES LESS THAN (7) 
,SUBPARTITION ap_07 VALUES LESS THAN (8) 
,SUBPARTITION ap_08 VALUES LESS THAN (9) 
,SUBPARTITION ap_09 VALUES LESS THAN (10) 
,SUBPARTITION ap_10 VALUES LESS THAN (11) 
,SUBPARTITION ap_11 VALUES LESS THAN (12) 
,SUBPARTITION ap_12 VALUES LESS THAN (13) 
,SUBPARTITION ap_cf VALUES LESS THAN (MAXVALUE))
 (PARTITION ledger_2018 VALUES LESS THAN (2019) PCTFREE 0 COMPRESS
,PARTITION ledger_2019 VALUES LESS THAN (2020) PCTFREE 0 COMPRESS
,PARTITION ledger_2020 VALUES LESS THAN (2021) 
,PARTITION ledger_2021 VALUES LESS THAN (2022) 
)
ENABLE ROW MOVEMENT NOLOGGING
/
@treeselectors
@popledger

This time I will create one materialized view with two range partitions for two fiscal years

CREATE MATERIALIZED VIEW mv_ledger_2020
PARTITION BY RANGE (FISCAL_YEAR)
SUBPARTITION BY RANGE (ACCOUNTING_PERIOD)
SUBPARTITION TEMPLATE
(SUBPARTITION ap_bf VALUES LESS THAN (1) 
,SUBPARTITION ap_01 VALUES LESS THAN (2) 
,SUBPARTITION ap_02 VALUES LESS THAN (3) 
,SUBPARTITION ap_03 VALUES LESS THAN (4) 
,SUBPARTITION ap_04 VALUES LESS THAN (5) 
,SUBPARTITION ap_05 VALUES LESS THAN (6) 
,SUBPARTITION ap_06 VALUES LESS THAN (7) 
,SUBPARTITION ap_07 VALUES LESS THAN (8) 
,SUBPARTITION ap_08 VALUES LESS THAN (9) 
,SUBPARTITION ap_09 VALUES LESS THAN (10) 
,SUBPARTITION ap_10 VALUES LESS THAN (11) 
,SUBPARTITION ap_11 VALUES LESS THAN (12) 
,SUBPARTITION ap_12 VALUES LESS THAN (13) 
,SUBPARTITION ap_cf VALUES LESS THAN (MAXVALUE))
(PARTITION ledger_2019 VALUES LESS THAN (2020)
,PARTITION ledger_2020 VALUES LESS THAN (2021)
) PCTFREE 0 COMPRESS PARALLEL
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT business_unit, account, chartfield1, fiscal_year, accounting_period,
sum(posted_total_amt) posted_total_amt
FROM ps_ledger
WHERE fiscal_year >= 2019
AND   ledger = 'ACTUALS'
AND   currency_cd = 'GBP'
GROUP BY business_unit, account, chartfield1, fiscal_year, accounting_period
/
@mvpop

After inserting and committing data for fiscal year 2020, period 7 USER_MVIEW_DETAIL_SUBPARTITION correctly identified the one stale sub-partition, and USER_MVIEW_DETAIL_PARTITION reports that one range subpartition is stale.

@pop2020m7.sql
19:09:50 SQL> SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME;

MVIEW_NAME      STALENESS           LAST_REF COMPILE_STATE
--------------- ------------------- -------- -------------------
MV_LEDGER_2020  NEEDS_COMPILE       COMPLETE NEEDS_COMPILE

19:09:50 SQL> select * from user_mview_detail_relations;

                           Detailobj
OWNER      MVIEW_NAME      Owner      DETAILOBJ_NAME  DETAILOBJ DETAILOBJ_ALIAS      D NUM_FRESH_PCT_PARTITIONS NUM_STALE_PCT_PARTITIONS
---------- --------------- ---------- --------------- --------- -------------------- - ------------------------ ------------------------
SCOTT      MV_LEDGER_2020  SCOTT      PS_LEDGER       TABLE     PS_LEDGER            Y                       55                        1

19:09:56 SQL> select * from user_mview_detail_subpartition where freshness != 'FRESH';

                           Detailobj
OWNER      MVIEW_NAME      Owner      DETAILOBJ_NAME  DETAIL_PARTITION_NAM DETAIL_SUBPARTITION_ DETAIL_SUBPARTITION_POSITION FRESH
---------- --------------- ---------- --------------- -------------------- -------------------- ---------------------------- -----
SCOTT      MV_LEDGER_2020  SCOTT      PS_LEDGER       LEDGER_2020          LEDGER_2020_AP_07                               8 STALE

Query rewrite continues to work on the fresh partitions.

Plan hash value: 589110139
-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                   | 13427 |  1062K|       |   683   (1)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY                    |                   | 13427 |  1062K|  1232K|   683   (1)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                       |                   | 13427 |  1062K|       |   427   (2)| 00:00:01 |       |       |
|*  3 |    INDEX RANGE SCAN               | PS_PSTREESELECT10 |   257 |  4883 |       |     2   (0)| 00:00:01 |       |       |
|*  4 |    HASH JOIN                      |                   | 52141 |  3156K|       |   424   (1)| 00:00:01 |       |       |
|   5 |     MERGE JOIN CARTESIAN          |                   |   210 |  6090 |       |     3   (0)| 00:00:01 |       |       |
|*  6 |      INDEX RANGE SCAN             | PS_PSTREESELECT05 |     2 |    22 |       |     1   (0)| 00:00:01 |       |       |
|   7 |      BUFFER SORT                  |                   |   105 |  1890 |       |     2   (0)| 00:00:01 |       |       |
|*  8 |       INDEX RANGE SCAN            | PS_PSTREESELECT10 |   105 |  1890 |       |     1   (0)| 00:00:01 |       |       |
|   9 |     PARTITION RANGE SINGLE        |                   |   496K|    15M|       |   420   (1)| 00:00:01 |     2 |     2 |
|  10 |      PARTITION RANGE ITERATOR     |                   |   496K|    15M|       |   420   (1)| 00:00:01 |     2 |     7 |
|* 11 |       MAT_VIEW REWRITE ACCESS FULL| MV_LEDGER_2020    |   496K|    15M|       |   420   (1)| 00:00:01 |    15 |    28 |
-------------------------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("MV_LEDGER_2020"."ACCOUNT"="L2"."RANGE_FROM_10")
   3 - access("L2"."SELECTOR_NUM"=30984)
   4 - access("MV_LEDGER_2020"."BUSINESS_UNIT"="L1"."RANGE_FROM_05" AND
              "MV_LEDGER_2020"."CHARTFIELD1"="L"."RANGE_FROM_10")
   6 - access("L1"."SELECTOR_NUM"=30982)
   8 - access("L"."SELECTOR_NUM"=30985)
  11 - filter("MV_LEDGER_2020"."ACCOUNTING_PERIOD"<=6 AND "MV_LEDGER_2020"."FISCAL_YEAR"=2020)

PCT correctly identifies stale partition in this query on period 7 only and prevents query rewrite.

Plan hash value: 1321682226
---------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                                             | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                                      |                   |     1 |    92 |     7  (15)| 00:00:01 |       |       |
|     1 |  HASH GROUP BY                                        |                   |     1 |    92 |     7  (15)| 00:00:01 |       |       |
|- *  2 |   HASH JOIN                                           |                   |     1 |    92 |     6   (0)| 00:00:01 |       |       |
|     3 |    NESTED LOOPS                                       |                   |     1 |    92 |     6   (0)| 00:00:01 |       |       |
|-    4 |     STATISTICS COLLECTOR                              |                   |       |       |            |          |       |       |
|- *  5 |      HASH JOIN                                        |                   |     1 |    73 |     5   (0)| 00:00:01 |       |       |
|     6 |       NESTED LOOPS                                    |                   |     1 |    73 |     5   (0)| 00:00:01 |       |       |
|-    7 |        STATISTICS COLLECTOR                           |                   |       |       |            |          |       |       |
|- *  8 |         HASH JOIN                                     |                   |     1 |    55 |     4   (0)| 00:00:01 |       |       |
|     9 |          NESTED LOOPS                                 |                   |     1 |    55 |     4   (0)| 00:00:01 |       |       |
|-   10 |           STATISTICS COLLECTOR                        |                   |       |       |            |          |       |       |
|    11 |            PARTITION RANGE SINGLE                     |                   |     1 |    44 |     3   (0)| 00:00:01 |     3 |     3 |
|    12 |             PARTITION RANGE SINGLE                    |                   |     1 |    44 |     3   (0)| 00:00:01 |     8 |     8 |
|  * 13 |              TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PS_LEDGER         |     1 |    44 |     3   (0)| 00:00:01 |    36 |    36 |
|  * 14 |               INDEX RANGE SCAN                        | PSXLEDGER         |     1 |       |     2   (0)| 00:00:01 |    36 |    36 |
|  * 15 |           INDEX RANGE SCAN                            | PS_PSTREESELECT05 |     1 |    11 |     1   (0)| 00:00:01 |       |       |
|- * 16 |          INDEX RANGE SCAN                             | PS_PSTREESELECT05 |     1 |    11 |     1   (0)| 00:00:01 |       |       |
|  * 17 |        INDEX RANGE SCAN                               | PS_PSTREESELECT10 |     1 |    18 |     1   (0)| 00:00:01 |       |       |
|- * 18 |       INDEX RANGE SCAN                                | PS_PSTREESELECT10 |     1 |    18 |     1   (0)| 00:00:01 |       |       |
|  * 19 |     INDEX RANGE SCAN                                  | PS_PSTREESELECT10 |     1 |    19 |     1   (0)| 00:00:01 |       |       |
|- * 20 |    INDEX RANGE SCAN                                   | PS_PSTREESELECT10 |     1 |    19 |     1   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."ACCOUNT"="L2"."RANGE_FROM_10")
   5 - access("A"."CHARTFIELD1"="L"."RANGE_FROM_10")
   8 - access("A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05")
  13 - filter("A"."CURRENCY_CD"='GBP')
  14 - access("A"."LEDGER"='ACTUALS' AND "A"."FISCAL_YEAR"=2020 AND "A"."ACCOUNTING_PERIOD"=7)
  15 - access("L1"."SELECTOR_NUM"=30982 AND "A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05")
       filter("A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05")
  16 - access("L1"."SELECTOR_NUM"=30982)
  17 - access("L"."SELECTOR_NUM"=30985 AND "A"."CHARTFIELD1"="L"."RANGE_FROM_10")
       filter("A"."CHARTFIELD1"="L"."RANGE_FROM_10")
  18 - access("L"."SELECTOR_NUM"=30985)
  19 - access("L2"."SELECTOR_NUM"=30984 AND "A"."ACCOUNT"="L2"."RANGE_FROM_10")
       filter("A"."ACCOUNT"="L2"."RANGE_FROM_10")
  20 - access("L2"."SELECTOR_NUM"=30984)

The query rewrite is prevented if a stale partition is not pruned. It is all or nothing. The query is not expanded and then rewritten to use materialised view for periods 1 to 6 and then the underlying table for period 7.

Plan hash value: 3827045647
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |   561 | 52173 |  3670   (1)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY               |                   |   561 | 52173 |  3670   (1)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                  |                   |   561 | 52173 |  3669   (1)| 00:00:01 |       |       |
|*  3 |    INDEX RANGE SCAN          | PS_PSTREESELECT10 |   227 |  4313 |     2   (0)| 00:00:01 |       |       |
|*  4 |    HASH JOIN                 |                   |  2468 |   178K|  3667   (1)| 00:00:01 |       |       |
|   5 |     MERGE JOIN CARTESIAN     |                   |   210 |  6090 |     3   (0)| 00:00:01 |       |       |
|*  6 |      INDEX RANGE SCAN        | PS_PSTREESELECT05 |     2 |    22 |     1   (0)| 00:00:01 |       |       |
|   7 |      BUFFER SORT             |                   |   105 |  1890 |     2   (0)| 00:00:01 |       |       |
|*  8 |       INDEX RANGE SCAN       | PS_PSTREESELECT10 |   105 |  1890 |     1   (0)| 00:00:01 |       |       |
|   9 |     PARTITION RANGE SINGLE   |                   | 23486 |  1032K|  3664   (1)| 00:00:01 |     3 |     3 |
|  10 |      PARTITION RANGE ITERATOR|                   | 23486 |  1032K|  3664   (1)| 00:00:01 |     2 |     8 |
|* 11 |       TABLE ACCESS FULL      | PS_LEDGER         | 23486 |  1032K|  3664   (1)| 00:00:01 |    29 |    42 |
------------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."ACCOUNT"="L2"."RANGE_FROM_10")
   3 - access("L2"."SELECTOR_NUM"=30984)
   4 - access("A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05" AND "A"."CHARTFIELD1"="L"."RANGE_FROM_10")
   6 - access("L1"."SELECTOR_NUM"=30982)
   8 - access("L"."SELECTOR_NUM"=30985)
  11 - filter("A"."ACCOUNTING_PERIOD"<=7 AND "A"."LEDGER"='ACTUALS' AND "A"."FISCAL_YEAR"=2020 AND
              "A"."CURRENCY_CD"='GBP')

Again, the materialized view refresh process truncates and repopulates the whole partition not the sub-partition. So we are still processing a whole fiscal year.

…
/* MV_REFRESH (ATB) */ ALTER TABLE "SCOTT"."MV_LEDGER_2020" TRUNCATE  PARTITION LEDGER_2020
…
/* MV_REFRESH (INS) */ INSERT /*+ APPEND BYPASS_RECURSIVE_CHECK */  INTO "SCOTT"."MV_LEDGER_2020" PARTITION ( LEDGER_2020 ) ("BUSINESS_UNIT",
"ACCOUNT", "CHARTFIELD1", "FISCAL_YEAR", "ACCOUNTING_PERIOD", "POSTED_TOTAL_AMT") SELECT  /*+ X_DYN_PRUNE */ "PS_LEDGER"."BUSINESS_UNIT" , 
"PS_LEDGER"."ACCOUNT" , "PS_LEDGER"."CHARTFIELD1" , "PS_LEDGER"."FISCAL_YEAR" P0, "PS_LEDGER"."ACCOUNTING_PERIOD" , 
SUM("PS_LEDGER"."POSTED_TOTAL_AMT")  FROM "PS_LEDGER" "PS_LEDGER" WHERE ("PS_LEDGER"."FISCAL_YEAR"=2020 AND "PS_LEDGER"."LEDGER"='ACTUALS' 
AND "PS_LEDGER"."CURRENCY_CD"='GBP') AND  ( ( (  (  (  ( "PS_LEDGER"."FISCAL_YEAR" < 2021 )  )  )  )  ) )GROUP BY
"PS_LEDGER"."BUSINESS_UNIT","PS_LEDGER"."ACCOUNT","PS_LEDGER"."CHARTFIELD1","PS_LEDGER"."FISCAL_YEAR","PS_LEDGER"."ACCOUNTING_PERIOD"
…

Demonstration 6: Mismatching Partitioning

In this example, I am still composite partitioning the ledger table and materialized view. It will have the same range partitioning on FISCAL_YEAR, I still will range subpartition it by ACCOUTING_PERIOD with 14 periods per fiscal year. I will use a template so that each partition will have the same subpartitions.

CREATE TABLE ps_ledger
(business_unit VARCHAR2(5) NOT NULL
…
) PCTFREE 10 PCTUSED 80
PARTITION BY RANGE (FISCAL_YEAR) 
SUBPARTITION BY RANGE (ACCOUNTING_PERIOD)
SUBPARTITION TEMPLATE
(SUBPARTITION ap_bf VALUES LESS THAN (1) 
,SUBPARTITION ap_01 VALUES LESS THAN (2) 
,SUBPARTITION ap_02 VALUES LESS THAN (3) 
,SUBPARTITION ap_03 VALUES LESS THAN (4) 
,SUBPARTITION ap_04 VALUES LESS THAN (5) 
,SUBPARTITION ap_05 VALUES LESS THAN (6) 
,SUBPARTITION ap_06 VALUES LESS THAN (7) 
,SUBPARTITION ap_07 VALUES LESS THAN (8) 
,SUBPARTITION ap_08 VALUES LESS THAN (9) 
,SUBPARTITION ap_09 VALUES LESS THAN (10) 
,SUBPARTITION ap_10 VALUES LESS THAN (11) 
,SUBPARTITION ap_11 VALUES LESS THAN (12) 
,SUBPARTITION ap_12 VALUES LESS THAN (13) 
,SUBPARTITION ap_cf VALUES LESS THAN (MAXVALUE))
 (PARTITION ledger_2018 VALUES LESS THAN (2019) PCTFREE 0 COMPRESS
,PARTITION ledger_2019 VALUES LESS THAN (2020) PCTFREE 0 COMPRESS
,PARTITION ledger_2020 VALUES LESS THAN (2021) 
,PARTITION ledger_2021 VALUES LESS THAN (2022)
) ENABLE ROW MOVEMENT NOLOGGING
/
@treeselectors
@popledger

I will create two materialized views, one for 2019 and one for 2020. I will only range partition the MV on accounting period because each contains only a single fiscal year.

CREATE MATERIALIZED VIEW mv_ledger_2019
PARTITION BY RANGE (ACCOUNTING_PERIOD)
(PARTITION ap_bf VALUES LESS THAN (1) 
,PARTITION ap_01 VALUES LESS THAN (2) 
,PARTITION ap_02 VALUES LESS THAN (3) 
,PARTITION ap_03 VALUES LESS THAN (4) 
,PARTITION ap_04 VALUES LESS THAN (5) 
,PARTITION ap_05 VALUES LESS THAN (6) 
,PARTITION ap_06 VALUES LESS THAN (7) 
,PARTITION ap_07 VALUES LESS THAN (8) 
,PARTITION ap_08 VALUES LESS THAN (9) 
,PARTITION ap_09 VALUES LESS THAN (10) 
,PARTITION ap_10 VALUES LESS THAN (11) 
,PARTITION ap_11 VALUES LESS THAN (12) 
,PARTITION ap_12 VALUES LESS THAN (13) 
,PARTITION ap_cf VALUES LESS THAN (MAXVALUE)
) PCTFREE 0 COMPRESS
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT business_unit, account, chartfield1, fiscal_year, accounting_period,
sum(posted_total_amt) posted_total_amt
FROM ps_ledger
WHERE fiscal_year = 2019
AND   ledger = 'ACTUALS'
AND   currency_cd = 'GBP'
GROUP BY business_unit, account, chartfield1, fiscal_year, accounting_period
/

CREATE MATERIALIZED VIEW mv_ledger_2020
PARTITION BY RANGE (ACCOUNTING_PERIOD)
(PARTITION ap_bf VALUES LESS THAN (1) 
,PARTITION ap_01 VALUES LESS THAN (2) 
,PARTITION ap_02 VALUES LESS THAN (3) 
,PARTITION ap_03 VALUES LESS THAN (4) 
,PARTITION ap_04 VALUES LESS THAN (5) 
,PARTITION ap_05 VALUES LESS THAN (6) 
,PARTITION ap_06 VALUES LESS THAN (7) 
,PARTITION ap_07 VALUES LESS THAN (8) 
,PARTITION ap_08 VALUES LESS THAN (9) 
,PARTITION ap_09 VALUES LESS THAN (10) 
,PARTITION ap_10 VALUES LESS THAN (11) 
,PARTITION ap_11 VALUES LESS THAN (12) 
,PARTITION ap_12 VALUES LESS THAN (13) 
,PARTITION ap_cf VALUES LESS THAN (MAXVALUE)
) PCTFREE 0 COMPRESS PARALLEL
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT business_unit, account, chartfield1, fiscal_year, accounting_period,
sum(posted_total_amt) posted_total_amt
FROM ps_ledger
WHERE fiscal_year = 2020
AND   ledger = 'ACTUALS'
AND   currency_cd = 'GBP'
GROUP BY business_unit, account, chartfield1, fiscal_year, accounting_period
/
@mvpop

USER_MVIEW_DETAIL_RELATIONS reports that PCT does apply to these materialized views. USER_MVIEW_DETAIL_SUBPARTITION correctly identified the one stale sub-partition into which new data was added is stale, but in both materialised views, even though we can see it is not needed by MV_LEDGER_2019.

@pop2020m7.sql
23:57:09 SQL> SELECT MVIEW_NAME, STALENESS, LAST_REFRESH_TYPE, COMPILE_STATE FROM USER_MVIEWS ORDER BY MVIEW_NAME;

MVIEW_NAME      STALENESS           LAST_REF COMPILE_STATE
--------------- ------------------- -------- -------------------
MV_LEDGER_2019  NEEDS_COMPILE       COMPLETE NEEDS_COMPILE
MV_LEDGER_2020  NEEDS_COMPILE       COMPLETE NEEDS_COMPILE

Elapsed: 00:00:00.00
23:57:09 SQL> select * from user_mview_detail_relations;

                           Detailobj
OWNER      MVIEW_NAME      Owner      DETAILOBJ_NAME  DETAILOBJ DETAILOBJ_ALIAS      D NUM_FRESH_PCT_PARTITIONS NUM_STALE_PCT_PARTITIONS
---------- --------------- ---------- --------------- --------- -------------------- - ------------------------ ------------------------
SCOTT      MV_LEDGER_2019  SCOTT      PS_LEDGER       TABLE     PS_LEDGER            Y                       55                        1
SCOTT      MV_LEDGER_2020  SCOTT      PS_LEDGER       TABLE     PS_LEDGER            Y                       55                        1

Elapsed: 00:00:13.46
23:57:23 SQL> select * from user_mview_detail_partition;

no rows selected

Elapsed: 00:00:00.00
23:57:23 SQL> select * from user_mview_detail_subpartition where freshness != 'FRESH';

                           Detailobj
OWNER      MVIEW_NAME      Owner      DETAILOBJ_NAME  DETAIL_PARTITION_NAM DETAIL_SUBPARTITION_ DETAIL_SUBPARTITION_POSITION FRESH
---------- --------------- ---------- --------------- -------------------- -------------------- ---------------------------- -----
SCOTT      MV_LEDGER_2019  SCOTT      PS_LEDGER       LEDGER_2020          LEDGER_2020_AP_07                               8 STALE
SCOTT      MV_LEDGER_2020  SCOTT      PS_LEDGER       LEDGER_2020          LEDGER_2020_AP_07                               8 STALE

Query on 2019 continues to be rewritten to use MV_LEDGER_2019 even though the MV needs compilation.

Plan hash value: 1498194812
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   |  1703 |   128K|   421   (2)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY                   |                   |  1703 |   128K|   421   (2)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                      |                   |  1703 |   128K|   420   (2)| 00:00:01 |       |       |
|*  3 |    INDEX RANGE SCAN              | PS_PSTREESELECT10 |   238 |  4522 |     2   (0)| 00:00:01 |       |       |
|*  4 |    HASH JOIN                     |                   |  7156 |   405K|   418   (2)| 00:00:01 |       |       |
|   5 |     MERGE JOIN CARTESIAN         |                   |   208 |  6032 |     3   (0)| 00:00:01 |       |       |
|*  6 |      INDEX RANGE SCAN            | PS_PSTREESELECT05 |     2 |    22 |     1   (0)| 00:00:01 |       |       |
|   7 |      BUFFER SORT                 |                   |   104 |  1872 |     2   (0)| 00:00:01 |       |       |
|*  8 |       INDEX RANGE SCAN           | PS_PSTREESELECT10 |   104 |  1872 |     1   (0)| 00:00:01 |       |       |
|   9 |     PARTITION RANGE ITERATOR     |                   | 68804 |  1948K|   415   (2)| 00:00:01 |     2 |     7 |
|* 10 |      MAT_VIEW REWRITE ACCESS FULL| MV_LEDGER_2019    | 68804 |  1948K|   415   (2)| 00:00:01 |     2 |     7 |
----------------------------------------------------------------------------------------------------------------------
….
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("MV_LEDGER_2019"."ACCOUNT"="L2"."RANGE_FROM_10")
   3 - access("L2"."SELECTOR_NUM"=30984)
   4 - access("MV_LEDGER_2019"."BUSINESS_UNIT"="L1"."RANGE_FROM_05" AND
              "MV_LEDGER_2019"."CHARTFIELD1"="L"."RANGE_FROM_10")
   6 - access("L1"."SELECTOR_NUM"=30982)
   8 - access("L"."SELECTOR_NUM"=30985)
  10 - filter("MV_LEDGER_2019"."ACCOUNTING_PERIOD"<=6)

Queries on periods 1-6 in 2020 also get rewritten

Plan hash value: 3016493666
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   | 12328 |   927K|       |   653   (2)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY                   |                   | 12328 |   927K|  1080K|   653   (2)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                      |                   | 12328 |   927K|       |   429   (2)| 00:00:01 |       |       |
|*  3 |    INDEX RANGE SCAN              | PS_PSTREESELECT10 |   238 |  4522 |       |     2   (0)| 00:00:01 |       |       |
|*  4 |    HASH JOIN                     |                   | 51748 |  2931K|       |   427   (2)| 00:00:01 |       |       |
|   5 |     MERGE JOIN CARTESIAN         |                   |   208 |  6032 |       |     3   (0)| 00:00:01 |       |       |
|*  6 |      INDEX RANGE SCAN            | PS_PSTREESELECT05 |     2 |    22 |       |     1   (0)| 00:00:01 |       |       |
|   7 |      BUFFER SORT                 |                   |   104 |  1872 |       |     2   (0)| 00:00:01 |       |       |
|*  8 |       INDEX RANGE SCAN           | PS_PSTREESELECT10 |   104 |  1872 |       |     1   (0)| 00:00:01 |       |       |
|   9 |     PARTITION RANGE ITERATOR     |                   |   496K|    13M|       |   423   (2)| 00:00:01 |     2 |     7 |
|* 10 |      MAT_VIEW REWRITE ACCESS FULL| MV_LEDGER_2020    |   496K|    13M|       |   423   (2)| 00:00:01 |     2 |     7 |
------------------------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("MV_LEDGER_2020"."ACCOUNT"="L2"."RANGE_FROM_10")
   3 - access("L2"."SELECTOR_NUM"=30984)
   4 - access("MV_LEDGER_2020"."BUSINESS_UNIT"="L1"."RANGE_FROM_05" AND
              "MV_LEDGER_2020"."CHARTFIELD1"="L"."RANGE_FROM_10")
   6 - access("L1"."SELECTOR_NUM"=30982)
   8 - access("L"."SELECTOR_NUM"=30985)
  10 - filter("MV_LEDGER_2020"."ACCOUNTING_PERIOD"<=6)

Quite correctly, the query on 2020 period 7 is not rewritten because the underlying partition is stale.

Plan hash value: 1321682226
---------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                                             | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                                      |                   |     1 |    92 |     7  (15)| 00:00:01 |       |       |
|     1 |  HASH GROUP BY                                        |                   |     1 |    92 |     7  (15)| 00:00:01 |       |       |
|- *  2 |   HASH JOIN                                           |                   |     1 |    92 |     6   (0)| 00:00:01 |       |       |
|     3 |    NESTED LOOPS                                       |                   |     1 |    92 |     6   (0)| 00:00:01 |       |       |
|-    4 |     STATISTICS COLLECTOR                              |                   |       |       |            |          |       |       |
|- *  5 |      HASH JOIN                                        |                   |     1 |    73 |     5   (0)| 00:00:01 |       |       |
|     6 |       NESTED LOOPS                                    |                   |     1 |    73 |     5   (0)| 00:00:01 |       |       |
|-    7 |        STATISTICS COLLECTOR                           |                   |       |       |            |          |       |       |
|- *  8 |         HASH JOIN                                     |                   |     1 |    55 |     4   (0)| 00:00:01 |       |       |
|     9 |          NESTED LOOPS                                 |                   |     1 |    55 |     4   (0)| 00:00:01 |       |       |
|-   10 |           STATISTICS COLLECTOR                        |                   |       |       |            |          |       |       |
|    11 |            PARTITION RANGE SINGLE                     |                   |     1 |    44 |     3   (0)| 00:00:01 |     3 |     3 |
|    12 |             PARTITION RANGE SINGLE                    |                   |     1 |    44 |     3   (0)| 00:00:01 |     8 |     8 |
|  * 13 |              TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PS_LEDGER         |     1 |    44 |     3   (0)| 00:00:01 |    36 |    36 |
|  * 14 |               INDEX RANGE SCAN                        | PSXLEDGER         |     1 |       |     2   (0)| 00:00:01 |    36 |    36 |
|  * 15 |           INDEX RANGE SCAN                            | PS_PSTREESELECT05 |     1 |    11 |     1   (0)| 00:00:01 |       |       |
|- * 16 |          INDEX RANGE SCAN                             | PS_PSTREESELECT05 |     1 |    11 |     1   (0)| 00:00:01 |       |       |
|  * 17 |        INDEX RANGE SCAN                               | PS_PSTREESELECT10 |     1 |    18 |     1   (0)| 00:00:01 |       |       |
|- * 18 |       INDEX RANGE SCAN                                | PS_PSTREESELECT10 |     1 |    18 |     1   (0)| 00:00:01 |       |       |
|  * 19 |     INDEX RANGE SCAN                                  | PS_PSTREESELECT10 |     1 |    19 |     1   (0)| 00:00:01 |       |       |
|- * 20 |    INDEX RANGE SCAN                                   | PS_PSTREESELECT10 |     1 |    19 |     1   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."ACCOUNT"="L2"."RANGE_FROM_10")
   5 - access("A"."CHARTFIELD1"="L"."RANGE_FROM_10")
   8 - access("A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05")
  13 - filter("A"."CURRENCY_CD"='GBP')
  14 - access("A"."LEDGER"='ACTUALS' AND "A"."FISCAL_YEAR"=2020 AND "A"."ACCOUNTING_PERIOD"=7)
  15 - access("L1"."SELECTOR_NUM"=30982 AND "A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05")
       filter("A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05")
  16 - access("L1"."SELECTOR_NUM"=30982)
  17 - access("L"."SELECTOR_NUM"=30985 AND "A"."CHARTFIELD1"="L"."RANGE_FROM_10")
       filter("A"."CHARTFIELD1"="L"."RANGE_FROM_10")
  18 - access("L"."SELECTOR_NUM"=30985)
  19 - access("L2"."SELECTOR_NUM"=30984 AND "A"."ACCOUNT"="L2"."RANGE_FROM_10")
       filter("A"."ACCOUNT"="L2"."RANGE_FROM_10")
  20 - access("L2"."SELECTOR_NUM"=30984)

Both MVs are compressed after the initial creation. Note the sizes of the partitions for fiscal year 2020; about 256 blocks, and 284 rows per block

                                          Sub-                                             Rows
                Part                      Part                                              per
TABLE_NAME       Pos PARTITION_NAME        Pos SUBPARTITION_NAME         NUM_ROWS BLOCKS  Block COMPRESS COMPRESS_FOR
--------------- ---- -------------------- ---- ------------------------- -------- ------ ------ -------- ----------------
MV_LEDGER_2019     1 AP_BF                                                  72886    252  289.2 ENABLED  BASIC
                   2 AP_01                                                  72925    252  289.4 ENABLED  BASIC
                   3 AP_02                                                  72736    251  289.8 ENABLED  BASIC
                   4 AP_03                                                  72745    251  289.8 ENABLED  BASIC
                   5 AP_04                                                  72649    251  289.4 ENABLED  BASIC
                   6 AP_05                                                  71947    249  288.9 ENABLED  BASIC
                   7 AP_06                                                  72903    252  289.3 ENABLED  BASIC
                   8 AP_07                                                  72510    250  290.0 ENABLED  BASIC
                   9 AP_08                                                  72520    251  288.9 ENABLED  BASIC
                  10 AP_09                                                  72965    252  289.5 ENABLED  BASIC
                  11 AP_10                                                  72209    250  288.8 ENABLED  BASIC
                  12 AP_11                                                  72647    251  289.4 ENABLED  BASIC
                  13 AP_12                                                  73121    253  289.0 ENABLED  BASIC
                  14 AP_CF                                                   1999     25   80.0 ENABLED  BASIC
                                                                           946762   3290  287.8

MV_LEDGER_2020     1 AP_BF                                                  72475    256  283.1 ENABLED  BASIC
                   2 AP_01                                                  72981    256  285.1 ENABLED  BASIC
                   3 AP_02                                                  72726    256  284.1 ENABLED  BASIC
                   4 AP_03                                                  72844    256  284.5 ENABLED  BASIC
                   5 AP_04                                                  72709    256  284.0 ENABLED  BASIC
                   6 AP_05                                                  72535    256  283.3 ENABLED  BASIC
                   7 AP_06                                                  72419    256  282.9 ENABLED  BASIC
                   8 AP_07                                                      0      0        ENABLED  BASIC
                   9 AP_08                                                      0      0        ENABLED  BASIC
                  10 AP_09                                                      0      0        ENABLED  BASIC
                  11 AP_10                                                      0      0        ENABLED  BASIC
                  12 AP_11                                                      0      0        ENABLED  BASIC
                  13 AP_12                                                      0      0        ENABLED  BASIC
                  14 AP_CF                                                      0      0        ENABLED  BASIC
                                                                           508689   1792  283.9

Let's look at the trace of the refresh processes. Both materialised views were marked as NEEDS_COMPILE, so both were refreshed. However, the trace shows that the refresh has changed from truncate to delete and the insert is not done in direct path mode. The refresh of MV_LEDGER_2019 didn't actually change any data because both refreshes tried to process 2020 because a 2020 subpartition had been changed. No data was deleted, and none was inserted.

…
/* MV_REFRESH (DEL) */ DELETE FROM "SCOTT"."MV_LEDGER_2019" WHERE  ( ( ( (2020 <= "FISCAL_YEAR" AND "FISCAL_YEAR" < 2021)  )) )
…
/* MV_REFRESH (INS) */ INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "SCOTT"."MV_LEDGER_2019" ("BUSINESS_UNIT", "ACCOUNT", "CHARTFIELD1", 
"FISCAL_YEAR", "ACCOUNTING_PERIOD", "POSTED_TOTAL_AMT") SELECT  /*+ X_DYN_PRUNE */ "PS_LEDGER"."BUSINESS_UNIT" , "PS_LEDGER"."ACCOUNT" , 
"PS_LEDGER"."CHARTFIELD1" , "PS_LEDGER"."FISCAL_YEAR" , "PS_LEDGER"."ACCOUNTING_PERIOD" , SUM("PS_LEDGER"."POSTED_TOTAL_AMT")  
FROM "PS_LEDGER" "PS_LEDGER" WHERE ("PS_LEDGER"."FISCAL_YEAR"=2019 AND "PS_LEDGER"."LEDGER"='ACTUALS' AND "PS_LEDGER"."CURRENCY_CD"='GBP') 
AND  ( ( ( (2020 <= "PS_LEDGER"."FISCAL_YEAR" AND "PS_LEDGER"."FISCAL_YEAR" < 2021) ) )  )GROUP BY 
"PS_LEDGER"."BUSINESS_UNIT","PS_LEDGER"."ACCOUNT","PS_LEDGER"."CHARTFIELD1","PS_LEDGER"."FISCAL_YEAR","PS_LEDGER"."ACCOUNTING_PERIOD"
…

…
/* MV_REFRESH (DEL) */ DELETE FROM "SCOTT"."MV_LEDGER_2020" WHERE  ( ( ( (2020 <= "FISCAL_YEAR" AND "FISCAL_YEAR" < 2021)  )) )
…
/* MV_REFRESH (INS) */ INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "SCOTT"."MV_LEDGER_2020" ("BUSINESS_UNIT", "ACCOUNT", "CHARTFIELD1", 
"FISCAL_YEAR", "ACCOUNTING_PERIOD", "POSTED_TOTAL_AMT") SELECT  /*+ X_DYN_PRUNE */ "PS_LEDGER"."BUSINESS_UNIT" , "PS_LEDGER"."ACCOUNT" , 
"PS_LEDGER"."CHARTFIELD1" , "PS_LEDGER"."FISCAL_YEAR" , "PS_LEDGER"."ACCOUNTING_PERIOD" , SUM("PS_LEDGER"."POSTED_TOTAL_AMT")  
FROM "PS_LEDGER" "PS_LEDGER" WHERE ("PS_LEDGER"."FISCAL_YEAR"=2020 AND "PS_LEDGER"."LEDGER"='ACTUALS' AND "PS_LEDGER"."CURRENCY_CD"='GBP') 
AND  ( ( ( (2020 <= "PS_LEDGER"."FISCAL_YEAR" AND "PS_LEDGER"."FISCAL_YEAR" < 2021) ) )  )GROUP BY 
"PS_LEDGER"."BUSINESS_UNIT","PS_LEDGER"."ACCOUNT","PS_LEDGER"."CHARTFIELD1","PS_LEDGER"."FISCAL_YEAR","PS_LEDGER"."ACCOUNTING_PERIOD"
…

However, the 2020 materialized view has gone from 256 blocks per period to 384 blocks, and from 285 to 189 rows per block because the data is no longer compressed because it was not inserted in direct path mode, although there was still a commit between the delete and insert statements.

                                          Sub-                                             Rows
                Part                      Part                                              per
TABLE_NAME       Pos PARTITION_NAME        Pos SUBPARTITION_NAME         NUM_ROWS BLOCKS  Block COMPRESS COMPRESS_FOR
--------------- ---- -------------------- ---- ------------------------- -------- ------ ------ -------- ------------------------------
MV_LEDGER_2019     1 AP_BF                                                  72886    252  289.2 ENABLED  BASIC
                   2 AP_01                                                  72925    252  289.4 ENABLED  BASIC
                   3 AP_02                                                  72736    251  289.8 ENABLED  BASIC
                   4 AP_03                                                  72745    251  289.8 ENABLED  BASIC
                   5 AP_04                                                  72649    251  289.4 ENABLED  BASIC
                   6 AP_05                                                  71947    249  288.9 ENABLED  BASIC
                   7 AP_06                                                  72903    252  289.3 ENABLED  BASIC
                   8 AP_07                                                  72510    250  290.0 ENABLED  BASIC
                   9 AP_08                                                  72520    251  288.9 ENABLED  BASIC
                  10 AP_09                                                  72965    252  289.5 ENABLED  BASIC
                  11 AP_10                                                  72209    250  288.8 ENABLED  BASIC
                  12 AP_11                                                  72647    251  289.4 ENABLED  BASIC
                  13 AP_12                                                  73121    253  289.0 ENABLED  BASIC
                  14 AP_CF                                                   1999     25   80.0 ENABLED  BASIC
                                                                           946762   3290  287.8

MV_LEDGER_2020     1 AP_BF                                                  72475    384  188.7 ENABLED  BASIC
                   2 AP_01                                                  72981    384  190.1 ENABLED  BASIC
                   3 AP_02                                                  72726    384  189.4 ENABLED  BASIC
                   4 AP_03                                                  72844    384  189.7 ENABLED  BASIC
                   5 AP_04                                                  72709    384  189.3 ENABLED  BASIC
                   6 AP_05                                                  72535    384  188.9 ENABLED  BASIC
                   7 AP_06                                                  72419    384  188.6 ENABLED  BASIC
                   8 AP_07                                                  72795   1006   72.4 ENABLED  BASIC
                   9 AP_08                                                      0      0        ENABLED  BASIC
                  10 AP_09                                                      0      0        ENABLED  BASIC
                  11 AP_10                                                      0      0        ENABLED  BASIC
                  12 AP_11                                                      0      0        ENABLED  BASIC
                  13 AP_12                                                      0      0        ENABLED  BASIC
                  14 AP_CF                                                      0      0        ENABLED  BASIC
                                                                           581484   3694  157.4

MV_CAPABILITIES reports PCT is available, and it is. It correctly identified stale partitions that prevent rewrite.

MVNAME                         CAPABILITY_NAME                P REL_TEXT             MSGTXT
------------------------------ ------------------------------ - -------------------- ------------------------------------------------------------
MV_LEDGER_2019                 PCT                            Y
                               REFRESH_COMPLETE               Y
                               REFRESH_FAST                   Y
                               REWRITE                        Y
                               PCT_TABLE                      Y PS_LEDGER
                               REFRESH_FAST_AFTER_INSERT      N SCOTT.PS_LEDGER      the detail table does not have a materialized view log
                               REFRESH_FAST_AFTER_ONETAB_DML  N POSTED_TOTAL_AMT     SUM(expr) without COUNT(expr)
                               REFRESH_FAST_AFTER_ONETAB_DML  N                      see the reason why REFRESH_FAST_AFTER_INSERT is disabled
                               REFRESH_FAST_AFTER_ONETAB_DML  N                      COUNT(*) is not present in the select list
                               REFRESH_FAST_AFTER_ONETAB_DML  N                      SUM(expr) without COUNT(expr)
                               REFRESH_FAST_AFTER_ANY_DML     N                      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 PS_LEDGER

MV_LEDGER_2020                 PCT                            Y
                               REFRESH_COMPLETE               Y
                               REFRESH_FAST                   Y
                               REWRITE                        Y
                               PCT_TABLE                      Y PS_LEDGER
                               REFRESH_FAST_AFTER_INSERT      N SCOTT.PS_LEDGER      the detail table does not have a materialized view log
                               REFRESH_FAST_AFTER_ONETAB_DML  N POSTED_TOTAL_AMT     SUM(expr) without COUNT(expr)
                               REFRESH_FAST_AFTER_ONETAB_DML  N                      see the reason why REFRESH_FAST_AFTER_INSERT is disabled
                               REFRESH_FAST_AFTER_ONETAB_DML  N                      COUNT(*) is not present in the select list
                               REFRESH_FAST_AFTER_ONETAB_DML  N                      SUM(expr) without COUNT(expr)
                               REFRESH_FAST_AFTER_ANY_DML     N                      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 PS_LEDGER

Mismatching partitioning caused non-atomic refresh to go back to atomic mode and so the data was no longer compressed.

Demonstration 7: Partition on Accounting Period, Subpartition on Fiscal Year!

This final example still composite partitions the ledger table, but now I will swap the partitioning and sub-partitioning. I will range partition on ACCOUNTING PERIOD into 14 partitions per fiscal year and will subpartition on FISCAL_YEAR. The intention is to demonstrate that the partition elimination will still work correctly and that I will only have to refresh a single accounting period.

However, you will see that there are some problems, and I can't work around all of them.

I will use a template so that each accounting period partition will have the same fiscal year subpartitions.

I will still only range partition the MV on accounting period. We don't need to partition it on FISCAL_YEAR since it only contains a single year.

CREATE TABLE ps_ledger
(business_unit VARCHAR2(5) NOT NULL
…
) PCTFREE 10 PCTUSED 80
PARTITION BY RANGE (ACCOUNTING_PERIOD)
SUBPARTITION BY RANGE (FISCAL_YEAR) 
SUBPARTITION TEMPLATE
(SUBPARTITION ledger_2018 VALUES LESS THAN (2019) 
,SUBPARTITION ledger_2019 VALUES LESS THAN (2020)
,SUBPARTITION ledger_2020 VALUES LESS THAN (2021) 
,SUBPARTITION ledger_2021 VALUES LESS THAN (2022))
(PARTITION ap_bf VALUES LESS THAN (1) 
,PARTITION ap_01 VALUES LESS THAN (2) 
,PARTITION ap_02 VALUES LESS THAN (3) 
,PARTITION ap_03 VALUES LESS THAN (4) 
,PARTITION ap_04 VALUES LESS THAN (5) 
,PARTITION ap_05 VALUES LESS THAN (6) 
,PARTITION ap_06 VALUES LESS THAN (7) 
,PARTITION ap_07 VALUES LESS THAN (8) 
,PARTITION ap_08 VALUES LESS THAN (9) 
,PARTITION ap_09 VALUES LESS THAN (10) 
,PARTITION ap_10 VALUES LESS THAN (11) 
,PARTITION ap_11 VALUES LESS THAN (12) 
,PARTITION ap_12 VALUES LESS THAN (13) 
,PARTITION ap_cf VALUES LESS THAN (MAXVALUE))
ENABLE ROW MOVEMENT NOLOGGING
/

I can't specify physical attributes on subpartitions, only partitions. So I have to come along afterwards and alter the sub-partitions. I am going to do that before I populate the data so it is compressed on load rather than load it and rebuild it afterwards.

set serveroutput on 
DECLARE
 l_sql CLOB;
BEGIN
 FOR i IN (
  select * 
  from user_tab_subpartitions
  where table_name = 'PS_LEDGER'
  and subpartition_name like 'AP%LEDGER%201%'
  and (compression = 'DISABLED' OR pct_free>0)
  order by table_name, partition_position, subpartition_position
 ) LOOP
  l_sql := 'ALTER TABLE '||i.table_name||' MOVE SUBPARTITION '||i.subpartition_name||' COMPRESS UPDATE INDEXES';
  dbms_output.put_line(l_sql);
  EXECUTE IMMEDIATE l_sql;
 END LOOP;
END;
/
@treeselectors
@popledger

                                          Sub-                                             Rows
                Part                      Part                                              per
TABLE_NAME       Pos PARTITION_NAME        Pos SUBPARTITION_NAME         NUM_ROWS BLOCKS  Block COMPRESS COMPRESS_FOR
--------------- ---- -------------------- ---- ------------------------- -------- ------ ------ -------- ------------
PS_LEDGER          1 AP_BF                                                 261458   5147   50.8 NONE
                   1                         1 AP_BF_LEDGER_2018            84565   1372   61.6 ENABLED  BASIC
                   1                         2 AP_BF_LEDGER_2019            84519   1371   61.6 ENABLED  BASIC
                   1                         3 AP_BF_LEDGER_2020            84673   2193   38.6 DISABLED
                   1                         4 AP_BF_LEDGER_2021             7701    211   36.5 DISABLED
                   2 AP_01                                                 261108   5174   50.5 NONE
                   2                         1 AP_01_LEDGER_2018            84268   1368   61.6 ENABLED  BASIC
                   2                         2 AP_01_LEDGER_2019            84233   1366   61.7 ENABLED  BASIC
                   2                         3 AP_01_LEDGER_2020            84831   2224   38.1 DISABLED
                   2                         4 AP_01_LEDGER_2021             7776    216   36.0 DISABLED
                   3 AP_02                                                 261174   5172   50.5 NONE
                   3                         1 AP_02_LEDGER_2018            84372   1369   61.6 ENABLED  BASIC
                   3                         2 AP_02_LEDGER_2019            84444   1370   61.6 ENABLED  BASIC
                   3                         3 AP_02_LEDGER_2020            84596   2218   38.1 DISABLED
                   3                         4 AP_02_LEDGER_2021             7762    215   36.1 DISABLED
                   4 AP_03                                                 259982   5149   50.5 NONE
                   4                         1 AP_03_LEDGER_2018            84105   1364   61.7 ENABLED  BASIC
                   4                         2 AP_03_LEDGER_2019            83820   1360   61.6 ENABLED  BASIC
                   4                         3 AP_03_LEDGER_2020            84284   2210   38.1 DISABLED
                   4                         4 AP_03_LEDGER_2021             7773    215   36.2 DISABLED
                   5 AP_04                                                 261376   5177   50.5 NONE
                   5                         1 AP_04_LEDGER_2018            84378   1369   61.6 ENABLED  BASIC
                   5                         2 AP_04_LEDGER_2019            84649   1374   61.6 ENABLED  BASIC
                   5                         3 AP_04_LEDGER_2020            84652   2220   38.1 DISABLED
                   5                         4 AP_04_LEDGER_2021             7697    214   36.0 DISABLED
                   6 AP_05                                                 261772   5180   50.5 NONE
                   6                         1 AP_05_LEDGER_2018            84984   1378   61.7 ENABLED  BASIC
                   6                         2 AP_05_LEDGER_2019            84656   1374   61.6 ENABLED  BASIC
                   6                         3 AP_05_LEDGER_2020            84507   2216   38.1 DISABLED
                   6                         4 AP_05_LEDGER_2021             7625    212   36.0 DISABLED
                   7 AP_06                                                 260581   5165   50.5 NONE
                   7                         1 AP_06_LEDGER_2018            83994   1363   61.6 ENABLED  BASIC
                   7                         2 AP_06_LEDGER_2019            84150   1366   61.6 ENABLED  BASIC
                   7                         3 AP_06_LEDGER_2020            84729   2222   38.1 DISABLED
                   7                         4 AP_06_LEDGER_2021             7708    214   36.0 DISABLED
                   8 AP_07                                                 184118   3163   58.2 NONE
                   8                         1 AP_07_LEDGER_2018            84863   1377   61.6 ENABLED  BASIC
                   8                         2 AP_07_LEDGER_2019            84155   1366   61.6 ENABLED  BASIC
                   8                         3 AP_07_LEDGER_2020             7587    211   36.0 DISABLED
                   8                         4 AP_07_LEDGER_2021             7513    209   35.9 DISABLED
                   9 AP_08                                                 184619   3173   58.2 NONE
                   9                         1 AP_08_LEDGER_2018            84547   1372   61.6 ENABLED  BASIC
                   9                         2 AP_08_LEDGER_2019            84775   1376   61.6 ENABLED  BASIC
                   9                         3 AP_08_LEDGER_2020             7662    213   36.0 DISABLED
                   9                         4 AP_08_LEDGER_2021             7635    212   36.0 DISABLED
                  10 AP_09                                                 184375   3168   58.2 NONE
                  10                         1 AP_09_LEDGER_2018            84407   1370   61.6 ENABLED  BASIC
                  10                         2 AP_09_LEDGER_2019            84645   1373   61.6 ENABLED  BASIC
                  10                         3 AP_09_LEDGER_2020             7570    210   36.0 DISABLED
                  10                         4 AP_09_LEDGER_2021             7753    215   36.1 DISABLED
                  11 AP_10                                                 184327   3166   58.2 NONE
                  11                         1 AP_10_LEDGER_2018            84300   1368   61.6 ENABLED  BASIC
                  11                         2 AP_10_LEDGER_2019            84738   1374   61.7 ENABLED  BASIC
                  11                         3 AP_10_LEDGER_2020             7656    212   36.1 DISABLED
                  11                         4 AP_10_LEDGER_2021             7633    212   36.0 DISABLED
                  12 AP_11                                                 184489   3167   58.3 NONE
                  12                         1 AP_11_LEDGER_2018            84406   1369   61.7 ENABLED  BASIC
                  12                         2 AP_11_LEDGER_2019            84861   1376   61.7 ENABLED  BASIC
                  12                         3 AP_11_LEDGER_2020             7700    213   36.2 DISABLED
                  12                         4 AP_11_LEDGER_2021             7522    209   36.0 DISABLED
                  13 AP_12                                                 184244   3168   58.2 NONE
                  13                         1 AP_12_LEDGER_2018            84611   1373   61.6 ENABLED  BASIC
                  13                         2 AP_12_LEDGER_2019            84155   1365   61.7 ENABLED  BASIC
                  13                         3 AP_12_LEDGER_2020             7776    216   36.0 DISABLED
                  13                         4 AP_12_LEDGER_2021             7702    214   36.0 DISABLED
                  14 AP_CF                                                   4800    154   31.2 NONE
                  14                         1 AP_CF_LEDGER_2018             2200     53   41.5 ENABLED  BASIC
                  14                         2 AP_CF_LEDGER_2019             2200     53   41.5 ENABLED  BASIC
                  14                         3 AP_CF_LEDGER_2020              200     24    8.3 DISABLED
                  14                         4 AP_CF_LEDGER_2021              200     24    8.3 DISABLED
                                                                          2938423  55323   53.1

If I query periods 1-6 in 2018 I get correct partition elimination. Oracle inspects 6 partitions, 1 sub-partition on each. So swapping the composite partitioning types and columns should not affect performance.

Plan hash value: 2690363151
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |   717 | 66681 |  2244   (1)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY              |                   |   717 | 66681 |  2244   (1)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                 |                   |   717 | 66681 |  2243   (1)| 00:00:01 |       |       |
|*  3 |    INDEX RANGE SCAN         | PS_PSTREESELECT10 |   258 |  4902 |     2   (0)| 00:00:01 |       |       |
|*  4 |    HASH JOIN                |                   |  2776 |   200K|  2241   (1)| 00:00:01 |       |       |
|   5 |     MERGE JOIN CARTESIAN    |                   |   208 |  6032 |     3   (0)| 00:00:01 |       |       |
|*  6 |      INDEX RANGE SCAN       | PS_PSTREESELECT05 |     2 |    22 |     1   (0)| 00:00:01 |       |       |
|   7 |      BUFFER SORT            |                   |   104 |  1872 |     2   (0)| 00:00:01 |       |       |
|*  8 |       INDEX RANGE SCAN      | PS_PSTREESELECT10 |   104 |  1872 |     1   (0)| 00:00:01 |       |       |
|   9 |     PARTITION RANGE ITERATOR|                   | 26693 |  1173K|  2238   (1)| 00:00:01 |     2 |     7 |
|  10 |      PARTITION RANGE SINGLE |                   | 26693 |  1173K|  2238   (1)| 00:00:01 |     1 |     1 |
|* 11 |       TABLE ACCESS FULL     | PS_LEDGER         | 26693 |  1173K|  2238   (1)| 00:00:01 |       |       |
-----------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"."ACCOUNT"="L2"."RANGE_FROM_10")
   3 - access("L2"."SELECTOR_NUM"=30984)
   4 - access("A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05" AND "A"."CHARTFIELD1"="L"."RANGE_FROM_10")
   6 - access("L1"."SELECTOR_NUM"=30982)
   8 - access("L"."SELECTOR_NUM"=30985)
  11 - filter("A"."ACCOUNTING_PERIOD"<=6 AND "A"."FISCAL_YEAR"=2018 AND "A"."LEDGER"='ACTUALS' AND
              "A"."CURRENCY_CD"='GBP')

CREATE MATERIALIZED VIEW mv_ledger_2019
PARTITION BY RANGE (ACCOUNTING_PERIOD)
(PARTITION ap_bf VALUES LESS THAN (1) 
,PARTITION ap_01 VALUES LESS THAN (2) 
,PARTITION ap_02 VALUES LESS THAN (3) 
,PARTITION ap_03 VALUES LESS THAN (4) 
,PARTITION ap_04 VALUES LESS THAN (5) 
,PARTITION ap_05 VALUES LESS THAN (6) 
,PARTITION ap_06 VALUES LESS THAN (7) 
,PARTITION ap_07 VALUES LESS THAN (8) 
,PARTITION ap_08 VALUES LESS THAN (9) 
,PARTITION ap_09 VALUES LESS THAN (10) 
,PARTITION ap_10 VALUES LESS THAN (11) 
,PARTITION ap_11 VALUES LESS THAN (12) 
,PARTITION ap_12 VALUES LESS THAN (13) 
,PARTITION ap_cf VALUES LESS THAN (MAXVALUE)
) PCTFREE 0 COMPRESS
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT business_unit, account, chartfield1, fiscal_year, accounting_period,
sum(posted_total_amt) posted_total_amt
FROM ps_ledger
WHERE fiscal_year = 2019
AND   ledger = 'ACTUALS'
AND   currency_cd = 'GBP'
GROUP BY business_unit, account, chartfield1, fiscal_year, accounting_period
/

CREATE MATERIALIZED VIEW mv_ledger_2020
PARTITION BY RANGE (ACCOUNTING_PERIOD)
(PARTITION ap_bf VALUES LESS THAN (1) 
,PARTITION ap_01 VALUES LESS THAN (2) 
,PARTITION ap_02 VALUES LESS THAN (3) 
,PARTITION ap_03 VALUES LESS THAN (4) 
,PARTITION ap_04 VALUES LESS THAN (5) 
,PARTITION ap_05 VALUES LESS THAN (6) 
,PARTITION ap_06 VALUES LESS THAN (7) 
,PARTITION ap_07 VALUES LESS THAN (8) 
,PARTITION ap_08 VALUES LESS THAN (9) 
,PARTITION ap_09 VALUES LESS THAN (10) 
,PARTITION ap_10 VALUES LESS THAN (11) 
,PARTITION ap_11 VALUES LESS THAN (12) 
,PARTITION ap_12 VALUES LESS THAN (13) 
,PARTITION ap_cf VALUES LESS THAN (MAXVALUE)
) PCTFREE 0 COMPRESS PARALLEL
REFRESH COMPLETE ON DEMAND
ENABLE QUERY REWRITE AS
SELECT business_unit, account, chartfield1, fiscal_year, accounting_period,
sum(posted_total_amt) posted_total_amt
FROM ps_ledger
WHERE fiscal_year = 2020
AND   ledger = 'ACTUALS'
AND   currency_cd = 'GBP'
GROUP BY business_unit, account, chartfield1, fiscal_year, accounting_period
/
@mvpop

USER_MVIEW_DETAIL_SUBPARTITION correctly identified the one stale sub-partition, but USER_MVIEW_DETAIL_PARTITION reports that one range partition is stale

@pop2020m7.sql
MVIEW_NAME      STALENESS           LAST_REF COMPILE_STATE
--------------- ------------------- -------- -------------------
MV_LEDGER_2019  NEEDS_COMPILE       COMPLETE NEEDS_COMPILE
MV_LEDGER_2020  NEEDS_COMPILE       COMPLETE NEEDS_COMPILE

01:02:53 SQL> select * from user_mview_detail_relations;

                           Detailobj
OWNER      MVIEW_NAME      Owner      DETAILOBJ_NAME  DETAILOBJ DETAILOBJ_ALIAS      D NUM_FRESH_PCT_PARTITIONS NUM_STALE_PCT_PARTITIONS
---------- --------------- ---------- --------------- --------- -------------------- - ------------------------ ------------------------
SCOTT      MV_LEDGER_2019  SCOTT      PS_LEDGER       TABLE     PS_LEDGER            Y                       55                        1
SCOTT      MV_LEDGER_2020  SCOTT      PS_LEDGER       TABLE     PS_LEDGER            Y                       55                        1

01:03:06 SQL> select * from user_mview_detail_subpartition where freshness != 'FRESH';

                           Detailobj
OWNER      MVIEW_NAME      Owner      DETAILOBJ_NAME  DETAIL_PARTITION_NAM DETAIL_SUBPARTITION_ DETAIL_SUBPARTITION_POSITION FRESH
---------- --------------- ---------- --------------- -------------------- -------------------- ---------------------------- -----
SCOTT      MV_LEDGER_2019  SCOTT      PS_LEDGER       AP_07                AP_07_LEDGER_2020                               3 STALE
SCOTT      MV_LEDGER_2020  SCOTT      PS_LEDGER       AP_07                AP_07_LEDGER_2020                               3 STALE

I get query rewrite as you would expect, and as seen in demo 5. Fiscal year 2019, period 7 still rewrites because the partition is not stale

Plan hash value: 387550712
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   |  1967 |   147K|    76   (3)| 00:00:01 |       |       |
|   1 |  HASH GROUP BY                   |                   |  1967 |   147K|    76   (3)| 00:00:01 |       |       |
|*  2 |   HASH JOIN                      |                   |  1967 |   147K|    75   (2)| 00:00:01 |       |       |
|*  3 |    INDEX RANGE SCAN              | PS_PSTREESELECT10 |   258 |  4902 |     2   (0)| 00:00:01 |       |       |
|*  4 |    HASH JOIN                     |                   |  7576 |   429K|    73   (2)| 00:00:01 |       |       |
|   5 |     MERGE JOIN CARTESIAN         |                   |   208 |  6032 |     3   (0)| 00:00:01 |       |       |
|*  6 |      INDEX RANGE SCAN            | PS_PSTREESELECT05 |     2 |    22 |     1   (0)| 00:00:01 |       |       |
|   7 |      BUFFER SORT                 |                   |   104 |  1872 |     2   (0)| 00:00:01 |       |       |
|*  8 |       INDEX RANGE SCAN           | PS_PSTREESELECT10 |   104 |  1872 |     1   (0)| 00:00:01 |       |       |
|   9 |     PARTITION RANGE SINGLE       |                   | 72486 |  2052K|    70   (2)| 00:00:01 |     8 |     8 |
|* 10 |      MAT_VIEW REWRITE ACCESS FULL| MV_LEDGER_2019    | 72486 |  2052K|    70   (2)| 00:00:01 |     8 |     8 |
----------------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("MV_LEDGER_2019"."ACCOUNT"="L2"."RANGE_FROM_10")
   3 - access("L2"."SELECTOR_NUM"=30984)
   4 - access("MV_LEDGER_2019"."BUSINESS_UNIT"="L1"."RANGE_FROM_05" AND
              "MV_LEDGER_2019"."CHARTFIELD1"="L"."RANGE_FROM_10")
   6 - access("L1"."SELECTOR_NUM"=30982)
   8 - access("L"."SELECTOR_NUM"=30985)
  10 - filter("MV_LEDGER_2019"."ACCOUNTING_PERIOD"=7)

Fiscal year 2020 period 7 doesn't rewrite, because the subpartition is stale.

Plan hash value: 1321682226
---------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                                             | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                                      |                   |     1 |    92 |     7  (15)| 00:00:01 |       |       |
|     1 |  HASH GROUP BY                                        |                   |     1 |    92 |     7  (15)| 00:00:01 |       |       |
|- *  2 |   HASH JOIN                                           |                   |     1 |    92 |     6   (0)| 00:00:01 |       |       |
|     3 |    NESTED LOOPS                                       |                   |     1 |    92 |     6   (0)| 00:00:01 |       |       |
|-    4 |     STATISTICS COLLECTOR                              |                   |       |       |            |          |       |       |
|- *  5 |      HASH JOIN                                        |                   |     1 |    73 |     5   (0)| 00:00:01 |       |       |
|     6 |       NESTED LOOPS                                    |                   |     1 |    73 |     5   (0)| 00:00:01 |       |       |
|-    7 |        STATISTICS COLLECTOR                           |                   |       |       |            |          |       |       |
|- *  8 |         HASH JOIN                                     |                   |     1 |    55 |     4   (0)| 00:00:01 |       |       |
|     9 |          NESTED LOOPS                                 |                   |     1 |    55 |     4   (0)| 00:00:01 |       |       |
|-   10 |           STATISTICS COLLECTOR                        |                   |       |       |            |          |       |       |
|    11 |            PARTITION RANGE SINGLE                     |                   |     1 |    44 |     3   (0)| 00:00:01 |     8 |     8 |
|    12 |             PARTITION RANGE SINGLE                    |                   |     1 |    44 |     3   (0)| 00:00:01 |     3 |     3 |
|  * 13 |              TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| PS_LEDGER         |     1 |    44 |     3   (0)| 00:00:01 |    31 |    31 |
|  * 14 |               INDEX RANGE SCAN                        | PSXLEDGER         |     1 |       |     2   (0)| 00:00:01 |    31 |    31 |
|  * 15 |           INDEX RANGE SCAN                            | PS_PSTREESELECT05 |     1 |    11 |     1   (0)| 00:00:01 |       |       |
|- * 16 |          INDEX RANGE SCAN                             | PS_PSTREESELECT05 |     1 |    11 |     1   (0)| 00:00:01 |       |       |
|  * 17 |        INDEX RANGE SCAN                               | PS_PSTREESELECT10 |     1 |    18 |     1   (0)| 00:00:01 |       |       |
|- * 18 |       INDEX RANGE SCAN                                | PS_PSTREESELECT10 |     1 |    18 |     1   (0)| 00:00:01 |       |       |
|  * 19 |     INDEX RANGE SCAN                                  | PS_PSTREESELECT10 |     1 |    19 |     1   (0)| 00:00:01 |       |       |
|- * 20 |    INDEX RANGE SCAN                                   | PS_PSTREESELECT10 |     1 |    19 |     1   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------------------------------------
…
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"."ACCOUNT"="L2"."RANGE_FROM_10")
   5 - access("A"."CHARTFIELD1"="L"."RANGE_FROM_10")
   8 - access("A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05")
  13 - filter("A"."CURRENCY_CD"='GBP')
  14 - access("A"."LEDGER"='ACTUALS' AND "A"."FISCAL_YEAR"=2020 AND "A"."ACCOUNTING_PERIOD"=7)
  15 - access("L1"."SELECTOR_NUM"=30982 AND "A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05")
       filter("A"."BUSINESS_UNIT"="L1"."RANGE_FROM_05")
  16 - access("L1"."SELECTOR_NUM"=30982)
  17 - access("L"."SELECTOR_NUM"=30985 AND "A"."CHARTFIELD1"="L"."RANGE_FROM_10")
       filter("A"."CHARTFIELD1"="L"."RANGE_FROM_10")
  18 - access("L"."SELECTOR_NUM"=30985)
  19 - access("L2"."SELECTOR_NUM"=30984 AND "A"."ACCOUNT"="L2"."RANGE_FROM_10")
       filter("A"."ACCOUNT"="L2"."RANGE_FROM_10")
  20 - access("L2"."SELECTOR_NUM"=30984)

As we have already seen refresh processes all subpartitions for a partition. Now, not surprisingly, the refresh process truncates the partition for period 7 in both the 2019 and 2020 MVs even though only the 2020 data was affected. So because period 7 was stale in one fiscal year, it processed all fiscal years. We would have had the same problem if I had composite partitioned the materialized view to match table, it would have truncated and reprocessed fiscal yeares for period 7.

…
/* MV_REFRESH (ATB) */ ALTER TABLE "SCOTT"."MV_LEDGER_2019" TRUNCATE  PARTITION AP_07 UPDATE GLOBAL INDEXES
…
/* MV_REFRESH (INS) */ INSERT /*+ APPEND BYPASS_RECURSIVE_CHECK */  INTO "SCOTT"."MV_LEDGER_2019" PARTITION ( AP_07 ) ("BUSINESS_UNIT", 
"ACCOUNT", "CHARTFIELD1", "FISCAL_YEAR", "ACCOUNTING_PERIOD", "POSTED_TOTAL_AMT") SELECT  /*+ X_DYN_PRUNE */ "PS_LEDGER"."BUSINESS_UNIT" , 
"PS_LEDGER"."ACCOUNT" , "PS_LEDGER"."CHARTFIELD1" , "PS_LEDGER"."FISCAL_YEAR" , "PS_LEDGER"."ACCOUNTING_PERIOD" P0, 
SUM("PS_LEDGER"."POSTED_TOTAL_AMT")  FROM "PS_LEDGER" "PS_LEDGER" WHERE ("PS_LEDGER"."FISCAL_YEAR"=2019 AND "PS_LEDGER"."LEDGER"='ACTUALS' 
AND "PS_LEDGER"."CURRENCY_CD"='GBP') AND  ( ( (  (  (  ( "PS_LEDGER"."ACCOUNTING_PERIOD" >= 7 )  )  )  
AND  (  (  ( "PS_LEDGER"."ACCOUNTING_PERIOD" < 8 )  )  )  )  ) )GROUP BY 
"PS_LEDGER"."BUSINESS_UNIT","PS_LEDGER"."ACCOUNT","PS_LEDGER"."CHARTFIELD1","PS_LEDGER"."FISCAL_YEAR","PS_LEDGER"."ACCOUNTING_PERIOD"
…
/* MV_REFRESH (ATB) */ ALTER TABLE "SCOTT"."MV_LEDGER_2020" TRUNCATE  PARTITION AP_07 UPDATE GLOBAL INDEXES
…
/* MV_REFRESH (INS) */ INSERT /*+ APPEND BYPASS_RECURSIVE_CHECK */  INTO "SCOTT"."MV_LEDGER_2020" PARTITION ( AP_07 ) ("BUSINESS_UNIT", 
"ACCOUNT", "CHARTFIELD1", "FISCAL_YEAR", "ACCOUNTING_PERIOD", "POSTED_TOTAL_AMT") SELECT  /*+ X_DYN_PRUNE */ "PS_LEDGER"."BUSINESS_UNIT" , 
"PS_LEDGER"."ACCOUNT" , "PS_LEDGER"."CHARTFIELD1" , "PS_LEDGER"."FISCAL_YEAR" , "PS_LEDGER"."ACCOUNTING_PERIOD" P0, 
SUM("PS_LEDGER"."POSTED_TOTAL_AMT")  FROM "PS_LEDGER" "PS_LEDGER" WHERE ("PS_LEDGER"."FISCAL_YEAR"=2020 AND "PS_LEDGER"."LEDGER"='ACTUALS' 
AND "PS_LEDGER"."CURRENCY_CD"='GBP') AND  ( ( (  (  (  ( "PS_LEDGER"."ACCOUNTING_PERIOD" >= 7 )  )  )  
AND  (  (  ( "PS_LEDGER"."ACCOUNTING_PERIOD" < 8 )  )  )  )  ) )GROUP BY 
"PS_LEDGER"."BUSINESS_UNIT","PS_LEDGER"."ACCOUNT","PS_LEDGER"."CHARTFIELD1","PS_LEDGER"."FISCAL_YEAR","PS_LEDGER"."ACCOUNTING_PERIOD"
…

Partition pruning still worked correctly after swapping the partitioning and sub-partitioning columns.

It also correctly controlled query rewrite.

However, the PCT refresh processed all years for the single accounting period, rather than all accounting periods for the single year. That is less work if you have fewer fiscal years than accounting periods. Generally, I see systems only contain 3 to 6 fiscal years of data. However, it is also refreshing MVs that didn't need to be refreshed

Swapping the partitioning columns has also made the management of the partitions in the ledger table much more complicated.

I can't interval sub-partition, so I can't automatically add partitions for future fiscal years on demand. Instead, I am going to have to add new fiscal year subpartitions to each of the 1 4 range partitions.
I can't specify storage options or compression attributes on sub-partitions in the create table DDL command, so I have to come along afterwards with PL/SQL to alter the sub-partitions.

On balance, I don't think I would choose to implement this.

Conclusion

PCT does track individually stale partitions and subpartitions, but the subsequent refresh is only done by partition. If one subpartition is stale, then all the subpartitions in that partition is refreshed. If you use composite partitioning then you may have to accept reprocessing more data than is absolutely necessary rather than create a partitioning strategy that is less effective.

The subpartition key should be subordinate to the partition key. In the ledger example that I have used, I think it is better to partition by fiscal year and subpartition by accounting period (demonstration 5) than vice versa (demonstration 7).

PCT doesn't work when there are multiple partitioning key columns. So you need to find a single partition key column that is used by the application that is sufficiently selective to restrict the number of partitions being refreshed.

The partitioning on the table and the materialized view must be the same type of partitioning and on the same column. Otherwise, while PCT may still work, the refresh process may not be possible to populate the materialized view in direct-path mode, and it may not be possible to maintain compressed materialized views.

There will be a balance to be struck. On the one hand application performance can be improved by partitioning application tables in a way that partition elimination is effective, but that partitioning strategy may not work with PCT. On the other, reporting performance can be improved maintaining fresh pre-aggregated data in materialized views, and PCT can help to keep the materialized fresh with less overhead.

Subscribe to: Posts ( Atom )

The /*+Go-Faster*/ Oracle & PeopleSoft Blog

Tuesday, September 02, 2025

Partition Pruning/Elimination on Reference Partitioned Tables

Single Table Query

Trace Test

Database Objects and IDs

Trace File

Locally Partitioned Index

TL;DR

Thursday, June 26, 2025

Optimising Journal Line Queries: 3. Partitioning

Interval Partitioning

Renaming Partitions

Tuesday, March 18, 2025

Locally Partitioned Unique Indexes on Reference Partitioned Tables

What is going on here?

Natural -v- Surrogate Keys

TL;DR

Tuesday, November 24, 2020

PeopleSoft Financials Ledger Partitioning Recommendations

Summary Ledgers or Materialized Views

Alternatives for Partitioning PS_LEDGER

1. Multi-column composite partitioning

2. Composite Range-Range Partitioning on FISCAL_YEAR and ACCOUNTING_PERIOD

3. Composite Range-Range Partitioning on ACCOUNTING_PERIOD and FISCAL_YEAR

Conclusion

Monday, November 23, 2020

Partition Change Tracking During Materialized View Refresh and Query Rewrite

Contents

Introduction

Documented Preconditions and Limitations

Demonstrations

Demonstration 1: Multi-column composite partitioning

Demonstration 2: Simple 1-Dimensional Range Partitioning

Demonstration 3: Interval Partitioning

Demonstration 4: Composite (Range-List) Partitioning

Demonstration 5: Composite (Range-Range) Partitioning

Demonstration 6: Mismatching Partitioning

Demonstration 7: Partition on Accounting Period, Subpartition on Fiscal Year!

Conclusion

Announcements

My Book

Oracle ACE Director

BlueSky / Twitter

Blog Archive

Subscribe

Tags

Other Oracle Blogs

Other PeopleSoft Blogs

OakTable Network

Oracle ACE Director

©David Kurtz

The /+Go-Faster/ Oracle & PeopleSoft Blog