Oracle and MS-SQL-like row counts

My applications often need an accurate but perhaps imperfect row count. Billion-row tables often get frequent writes, so "perfect" is a matter of definition and timing.

Microsoft SQL keeps very good metadata, allowing a query such as:

SELECT SUM(rows) FROM sys.partitions
WHERE object_id = object_id('TABLE_NAME') 
AND index_id < 2;

(There are other methods with different metadata). This provides a nearly perfect row count, perhaps omitting certain in-progress transactions or other minutiae. Such techniques work for all but the most demanding situations and are practically instantaneous.

In Oracle, we can use statistics:

SELECT num_rows
FROM all_tables
WHERE table_name = 'TABLE_NAME'

This is unreliable because, if statistics have been gathered at all, they are often out of date depending on DBA policy.

I can sacrifice significant accuracy for some improvement in speed using sampling:

SELECT COUNT(*) * 1000 rc_sampled FROM lot_size SAMPLE(.1) SEED(42)

However this is inaccurate by design, still quite slow (115 seconds on a 500M row test), and is almost useless when the application does not already have a row count estimate. Running that SQL on a table with 800 rows is like asking, "what is the price of that candy bar, give or take $75.00?")

Does Oracle provide a practical means of getting an accurate, fast row count for arbitrary tables, such as that which Microsoft SQL and others provides?

SELECT COUNT() 1000 rc_sampled FROM lot_size SAMPLE(.1) SEED(42)


…still quite slow (115 seconds on a 500M row test)…

You will find that SAMPLE BLOCK is a great deal faster because it does a tiny fraction of the IO that SAMPLE does. Of course if the data isn't evenly distributed it will cause the result to be less accurate, but this can be more than offset by increasing the sample size.

…but…


In Oracle, we can use statistics…This is unreliable because, if statistics have been gathered at all, they are often out of date depending on DBA policy.

This is the real problem. Out of date stats cripple the CBO and are therefore "a bad thing". You likely need to change your DBA policy.

StackExchange Database Administrators Q#166377, answer score: 3