Filter Factors

Although it might be named differently, a filter factor applies to all optimizers. It estimates how effective a particular index will be in narrowing a search.

Assume we have a table in which financial results are stored by fiscal year, period, and account number. The table has two indexes, one on fiscal year and another on account. Assume that our query contains the following:

WHERE FISCAL_YEAR = 1994 AND ACCOUNT='500120'

If the table has data for 4 fiscal years and 800 accounts, then the filter factor for the fiscal year index is ¼ or 0.25; and the filter factor for the account index is 1/800 or 0.00125. Using the index for fiscal year narrows the search to about one fourth of the total table, which is not that great. But using the index for account narrows the data searched to about one eight hundredth of the total table and will be much more efficient. Of course, an index combining fiscal year and account would be even better.

The measure of the selectivity of an index, or a column within an index, is often called its cardinality. Cardinality is the number of discrete values in that column or the number of discrete combinations represented by a multi-column index. Cardinality is one of the most important statistics used by optimizers to select indexes and access paths. Like most other statistics, cardinality is updated on request rather than constantly.

When data changes substantially, update the statistics so the optimizer has accurate information. Updating statistics requires different processes on different database platforms.

See the PeopleTools Installation Guide.