Re: Counting Records

From: Anurag Varma <avarmadba.skipthis_at_yahoo.com>
Date: Sun, 12 Oct 2003 16:50:38 GMT
Message-ID: <yDfib.3287$zw4.2515@nwrdny01.gnilink.net>

"Tom Miskiewicz" <miskiewicz2_at_yahoo.com> wrote in message news:bmbt0b$it9$1_at_news.gatel.net...
> Hello!
>
> What is the most efficient way of counting records in a table?
> I want to count records in a table that match my query. How do I do that?
> I guess that select count(1) from table_name where fieldA='something'
isn't
> the most elegant thing I can do?
>
>
> Thanks
> Tom
>
>

Well it is. Depends on what the exact requirement is. If you want to find out if there are *any* records in the table which have that value, then yes, there are more elegant ways to do it.

For example:

select count(1) from dual
where exists (select 1 from table_name where fielda='something') /

would get you the results faster. It will return 0 if no records exist and 1 if 1 or more than 1 records exist.

You can also look into materialized view aggregates, however, I'm not if it will help in your case. Also, depends on what your env is so maybe if you run the query frequently, bitmap/normal index on fieldA would help.

Anurag Received on Sun Oct 12 2003 - 11:50:38 CDT