Re: count(*) ?

On Jan 15, 2:50 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Jan 15, 9:34 am, nick <cupofjava1..._at_aol.com> wrote:
>
> > I understand that when you feed the count( ) function an asterisk as
> > an argument it runs
> > slower than if you use a column name as an argument. Can someone tell
> > me why this is so?
>
> > Thanks
>
> Count on a column (or an expression) does not count nulls.  Count with
> an asterisk (or some non-null constant) counts all rows even if all
> columns are null.
>
> So you can try to use nulls in your data to prove it either way.
>
> Note also that Oracle will attempt to satisfy a count from an index,
> so if you have an index that is small and has some nulls, it might be
> faster than what Oracle might look at for count(*), and one without
> nulls might be faster still.
>
> Then there are analytics...
>
> Count what you need to count!
>
> I think at one time there was a myth that the asterisk meant Oracle
> had to spend time parse all the column names for the table or
> something like that.
>
> There may also be an ability with the asterisk to not have to look at
> all the data in a block, as the block header knows how many rows it
> contains.  So small rows with little pctfree might count faster.
>
> See what the plan says, and tracing variants may be interesting.
>
> jg
> --
> @home.com
> "Aim towards the Enemy" - Instructions printed on U.S. Rocket
> Launcher

Just a comment on Joel's wording for any less experienced readers, "if you have an index that is small and has some nulls, it might be faster than what Oracle might look at for count(*), and one without nulls might be faster still. "

For a normal index Oracle does not index table rows where all indexed columns (defined to a single index) are null. In the case of an index built on a single column nulls are not indexed at all.

The CBO chooses how count() queries are performed just like any other query based on the estimated cost of performing the query.

Bitmap indexes do index null values.

HTH -- Mark D Powell -- Received on Tue Jan 15 2008 - 15:31:59 CST