Difference in num_rows and COUNT function. [message #572203] |
Fri, 07 December 2012 06:41  |
ajaykumarkona
Messages: 399 Registered: August 2010
|
Senior Member |
|
|
Hi Experts,
SELECT COUNT(*) FROM APP.Big_table; Number of record--222653402
This is a big table to get the it's taking 8 minutes time,
so that I tried the following SQL to get the count quickly.
SELECT num_rows FROM all_tables WHERE table_NAME='BIG_TABLE'
and owner='APP'; --Number of records 237213998
But I got the different count for the two SQLs.
Why there is a difference in the count.
Which one is correct.
Please help me.
Thanks in advance.
|
|
|
|
Re: Difference in num_rows and COUNT function. [message #572205 is a reply to message #572204] |
Fri, 07 December 2012 06:50  |
cookiemonster
Messages: 13967 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Select count(*) is correct. It always is.
Num_rows is only set when statistics are gathered, so unless the number of rows in the table never changes, it's usually wrong.
Num_rows should be regarded as a rough estimate of the number of rows.
|
|
|