Report - Index Selectivity

 


This report is extremely useful when planning queries against legacy systems.  Use this report to find out how selective your different access paths are.  SQLThing only shows logical files which can be used in resolution of a query, (i.e. multi-format logicals are not shown). Note that it may take several minutes to generate the report against physical files that have a large number of logicals attached. Here is an example of the report:

In the above example, note the logical file OELTOH1 has the same number of records as the physical file, as it is not a select omit index. Also, note that the key field OENO01 has then same number of unique values as the number of records in the physical file; this indicates that the OENO01 field is a unique key, even though the logical was not created as a unique index.

The ABS Select column shows how selective the index would be when using a key value.  For example, logical OELTOH10 has a key ARNO15 that has 1 unique value, which means that if I select where ARNO15 is equal to a value I would retrieve, on average, 14,984 records in my query.  This indicates that ARNO15 is not a very selective key.  However, if I do a query on ARNO15 and OENO01, I achieve a absolute selectivity of 1.00 which indicates I have uniquely identified a record.

The Relative Select column indicates how much more selective the combination of keys is.  For example, in Index OELTOH11, the first key OENO08 has 75 unique values and points, on average, to 11.52 records in the set of 864 records which means that using the key in this logical is .09 times more selective than reading the logical without a key.  Using the combination of keys OENO08+ARNO01 is 8.21 times more selective than using just OENO08 as a key against the logical. Using the combination of keys OENO08+ARNO01+OENO01 is only 1.4 times more selective than using the combination OENO08+ARNO01.