Sterling™ Order Management System Software schema: Db2 index monitoring and tuning
You might have to adjust your indexes to suit your individual environment.
As we mentioned in Indexes, you may have to adjust the base starting index set to suit your operational environment. You can find out what indexes are used (and by corollary, which ones are not used) You can use Design Advisor of Db2 to monitor index usage. The Design Advisor recommends additional indexes as well as indexes that are not used.
Index and table statistics
The database optimizers rely on up-to-date accurate table and index statistics to generate optimal access plans.
In addition, columns, such as enterprise_key in
the yfs_order_header, can exhibit high skew - for
example, there could be many orders for one enterprise and a few orders
for another enterprise. Columns such as derived_from_order_header_key in
the yfs_order_header table could have very high skew,
which results in low cardinality because they only contain spaces.
This can happen when customers have small numbers of derived orders.
Queries, such as the one below, against columns with high skew and/or low cardinality:
select *
from yfs_order_header
where derived_from_order_header_key = '2011012412213801928344';
can result in table scans even if the columns are indexed. The example above was from an actual case (see below).
From the perspective of the optimizer, the queries against these columns either return a small or a very large result set. To err on the side of caution, the optimizer generally chooses a table scan over an index range scan.
You can get the optimizer to choose a more optimal access plan by providing additional statistics in the form of histograms.
Issue the following command to create histograms in Db2:
db2 runstats on table <table name> on key columns with distribution on key
columns and sampled detailed indexes all allow read access