Re: select count(*) optimization

From: Guy Harrison <gharriso_at_werple.net.au>
Date: 1997/03/10
Message-ID: <01bc2d28$21f6dea0$3f2a11cb@gharriso>#1/1

jim nash <jimnash_at_sprintmail.com> wrote in article <331D9E0D.4B3E_at_sprintmail.com>...
> Anyone know how to fool the optimizer into using a
> primary key access path to count all rows in a table?

The cost based optimizer might use the primary key index if you tried "select count(primary_key_column) from table. Otherwise, you could use a the index hint: for instance,

select /*+ index(emp pk_emp) */ count(empno) from scott.emp

---

Guy Harrison
gharriso_at_werple.net.au
http://werple.net.au/~gharriso
(61) 0419 377 964

jim nash <jimnash_at_sprintmail.com> wrote in article <331D9E0D.4B3E_at_sprintmail.com>...
> 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.
>
> - Jim
>
>
Received on Mon Mar 10 1997 - 00:00:00 CST