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).

1. Problem Statement
2. Exadata System Statistics
3. Partitioning
4. Compression
5. Conclusion

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

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.

nVision Performance Tuning: 9. Using Compression without the Advanced Compression Licence

This blog post is part of a series that discusses how to get optimal performance from PeopleSoft nVision reporting as used in General Ledger.

Table compression can significantly decrease the size of tables and reduce the volume of I/O required to retrieve data.  Compression of the ledger, ledger budget, and summary ledger tables can significantly improve the performance of scans in nVision.
The Advanced Compression licence enables compression features to be used where segments are still updated by the application.  Without this licence, only simple table compression can be used, although Hybrid Column Compression (HCC) can only be used on an engineered system.  Neither forms of compression can be used in on-line line transaction processing mode.  A table that is marked for compression, but that is populated in conventional path mode will not be compressed.  Any compressed blocks will no longer be compressed after being updated.
However, in most systems, ledger rows cease to be updated within a month or two of the period closing.  The ledger tables will usually be partitioned by fiscal year and accounting period, so a month after the period has closed the corresponding partition will cease to be updated.  Therefore, it could then be compressed.
I usually compress historical partitions when I introduce partitioning.  This following example comes from an engineered system, so Hybrid Columnar Compression has been used.

• There is one range partition each for the whole of fiscal years 2014 and 2015.  This data is historical and rarely queried, and then not by a single period.  A more aggressive compression QUERY HIGH has been used.  
• Monthly partitioning is used for the previous fiscal year, 2016.  These partitions are compressed, but using QUERY LOW which should deliver better performance with lower CPU overhead than QUERY HIGH (see Choosing the Right Compression).
• Partition LEDGER_2017_05 is not partitioned because it is current (at the time of building this script) and could still be updated by the application.

CREATE TABLE sysadm.ps_ledger
(…)
TABLESPACE GLLARGE
PCTFREE 10 PCTUSED 80
PARTITION BY RANGE(FISCAL_YEAR,ACCOUNTING_PERIOD)
SUBPARTITION BY LIST (LEDGER)
(PARTITION ledger_2014 VALUES LESS THAN (2015,0) PCTFREE 0 COMPRESS FOR QUERY HIGH
(SUBPARTITION ledger_2014_actuals VALUES ('ACTUALS')
…
,SUBPARTITION ledger_2014_z_others VALUES (DEFAULT)
)
,PARTITION ledger_2015 VALUES LESS THAN (2016,0) PCTFREE 0 COMPRESS FOR QUERY HIGH
(SUBPARTITION ledger_2015_actuals VALUES ('ACTUALS')
…
,SUBPARTITION ledger_2015_z_others VALUES (DEFAULT)
)
,PARTITION ledger_2016_bf VALUES LESS THAN (2016,1) PCTFREE 0 COMPRESS FOR QUERY LOW
(SUBPARTITION ledger_2016_bf_actuals VALUES ('ACTUALS')
…
,SUBPARTITION ledger_2016_bf_z_others VALUES (DEFAULT)
)
…
,PARTITION ledger_2017_05 VALUES LESS THAN (2017,6)
(SUBPARTITION ledger_2017_05_actuals VALUES ('ACTUALS')
…
,SUBPARTITION ledger_2017_05_z_others VALUES (DEFAULT)
)
…
)
ENABLE ROW MOVEMENT
PARALLEL
NOLOGGING
/

As periods close there are more partitions that can be compressed.  However, it may not be practical to take an outage to rebuild ledger partitions each month, and it certainly isn't practical to rebuild the entire ledger table every month.  Instead, from 11g, partitions can be rebuilt in place in an online operation.  The compression attribute can only be specified on the partition, and then the sub-partitions can be rebuilt.  The data in the partition can still be read while it is being moved.

ALTER TABLE ps_ledger MODIFY PARTITON ledger_2017_05 PCTFREE COMPRESS FOR QUERY LOW;
ALTER TABLE ps_ledger MODIFY SUBPARTITION ledger_2017_05_actuals  ONLINE PARALLEL 32;
…
ALTER TABLE ps_ledger MODIFY SUBPARTITION ledger_2017_05_z_others ONLINE PARALLEL 32;

NB: Moving a partition online can fail in 12.1.0.1 or later of the database due to bug 2070300.  This is documented in MOS Doc ID 2040742.1.

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00932: inconsistent datatypes: expected NUMBER got BINARY

The bug is fixed in 12.2 and can be addressed in 12.1 by patch 2070300.

nVision Performance Tuning: 4. Partitioning of Ledger, Ledger Budget, and Summary Ledger Tables

This blog post is part of a series that discusses how to get optimal performance from PeopleSoft nVision reporting as used in General Ledger.

Note: Partitioning is a licenced option in Oracle RDBMS, and is only available on Enterprise Edition.

nVision queries always contain single value predicates on LEDGER and FISCAL_YEAR.  They will also always have either single value predicate or a range predicate on ACCOUNTING_PERIOD.  Therefore, partitioning the ledger tables on these columns is an effective way to cut down the data to be processed by the query as early as possible.

SELECT … SUM(A.POSTED_BASE_AMT) 
FROM PS_LEDGER A, …
WHERE A.LEDGER='ACTUALS' 
AND A.FISCAL_YEAR=2015 
AND A.ACCOUNTING_PERIOD BETWEEN 1 AND 11 
…

I usually partition the ledger, ledger budget and summary ledger tables on the combination of FISCAL_YEAR and ACCOUNTING_PERIOD in a single range.
Most customers have monthly accounting periods, in which case I create 14 partitions for the current and previous fiscal years, but only have a single partition for each previous fiscal years.

• One for each of the 12 accounting periods
• One for period 0 (brought forward)
• One for periods 998 and 999 (carry forward and adjustments)

I have seen one system with daily accounting periods that also had 14 partitions per year, in groups of 30 days.  This also worked very well.
I would then consider sub-partitioning on another column depending on the nature of data and the processing.  For example:

• LEDGER is often a good candidate.  In which case, I would create one list sub-partition for each of the larger ledgers and have a default list partition for the rest.
• On one a global system with various regional, but locally overnight, batch processing windows ledger was list sub-partitioned on BUSINESS_UNIT. A list partition was proposed for each region containing the top business units for that region.  This not only helped regional reporting but also minimised inter-regional contention.
• It would even be possible to vary the sub-partitioning in different fiscal years if a change occurred in the business.

This example shows part of the DDL used to create the LEDGER table.

• There is one range partition for the whole of the fiscal year 2015 because it is historical and rarely queried, and then not usually by a single period.
• Monthly partitioning is used from the previous fiscal year, 2016, onwards.
• Historical partitions are created without any reservation for free space as they are closed and won't be updated any further.  The could also be compressed.

CREATE TABLE sysadm.gfc_ledger
(…)
TABLESPACE GLLARGE
PCTFREE 10 PCTUSED 80
PARTITION BY RANGE(FISCAL_YEAR,ACCOUNTING_PERIOD)
SUBPARTITION BY LIST (LEDGER)
(PARTITION ledger_2014 VALUES LESS THAN (2015,0) PCTFREE 0 …
(SUBPARTITION ledger_2014_actuals VALUES ('ACTUALS')
…
,SUBPARTITION ledger_2014_z_others VALUES (DEFAULT)
)
,PARTITION ledger_2015_bf VALUES LESS THAN (2015,1) PCTFREE 0…
(SUBPARTITION ledger_2015_bf_actuals VALUES ('ACTUALS')
…
,SUBPARTITION ledger_2015_bf_z_others VALUES (DEFAULT)
)
,PARTITION ledger_2015_01 VALUES LESS THAN (2015,2) PCTFREE 0 …
(SUBPARTITION ledger_2015_01_actuals VALUES ('ACTUALS')
…
,SUBPARTITION ledger_2015_01_z_others VALUES (DEFAULT)
)
…
,PARTITION ledger_2015_12 VALUES LESS THAN (2015,13) PCTFREE 0 …
(SUBPARTITION ledger_2015_12_actuals VALUES ('ACTUALS')
…
,SUBPARTITION ledger_2015_12_z_others VALUES (DEFAULT)
)
,PARTITION ledger_2015_cf VALUES LESS THAN (2016,0) PCTFREE 0 …
(SUBPARTITION ledger_2015_cf_actuals VALUES ('ACTUALS')
…
,SUBPARTITION ledger_2015_cf_z_others VALUES (DEFAULT)
)
,PARTITION ledger_2016_bf VALUES LESS THAN (2016,1) PCTFREE 0 …
(SUBPARTITION ledger_2016_bf_actuals VALUES ('ACTUALS')
…
,SUBPARTITION ledger_2016_bf_z_others VALUES (DEFAULT)
)
…
)
ENABLE ROW MOVEMENT
PARALLEL
NOLOGGING
/

I usually recommend locally partitioning all indexes. 
The unique index on PS_LEDGER is perhaps an exception.  It can be locally partitioned because FISCAL_YEAR and ACCOUNTING_PERIOD are the last two of 25 columns in the index.  However, the OLTP processes that query the ledger table frequently do not use FISCAL_YEAR but the leading columns.  Therefore, every such query would have to probe every partition.

CREATE UNIQUE INDEX sysadm.ps_ledger_new ON sysadm.ps_ledger
(business_unit,ledger,account,altacct,deptid
,operating_unit,product,fund_code,class_fld,program_code
,budget_ref,affiliate,affiliate_intra1,affiliate_intra2,chartfield1
,chartfield2,chartfield3,project_id,book_code,gl_adjust_type
,date_code,currency_cd,statistics_code,fiscal_year,accounting_period
) LOCAL
(PARTITION ledger_2014 PCTFREE 0
(SUBPARTITION ledger_2014_actuals
…
,SUBPARTITION ledger_2014_z_others
)
…
)
TABLESPACE GLLEDGER_IDX
PCTFREE 5 COMPRESS 3
PARALLEL 
/
ALTER INDEX ps_ledger NOPARALLEL
/

Maintenance Tasks

The introduction of range partitioning on FISCAL_YEAR brings some regular maintenance tasks.

• New partitions must be added to the ledger and summary ledger tables for each new fiscal year before it is opened and transactions posted to it.  The LEDGER_BUDG table can be similarly partitioned and partitions should be added before budget entries are made.
• I deliberately do not create MAXVALUE partitions on ledger tables.  If the application attempted to post data to an accounting period for which new partitions had not been created it would experience an Oracle error.  Therefore it is essential to remember to add the partitions in advance. I think this is preferable to forgetting to add the partitions and having performance degrade as data accumulates in the MAXVALUE partition.
• As periods close and the application ceases to insert or update rows, their partitions can be compressed using

ALTER TABLE … PARTITION … COMPRESS UPDATE ALL INDEXES

• As and when old fiscal years are no longer needed they can be archived by simply dropping the partitions, or exchanging them out to another table.

Managing Partitioning in PeopleSoft

Application Designer is not good at managing partitioning.  I have written previously about the limited support for partitioning introduced in PeopleTools 8.54.  It uses Oracle's DBMS_METADATA package to preserve existing settings, including partitioning, but the support for initially implementing partitioning is poor.
It may be reasonable to manually manage partitioning on a single table, but if you also have a number of summary ledgers, and perhaps have also built materialized views on them, you can have a significant number of partitioned objects to manage.  Manual scripting is going to become a significant overhead, particularly as you add new partitions for new fiscal years.  You might want to look at Managing Oracle Table Partitioning in PeopleSoft Applications with GFC_PSPART package.

PeopleTools 8.54: Table/Index Partitioning

This is part of a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.

Partitioning in Oracle

Partitioning of table (and index) segments involves breaking them into several smaller segments where certain data values only occur in certain segments.  Thus if a query is looking for a certain data value it may be able to eliminate some partitions without having to scan them because by definition those values cannot occur in those partitions.  Thus saving logical and physical read, and improving performance.  This is called partition elimination or pruning.  It is often the principal reason for partitioning a table.
Physically each partition in a partitioned table is a table, but logically the partitions form a single table.  There should be no need to change application code in order to use partitioning, but the way the code is written will affect Oracle's ability to perform partition elimination.
The following diagram is taken from the Oracle 11g Database VLDB and Partitioning Guide

If a query was only looking data in March, then it could eliminate the January and February partitions without inspecting them.  However, if it was filtering data by another column then it would still have to inspect all three partitions.  Application design will determine whether, and if so how to partition a table.
NB: I can't mention partitioning without also saying that Partitioning Option is a licensed feature of Oracle Database Enterprise Edition.

Partitioning in PeopleTools prior to 8.54

I have to declare an interest.  I have been using partitioning in PeopleSoft since PeopleTools 7.5 when it was introduced in Oracle 8i.  The line from PeopleSoft was that you can introduce partitioning without invalidating your support (the same is not true of E-Business suite).  Application Designer won't generate partition DDL, so you were supposed to give your DDL scripts to the DBA who would add the partition clauses.  So you if wanted to use partitioning, you would be plunged into a hellish world of manual scripting.  One of the key benefits of Application Designer is that it generates the DDL for you.
Since 2001, I have developed a PL/SQL utility that effectively reverse engineers the functionality of Application Designer that builds DDL scripts, but then adds the partitioning clauses.  It also adds partitions, and has been extended to assist with partition-wise data archive/purge.  It is in uses at a number sites using Global Payroll (for which it was originally designed) and Financials (see Managing Oracle Table Partitioning in PeopleSoft Applications with GFC_PSPART Package)
So in investigating the new partitioning feature of PeopleTools 8.54 I was concerned:

• Is my partitioning utility was now obsolete?  Or should I continue to use it?
• How would I be able to retrofit existing partitioning into PeopleTools?

Partitioning in PeopleTools 8.54

I am going to illustrate the behaviour of the new partition support with a number of example.

Example 1: Range Partitioning PSWORKLIST

In this example, I will range partition table PSWORKLIST on the INSTSTATUS column. The valid statuses for this column are:

INSTSTATUS	Description
0	Available
1	Selected
2	Worked
3	Cancelled

• the first partition will only contain statuses 0 and 1, which are the open worklist items, 
• the other partition will contain the other statuses; 2 and 3 which are the closed items. 

The application repeatedly queries this table looking for work lists items to be processed, using the criterion INSTSTATUS < 2.  Over time, unless data is archived, the vast majority of entries are closed.  This partitioning strategy will enable the application to find the open worklist items quickly by eliminating the larger closed partition only querying the smaller open item partition.  As items are worked or cancelled, their statuses are updated to 2 or 3, and they will automatically be moved to the other partition.
This is something that I have actually done on a customer site, and it produced a considerable performance improvement.
PeopleSoft provides a component that allows you to configure the partitioning strategy for a record.  However, I immediately ran into my first problem. 

• The Partitioning Utility component will only permit me to partition by a PeopleSoft unique key column.  If a query doesn't have a predicate on the partitioning column, then Oracle will certainly not be able to prune any partitions, and the query will perform no better than if the table had not been partitioned.  While a column frequently used in selective criteria is often the subject of an index, and sometimes the unique key, this is not always the case.  It does not make sense to assume this in this utility component.
• In this case, INSTSTATUS is not part of any delivered index, though I added it to index B.  I have seen that the application frequently queries the PSWORKLIST table by INSTSTATUS, so it does make sense to partition it on that column.

However, I can customise the component to get around this.  The key field is validated by the view PPMU_RECKEYS_VW.

SELECT A.RECNAME 
 ,A.FIELDNAME 
  FROM PSRECFIELDALL A 
 WHERE %DecMult(%Round(%DECDIV(A.USEEDIT,2),0 ) , 2) <> A.USEEDIT

I can change the view as follows:

DROP TABLE PS_ST_RM2_TAO
/
SELECT A.RECNAME 
 ,A.FIELDNAME 
  FROM PSRECFIELDALL A /* WHERE %DecMult(%Round(%DECDIV(A.USEEDIT,2),0 ) , 2) <> A.USEEDIT*/ 
  , PSDBFIELD B 
 WHERE A.FIELDNAME = B.FIELDNAME 
   AND B.FIELDTYPE IN(0,2,3,4,5,6)

So, now I can specify the partitioning for this table in the Partitioning Utility Component

 I notice that can leave tablespace blank in component, but the tablespace keyword is lying around - so I have to put a tablespace in.  It is valid to omit physical attributes at partition level and they will be inherited from table level, and similarly for table level.

• The component automatically adds a MAXVALUE partition.  This means that is valid to put any value into the partition column, otherwise it can cause an error.  However, it might not be what I want.
• The component also adds a table storage clause, overriding anything specified in the record, with a fixed PCTFREE 20 which applies to all partitions.  Again this might not be what I want.  The value of PCTFREE depends on whether and how I update data in the table. 
• There are a number of things that I can't control in this component
• The name of MAXVALUE partition
• The tablespace of the MAXVALUE partition, which defaults to be the same tablespace as the last defined partition, which might not be what I want.
• Any other physical attribute of any of the partitions, for example I might want a different PCTFREE on partitions containing data will not be updated.
• The component adds clause to enable row movement.  This permits Oracle to move rows between partitions if necessary when the value of the partitioning key column is updated.  In this case it is essential because as worklist items are completed they move from the first partition to the other.  ALTER TABLE ... SHRINK requires row moment, so it is useful to enable it generally.

The partitioning definition can be viewed in Application Designer under Tools -> Data Administration -> Partitioning.


The create table script (PSBUILD.SQL) does not contain any partition DDL.  So first you build the table and then alter it partitioned.  To be fair, this limitation is set out in the PeopleTools documentation, and it is not unreasonable as you would often build the table and then decide to partition it.  I do the same in my own utility.

-- Start the Transaction 


-- Create temporary table 

CREATE TABLE PSYPSWORKLIST (BUSPROCNAME VARCHAR2(30)  DEFAULT ' ' NOT
 NULL,
…
   DESCR254_MIXED VARCHAR2(254)  DEFAULT ' ' NOT NULL) PARTITION BY
 RANGE (INSTSTATUS) 
(
 PARTITION OPEN VALUES LESS THAN (2) TABLESPACE PTTBL, 
 PARTITION PE_MAXVALUE VALUES LESS THAN (MAXVALUE) TABLESPACE PTTBL
) 
PCTFREE 20 ENABLE ROW MOVEMENT
/

-- Copy from source to temp table 

INSERT INTO PSYPSWORKLIST (
        BUSPROCNAME,
… 
    DESCR254_MIXED)
  SELECT
        BUSPROCNAME,
…
    DESCR254_MIXED
  FROM PSWORKLIST
/

-- CAUTION: Drop Original Table 

DROP TABLE PSWORKLIST
/

-- Rename Table 

RENAME PSYPSWORKLIST TO PSWORKLIST
/

-- Done 

CREATE UNIQUE  INDEX PS_PSWORKLIST ON PSWORKLIST (BUSPROCNAME,
   ACTIVITYNAME,
   EVENTNAME,
   WORKLISTNAME,
   INSTANCEID) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 40960 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PSINDEX"
/
ALTER INDEX PS_PSWORKLIST NOPARALLEL LOGGING
/
…
CREATE   INDEX PSBPSWORKLIST ON PSWORKLIST (OPRID,
   INSTSTATUS) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 40960 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PSINDEX"
/
ALTER INDEX PSBPSWORKLIST NOPARALLEL LOGGING
/
CREATE INDEX PSBWORKLIST ON PSWORKLIST
  ('')  LOCAL TABLESPACE PTTBL
/

The DDL to create partitioned index does not seem to appear properly.  The first CREATE INDEX command was generated by Application Designer extracting it from the catalogue with DBMS_METADATA.  This functionality was introduced in PeopleTools 8.51 to preserve existing configuration.The second create index comes from the partitioning definition.

• The index column list is missing, it should come from the column list is defined in Application Designer.
• The locally partitioned index is the same tablespace as the table instead of the tablespace defined on the index. 
• I would not normally keep indexes in the same tablespace as the table (the rationale is that in the case of having to recover only a tablespace with indexes then I could rebuild it instead of recovering it).

I also note that the table is not altered NOLOGGING.  Yet the indexes are still made NOPARALLEL.  The default degree of parallelism on a partitioned table is equal to the number of partitioned, so it will cause parallel query to be invoked on the table access. 

• I strongly recommend against generally allowing parallel query in all SQLs that reference a partitioned table in an OLTP system, which is what PeopleSoft is.  There are occasions where parallel query is the right thing to do, and in those cases I would use a hint, or SQL profile or SQL patch.

If I leave the Partitioning Utility component and then go back to a record where I have previously created partition DDL, then the partition DDL is still there, but all the other information that I typed in has disappeared.



If you trace the SQL generated by this component while entering partition details and generating partition DDL, then the only two tables that are updated at all;  PS_PTTBLPARTDDL and PS_PTIDXPARTDDL.  They are both keyed on RECNAME and PLATFORMID and have just one other column, a CLOB to hold the DDL.

• The partition information disappears because there is nowhere to hold it persistently, and the component cannot extract it from the DDL.  It was being entered into a derived work record.
• So it is not going to be much help when I want to adjust partitioning in a table that is already partitioned.  For example, over time, I might want to add new partitions, compress static partitions, or purge old ones.
• It is also clear that there is no intention to support different partitioning strategies for different indexes on the same table.  There are certainly cases where a table will one or more locally partitioned indexes and some global indexes that may or may not be partitioned.
• Even these two tables are not fully integrated into Application Designer.  There is a throwaway line in Appendix E of the Data Management Guide - Administering Databases on Oracle:"Record and index partitioning is not migrated as part of the IDE project. If you want to migrate the partitioning metadata along with the record, you will need to…" copy it yourself and it goes on to recommend creating a Data Migration Project in the Data Migration Workbench"

Sample 2: Import Existing Partitioning

Sticking with PSWORKLIST, I have partitioned it exactly the way I want.  The partition DDL was generated by my own partitioning utility .  I have added INSTSTATUS to index B.

CREATE TABLE sysadm.psworklist
(busprocname VARCHAR2(30) NOT NULL
…
,descr254_mixed VARCHAR2(254) NOT NULL
)
TABLESPACE PTTBL
PCTFREE 10 PCTUSED 80
PARTITION BY RANGE(INSTSTATUS)
(PARTITION psworklist_select_open VALUES LESS THAN ('2')
,PARTITION psworklist_worked_canc VALUES LESS THAN (MAXVALUE) PCTFREE 1 PCTUSED 90
)
ENABLE ROW MOVEMENT
PARALLEL
NOLOGGING
/
…
ALTER TABLE sysadm.psworklist LOGGING NOPARALLEL MONITORING
/
…
CREATE INDEX sysadm.ps0psworklist ON sysadm.psworklist
(transactionid
,busprocname
,activityname
,eventname
,worklistname
,instanceid
)
TABLESPACE PSINDEX
PCTFREE 10
PARALLEL
NOLOGGING
/
…
CREATE INDEX sysadm.psbpsworklist ON sysadm.psworklist
(oprid
,inststatus
)
LOCAL
(PARTITION psworklistbselect_open
,PARTITION psworklistbworked_canc PCTFREE 1
)
TABLESPACE PSINDEX
PCTFREE 10
PARALLEL
NOLOGGING
/
ALTER INDEX sysadm.psbpsworklist LOGGING
/
ALTER INDEX sysadm.psbpsworklist NOPARALLEL
/

The DDL in the Maintain Partitioning box in Application Designer is extracted from the data dictionary using the Oracle supplied DBMS_METADATA package.  Application Designer has done this since PeopleTools 8.51 for index build scripts, but now you can see the DDL directly in the tool.
When I generate an alter table script I still get two create index command for the partitioned index.  The second one comes from the generated partition DDL and is not correct because it still doesn't have a column list.

CREATE   INDEX PSBPSWORKLIST ON PSWORKLIST (OPRID,
   INSTSTATUS) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255  LOGGING 
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PSINDEX"  LOCAL
 (PARTITION "PSWORKLISTBSELECT_OPEN" 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PSINDEX" , 
 PARTITION "PSWORKLISTBWORKED_CANC" 
  PCTFREE 1 INITRANS 2 MAXTRANS 255 LOGGING 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PSINDEX" )
/
ALTER INDEX PSBPSWORKLIST NOPARALLEL LOGGING
/
CREATE INDEX PSBPSWORKLIST ON PSWORKLIST   ('')  LOCAL TABLESPACE
 PTTBL 
/

Example 3 - GP_RSLT_ACUM

I have now chosen to partition one of the Global Payroll result tables.  This is often the largest table in a payroll system.  I have seen more than 1 billion rows in this table at one customer.  In a Global Payroll system, I usually:

• range partition payroll tables on EMPLID to match the streamed processing (in GP streaming means concurrently running several Cobol or Application Engine programs to process different ranges of employees).  So there is a 1:1 relationship between payroll processes and physical partitions
• the largest result tables are sub-partitioned on CAL_RUN_ID so each payroll period is in a separate physical partition.  Later I can archive historical payroll data by partition.

Here, I have swapped the partitioning over.  I have partitioned by CAL_RUN_ID and sub-partitioned by EMPLID.  I explain why below.

And this is Table DDL that the utility generated.

PARTITION BY RANGE (CAL_RUN_ID) 
SUBPARTITION BY RANGE (EMPLID) 
SUBPARTITION TEMPLATE
(
 SUBPARTITION SUB1 VALUES LESS THAN ('K9999999999'), 
 SUBPARTITION SUB2 VALUES LESS THAN ('KE999999999'), 
 SUBPARTITION SUB3 VALUES LESS THAN ('KM999999999'), 
 SUBPARTITION SUB4 VALUES LESS THAN ('KT999999999') , 
 SUBPARTITION PE_MAXVALUE VALUES LESS THAN (MAXVALUE)
) 
(
 PARTITION STRM1 VALUES LESS THAN ('K9999999999') TABLESPACE GPPART1, 
 PARTITION STRM2 VALUES LESS THAN ('KE999999999') TABLESPACE GPPART2, 
 PARTITION STRM3 VALUES LESS THAN ('KM999999999') TABLESPACE GPPART3, 
 PARTITION STRM4 VALUES LESS THAN ('KT999999999') TABLESPACE GPPART4, 
 PARTITION PE_MAXVALUE VALUES LESS THAN (MAXVALUE) TABLESPACE GPPART4
) 
PCTFREE 20 ENABLE ROW MOVEMENT

• Use of the sub-partition template clause simplifies the SQL.  There is certainly a lot less of it.  However, it means you get all the sub-partitions within in all partitions.  That might not be what you want.  In this demo database both employees and calendars are prefixed by something that corresponds to legislature, so some partitions will be empty.  They won't take up any physical space, due to deferred segment creation, but it would be better not to build them at all.
• I can specify tablespace on the sub-partitions in the utility component, but there is no tablespace on the sub-partition template in the DDL.  I care more about putting different payroll periods into different tablespaces, than different ranges of employees (so I can compress and purge data later) so I swapped the partition key columns and have range partitioned on CAL_RUN_ID and sub-partitioned on EMPLID.

In Global Payroll, partitioning is required to support streamed processing.  Tables are range partitioned on EMPLID to match the stream definitions.  In UK payroll, there are 45 tables that are updated or heavily referenced by streamed payroll processes that should all have similar range partitioning on EMPLID. 
In PeopleTools 8.54, it is not possible to define a single partitioning strategy and consistently apply it to several tables.  Even if the data entered into the partition utility component was retained, I would have to enter it again for each table.

Conclusion

When I heard that PeopleTools would have native support for partitioning, if only in Oracle, I was hopeful that we would get something that would bring the process of migrating and building partitioned tables in line with normal tables. Unfortunately, I have to say that I don't think the partitioning support that I have seen so far is particularly useful.

• There is no point typing in a lot of partition data into a utility component that doesn't retain the data.
• As with materialized views, table partitioning is something on which DBAs will have to advise and will probably implement and maintain.  This component doesn't really help them do anything they already do with a text editor!
• Even the minimal partition data that the utility component does retain is not migrated between environments by Application Designer when you migrate the record.

Again, I think the problems stem from PeopleTools development trying to minimize the level of alteration to the Application Designer.  The partitioning utility component looks good because it sets out a lot of the partition attributes and metadata that you do need to consider, but there is no data structure behind that to hold it.
I would like to see PeopleTools tables to hold partitioning metadata for tables and indexes, and for Application Designer to build DDL scripts to create and alter partitioned tables, to add partitions to existing tables, and then to migrate those definitions between environments.
One positive that I can take from this is that Oracle has now clearly stated that it is reasonable to introduce partitioning into your PeopleSoft application without invalidating your support. The position hasn't actually changed, but now there is clarity.

Statistics Management for Partitioned Objects in PeopleSoft

I have implemented partitioned objects in a number of PeopleSoft systems on Oracle. Recently, I was working on a system where a table was partitioned into weekly range partitions, and I encountered a performance problem when Oracle's automatic maintenance window job to collect statistics did not run between populating the new partition for the first time, and running a batch process that referenced that partition. Oracle, understandably produced a execution plan for a statement that assumed the partition was empty, but as the partition actually had quite a lot of data, the statement ran for a long time. The solution was to tell Oracle the truth by gathering statistics for that partition. However, I didn't want to refresh the statistics for the whole table. There were many partitions with historical data that has not changed, so I don't need to refresh those partitions. I only need to refresh just the stale partitions, and here is the problem. Unfortunately, dbms_stats package will let you gather stale and missing statistics for all tables in a given schema, or the whole database, but not for a named table. It is not completely unreasonable, if you are targeting a single table then you ought to know what needs to be refreshed. I have written a PL/SQL procedure to flush the table monitoring statistics to the data dictionary and determine whether the statistics on the table, any of its partitions and sub-partitions are stale or missing, and if so gather statistics on those segments. It uses (I believe) the same criteria as dbms_stats to determine stale objects: 10% change relative to last gathered statistics, or if the segment has been truncated. I have incorporated the new refresh_stats procedure into my PL/SQL packaged procedure wrapper which can be called by the %UpdateStats PeopleCode macro via a customised DDL model. The new procedure is only called for partitioned tables. All that is necessary it to use the %UpdateStats macro in an Application Engine program. This is all still work-in-progress, but so far, the results are encouraging.