Using Indexes

This section discusses:

• Identifying Appropriate IndexesI.

• Selecting Indexes.

• Making the Rules Practical.

• Using Indexes in the Demo System.

• Optimizing Indexes.

Depending on the database platform, volume and distribution of data, the correct index can speed processing greatly. As the volume of data in your ledger and journal tables grows, periodically review the plan and indexes to ensure that they remain efficient.

The indexes delivered with the demonstration database were created for small amounts of data and are specific to the delivered sample ChartFields—these might be inappropriate for your situation. Develop indexes specific to your own data and configuration.

Indexes are sets of information used to access data in a record. They are stored separately from the records but updated simultaneously when records are updated, inserted, or deleted. Each of your records should have one unique index. This provides a valuable edit function by preventing duplicate rows of data from being added to the database. Add more indexes to improve performance when locating and accessing the data.

When you create a record in PeopleSoft Application Designer, the system automatically creates a unique index with the same name as the record. So, journal line record (PS_JRNL_LN) is created with a unique index, usually named PS_JRNL_LN. This unique index includes all the keys that were identified on the record. The combination of these keys should identify one unique row of information.

PeopleSoft Application Designer sometimes creates additional indexes when you add a record. These are generated from fields identified as alternate search keys on the record itself and are not included in the unique index mentioned above. For example, the GL_ACCOUNT_TBL has Alternate Search Keys designated on Account Type and Description fields. When the table is created in Application Designer, two additional indexes (PS0GL_ACCOUNT_TBL and PS1GL_ACCOUNT_TBL) is created with each of these fields listed. These additional indexes are always termed duplicate indexes, because they may not point to unique rows of data.

General Ledger includes system tables that enable you to view the created indexes and their columns.