Managing Indexes

Table indexes should be managed internally through application designer for each record definition. You can view indexes by opening a record definition (e.g. PSRECDEFN) and selecting Tools > Data Administration > Indexes.

This will give you a list of the indexes currently defined through application designer for that record:

As you can see in this example, the record PSRECDEFN has six indexes. One key index (index ID _) and five user indexes (index IDs A-E).

If you choose any of the index IDs (first column) and press the Edit Index button you will see a window that gives you further options and a comment for that index:

It is a good idea of getting into the habit of adding comments when you add your own user indexes to records.

Index Information

The following queries gives you a summary of the indexes on a particular PeopleSoft record.

The first query works at the PeopleTools meta-data layer. It uses the PeopleTools record definition table (PSRECDEFN) and the PeopleTools index definition table (PSINDEXDEFN). It returns the record name, description, a count of the number of indexes set on that record in PeopleTools, the SQL table name (if set), the PeopleSoft index name, index ID and index type.

Note that the primary key index is always unique and is always named PS_ + RECNAME. For instance the primary index for PSRECDEFN is PS_PSRECDEFN. Also the index ID is always a single underscore _ and the index type is 1 for key.

The second query works at the Oracle database layer by checking the DBA_INDEXES Oracle system table for indexes that relate to the specified PeopleSoft record. In order to run this query, you will need to be logged in as a user with access to DBA_INDEXES.

Note that you can substitute DBA_INDEXES with ALL_INDEXES however, DBA_INDEXES can see more than ALL_INDEXES and may include indexes not visible throughALL_INDEXES depending on the account you are logged in as.

Don't forget to replace <record_name> with the appropriate PeopleTools record name.

PeopleTools Index Information

select
    R.RECNAME,
    R.RECDESCR,
    R.INDEXCOUNT,
    R.SQLTABLENAME,
    'PS' || I.INDEXID || I.RECNAME as PS_INDEXNAME,
    I.INDEXID,
    (case
        when I.INDEXTYPE = 1 then 'Key'
        when I.INDEXTYPE = 2 then 'User'
        when I.INDEXTYPE = 3 then 'Alt'
        when I.INDEXTYPE = 4 then 'User'
        else 'Unknown'
     end) as PS_INDEXTYPE   
from
    PSRECDEFN R inner join PSINDEXDEFN I
    on R.RECNAME = I.RECNAME
where
    R.RECTYPE = 0
    and R.RECNAME = '<record_name>'
order by
    R.RECNAME, PS_INDEXTYPE, I.INDEXID
;

Oracle Database Index Information

select
    R.RECNAME,
    R.RECDESCR,
    R.SQLTABLENAME,
    DI.OWNER,
    DI.TABLE_NAME as DB_TABLENAME,
    DI.INDEX_NAME as DB_INDEXNAME,
    DI.INDEX_TYPE as DB_INDEXTYPE,
    DI.UNIQUENESS,
    DI.STATUS,
    DI.NUM_ROWS,
    DI.LAST_ANALYZED
from
    PSRECDEFN R inner join DBA_INDEXES DI
    on R.RECNAME = replace(DI.TABLE_NAME, 'PS_', '') 
where
    R.RECTYPE = 0
    and R.RECNAME = '<record_name>'
order by
    DI.TABLE_NAME, DI.UNIQUENESS desc, DI.INDEX_NAME
;

PeopleTools Indexes vs Database Indexes

While running the above queries, you may find some discrepencies between index information in PeopleTools and index information in the Oracle database. To further complicate matters, application designer may be misinforming you!

To explain what I mean, I found that the record ACCT_CD_TBL only had three indexes in PeopleTools. I confirmed this using the PeopleTools index information query above. The index count returned by PSRECDEFN was 3, and there were only 3 index IDs in PSINDEXDEFN (_, 0, 1).

However, when I looked at the index information through application designer for this table, it was showing the same information as the second query which uses the Oracle system table DBA_INDEXES:

A total of 11 indexes. What's going on? By the way I've only shown the bottom of the scroll in the screenshot, but be assured there were a total of 11 indexes including the primary index (_) and 10 user indexes (0-9).

Initially I thought I was looking in the wrong PeopleTools tables. But eventually I figured it was application designer playing tricks. To confirm this, I traced application designer while performing a build index script (Build > Current Definition > Create Indexes & Build script file).

Here's the trace output:

Turns out application designer also uses DBA_INDEXES when it loads index information and creates build scripts! So it can appear that indexes exist in PeopleTools when in fact they really don't.

Index Management

To get a better picture of PeopleTools indexes versus database indexes, the following queries identify cases where indexes exist in PeopleTools but not in the database or vice versa, when they exist in the database but not in PeopleTools.

Indexes that exist in PeopleTools but not in the database

select
    R.RECNAME,    
    R.RECDESCR,
    R.INDEXCOUNT,
    R.SQLTABLENAME,
    'PS' || I.INDEXID || I.RECNAME as PS_INDEXNAME,
    I.INDEXID,
    (case
        when I.INDEXTYPE = 1 then 'Key'
        when I.INDEXTYPE = 2 then 'User'
        when I.INDEXTYPE = 3 then 'Alt'
        when I.INDEXTYPE = 4 then 'User'
        else 'Unknown'
     end) as PS_INDEXTYPE
from
    PSRECDEFN R inner join PSINDEXDEFN I
    on R.RECNAME = I.RECNAME
where
    R.RECTYPE = 0
    and not exists (
        select  1
        from    DBA_INDEXES
        where   INDEX_NAME = 'PS' || I.INDEXID || I.RECNAME
    )
order by
    R.RECNAME, PS_INDEXNAME
;

Indexes that exist in the database but not in PeopleTools

Note you will need to replace <owner> with the database user that owns your PeopleTools tables (e.g. SYSADM).

select
    DI.OWNER,
    DI.TABLE_NAME,
    DI.INDEX_NAME,
    DI.INDEX_TYPE as DB_INDEXTYPE,
    DI.UNIQUENESS,
    DI.STATUS,
    DI.NUM_ROWS,
    DI.LAST_ANALYZED,
    nvl(R.RECNAME, 'Not Found in Application Designer') as RECNAME
from
    DBA_INDEXES DI left outer join PSRECDEFN R
    on replace(DI.TABLE_NAME, 'PS_', '') = R.RECNAME
where
    DI.OWNER = '<owner>'
    and not exists (
        select  1
        from    PSINDEXDEFN
        where   'PS' || INDEXID || RECNAME = DI.INDEX_NAME
    )
order by
    DI.OWNER, DI.TABLE_NAME
;

Hopefully these queries help you find out what's really going on with your indexes.