Examples: Key Distribution Statistics

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

Example 7-4 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 7-5 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 7-6 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 7-7 Size of the tables

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

SELECT tableName,TableSize,
tableSizeWithTombstones FROM SYS$TableStatsPartition 
WHERE NOT contains(tableName,"$");

For tables without tombstones (For example, non-multi-region tables, system tables, etc,), the metric tableSizeWithTombstones would be the same as the metric tableSize in the system table. The difference between the two metrics is the total storage size of tombstones in the table.

Example 7-8 Determine the size before a table export

You want to export a gigantic table to another place (another disk, kvstore, etc.), You can use tableSize to determine the size of the data. You can determine the size of live data without tombstone for that table since export does not copy tombstones.

SELECT tableName,TableSize FROM SYS$TableStatsPartition 
WHERE NOT contains(tableName,"$");