Table size frequencies

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

	WITH r AS (
	SELECT TRUNC(LOG(10, CASE WHEN num_rows = 0 THEN 1 ELSE num_rows END)) p
	FROM all_tables
	WHERE owner = '(your schema)'
	),
	s AS (
	SELECT TO_CHAR(POWER(10, p), '999,999,999,999') || ' -' || TO_CHAR(POWER(10, p+1), '9999,999,999,999') row_count
	FROM r
	)
	SELECT row_count, COUNT(*) num_tables
	FROM s
	GROUP BY ROLLUP(row_count)

view raw table-size-frequencies.sql hosted with ❤ by GitHub

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