Re: how to speed up a "select count(*) from ..."

In article <3912bb1a$0$63041_at_news.execpc.com>,   "K. Friday" <kfriday_at_execpc.com> wrote:
> Hi,
>
> Does the statement have a "where" clause ? The index will only help if
> you're subsetting the table. If what you're doing is a count of all
 rows in
> the table it will do a full table scan (in fact you generally want it
 to)
> regardless of available indexing.
>
> Ken F.
> "Paolo Polce" <java.group_at_bricabrac.it> wrote in message
> news:8eu49s$4676$1_at_stargate1.inet.it...
> > Hello,
> >
> > I have a table with 40.000 records. There was no primary key on
 the
 table
> > (!) and a "select count(*) from mytable" took about 15 sec.
> > So, now, I've created a primary index on mytable.id field...
> >
> > Well... it still takes 15 sec to count the records... :-(
> > May someone help me?
> >
> > Thank you.
> > Paolo.
> >

Change your select to be like:
select /*+ INDEX_FFS(label PK_NAME) */

       count(key)
from the_Table label;

This should cause Oracle to use the PK to count the number of rows via the index. You can try it without the hint first and see what the explain plan says. Then you can add the hint if necessary.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.