select count(*) takes 2 hours

[ronnie]

Hi,

It takes around 2 hours to do a select count(*) om a table having 90000 records.

Why???

Is it because of the HWM.
How can i reset it if thats the case

[Shestakov]

Does this table has primary key?
If does then:

select count(pk_field) om from ur_table;

dont forget analyze this table periodicaly.

[tamilselvan]

Yes, Check the HWM of the table.

[svk]

HWM need not be the only reason.
If you really wish to see the effect of HWM, calsulate an 'estimated' size of this table and compare it with its actual size. If the difference is too much, then HWM is the reason.

Otherwise, there can be so many other reasons.....your server power, load on the server/database,...

[gandolf989]

It could also be that you db buffer blocks is way to small, and Oracle is doing a disk sort plus reading from the hard drive. Look at how much memory you are giving Oracle. Your sever should have at least 256 MB RAM if not 512 MB RAM.

Conerning stats, either you are commited to analyzing tables and keeping the stats current or you should just delete all of the stats and use rule based optimization. You might try setting optimizer_method to rule and restarting the database and see if that helps.

[pando]

well... rule wont help at all since it will force a FTS, if you are in 8i and you analyze your table Oracle will perform INDEX FAST FULL SCAN for a select count(*) which is much much faster

But anyway, counting 90000 rows takes 2 hours it's too much, even in my PC with an SGA of 40MB that can be done between 10~15 minutes (I am the only user of course)

To find out HWM without analyzing use unused_space procedure in dbms_space package provided by Oracle

[Edited by pando on 03-22-2002 at 06:52 PM]

[dbafreak]

You may also try COUNT(ROWID) instead. I am sure there will be significant difference.

[julian]

Originally posted by dbafreak
You may also try COUNT(ROWID) instead. I am sure there will be significant difference.

And I am sure that if Jurij notices your statement, you will get an answer to that "significant difference" :-))

[dbafreak]

Julian, correct me if I am wrong. I believe COUNT(rowid) works very fast compared to COUNT(*). Excuse me if I mentioned anythig wrong.

[gpsingh]

run a trace on it and find out where are the waits.
select count(pk) should be faster since it gets everything from index.
select count(*) is equivalent to count(1) where it scans all the rows of the table upto HWM.
All do sorts , so be sure ur sort area size is good enough.