Re: Can someone please straighten me out on the COUNT function?

In article <77fb4c$jn0$1_at_nnrp1.dejanews.com>,   tmcguiga_at_my-dejanews.com wrote:
> I've read such conflicting posts such as --
>
> (a) "Count (*) is also quicker because it does not need to query
> the underlying data to determine whether to include the value
> or not"
>
> (b) "For better performance, Count(*) should not be used because the
> Oracle must first resolve all column names in the table which is a
> step that may not be required if ur just counting rows. Generally
> select count(indexed column) and select count(rowid) are faster."
>
> So no wonder I've no solid understanding of it! What I'd like to know is --
> 1. What does COUNT(*) do and have Oracle optimised it?
> 2. Which is better for counting rows --
> a. COUNT(*)
> b. COUNT(1)
> c. COUNT(rowid) or
> d. COUNT(<indexed column>) ?
> 3. What part do NULLable columns play in all this?
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>

Tom Kyte will have the best information, but I'll chime in with these comments:

For #1, I believe that Oracle's parser recognizes the count(*) and optimizes it. So in general you do not have to fear count(*) on a single table.

For # 2 (Tom please correct me here) I understand it that if the table statistics are current, it can obtain the count there (a single lookup). Note the answer to #3 and items a, b, c are essentially equal, but d may potentially come up with a different answer (if the index allows nulls) and so may come up with a different execution time.

For the answer to #3, look in your oracle documentation,

    COUNT(*) returns the total number of rows and     count(column) returns number of non-null values So that's where NULL columns play. Those two results can be very different.

I think the reason there are conflicting reports is that COUNT(*) really isn't that slow. Only worry about it when you your application needs that information faster than count(*) gives it.

Is counting a table really a performance bottleneck for you? If it is, I would consider a different design for that portion of the application.

Ed Prochak
Magic Interface, Ltd.
440-498-3702

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Jan 14 1999 - 03:02:30 CST