Inactivating/Deleting Database Index on Siebel DB2 zOS deployments (Doc ID 476961.1)

Last updated on JUNE 23, 2017

Applies to:

Siebel System Software - Version 7.0.4 [14068] to 15.0 [IP2015] [Release V7 to V15]
IBM z/OS on System z

Goal

 
This alert applies to customers running Siebel Applications on zSeries implementations only (database must be IBM DB2 UDB for z/OS and OS/390).
For others Database platforms please refer to Doc ID 502730.1.

Siebel eBusiness Applications are installed with many indexes for the database, providing great flexibility for running any portion of the application suite at high performance levels without the customer having to determine what indexes are required. For any particular customer implementation, there will be a number of unused, non-unique indexes. Dropping unused, non-unique indexes will improve the performance of insert, delete, and, to a lesser extent, update SQL activity raising overall system performance, providing greater scalability and lowering user response times. This effect is magnified in a Parallel Sysplex data-sharing environment.

Determining which indexes are candidates for inactivation requires some study. Oracle Corporation has provided a procedure for identifying unused indexes within Siebel applications but examining the behavior of the application in production.  

Restrictions

  

Unique Indexes May Not be Removed or Modified

Oracle does not allow removal or modifications of any kind to unique indexes.  For a delivered unique index to be removed or modified in any way, Oracle Engineering approval MUST be obtained.  Unique indexes perform a critical data integrity function.  Any modifications could lead to data corruption.  Correcting such a situation, if possible, could be time-consuming and costly.  Changes to unique indexes have been associated with significant application upgrade issues.

  

Standard (Out of the Box) Indexes May Not Be Modified

  

If an out of the box index has been judged to require modification, the only supported approach is to inactivate the index and create a new index with the required column definitions.  Delivered indexes are not to be modified in any way.

System Size

For smaller systems, the risk associated with removal of out of the box delivered indexes will usually exceed any potential benefit.  

Table Size

Tables which are empty or have few rows will not provide tangible benefits from index removal.  Risk can be incurred without providing any meaningful benefit.

Unusual Access Paths

Cost-based optimizers (CBO) can make use of very low cardinality indexes in some situations.  For example, on some platforms varieties of Merge, Undo and Delete processing can use an SQL construct called ‘Atomic SQL’.  The goal of ‘Atomic SQL’ is to provide a mechanism to support program based referential integrity ensuring that all SQL between the ‘Begin Atomic’ and ‘End’ phrases either all succeed or are all rolled back.  This guarantees that orphans cannot be created.
It is important to understand that in cases where these tables are large and where the foreign key contains only NULL or ‘No Match ID’ values, many CBO engines will use an available index.  If the index does not exist and the table is large, performance will suffer, in some cases very seriously.  Note that any index of this type would be shown as in use by the database.
Low cardinality does not preclude index usage in all cases.  An approach to index removal based only on cardinality could have significant side effects if this type of SQL is used by the application.  

Indexes Cannot be Removed from Certain Tables

Indexes for the Siebel Repository and Siebel logging tables are not valid candidates to be dropped.  Tables involved with Siebel Remote such as S_DOCK_TXN_LOG must also keep all of their delivered indexes.  The customer will communicate any changes in the Dropped Indexes list to the TAM who will update the customer profile.

 

 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms