tag:blogger.com,1999:blog-25740336.post521508733198917088..comments2023-08-24T22:26:48.675+01:00Comments on The PeopleSoft DBA Blog: PeopleTools 8.54: Descending Indexes are not supportedDavid Kurtzhttp://www.blogger.com/profile/00468908370233805717noreply@blogger.comBlogger7125tag:blogger.com,1999:blog-25740336.post-91021875410868234092019-09-01T23:54:58.140+01:002019-09-01T23:54:58.140+01:00You could look through the execution plans capture...You could look through the execution plans captured by AWR (DBA_HIST_SQL_PLAN) to find any execution plans and hence SQL statements that use these indexes. However, if you don’t find any plans, it does not mean that the indexes are not used, just that the plans were not captured by AWR. <br />It may be that some of the remaining function-based indexes are useful and you decide to retain them. However, there is a cost of ownership. You won’t be able to bring them into Application Designer, they will always have to be managed manually. They will always appear in DDDAUDIT. You will also have to be careful that the indexes are not dropped by Application Designer. This is something that might happen when altering the tables involved because they are not defined in the PeopleTools meta-data. The only way I can think of protecting them is with a DDL trigger – see <a href="http://blog.psftdba.com/2006/10/ddl-triggers-to-prevent-loss-of.html" rel="nofollow">DDL Triggers to prevent loss of database objects not managed by PeopleTools</a>David Kurtzhttps://www.blogger.com/profile/08139761793598085235noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-37716775225292273572019-07-10T22:08:24.820+01:002019-07-10T22:08:24.820+01:00Hi David,
We are upgrading from PeopleTools 8.54 ...Hi David,<br /><br />We are upgrading from PeopleTools 8.54 to 8.57 and have discovered that we have over 10K Descending Indexes still defined in our database instances (HCM and FSCM). We have removed those. Now we find that there are a few remaining function-based indexes (~20). I haven't been able to get a clear answer from Oracle as to if these are useful or not. <br /><br />The only relevant information I've found on your blog, etc. is that they are useful for when the UPPER() function is used on search pages.<br /><br />I believe the these indexes were added by our hosting provider DBA's after analyzing certain SQL statements. I am going to ask for the definitions of those.<br /><br />Thank you for your valuable insight.wydot_dlrosshttps://www.blogger.com/profile/10624164414565221031noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-81933381965615542202015-09-28T21:57:36.744+01:002015-09-28T21:57:36.744+01:00The only purpose of the parameter is to render the...The only purpose of the parameter is to render the DESCENDING keyword meaningless in the CREATE INDEX statement. <br /><br />Interestingly, Oracle Support Document 1909646.1: Removing Descending Indices for PeopleSoft Databases suggests setting this parameter in PT8.48 to 8.53 as it is beneficial for performance. However, it is not a mandatory parameter. <br /><br />Despite a reported problem in Oracle 11.2.0.3 (bug 20212407), I think I would leave it the setting in place to prevent anyone from creating descending key indexes manually.David Kurtzhttps://www.blogger.com/profile/08139761793598085235noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-85399840907538793992015-09-28T21:01:57.587+01:002015-09-28T21:01:57.587+01:00I have recently come accross this approach from Or...I have recently come accross this approach from Oracle<br />https://docs.oracle.com/cd/E58500_01/pt854pbh1/eng/pt/tadm/task_ConvertingDescendingIndexes.html#topofpage<br /><br />As of PeopleTools release 8.54, to optimize performance, descending indexes are no longer supported. If you are upgrading from an earlier version of PeopleTools, you must convert any descending indexes to ascending indexes by dropping descending indexes and recreating them as ascending indexes.<br /><br />The following scripts are provided in \SCRIPTS\ORA\ to accomplish this task:<br />postupgcreatedescindexes.sql<br />postupgdropdescindexes.sql<br /><br />To convert descending indexes to ascending indexes, complete these steps:<br /><br />Connect as AccessId, run postupgdropdescindexes.sql<br /><br />Connect as AccessId, run postupgcreatedescindexes.sql<br /><br />Connect as AccessId, run psdropdescindexes.sql<br /><br />Connect as SYSDBA and run alter system set "_ignore_desc_in_index"=true;<br /><br />Connect as AccessId, run pscreatedescindexes.sql<br /><br />Connect as SYSDBA and run alter system set "_ignore_desc_in_index"=false;<br /><br /><br />the script pscreatedescindexes.sql creates the indexes with the same desc in the index.<br />If we are recreating the indexes the same as before they were dropped what is the change here.<br /><br />what exactly is the parameter change helping with?<br /><br />thank you.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-25740336.post-28690263887302110622015-02-20T13:51:30.103+00:002015-02-20T13:51:30.103+00:00Thank you, David. We have found in the past that ...Thank you, David. We have found in the past that while delivered scripts will do the job, they are often not the most efficient.Erichttps://www.blogger.com/profile/14448573800625757326noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-41820954922448026992015-02-20T13:46:39.719+00:002015-02-20T13:46:39.719+00:00Yes, and it has the additional benefit that you wi...Yes, and it has the additional benefit that you will never not have a unique index, though you will have to maintain both for a while during DML.David Kurtzhttps://www.blogger.com/profile/08139761793598085235noreply@blogger.comtag:blogger.com,1999:blog-25740336.post-81589217233908657142015-02-19T21:03:55.271+00:002015-02-19T21:03:55.271+00:00A member of my DBA team has proposed an alternativ...A member of my DBA team has proposed an alternative to the Oracle method for dealing with descending indexes:<br /><br />Oracle method:<br />Set parameter.<br />Drop DESC indexes.<br />Recreate DESC indexes (DESC keyword still present in DDL).<br /><br />His method:<br />Set parameter<br />Create a copy of the DESC index as an invisible index (no DESC keyword).<br />Drop DESC index.<br />Rename the invisible index.<br />Make invisible index visible.<br /><br />Will this new method accomplish the same results as the Oracle method?Erichttps://www.blogger.com/profile/14448573800625757326noreply@blogger.com