Inactivating/Deleting Database Index on Siebel Schema (Doc ID 502730.1)

Last updated on DECEMBER 10, 2021

Applies to:

Goal

For customers using z/OS, please refer to Doc Id 476961.1.

PURPOSE

Object-Oriented (OO) design methodologies involve highly normalized tables.  A considerable number of foreign keys are required for referential purposes.  Often these foreign keys are indexed to provide maximum performance under a variety of configurations.  

Consistent with OO principles, Siebel applications are delivered with a considerable number of indexes.  Foreign and primary keys are very small fields containing non-descriptive data that are used to provide high performance for referential integrity checks.  Other indexes, usually composite index structures, exist to support performance requirements found during Siebel Engineering testing or previous experience in other implementations.

Not all of these indexes are used by every customer implementation.  For this reason, Oracle Advanced Customer Services occasionally receives customer requests to inactivate a subset of delivered indexes from their implementations to support physical removal of these indexes.

This document does not apply to situations where index removal is undertaken to improve EIM performance for initial data conversion.  Index removal for conversion is temporary in nature.  Customers with ongoing incremental loads may need to permanently inactivate and drop unused EIM indexes as well as some out of the box indexes on transaction tables.  This document is concerned with index removal which is intended to be permanent.  


REASONS FOR REMOVING UNUSED INDEXES

There are several reasons given by implementers for reducing the number of indexes on tables.  Common reasons provided include:

Improving performance of insert, update and delete operations
•    Reducing database server processor and memory resource utilization
•    Conserving disk space
•    Reducing the runtime of database utilities
•    EIM tasks running during periods when users are on the system
•    In some cases, indexes can induce the database engine to select less than optimal access paths

Multiple justifications for index removal are often provided.   Some are dependent on specific database engines and others are more generic.  Each reason must be thoughtfully considered and analyzed to ensure that the effort and risk involved is worthwhile.

WHAT TO EXPECT FROM UNUSED INDEX REMOVAL
While justifications for reducing the number of indexes often seem compelling, disappointment with the results of index removal is common.  Early versions of relational database management systems (RDBMS) sometimes imposed a considerable performance penalty for some SQL activity on tables with large numbers of indexes.  Modern versions of RDBMS products handle index overhead much more efficiently.  

Realistic evaluation of the possible positive benefits of index removal is required.  Failure to take this step can lead to disappointment and wasted effort.  

Each of the reasons provided to justify index removal should be reviewed to determine if the expected result is realistic.  A few considerations are:

•    Index removal will not provide tangible performance improvements for end users.  This is a common reason given for index removal.  If insert performance is improved from 2/10th of a second to 1/10th, users are not going to notice.  
•    Actions, online or batch, to update existing records will not perform meaningfully better.  
•    Any “upsert” or delete intensive batch process; i.e. WF, AM, eAI and COM may also benefit.  This will only provide tangible benefits at high transaction volumes on large tables.
•    Batch processes such as EIM will benefit somewhat from reduced index loads.  This has been observed on all platforms especially for data conversion and incremental data load efforts.  The volume of data being inserted or deleted must be relatively large for tangible benefits to be realized.
•    Reduced time for database utilities can be a valid reason for reducing the number of indexes on large tables.  
     o    Utilities which must read and evaluate all or most rows to populate statistics for cost-based optimizers (CBO) will run faster.  Each index is a database object and will incur some overhead during statistics gathering.
     o    Utilities such as table reorganizations will benefit from reducing the number of indexes.  Some DBMS utilities maintain indexes sequentially.  So for tables with many millions of rows, reducing the number of indexes can have a substantial beneficial effect on reorganization elapsed time.
     o    Backup and recovery utilities will run somewhat faster.  In some cases where this was the customer’s primary reason for index removal, the customer was generally disappointed with the degree of improvement.  
     o    Direct load utilities, generally against EIM tables only, will benefit from reducing the number of indexes on the table being loaded.
     o    For tables that are not large, the risk of poor performance will outweigh any potential benefits from the foregoing most of the time.
     o    Database server processor usage will be reduced to some degree.   For systems using chargeback schemes based on processor utilization, savings can be meaningful.  
•    When EIM must be run during business hours, reducing the number of indexes on tables being inserted into by EIM can be somewhat helpful.  
     o    Elapsed times will be shorter.
     o    Utilization of I/O subsystem resources will be reduced.  Online users and EIM will be competing for I/O resources.  If inserts have fewer indexes to maintain, I/O resource requirements will be less.
     o    Insertions will require fewer logging resources.  Some DBMS log changes to base table pages and any index page changes.  
     o    Note that this does not apply to EIM updates to the same degree.  An update will have to access the base table pages being affected and only those indexes containing columns being updated.  Removal of indexes which do not contain columns being updated will have no effect.  
•    In some rare situations, the presence of more than one similar index can induce DBMS optimization engines to select an inappropriate access path.  Often this is the result of incorrect DBMS parameter settings.  Occasionally, this type of DBMS behaviour indicates the need for DBMS software maintenance.  
•    Testing and evaluation must also include integration solutions such as Siebel eBusiness Application Integration (eAI). In some cases, indexes have been removed to improve end-user response times but have had a negative impact on eAI solutions. This can include columns that are predominantly NULL.
•    Some index changes may require an application modification to support them. To prevent a query that executes across a previously indexed column it may be necessary to alter the application configuration. One example would be queries that are concerned with a small subset of rows where a given column is “not null”. This type of design should be avoided: try not to use “null” and “not null” as principle search criteria.  


GUIDELINES AND RESTRICTIONS

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.

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 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.
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.  

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.