Monday, October 01, 2007

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.

8 comments :

Noons said...

you can also use dba_ind_expressions or all_ind_expressions, joined on index_owner, index_name and column_position: it gives you the name of the column in a descending index.

Dan Kibler said...

When we upgraded to PeopleTools 8.48 and Oracle 10g we experienced significant performance problems with descending indexes. Process that should have taken minutes were taking hours. We found that Oracle was creating the descending indexes as function based indexes. Our solution was to configure Oracle to ignore decending indexes.

Anonymous said...

What is function Based indexes ?

Why the descending keys inclusion will create function Based indexes ?

Do we need to set any parameter to set the function based index work correctly in the database ?

how to configure Oracle to ignore decending indexes ?

Should user build index by removing desc keyword from the index build script every time ?

David Kurtz said...

>What is function Based indexes ?see http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#sthref929

>Why the descending keys inclusion will create function Based indexes ?It is hard coded into Application Designer. It is more efficient for effective date queries. It was only ever removed because of bugs in Oracle 8 and 9 that have now been resolved.

>Do we need to set any parameter to set the function based index work correctly in the database ?No.

>how to configure Oracle to ignore decending indexes ?It is possible to prevent Oracle building descending indexes by setting _ignore_desc_in_index. But, I think you should have good reason before you do this.

>Should user build index by removing desc keyword from the index build script every time ?No. If you are that determined not to have descending indexes use _ignore_desc_in_index.

Jagamitra said...

Dave,
In 8.48 on 11G we are facing loads of problems with indexes on fields having key defined as desceding order.Classic Exampleis position_data table.
Since upgrade to 8.48 2 months back, intermittently many SQL/queries and processes arerunning dead slow or faling all together
Any suggestions?
regards
Ravi

David Kurtz said...

If you want to make Oracle ignore the DESC keyword in CREATE INDEX commands, and build all indexes as ascending, then set _ignore_desc_in_index = TRUE, and recreate the indexes.

Dan Kibler said...

Dave

We upgraded to 11g & pt8.49 in the last year and continue to have performance problems with the descending function-based index so we set _ignore_desc_in_index = TRUE again.
Dan

Jules said...

We are in the process of upgrading to PeopleTools 8.49, and have found that the DESC indexes slow our custom code down and tried the ignore suggestion, but that made delivered code produce the incorrect results, so we are forced to not ignore the "DESC" indexes. Can I force it to ignore the desceding indexes for one SQL? With a hint? Anything?