Re: select count(*) optimization

Thomas J. Kyte (tkyte_at_us.oracle.com) wrote:
: 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

If you are using Oracle7 with the cost based optimizer adding a where clause as suggested may not work. Instead you should add a optimizer hint:

select /*+ INDEX (BLAH BLAH_PK) */ COUNT(*) FROM BLAH;

Rgds
Steinar Heggelund Received on Fri Mar 07 1997 - 00:00:00 CST