Understanding Indexes

Indexes are an important part of your physical database because they affect the efficiency and speed with which your application can store and retrieve data. PeopleSoft application indexes are defined in two ways.

Some indexes are defined for you automatically, based on the key, search key, list box items, and alternate search keys that you specified in your record definition. The application processor uses these indexes for quick access when you use the online system.

However, sometimes you need to define additional indexes to improve the performance of demanding queries, reports, or batch programs. You define and modify these additional indexes using the data administration tools in PeopleSoft Application Designer.

Note:

You can only create indexes for record definition types that are SQL or temporary tables. You would not create indexes on views, such as a SQL view, because views are never physically created. The Indexes option in the Tools, Data Administration menu is disabled when a view is open.

When you use SQL Create or SQL Alter for a table, the system automatically builds database indexes to the SQL table that are based on the keys and list items that you specify in the record definition. Your database uses indexes to find definitions in the database the same way that you use indexes to find specific information in a book. If an index entry exists for a specific field in a table, the database can search and quickly find what it needs to complete your request; otherwise, it must search the contents of the entire table.

Indexes enhance system performance by generating key lists that the application processor uses to extract the data that it uses to populate pages and search records. The system automatically creates:

• Standard indexes (key or duplicate order key indexes) if at least one field in the table is a key or duplicate order key.

  The index contains all key and duplicate order key fields. The system automatically names this index with a prefix of PS_.

• Alternate search key indexes for each alternate search key.

  The system automatically names this index with a prefix of PSn, in which n is a number between 0 and 9.

• Custom indexes, which you can define using the Edit Index, Edit DDL, and Add Index dialog box in the PeopleSoft Application Designer administration tools.

  The system automatically names this index with a prefix of Psa, in which a can be any letter between A and V. The system does not create custom indexes automatically.

For most database tables (those with unique keys), the key index defines unique keys for a table, but the system also uses it to access high-level keys and orders by all keys. Alternate search key indexes support the search record and field prompts. The purpose of these different indexes is to enable the system to respond to all system prompts through index-only SQL access, minimizing direct database table access.

Note:

If you use Create Table or Alter, the Create Index option is automatically selected. With the Create Table or Alter options, you cannot deselect the Create Index option.

The alter process takes care of indexes regardless of the Alter Table Options you select on the Alter tab of the Build Settings dialog box. In the case of the Alter by Table Rename option, the indexes are dropped automatically when the temporary table is dropped. For the Alter in Place option, the index creation process goes through the Recreate index only if modified option on the Create tab.

When creating indexes with the Alter method, before altering the table, drop the statistics using "DROP STATISTICS" that were created explicitly in columns using "CREATE STATISTICS."

The following record definition modifications require you to create new indexes:

• Add, delete, or modify keys, duplicate order keys, alternate search keys, or descending keys in the Record Field Properties dialog box.

• Change the order of keys (ascending, as opposed to descending) in the Change Record Indexes dialog box.

• Change the Unique settings in the Edit Index dialog box.

Platform Support for Indexes

Most database platforms support indexes with an unlimited number of keys. However, Microsoft SQL Server supports indexes that have a maximum of 16 keys only. PeopleSoft Application Designer supports indexes for these databases by creating functional indexes. If a record definition has more than 16 keys, the Build process automatically alters the table to support functional indexes. The system adds a warning message to the log file indicating this.

For Microsoft SQL Server, if an index has more than 16 keys, the key fields are concatenated to a functional key. This key field is then added to the table as the last column, called MSSCONCATCOL. A unique index is created for MSSXCONCATCOL as well as an additional index called PSW<record name>, which is used as a search index.

Note:

For the Microsoft SQL Server platform, if your database has tables containing the MSSCONCATCOL column, you will see SQL to alter the tables and re-create their associated indexes, even though the underlying tables and indexes may not have changed.

You can find sample code for functional indexes for each of these platforms in the “Understanding Functional Indexes” topic.

Note:

Beginning with PeopleTools 8.54, descending indexes are no longer supported on the Oracle platform for performance reasons.