Re: alternative to select count(*) : do i need to ?

legarema_at_uqtr.uquebec.ca wrote in message <385A5E94.9472D42C_at_uqtr.uquebec.ca>...
>Hi all,
>
>I am mostly programming in Pro*C 8.0.5.0.0 with an
>oracle 8.0.5 database (on aix 4.2.1.0). I have read
>some posts on this forum and on some web pages that
>are saying that it's bad programming to do a :
>
>select count(*) from mytable where criteria
>
>Most of the time I use count(*) to know if there is
>zero, one or more than one record matching my criteria.
>
>Is it really "bad programming" ?
>If yes, is why ?
>What are the alternatives ?
>
>Thanks a lot,

Another technique that I've found to be handy, in general Oracle SQL:

If your WHERE clause does selection only on an indexed field, do a count of the same field. Eg:

SELECT COUNT(Key1) FROM table WHERE Key1 > value;

In most cases that I've seen, Oracle is smart enough to just use the index (it never even reads the data rows themselves). This can be much faster than COUNT(*).

--Barry Received on Sun Dec 19 1999 - 04:18:29 CET