Bookshelf Home | Contents | Index | Search | PDF

Siebel Analytics Performance Tuning Guide > The Database Servers > Periodic Tuning Tasks >

Periodic Index Evaluation

---

After you complete an ETL load of an underlying database, you should always analyze and evaluate all database indexes to determine whether individual indexes remain effective. A load operation, or successive load operations, can change the distribution of the data enough to alter the effectiveness of an index.

To illustrate how this can happen, consider an example based on an index supplied with Siebel Analytics. Siebel Systems recommendation to use this index is based on expected usage patterns against laboratory data. In practice, the index improves performance for a large number of customers but fails to be effective for a few. The failure occurs when the laboratory data is not representative of a customer's data.

The following example uses the index W_REVN_F_F23, a B-Tree index supplied with Siebel Analytics. After loading your data into the database, you may find that this particular index is ineffective, depending on your data shape.

Preliminary Analysis

To evaluate the index, analyze the index and retrieve a few statistics from the system catalog table all_indexes using the following query.

SQL> analyze index w_revn_f_f23 compute statistics ;
Index analyzed.
Elapsed: 00:00:12.48
SQL> select num_rows, distinct_keys, avg_leaf_blocks_per_key
from all_indexes
where index_name = `w_revn_f_f23'
num_rows      distinct_keys      avg_leaf_blocks_per_key
2789291             1                   4539

For this particular data, the B-Tree index is useless because there is exactly one index key. The same would be true of a bitmap index. You can learn a little more by looking at the contents of the column:

SQL> select pr_offer_wid, count (*) from w_revn_f
group by pr_offer_wid ;

PR_OFFER_WID COUNT(*)
------------ ----------
0 2789291

For this customer's data, the index is useless. This does not imply that the index is not useful at a large number of other sites; just not in this situation at this particular time. To take advantage of indexes, you must thoroughly understand your data.

You need to analyze and evaluate every index within the database each time you complete an ETL load. This evaluation is for all indexes: those initially supplied with Siebel Analytics and others which you have added to improve performance.

Suggested Action

In the previous case, the best solution is to simply drop the index.

You could take a further step and disable the index in the ddlsme.ctl file, the file that controls creation of the index. Of course, if the data makes another shift in the future, the index could become useful at that time.

Bookshelf Home | Contents | Index | Search | PDF