get actual row count via dba views 2

[sandtek]

Hi All,

We are working on a DWH environment and every time we need to get the actual number of rows from a table. But the problem is the table has large number of rows and
doing a select count (*) from huge_table is taking tooooo long.
Is there any way to get the actual or atleast closed to the actual number of rows from a table via DBA views aside from dba_tables.num_rows or dba_indexes.num_rows ?
Thanks!

 

[jimirvine]

Well, if that table has a primary key on it, you might consider counting on that instead. Oh, and if anyone suggests count(1) .... well, just don't

 

[Dagon]

Providing you do a regular estimate statistics on the tables (which doesn't take too long), the counts in dba_tables should be reasonably accurate.

 

[jimirvine]

@Dagon, true, but the op specifically asked if it could be done without looking at the num_rows column.

 

[Dagon]

Yes, I was aware of that. My response was based on the assumption that he had some objection to using the statistical information in dba_tables e.g. that it was not sufficiently up to date. In all other respects, it would be the ideal solution and I certainly don't know any other way getting row counts other than literally counting them.

 

[taupirho]

Just putting this out there, but MV anyone ?

It may give the appearance of being faster but agree with dagon, if you want to count something you have to count them !!


In order to understand recursion, you must first understand recursion.

 

[SantaMufasa]

Providing you do a regular estimate statistics on the tables (which doesn't take too long), the counts in dba_tables should be reasonably accurate.

Sandtek, Listen to, and follow, Dagon's advice...it is "Oracle Wisdom".[ul][li]If you have up-to-date (even estimated) statistics, you will have a remarkably close tally of number or rows for a table, and gathering estimated statistics takes a fraction of the time of a full table scan.[/li][li]If you do not have up-to-date statistics, you have bigger problems: Oracle's (cost-based) optimizer will not behave properly and all of your queries against tables with stale statistics will exhibit sub-par performance.[/li][/ul]

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[sandtek]

Thanks to you all.

 

[LKBrwnDBA]

If you have set "monitoring" on, you could combine the num_rows from the table with the data from dba_tab_modifications to get a closer result.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[sem]

I'm also for materialized view: gathering statistics is far more complex and resource consuming operation than [fast] snapshot refresh, that is almost immediate.

Regards, Dima

http://seminihin.narod.ru