OraFAQ Forum: SQL & PL/SQL » Different result of SELECT COUNT(*)

Home » SQL & PL/SQL » SQL & PL/SQL » Different result of SELECT COUNT(*)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Different result of SELECT COUNT(*) [message #306995]

Mon, 17 March 2008 08:24

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.

Report message to a moderator

Re: Different result of SELECT COUNT(*) [message #306997 is a reply to message #306995]

Mon, 17 March 2008 08:26

Maaher
Messages: 7065
Registered: December 2001

Senior Member

'1-MARCH-2008' is a string. Convert it to a date using TO_DATE and try again.

MHE

Report message to a moderator

Re: Different result of SELECT COUNT(*) [message #306999 is a reply to message #306997]

Mon, 17 March 2008 08:32

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?

Report message to a moderator

Re: Different result of SELECT COUNT(*) [message #307000 is a reply to message #306999]

Mon, 17 March 2008 08:36

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You are not alone on the database.
Someoneelse is working.

Regards
Michel

Report message to a moderator

Re: Different result of SELECT COUNT(*) [message #307002 is a reply to message #307000]

Mon, 17 March 2008 08:39

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..

Report message to a moderator

Re: Different result of SELECT COUNT(*) [message #307004 is a reply to message #307002]

Mon, 17 March 2008 08:44

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Each time you query it recomputes the count.
There is no relation with statistics.

Regards
Michel

Report message to a moderator

Re: Different result of SELECT COUNT(*) [message #307006 is a reply to message #306995]

Mon, 17 March 2008 08:48

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???

Report message to a moderator

Re: Different result of SELECT COUNT(*) [message #307009 is a reply to message #307006]

Mon, 17 March 2008 08:58

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

In any case, with or without "where" clause, it repeats the computation.

Regards
Michel

Report message to a moderator

Previous Topic:	Advance Shipment Notice Import program
Next Topic:	Bulk Collect Issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 14:44:21 CST 2024