Re: Count(*) is very slow

If you have a field with less than 10 distinct values, try putting a bitmapped index on that field (say COL1) and then doing a count of it. Should be subsecond response!

YOu might have to use a hint:

Select count(COL1) /*+ INDEX (MY_TABLE COL1) */ from MY_TABLE

• You might want to check the syntax of the hint. **

-Good luck,
-Frank
In article <70ff74$qol$1_at_as4100c.javanet.com>,   "George Meltser" <meltser_at_aigfpc.com> wrote:
> I have a table with 3,5 mln rows and after it's analyzed it takes 3 seconds
> to do
> a count(*), so please stop blaming dbms.
>
> Andreas Kyritz wrote in message <70fbcl$iu1$1_at_news.ipf.net>...
> >Hallo,
> >
> >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 ?!
> >
> >The table has a primary key and the statistics were computed before
> >executing this Statement.
> >It makes no difference using COUNT(PRIMARY_KEY) instead of COUNT(*).
> >It seems that the server is doing a full-table-scan !
> >
> >The server is a P200 with 96MB RAM, NT 4.0 SP3 and Oracle 8.0.5.
> >
> >I've never seen a DBMS, which takes so much time for such a simple query.
> >
> >Thanks for your help
> >
> >--
> >Andreas Kyritz
> >AKyritz_at_easy-soft-dresden.de
> >
> >
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Oct 21 1998 - 19:36:50 CDT