Re: select count(*) optimization

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1997/03/07
Message-ID: <33229391.32654264@nntp.mediasoft.net>#1/1

On Wed, 05 Mar 1997 08:23:41 -0800, 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.
>
>- Jim
>

if, for example, the primary key was a number > 0, you could:

select count(*) from blah where pk_column > 0;

this would cause an index range scan. You can use this with other datatypes and values as well.... Use a where clause on the column and this will typically cause it to use an index..
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com

• Check out Oracle Governments web site! ----- Follow the link to "Tech Center" and then downloadable Utilities for some free software...

  ---

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Fri Mar 07 1997 - 00:00:00 CST