Ingeniero

Database Configuration Guidelines

Comments

Index Recommendations
Bitmap indexes are not suitable for columns that are subject to frequent
changes (insert/update/delete).
Low selectivity(high cardinality) indexes are usually the mostof data is
An index on a low cardinality column may be OK if the range efficient.
heavily skewed (most of the data has the same value and the index will beused to identify the smaller subset).
An index on a low cardinality column may be OK if it is part of a composite
index that has low selectivity.
Foreign Keys must be indexed
Eliminate redundantindexes (indexes which mostly share the same
columns).
If a composite index includes the table PK it is probably redundant. If the
PK is specified in the where clause then the PK index will beused anyway. If
the PK is not specified then why is it in the index?
Test new queries/new indexes with a realistic volume of data. Collect
explain plans and incorporate into the design documentation.Document why indexes were created. E.g. which queries/functionality do
they support.
Monitor index usage and consider dropping unused indexes
Follow the product naming standard for CM Indexes.Page 1 of 4

Implementation Status

Partitioning
Partition high volume transactional tables that use randomly generated
Primary Key (PK) IDs (such as CI_TAX_FORM, CI_TAX_FORM_LOG, CI_FT) byrange on the PK_ID. Create indexes that have the Table PK_ID as the leading
column as LOCAL indexes.
FK indexes of tables with a common ancestor (i.e. they inherit from the
same PK) should be local--for example CI_FT.SA_ID.
All other indexes should be GLOBAL.
Ideally, the number of partitions created should be a multiple or divisor of
the number of threads you are running the job with. Forexample, if you
plan to submit jobs with 16 threads using 2 Thread pool workers, a good
number of partitions my be 4, 8 or 16.
Remember that when you want to add threads you should maintain the...