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

You can see for yourself what the optimizer is doing with the various types of 'counts' by doing an 'explain plan'. For example, if you're using 7.3.3 Enterprise and are using bitmap indexes, the optimizer will go to the highly compressed bitmap rather than do a full tablescan if you ask for "Select count (*) from <table>;

Explain Plan is documented, I believe in the Tuning Guide.

Craig Brady
Talisman Technologies Inc

tmcguiga_at_my-dejanews.com wrote in message <77fb4c$jn0$1_at_nnrp1.dejanews.com>...
>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
Received on Thu Jan 14 1999 - 18:51:07 CST