On entering the JOB component, the PIA generates the SQL to retrieve the job history for a particular employee. The two known keys are specified in the where clause, but all the keys were used to generate the ORDER BY clause. EFFDT and EFFSEQ are descending keys on the JOB record, so the query is in descending order on those columns.
The descending key attribute has another purpose. Prior to PeopleTools 8.14, if you specified a key field as descending in Application Designer, that column would also be indexed in descending order where it appeared in indexes. However, PeopleSoft removed this functionality because of Oracle Bug 869177 which in most versions of Oracle 8.1 could cause ORA-3113 when accessing the index.
Now that Oracle has fixed the bug in the database, PeopleTools once again builds indexes with descending columns on Oracle RDBMS from PeopleTools 8.48.
However, as always, there is a snag (or I wouldn't have bothered to write this note). The name of the descending column is not reported in catalogue views. Instead the column name is replaced with a system generated column name.
This could cause problems with SQL scripts that use the catalogue views. For example, I have had to change my DDL Trigger to protect database objects not managed by Application Designer.
So how do you find the name of the underlying column? You have to look in the sys owned catalogue tables. If you look at the query in $ORACLE_HOME/rdbms/admin/catalog.sql at the code behind the view, you find that the column names are stored on sys.col$. There is another column on that table called DEFAULT$, which stores the original column name.
My thanks to Noons for pointing out that you can get the underlying column or expression from DBA_IND_EXPRESSIONS.