Helen Borrie wrote:

> At 10:22 PM 4/11/2003 -0500, you wrote:
>
>
>>Hello all,
>>
>>Are there any hard rules about selectivity for indexes and the numbers
>>that I should look for? I in the process of writing an app that will
>>house many record in a FB 1.5 DB and I want to make sure that I have not
>>introduced indices that will adversely affect performance.
>
>
> 1. Run SET STATISTICS on the database containing real-life data.
> 2. Do a query to get the current selectivity for your indexes:
>
> SELECT RDB$INDEX_NAME, RDB$STATISTICS
> FROM RDB$INDICES
> WHERE RDB$RELATION_NAME NOT STARTING WITH 'RDB$'
>
> Here's what Ann says regarding this magic number in RDB$STATISTICS:
> It is the selectivity. It's set when index fast-load runs (e.g. restore
> from a backup, reactivate) and as a result of the
> SET STATISTICS INDEX <name>;
> command. The formula is simple.
> -- If the index is empty, then the value is 0.
> -- Otherwise, the value is
> 1 divided by (the number of index entries - the number of duplicates).
>
> The smaller the selectivity value, the better the index.
>
> I'd add, the larger the selectivity value, the LOWER the selectivity of the
> index. A unique index has the lowest selectivity value possible and it has
> the highest possible selectivity.
>
> However, you should also run
> gstat -index db_path_and_name -t table-name
> to look at the figures on the index trees on each index. Look at the
> figures for nodes (= number of entries in the index), total dup (the number
> of nodes that have duplicates) and max dup (the length of the longest
> duplicate chain). These will tell you useful stuff about the geometry of
> the index. Long duplicate chains are a sign of an index you should drop.
>
> h.
>

Nice! Thanks for the great response, Helen! You should add this to the
KB on IBPhoenix.


--

Warm Regards,

Lee