Previous Next JavaScript must be enabled to correctly display this content

## Examples: Key Distribution Statistics

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

Example 5-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 5-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 5-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 5-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 5-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,"$");