Re: Slow select count(*)

A copy of this was sent to kasthuri_at_viisage.com (if that email address didn't require changing) On Tue, 02 Nov 1999 19:20:12 GMT, you wrote:

>Hi,
>
> I am running Oracle 7.2.3 on Dec OSF 4.0d. There is a table t1 (pan
>varchar(18), image long raw, len int)
>A select count(*) on this tables takes a long time to return. This table
>is being updated constantly and is a production database.
>
>there is a primary key constraint on the PAN on a index_tablespace.
>
>Any ways to improve, Please suggest
>
>Thanks & Regards,

1. use the CBO which will see the primary key and use it to perform the count(*) (given that the table was analyzed)
2. use a hint select /*+ index( index_name t1 ) */ count(pan) from t1
3. trick the RBO into using an index with a where clause:

   selec count(pan) from t1 where pan > chr(0); (greater then ascii NUL)

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Nov 02 1999 - 13:49:28 CST