Index Matching

The effective filter factor for an index is the combined cardinalities for the index columns actually used in a particular access. For example, if an index is built over FISCAL_YEAR, LEDGER, and ACCOUNT, and the table contains 4 years, 5 ledgers, and 800 accounts, the potential filter factor is 1/(4*5*800), or 1/16000, or 0.0000625. However, if the ACCOUNT field in the index couldn’t be used because of the nature its criteria, the filter factor would be only 1/20, which isn’t very selective.

These general rules apply to matching index columns:

• Database systems provide direct access to data very quickly if the criteria can be processed through an optimized look-up process (such as searching a tree structure) within the index.

  Scanning index pages to satisfy criteria is much slower, although it is usually much faster than scanning the corresponding data.

• Columns are matched from left to right in the order they were specified when the index was created.

  If, for example, an index is created over DEPTID, BUSINESS_UNIT, and ACCOUNT, but no criteria were provided for BUSINESS_UNIT, only the DEPTID field in the index would be matched, even if criteria were specified for ACCOUNT.

• To get index matching on multiple columns, the leftmost columns must have simple criteria, often equality (such as FISCAL_YEAR=1996).

  More complex criteria, such as In (...), Between, or a Join to another table, generally either prevent a random-access match on the index column or prevent matching any of the columns to its right.