Home » SQL & PL/SQL » SQL & PL/SQL » Different result of SELECT COUNT(*)
Different result of SELECT COUNT(*) [message #306995] Mon, 17 March 2008 08:24 Go to next message
juicyapple
Messages: 92
Registered: October 2005
Member
Hi, I executed a table count with condition

SELECT COUNT(*) FROM TABLEA WHERE DTRECORD < '1-MARCH-2008'


The query returns different result values for each time I execute it and I found that the value keeps increasing.

1st result : 17036986
2nd result : 17037903
3rd result : 17038309

Any idea??? DTRECORD is in datetime datatype and it refers to current date time, so I just query the history records and it shouldn't has changes. Please advise.
Re: Different result of SELECT COUNT(*) [message #306997 is a reply to message #306995] Mon, 17 March 2008 08:26 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
'1-MARCH-2008' is a string. Convert it to a date using TO_DATE and try again.

MHE
Re: Different result of SELECT COUNT(*) [message #306999 is a reply to message #306997] Mon, 17 March 2008 08:32 Go to previous messageGo to next message
juicyapple
Messages: 92
Registered: October 2005
Member
I tried that, the problem is still the same

SELECT COUNT(*) FROM TABLEA WHERE DTRECORD < to_date('2008/03/01', 'yyyy/mm/dd') 


any idea?
Re: Different result of SELECT COUNT(*) [message #307000 is a reply to message #306999] Mon, 17 March 2008 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You are not alone on the database.
Someoneelse is working.

Regards
Michel
Re: Different result of SELECT COUNT(*) [message #307002 is a reply to message #307000] Mon, 17 March 2008 08:39 Go to previous messageGo to next message
juicyapple
Messages: 92
Registered: October 2005
Member
I have one question, is that any possibility when a table size is huge, or because there are alot transactions on the table, it affects the select count(*) result even I am selecting on history data? I heard from someone it seems because the statistic is still updating...not very sure what is that..
Re: Different result of SELECT COUNT(*) [message #307004 is a reply to message #307002] Mon, 17 March 2008 08:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Each time you query it recomputes the count.
There is no relation with statistics.

Regards
Michel
Re: Different result of SELECT COUNT(*) [message #307006 is a reply to message #306995] Mon, 17 March 2008 08:48 Go to previous messageGo to next message
juicyapple
Messages: 92
Registered: October 2005
Member
meaning that my case is because of data with DTRECORD < 1-March-2008 is still pumped in to TABLEA???
Re: Different result of SELECT COUNT(*) [message #307009 is a reply to message #307006] Mon, 17 March 2008 08:58 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In any case, with or without "where" clause, it repeats the computation.

Regards
Michel
Previous Topic: Advance Shipment Notice Import program
Next Topic: Bulk Collect Issue
Goto Forum:
  


Current Time: Sat Dec 03 20:37:28 CST 2016

Total time taken to generate the page: 0.08360 seconds