Examples: Key Distribution Statistics

Key distribution statistics can also be used to provide estimates of other information about tables that may prove useful.

Example E-1 Key Distribution Statistics

To estimate the number of elements in each table, perform the following query:

SELECT tableName, 
    sum(count) AS count
FROM SYS$TableStatsPartition
WHERE NOT contains (tableName, "$")
GROUP BY tableName

The clause WHERE NOT CONTAINS (tableName, "$") filters out system tables by only including tables whose names do not contain the "$" character.

The clause GROUP BY tableName is what causes the sums to be computed over all of the partition entries for the same table.

Example E-2 Key Distribution Statistics

To estimate the average key size for each table, perform the following query:

SELECT tableName,
    CASE WHEN sum(count) = 0
        THEN 0
        ELSE sum(avgKeySize*count)/sum(count)
    END AS avgKeySize
FROM SYS$TableStatsPartition
WHERE NOT contains(tableName, "$")
GROUP BY tableName

The case clause skips entries whose count is zero, and otherwise weights each entry by the element count, dividing the result by the total count.

Example E-3 Key Distribution Statistics

To estimate the number of elements in each index, perform the following query:

SELECT tableName,
       indexName,
       sum(count) AS count
FROM SYS$TableStatsIndex
WHERE NOT contains(tableName, "$")
GROUP BY tableName, indexName

Example E-4 Key Distribution Statistics

To estimate the average key size for each index, perform the following query:

SELECT tableName,
    indexName,
    CASE WHEN sum(count) = 0
        THEN 0
        ELSE sum(avgKeySize*count)/sum(count)
    END AS avgKeySize
FROM SYS$TableStatsIndex
WHERE NOT contains(tableName, "$")
GROUP BY tableName, indexName