Home » SQL & PL/SQL » SQL & PL/SQL » Difference in num_rows and COUNT function. (Oracle 11g)
Difference in num_rows and COUNT function. [message #572203] Fri, 07 December 2012 06:41 Go to next message
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 #572204 is a reply to message #572203] Fri, 07 December 2012 06:44 Go to previous messageGo to next message
Littlefoot
Messages: 19636
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Try to gather statistics, and then run the second query again.
Re: Difference in num_rows and COUNT function. [message #572205 is a reply to message #572204] Fri, 07 December 2012 06:50 Go to previous message
cookiemonster
Messages: 10961
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.
Previous Topic: Schema name
Next Topic: types of pl/sql value or numric error.
Goto Forum:
  


Current Time: Tue Sep 23 12:14:24 CDT 2014

Total time taken to generate the page: 0.15811 seconds