Optimizing Indexes With Dynamic Selectors

If you use the dynamic selector technique heavily for certain criteria fields, you should try an index on that field’s selector table that is optimized around this technique. For example, a ACCOUNT is a six-character field (meaning its selector table is PSTREESELECT06) and you plan to use dynamic selectors, with single values, for the ACCOUNT trees on most of your production reports. You should create an index on PSTREESELECT06 on SELECTOR_NUM and RANGE_FROM_06, since these are the only fields that will appear in the Where clause with single-value dynamic selectors. But also note that:

• Other fields that are the same size may use the same selector table, so you might not want to eliminate an index if removing it would penalize those reports.

• Although only SELECTOR_NUM and RANGE_FROM_06 will appear in the Where clause, TREE_NODE_NUM may appear in the Select list (if PS/nVision needs to Group By tree node).

  An index that includes this field as well would enable index-only access (that is, access with no need to read the data table) when using this selector.

• Index use varies depending on the optimizer and the volume and distribution of data, so experiment to get optimum results.