Re: Count(*) is very slow

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Tue, 20 Oct 1998 07:40:55 +0200
Message-ID: <70h7sq$1mv$1@hermes.is.co.za>

Andreas Kyritz wrote in message <70fbcl$iu1$1_at_news.ipf.net>...
>
>does anybody know, why a
> SELECT COUNT(*) FROM ATABLE
>Statement is so slow ? On a table with 250.000 rows it takes more then 30
>seconds ?!

I've read several responses to this and everyone seems to suggest that an analyze will make it faster. I'm not so sure. Does Oracle not always use a FTS when doing a SELECT count(*)? I use to run SELECT count(*) on large tables to test PQ - and PQ only kicks in with FTS (at least with 7.x).

The only reason IMHO why a SELECT count(*) may be faster when an analyze has been done, is for PQ processing. If an estimate stats has been made, Oracle spreads the processing better between the PQ processes (it uses rowid per select to give each PQ a chunk of the table to process). If there are no stats, Oracle often give all the work to 1 or 2 PQs only while the rest sit idle.

Or am I trying to drink from an empty Tennessee whiskey bottle here?

regards,
Billy Received on Tue Oct 20 1998 - 00:40:55 CDT