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

oracle count
Eli Courtwright picture Eli Courtwright · Dec 3, 2009 · Viewed 185.2k times · Source

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?

Answer

Jeffrey Kemp picture Jeffrey Kemp · Dec 4, 2009

If you want just a rough estimate, you can extrapolate from a sample:

SELECT COUNT(*) * 100 FROM sometable SAMPLE (1);

For greater speed (but lower accuracy) you can reduce the sample size:

SELECT COUNT(*) * 1000 FROM sometable SAMPLE (0.1);

For even greater speed (but even worse accuracy) you can use block-wise sampling:

SELECT COUNT(*) * 100 FROM sometable SAMPLE BLOCK (1);

Related questions

Select count(*) from multiple tables

How can I select count(*) from two different tables (call them tab1 and tab2) having as result: Count_1 Count_2 123 456 I've tried this: select count(*) Count_1 from schema.tab1 union all select count(*) Count_2 from schema.tab2 But all I have …

Read More
How to count the number of occurrences of a character in an Oracle varchar value?

How can I count number of occurrences of the character - in a varchar2 string? Example: select XXX('123-345-566', '-') from dual; ---------------------------------------- 2

Read More
Counting number of joined rows in left join

I'm trying to write an aggregate query in SQL which returns the count of all records joined to a given record in a table; If no records were joined to the given record, then the result for that record should …

Read More