Re: select count(*) optimization

From: Scott T. Johnson <sjohnson_at_ibm.net>
Date: 1997/03/11
Message-ID: <01bc2e19$d730f020$f44a48a6@7l603>#1/1

If you run 'select count(primarykey) from blah through explain plan, you'll find that it will still do a full table scan.

Try this:
select count(*) from blah
where primarykey > 0

Oracle can now use the index to count. This solution assumes that your primary key
is a number and is > 0. I've also been able to to use character string keys as well.

---

Scott Johnson
Oracle DBA
MCI Systemhouse
Eastern Region

g.r.s. deisz <g.r.s.deisz_at_ptt-telecom.unisource.nl> wrote in article <5g0efj$3mr_at_pwxl01.telecom.ptt.nl>...
> In article <331D9E0D.4B3E_at_sprintmail.com>,
> jim nash <jimnash_at_sprintmail.com> wrote:
> >Anyone know how to fool the optimizer into using a
> >primary key access path to count all rows in a table?
> >e.g.
> > select count(*) from blah;
> >
> >In my case the rows are very long, the prim key is very short,
> >and the full table scan brings the machine to its knobby knees.
>
> The following might help:
>
> select count(primary key) from blah;
>
> (I suppose NULL values are not allowed in your PK, and you
> have an index on PK)
>
> Stefan.
>
>
> ---------------------------------------------------------------------
> Name :G.R.S. Deisz
> Phone :+31-50-5855954
> E mail :G.R.S.Deisz_at_PTT-Telecom.Unisource.NL
> DISCLAIMER:This statement is not an official statement from, nor
> does it represent an official position of, PTT Telecom BV.
>
Received on Tue Mar 11 1997 - 00:00:00 CST