Re: count(*) ?

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