What Happens When You Run SELECT COUNT(*) on a Large DB2 Table?

To better understand what happens behind the scenes when executing a SELECT COUNT(*) query, I analyzed the two largest tables in our DB2 environment. The goal was to determine whether DB2 counts rows by scanning the table itself or by leveraging one of the available indexes.

Scenario 1: Indexes Larger Than the Table

Table_1 had two indexes, both of which were poorly optimized. A large number of table columns had been included in the indexes, resulting in index sizes that exceeded the size of the base table.

Table_1 Statistics

Metric	Value
Row Count	120,632,075
Average Row Length	95 bytes
Used Pages	3,093,397
Space Utilized	13,631,040 KB

 
Index Statistics
 

Index	Allocated Space (KB)	Leaf Pages	Levels	Avg. Key Length
Index_1	15,730,560	3,655,518	6	101
Index_2	13,633,200	3,165,273	5	84

 
DB2 Optimizer Decision
 
When the following query was executed:
 
SELECT COUNT(*) FROM Table_1 WITH UR;
 
DB2 evaluated the available statistics and determined that scanning the table space was more efficient than scanning either index. Since both indexes were larger than the table itself, the optimizer chose a table scan to perform the row count.
 
Scenario 2: Index Smaller Than the Table
 
Table_2 contained seven indexes. Unlike the first scenario, several of these indexes were significantly smaller than the table space, making them potential candidates for an index-only count operation.
 
Table_2 Statistics

Metric	Value
Row Count	1,006,831,073
Average Row Length	30 bytes
Used Pages	8,078,681
Space Utilized	32,506,560 KB

 
Index Statistics
 

Index	Allocated Space (KB)	Leaf Pages	Levels	Avg. Key Length
Index_1	30,412,080	7,507,065	5	18
Index_2	30,412,080	7,507,065	5	18
Index_3	30,412,080	7,507,065	5	18
Index_4	7,341,840	1,726,625	4	5
Index_5	7,341,840	1,731,545	4	11
Index_6	30,412,080	7,507,065	5	18
Index_7	39,849,840	9,773,348	5	24

 
DB2 Optimizer Decision
 
When the following query was executed:
 
SELECT COUNT(*) FROM Table_2 WITH UR;
 
DB2 analyzed the table and index statistics and selected Index_4 as the access path for the count operation. Because Index_4 was substantially smaller than the table space and contained fewer pages to scan, counting the index entries was more efficient than scanning the entire table.
 
Key Takeaway
 
When executing SELECT COUNT(*), DB2 does not automatically scan the underlying table. Instead, the optimizer evaluates available statistics and chooses the most cost-effective access path.

• If all indexes are as large as—or larger than—the table, DB2 may perform a table space scan.

• If a smaller, efficient index exists, DB2 may perform an index scan and count index entries instead.