Re: Count(*) is very slow

> snip
> Sorry, but it really takes 36 seconds (3 seconds CPU-time on the server !!)
> after analyzing the table. There is, except of the operating system, no
> other proccess on the server. On the same machine the results with MS SQL
> Server, Centura, Informix and DB/2 - dbms where never so bad.
>
> The database I've used, was created with default values. Do you know, which
> parameter can tune this query ? Is it better, to work with a clustered
> primary key ?
>
>
> --
> Andreas Kyritz
> AKyritz_at_easy-soft-dresden.de

1. select count(*) will always do a full-table scan (FTS).

   The main reason for poor performance has nothing to do with init.or parameters, but is simply due to the physical organisation of the table. If the table is sparse (i.e. has rows spread across the pages allocated for, say, 10M rows), then the FTS takes the same time (approximately) as if it were full.
Check how much space is allocated and, if necessary, re-organise the table into a smaller table-space.

2) Using count(PrimaryKey) *can* result in an index scan but, again, the *whole* index segment must be scanned and this may be no faster (and could even be slower) than scanning the data segment.

In either case, the problem may be worse if the tablespace is physically fragmented across the disk (and, no, I do *NOT* mean having multiple Oracle extents: this makes almost no mesurable difference). This is likely to depend on your OS and/or disk access method.

HTH Chrysalis

--
FABRICATE DIEM PVNC
("To Protect and to Serve")
Motto of the Night Watch
Terry Pratchett - "Guards, Guards" Received on Tue Oct 20 1998 - 12:52:19 CDT