Using Indexes in the Demo System
PeopleSoft software provides the sample database with the indexes listed below. The columns in the indexes have been noted, followed by a brief explanation of why each index is included and how it affects performance. These indexes are included in PeopleSoft Application Designer and are created when indexes are created for the record.
Note:
It might be beneficial to drop these indexes before performing a large load or update. Loading data into a table means that each of the indexes on the table need to be updated; this can amount to considerable overhead when many rows are inserted. It might be beneficial to drop these indexes, load the data or perform the background process, and then run the script again to recreate the indexes on the tables.
PS_LEDGER: (All Platforms)
| PS_LEDGER | PSCLEDGER | PSFLEDGER |
|---|---|---|
|
BUSINESS_UNIT |
FISCAL_YEAR |
ACCOUNT |
|
LEDGER |
LEDGER |
ACCOUNTING_PERIOD |
|
ACCOUNT |
BUSINESS_UNIT |
PRODUCT |
|
ALTACCT |
ACCOUNT |
FISCAL_YEAR |
|
DEPTID |
||
|
OPERATING UNIT |
||
|
PRODUCT |
||
|
FUND_CODE |
||
|
CLASS_FLD |
||
|
PROGRAM_CODE |
||
|
BUDGET_REF |
||
|
AFFILIATE |
||
|
AFFILIATE_INTRA1 |
||
|
AFFILIATE_INTRA2 |
||
|
PROJECT_ID |
||
|
BOOK_CODE |
||
|
GL_ADJUST_TYPE |
||
|
CURRENCY_CD |
||
|
STATISTICS_CODE |
||
|
FISCAL_YEAR |
||
|
ACCOUNTING_PERIOD |
PS_LEDGER
This is the original, unique index from PeopleSoft Application Designer, which was left in place as the unique index. Change this index to reflect your own ChartField configuration. Because it is generated from Application Designer, making the record changes to the LEDGER record should produce the correct index for you.
Some platforms have a 16-column limit for indexes and this index already has 21 columns, and is not supported for some platforms (namely Microsoft SQL Server, DB2/Unix, and Informix). PeopleSoft software resolves this by creating 'Functional Indexes' behind the scene with index based on a field which is the concatenation of all the key fields.
While no PeopleSoft processes rely on the presence of the unique index on a table, your database manager should carefully consider any decision not to have one. A unique index is a data safeguard enforced by your database engine. It guards against duplicate data resulting from a process that does not work correctly or from invalid data in an SQL script.
Note:
If you are a DB2 customer and you want to partition the dataset based on fiscal year, you might want to delete this index and replace it with a unique index that has Fiscal Year as the leading field.
See PeopleTools Documentation: PeopleSoft Application Designer Developer's Guide, "Understanding Functional Indexes"
See PeopleTools Documentation: PeopleSoft Application Designer Developer's Guide, "Planning Records, Control Tables, and TableSets"
PSALEDGER (All Platforms Except Oracle)
Experienced General Ledger customers recognize this index. This is the most efficient index for PS/nVision reporting and also helps out the Closing and Summary ledger COBOL processes. Note that it is similar to the PSBLEDGER index except that it starts with Fiscal Year (so those processes that do have an equality for accounting period choose the "B" version over this one). The index also includes the Account field, thereby adding efficiency for any "where" clause selecting specific account values. This is an index you probably have to modify to reflect your own ChartField configuration. The last fields of this index should include the ChartFields with the highest cardinality that are usually entered and used in "where" clauses. Avoid adding all your ChartFields because that would create a great deal of overhead when any of the ChartFields are added or changed in the ledger. It is usually best to include the minimum number of fields to do the job in an index.
Note:
If you are doing PS/nVision reporting on your summary ledger, be sure to create this index for the summary ledger record. See the notes under the PSBLEDGER index for additional thoughts on this topic.
PS_LEDGER (All Platforms Except Oracle)
| PSALEDGER | PSBLEDGER |
|---|---|
|
ACCOUNT |
ACCOUNTING_PERIOD |
|
FISCAL_YEAR |
FISCAL_YEAR |
|
BUSINESS_UNIT |
LEDGER |
|
LEDGER |
BUSINESS_UNIT |
The leading field on this index is the Account field. This index helps speed performance on all processes that access the ledger using a specific account selection. This includes the Trial Balance and General Ledger Activity SQRs, as well as the COBOL processes of Closing and Consolidations. Online, this index helps in the budget entry process and the ledger inquiry pages. You need to modify this index for your own ChartField configuration. The leading fields on the index should be the ChartFields that are always entered and have the greatest cardinality. Because the Fiscal Year, Business Unit, and Ledger fields are consistently requested with the Account field, they are also on the index and should be appropriate on your version of the index.
PSBLEDGER
This index begins with the Accounting Period field and is called into use when the accounting period and fiscal year are specified without specific ChartField references. This enhances performance in the Closing Trial Balance SQR, Closing and Summary Ledger processes, and the online budget copy process. You should be able to use this index as is without modifications.
Note:
The summary ledgers delivered with the demo database (PS_S_LEDGER_ACCTS, S_LEDGER_ACTDEP, and PS_S_LEDGER_SUM) realized performance gains in the summary ledger background process when this index was created for them. The "B" type of index might be beneficial for your own summary ledgers. Because this index is similar to the PSCLEDGER index, you might want to do some timings and analysis before deciding if the additional indexes on your own summary ledgers are worth the additional overhead during inserts and updates.
PS_LEDGER: (Oracle Only)
| PSDLEDGER | PSELEDGER |
|---|---|
|
BUSINESS_UNIT |
FISCAL_YEAR |
|
LEDGER |
ACCOUNTING_PERIOD |
|
FISCAL_YEAR |
BUSINESS_UNIT |
|
ACCOUNTING_PERIOD |
LEDGER |
|
CURRENCY_CD |
ACCOUNT |
|
STATISTICS_CODE |
PRODUCT |
|
ACCOUNT |
PROJECT_ID |
|
AFFILIATE |
|
|
CURRENCY_CD |
PSDLEDGER (Oracle Only)
This index is used in the same way the PSALEDGER index is used on the other platforms—to optimize performance in online processes, SQRs, and COBOL processes when the entire ledger key is not specified. Specifically, the Trial Balance and General Ledger Activity SQRs, the Closing and Consolidations COBOL processes, and the budget and ledger inquiry online pages use it. The index leads off with the common fields of Business Unit and Ledger and includes more of the "where" clause columns than its PSALEDGER counterpart. As in the PSALEDGER version, when building this index on your production system you should change the Account field in the demo database to be the ChartFields you always enter that have the highest cardinality.
PSELEDGER (Oracle Only)
The closing process Closing Trial Balance SQR and the closing COBOL process primarily use this index. The summary ledger COBOL process also favors it. If you do not run either of those processes (or run them infrequently), you might not need this index. To modify the index, replace the demo ChartFields with your own. The leading ChartFields should be those you always enter that have the highest cardinality.
PSWLEDGER (Sql Server, DB2/UDB for Linux, Unix, and Windows, Informix)
The PSW<record name> index is used to build the search index for database platforms that have the 16–column limit on indexes. This index will not be a unique index, instead, the concatenated 'Functional Index' plays the role of the unique index.
This index will be created with the first 16 key fields of the functional index. The recommendation is to customize the index to your need as followed: The first 5 columns of the PSW<record name> index should be: Business_unit, Ledger, Fiscal_year, Accounting_period, Account. The subsequent 11 columns should consist of the other ChartFields that are always entered and have the greatest cardinality. This index is very crucial to the performance of Post Journals and Post Budget Journals processes.
PS_JRNL_HEADER (All Platforms)
The same analysis processes were applied to the JRNL_LN and JRNL_HEADER records, and the following indexes are delivered with your demo database as a result of this study:
| PS_JRNL_ HEADER | PSCJRNL_ HEADER | PSDJRNL_ HEADER |
|---|---|---|
|
BUSINESS_UNIT |
PROCESS_INSTANCE |
JOURNAL_ID |
|
JOURNAL_ID |
JRNL_HDR_STATUS |
JOURNAL_DATE |
|
JOURNAL_DATE |
JRNL_PROCESS_REQST |
BUSINESS_UNIT_IU |
|
UNPOST_SEQ |
UNPOST_SEQ |
|
|
PROCESS_INSTANCE |
PROC_PART_ID |
BUSINESS_UNIT |
|
SYSTEM_SOURCE |
BUSINESS_UNIT |
PROCESS_INSTANCE |
|
LEDGER_GROUP |
LEDGER_GROUP |
|
|
JRNL_PROCESS_REQST |
JOURNAL_ID |
|
|
JOURNAL_ID |
JOURNAL_DATE |
|
|
UNPOST_SEQ |
PS_JRNL_HEADER
This is the unique index created by PeopleSoft Application Designer. It is used each time the journal is referenced by the key values. This includes the OpenItem and SJE Status SQRs, posting and journal edit processes, and online journal inquiry and entry processes. You should not need to change this index for any ChartField configuration.
PSCJRNL_HEADER
Because the leading field on this index is PROCESS_INSTANCE, the index helped speed processing in the journal posting and journal edit jobs where the statements select from the journal header based on PROCESS_INSTANCE. If you run those jobs, you want this index. You should not need to change it for any ChartField configuration.
PSDJRNL_HEADER
This index is used by the online system to obtain the journal headers and journal lines for InterUnit subjournals.
PS_JRNL_HEADER (All Platforms Except Oracle)
| PSAJRNL_HEADER | PSBJRNL_HEADER |
|---|---|
|
ACCOUNTING_PERIOD |
JRNL_PROCESS_REQST |
|
SOURCE |
BUSINESS_UNIT |
|
FISCAL_YEAR |
LEDGER_GROUP |
|
BUSINESS_UNIT |
|
|
LEDGER_GROUP |
PSAJRNL_HEADER
This index is selected when the accounting period, source, and fiscal year are specified in the "where" clause. The sequence of the columns was chosen by cardinality for the demo database (source has seven unique values and accounting period has 12). You need to analyze your own system to determine which column should come first—source or accounting period. Choose the column with the greatest cardinality (unique values). This index helps speed processing in the Trial Balance and General Ledger Activity SQRs and in the online inquiry and journal unpost functions.
PSBJRNL_HEADER
This index helped the Posting process by indexing on the JRNL_PROCESS_REQST field. You should not need to change this index for ChartField configuration.
PS_JRNL_HEADER (Oracle Only)
| PSEJRNL_HEADER | PSFJRNL_HEADER |
|---|---|
|
BUSINESS_UNIT |
JOURNAL_ID |
|
LEDGER_GROUP |
SOURCE |
|
JRNL_HDR_STATUS |
JRNL_HDR_STATUS |
|
FISCAL_YEAR |
BUSINESS_UNIT |
|
ACCOUNTING_PERIOD |
LEDGER_GROUP |
PSEJRNL_HEADER (Oracle Only)
This index assists the processing of the SJE Status SQR and the Journal Post and Allocations processes. No modifications should be necessary for ChartField changes.
PSFJRNL_HEADER (Oracle Only)
The only process that uses this index is the Allocations process. If you do not run that job, you should not need the index.
PS_JRNL_LN (All Platforms)
| PS_JRNL_LN | PSDJRNL_LN | PSFJRNL_LN |
|---|---|---|
|
BUSINESS_UNIT |
PROCESS_INSTANCE |
JOURNAL_DATE |
|
JOURNAL_ID |
BUSINESS_UNIT |
BUSINESS_UNIT |
|
JOURNAL_DATE |
ACCOUNT |
UNPOST_SEQ |
|
UNPOST_SEQ |
JOURNAL_ID |
|
|
JOURNAL_LINE |
JRNL_LN_SOURCE |
|
|
LEDGER |
PS_JRNL_LN
This is the unique index created by PeopleSoft Application Designer. It matches the keys on the parent record (PS_JRNL_HEADER) with the addition of the Journal Line field. This is used in processing when you update the journal line in the Posting and Journal Edit programs, and online Posting and Journal Update processes.
PSDJRNL_LN
This index assists the batch processes in accessing the journal line information. It is used in the Journal Edit and Journal Combo Edit processes, and should not require modification for ChartField changes. If the index is not chosen by the optimizer, update statistics for the table with histogram information.
For Oracle run the following command: Analyze table PS_JRNL_LN compute statistics for columns (process_instance).
For DB2/Unix, run the following command: Runstats on table <owner>.PS_JRNL_LN with distribution and indexes all.
PSFJRNL_LN
This index is used by the GLAJES SQR report, Posting, Consolidations, Currency Translation, and Allocations processes, as well as the online inquiry and posting pages.
PS_JRNL_LN (All Platforms Except Oracle)
| PSAJRNL_LN | PSBJRNL_LN |
|---|---|
|
ACCOUNT |
JOURNAL_ID |
|
BUSINESS_UNIT |
JOURNAL_DATE |
|
CURRENCY_CD |
BUSINESS_UNIT |
|
UNPOST_SEQ |
PSAJRNL_LN
This index facilitates those queries that look for a specific match on the Account ChartField. It includes the additional fields of Business Unit and Currency Code, because those fields are consistently included in the "where" clauses of these statements. It enhances performance in the GLAOITEM SQR, Journal Posting COBOL process, and the online Journal Inquiry and Unposting processes. If changes are made to the ChartFields, you need to modify this. You should include the ChartField that is always entered and has the greatest cardinality in this index.
PSBJRNL_LN
The items in this index are similar to the unique index except that the sequence is different and the JOURNAL_LINE field is left off. The JOURNAL_LINE field was eliminated because it is almost never referenced in "where" clauses. The sequence was changed because JOURNAL_ID is a high-cardinality field and is frequently referenced in select statements. In the select statements that specify JOURNAL_ID, the BUSINESS_UNIT, JOURNAL_DATE, and UNPOST_SEQ fields are also referenced; so these were included on this index in order of cardinality.
This index is frequently used: in the GLALEDGD, GLALEDGS and GLAJES SQRs as well as the Posting, Consolidations, Currency Translation, and Journal Edit background processes. Online, it is used on the inquiry and posting pages. This index should not require changes for ChartField configuration.
Note:
Consider adding the JOURNAL_LINE and LEDGER to the end of the PSBJRNL_LN index and making it the unique index (eliminating the existing PS_JRNL_LN index). This is an option because the order of the index columns can differ from PeopleSoft Application Designer field sequence.
PS_JRNL_LN (Oracle Only)
| PSEJRNL_LN |
|---|
|
BUSINESS_UNIT |
|
CURRENCY_CD |
|
ACCOUNT |
PSEJRNL_LN (Oracle Only)
This index is comparable to the PSAJRNL_LN index, except that the sequence is different. Consistent with the cost-based optimizer approach, the more common fields (Business Unit and Currency Code) are included at the beginning of the index. The SQR processes of Trial Balance, General Ledger Activity, and OpenItem Status use this index.