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