Faster alternative in Oracle to SELECT COUNT(*) FROM sometable

Question

I've notice that in Oracle, the query

SELECT COUNT(*) FROM sometable;

is very slow for large tables. It seems like the database it actually going through every row and incrementing a counter one at a time. I would think that there would be a counter somewhere in the table how many rows that table has.

So if I want to check the number of rows in a table in Oracle, what is the fastest way to do that?

user_8738942 · Answer

This worked well for me

select owner, table_name, nvl(num_rows,-1) 
from all_tables 
--where table_name in ('cats', 'dogs')
order by nvl(num_rows,-1) desc

from https://livesql.oracle.com/apex/livesql/file/content_EPJLBHYMPOPAGL9PQAV7XH14Q.html

Faster alternative in Oracle to SELECT COUNT(*) FROM sometable

11 Answers