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.
#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">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.
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:
This example shows part of the DDL used to create the LEDGER table.
#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">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. Even though FISCAL_YEAR and ACCOUNTING_PERIOD are the 24th and 25th columns on the unique index on LEDGER, I would still locally partition it.
#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">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
/
The introduction of range partitioning on FISCAL_YEAR brings some regular maintenance tasks.
#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">ALTER TABLE … PARTITION … COMPRESS UPDATE ALL INDEXES
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 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.
Full name
David Kurtz
My company
https://www.enkitec.com/about/bios/david.kurtz
My blog
http://blog.psftdba.com
Recent comments
1 year 45 weeks ago
2 years 5 weeks ago
2 years 9 weeks ago
2 years 10 weeks ago
2 years 15 weeks ago
2 years 36 weeks ago
3 years 4 weeks ago
3 years 34 weeks ago
4 years 18 weeks ago
4 years 18 weeks ago