Re: How to improve this query?

"Tom Miskiewicz" <miskiewicz2_at_yahoo.com> wrote in message news:<bpt2l6$91h$1_at_news.gatel.net>...
> Hi!
>
> Thanks for your reply.
>
> > If all you want to test for is not null and your column is indexed then
> you
> > should use
> > select count(*)
> > from table
> > where col is not null;
>
> I have been told, that there is no difference between count(*) and count(1).
> Is that right?
>
> Thomas

Correct. However count(field_Name) will count how many rows in field_Name that are not null.

Actually, the Oracle 9i sql reference guide (under COUNT syntax) is a bit misleading. It points out that if you use an asterisk, you will get a count of all rows including duplicates and nulls, but if you use an expression, you get just rows with values in whatever the expression evaluates to. Somewhere along the line I picked up the myth that count(1) was marginally faster than count(*) because it didn't have to parse all column names rather than "1" to the first column - such a trivial thing, it had never ocurred to me to check. But that is obviously wrong (I suppose a confusion with order by), and I think the manual is also wrong in implying that count(*) and count(1) is different (unless 1 is not considered an expression???).

So, is there a doc error?

jg

--
@home.com is bogus.
http://www.searchengineguide.com/lloyd/2003/1125_bl1.html