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

I found the best performing COUNT function was with the following:

SELECT COUNT(1) FROM table_name;

Try it.

Alex.

In article <8euhlu$pfd$1_at_nnrp1.deja.com>,   tjmxyz_at_my-deja.com wrote:
> 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.
>

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