PeopleTools 8.54: Descending Indexes are not supported

This is the first in a series of articles about new features and differences in PeopleTools 8.54 that will be of interest to the Oracle DBA.

"With PeopleTools 8.54, PeopleTools will no longer support descending indexes on the Oracle database platform" - PeopleTools 8.54 Release Notes

They have gone again!  Forgive the trip down memory lane, but I think it is worth reviewing their history.

• Prior to PeopleTools 8.14, if you specified a key or alternate search field in record as descending then where it appeared in automatically generated key and alternate search key indexes, that column would be descending.  PeopleTools would add the DESC keyword after the column in the CREATE INDEX DDL.  Similarly, columns can be specified as descending in user indexes (that is to say ones created by the developer with index ID A through Z).
• In PeopleTools 8.14 to 8.47, descending indexes were not built by Application Designer because of a problem with the descending key indexes in some versions of Oracle 8i.  
• PeopleSoft had previously recommended setting an initialisation parameter on 8i to prevent descending indexes from being created even if the DESC keyword was specified.parameters.

_IGNORE_DESC_IN_INDEX=TRUE

• From PeopleTools 8.48 the descending keyword came back because this was the first version of PeopleTools that was only certified from Oracle 9i, in which the descending index bug never occurred. (see blog posting 'Descending Indexes are Back!' October 2007).
• In PeopleTools 8.54, there are again no descending indexes because the descending keyword has been omitted from the column list in the CREATE TABLE DDL. You can still specify descending keys in Application Designer because that controls the order in which rows are queried into scrolls in the PIA.  You can also still specify descending order on user indexes, but it has no effect upon either the application or the index DDL.

I haven’t found any documentation that explains why this change has been made.  This time there is no suggestion of a database bug.  However, I think that there are a good database design reasons behind it.

Normally creation of a primary key automatically creates a unique index to police the constraint.  It is possible to create a primary key constraint using a pre-existing index.  The index does not have to be unique, but it may as well.  However, there are some limitations.

• You cannot create primary key on nullable columns - that is a fundamental part of the relational model.  This is rarely a problem in PeopleSoft where only dates that are not marked 'required' in the Application Designer are created nullable in the database.
• You can create a unique index on nullable columns, which is probably why PeopleSoft has always used unique indexes.  
• You cannot use a descending index in a primary key constraint because it is implemented as a function-based index.

CREATE TABLE t (a NUMBER NOT NULL)
/
CREATE UNIQUE INDEX t1 ON t(A DESC)
/
ALTER TABLE t ADD PRIMARY KEY (a) USING INDEX t1
/
ORA-14196: Specified index cannot be used to enforce the constraint.

• We can see that the descending key column is actually a function of a column and not a column, and so cannot be used in a primary key.

SELECT index_name, index_type, uniqueness FROM user_indexes WHERE table_name = 'T'
/

INDEX_NAME INDEX_TYPE                  UNIQUENES
---------- --------------------------- ---------
T1         FUNCTION-BASED NORMAL       UNIQUE 

SELECT index_name, column_name, column_position, descend FROM user_ind_columns WHERE table_name = 'T'
/

INDEX_NAME COLUMN_NAME  COLUMN_POSITION DESC
---------- ------------ --------------- ----
T1         SYS_NC00002$               1 DESC

SELECT * FROM user_ind_expressions WHERE table_name = 'T'
/

INDEX_NAME TABLE_NAME COLUMN_EXPRESSION    COLUMN_POSITION
---------- ---------- -------------------- ---------------
T1         T          "A"                                1

• Non-PeopleSoft digression: you can create a primary key on a virtual column.  An index on the virtual column is not function-based.  So you can achieve the same effect if you move the function from the index into a virtual column, and you can have a primary key on the function.  However, PeopleTools Application Designer doesn't support virtual columns.

I think that descending keys have removed from PeopleTools because:

• It permits the creation of primary key constraints using the unique indexes.
• It does not pose any performance threat.  In Oracle, index leaf blocks are chained in both directions so it is possible to use an ascending index for a descending scan and vice versa. 
• Update 20.4.2016: There is an optimisation in Oracle 11.2.0.4 that improves the performance of the MAX() function in correlated sub-queries on ascending indexes only.  This will benefit all PeopleSoft applications, but especially HCM.

What are the advantages of having a primary key rather than just a unique constraint?

• The optimizer can only consider certain SQL transformations if there is a primary key.  That mainly affects star transformation.
• It allows the optimizer to rewrite a query to use a materialized view. 
• It allows a materialized view refresh to be based on the primary key rather than the rowid (the physical address of the row in a table).  This can save you from performing a full refresh of the materialized view if you rebuild the table (I will come back to materialized views in another posting).
• If you are using logical standby, you need to be able to uniquely identify a row of data otherwise Oracle will perform supplemental logging.  Oracle will additionally log all bounded-size columns (in PeopleSoft, that means everything except LOBs).  Oracle can use non-null unique constraint, but it cannot use a unique function-based index.
• Logical standby can be a useful way to minimise downtime during a migration.  For example, when migrating the database from a proprietary Unix to Linux where there is an Endian change.  Minimising supplemental logging would definitely be of interest in this case.

The descending index change is not something that can be configured by the developer or administrator.  It is just something that is hard coded in Application Design and Data Mover that changes the DDL that they generate.

There are some considerations on migration to PeopleTools 8.54:

• It will be necessary to rebuild all indexes with descending keys to remove the descending keys.  
• There are a lot of descending indexes in a typical PeopleSoft application (I counted the number of function-based indexes in a typical system: HR ~11000 , Financials: ~8500).  If you choose to do this during the migration it may take considerable time.
• In Application Designer, if your build script settings are set to only recreate an index if modified, Application Designer will not detect that the index has a descending key and rebuild it.  So you will have to work out for yourself which indexes have descending key columns and handle the rebuild manually.
• With migration to PeopleTools 8.54 in mind, you might choose to prevent Oracle from building descending indexes by setting _IGNORE_DESC_IN_INDEX=TRUE. Then you can handle the rebuild in stages in advance

Conclusion

I think the removal of descending indexes from PeopleSoft is a sensible change that enables a number of Oracle database features, while doing no harm.

PeopleTools 8.48: Descending Indexes are back!

In Application Designer, you can mark a key field as descending. When that record is used in a scroll on a page, PeopleTools automatically orders the query generated to populate the scroll in key order, and that includes specifying descending order for descending keys. The JOB record in HR is a good example.

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.

SELECT EMPLID,...
FROM PS_JOB
WHERE EMPLID=:1
AND EMPL_RCD=:2
ORDER BY EMPLID, EMPL_RCD, EFFDT DESC, EFFSEQ DESC

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.

SELECT table_name, index_name, column_position
,      column_name, descend
FROM   dba_ind_columns
WHERE  index_name = 'PS_JOB'

TABLE_NAME INDEX_NAME COLUMN_POSITION COLUMN_NAME  DESCEND
---------- ---------- --------------- ------------ ------------
PS_JOB     PS_JOB                   1 EMPLID       ASC
PS_JOB     PS_JOB                   2 EMPL_RCD     ASC
PS_JOB     PS_JOB                   3 SYS_NC00163$ DESC
PS_JOB     PS_JOB                   4 SYS_NC00164$ DESC

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.

COLUMN property        FORMAT 999999
COLUMN table_owner     FORMAT a8  HEADING 'Table|Owner'
COLUMN default$        FORMAT a8
COLUMN column_name     FORMAT a12 HEADING 'Column|Name'
COLUMN index_name      FORMAT a6  HEADING 'Index|Name'
COLUMN table_name      FORMAT a6  HEADING 'Table|Name'
COLUMN column_position FORMAT 99  HEADING 'Column|Position'
COLUMN column_length   FORMAT 999 HEADING 'Column|Length'
COLUMN char_length     FORMAT 999 HEADING 'Char|Length'
COLUMN descend         FORMAT a7

select /*i.obj#
,      io.name   index_owner
,      */idx.name  index_name
,      bo.name   table_owner
,      base.name table_name
,      decode(bitand(c.property, 1024), 1024,
(select decode(bitand(tc.property, 1), 1, ac.name, tc.name)
 from   sys.col$ tc, attrcol$ ac
 where  tc.intcol# = c.intcol#-1
   and  tc.obj# = c.obj#
   and  tc.obj# = ac.obj#(+
   and  tc.intcol# = ac.intcol#(+)),
decode(ac.name, null, c.name, ac.name)) column_name
,      c.property, c.default$
,      ic.pos#  column_position
,      c.length column_length
,      c.spare3 char_length
,      decode(bitand(c.property, 131072), 131072, 'DESC', 'ASC') descend
from sys.col$ c, sys.obj$ idx, sys.obj$ base, sys.icol$ ic,
sys.user$ io, sys.user$ bo, sys.ind$ i, sys.attrcol$ ac
where ic.bo# = c.obj#
and decode(bitand(i.property,1024),0,ic.intcol#,ic.spare2) = c.intcol#
and ic.bo# = base.obj#
and io.user# = idx.owner#
and bo.user# = base.owner#
and ic.obj# = idx.obj#
and idx.obj# = i.obj#
and i.type# in (1, 2, 3, 4, 6, 7, 9)
and c.obj# = ac.obj#(+)
and c.intcol# = ac.intcol#(+)
and io.name = 'SYSADM'
and idx.name = 'PS_JOB'
and bo.name = 'SYSADM'
and base.name = 'PS_JOB'
/

Index  Table    Table  Column                           Column Column   Char
Name   Owner    Name   Name         PROPERTY DEFAULT$ Position Length Length DESCEND
------ -------- ------ ------------ -------- -------- -------- ------ ------ -------
PS_JOB SYSADM   PS_JOB EMPLID              0                 1     11     11 ASC
PS_JOB SYSADM   PS_JOB EMPL_RCD            0                 2     22      0 ASC
PS_JOB SYSADM   PS_JOB SYS_NC00163$   196904 "EFFDT"         3     12      0 DESC
PS_JOB SYSADM   PS_JOB SYS_NC00164$   196904 "EFFSEQ"        4     34      0 DESC

My thanks to Noons for pointing out that you can get the underlying column or expression from DBA_IND_EXPRESSIONS.