Software Hero Worship: Table size frequencies

Friday, September 11, 2009

Table size frequencies

Here's an interesting query to run on your Oracle database:

Here's what I get on the main application I've been working on the last several years:


ROW_COUNT               NUM_TABLES
----------------------  ----------
1-10                            36
10-100                          22
100-1000                        22
1000-10000                      22
10000-100000                    22
100000-1000000                  33
1000000-10000000                20
10000000-100000000               2
100000000-1000000000             3
1000000000-10000000000           1
                               183

1 comment:

AnkhDjedSeneb said...: Great post.

Here's a modified version that displays tables with zero rows at the end:

WITH r AS (
SELECT
CASE num_rows
WHEN 0 THEN -1
ELSE TRUNC(LOG(10, num_rows)) END p
FROM all_tables
WHERE owner = 'CISADM'
),
s AS (
SELECT
CASE p
WHEN -1 THEN '0'
ELSE TO_CHAR(POWER(10, p), '999,999,999,999') || ' -' || TO_CHAR(POWER(10, p+1), '9999,999,999,999') END row_count
FROM r
)
SELECT row_count, COUNT(*) num_tables
FROM s
GROUP BY ROLLUP(row_count)
order by row_count
/; May 18, 2015 at 10:50 AM