Function Based Indexes in PeopleSoft
Those who have not comes across performance issues in PeopleSoft, especially PeopleSoft on Oracle might not be aware of this thing called performance issues due to function based indexes. PeopleSoft has given a lot of headache to Oracle database engineers as well as customers when it comes to function based indexes.
By definition, a function-based index is an index created :
- On an expression, such as an arithmetic expression or an expression containing a package function.
- On a descending ordered column.
Then there is something called Function-based Index with Expression – These indexes are indexes that have an expression wrapped around the column of the index. They are very rare in PeopleSoft and created to improve performance of a particular SQL statement.
Table PS_JOB has an index PS0JOB with Name Last_Name as leading column:
SELECT EMPLID, LAST_NAME FROM PS_JOB WHERE LAST_NAME LIKE 'Watson%'
SELECT EMPLID, LAST_NAME FROM PS_JOB WHERE LAST_NAME LIKE 'WATSON%'
The above two SQLs are likely to produce different results because of the way in which the last name is provided. The likely reason could be that the data is stored in mixed case and the only way to get the result using a consistent case filter is by using the function “UPPER.”
So, the effective SQL is
SELECT EMPLID, LAST_NAME FROM PS_JOB WHERE UPPER(LAST_NAME) LIKE 'WATSON%'
When you use the UPPER function, the SQL might take time to return the result, however, the result will be correct. In such cases, creating function based index is helpful. An example of a function-based index that can be created:
CREATE INDEX PSXJOB ON PS_JOB (UPPER(LAST_NAME));
In this case the second SQL will provide quick result because it will be able to make use of this index.
Starting with PeopleTools 8.48, PeopleSoft generates indexes with DESCENDING column. These indexes are considered function-based indexes in Oracle. Here is an example:
CREATE UNIQUE INDEX PS_GL_ACCOUNT_TBL ON PS_GL_ACCOUNT_TBL (SETID, ACCOUNT, EFFDT DESC) TABLESPACE PSINDEX STORAGE (INITIAL 45056 NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10 PARALLEL NOLOGGING
When you select the descending column name from DBA_IND_COLUMNS, its shows something like SYS_NC00033$, which is a system-generated column name. To find the real column name, you have to look in COLUMN_EXPRESSION of DBA_IND_EXPRESSIONS.
Recent Oracle 11gR2 benchmarks show that the execution plan using function-based indexes costs higher and causes the plan to be discarded, thereby causing performance issues. Oracle recommends that Function-based indexes with descending columns are being recreated as regular indexes with ascending columns.
Function-based indexes can be created on the case-sensitive columns, but Application Designer is not capable of generating them automatically, so they have to be managed manually.
