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

I did this a while back for me the best performing was:

select /*+index_ffs(the_Table)*/ count(*) cnt from the_Table WHERE ID > -999999 ;

This is assuming you will never have negative values in you PK.

HTH
> >
> > 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri May 05 2000 - 00:00:00 CDT