Re: Performance off "count(*)"

It was only when reading Greg's note that the real importance of the distinction between the 'block' header and 'row' header struck me. If Oracle moved the flag byte from the row header into the "row index" in the block header, then the count(*) could be highly optimized - but that's probably far too much risky coding effort for little reward.

But the 'optimization' of count(col) to count(*) could simply be that count(*) can bound back and fore between the "row index" and the row header it's pointing at, while count(col) has to walk along each row to find the column and check it.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- From: "Tanel Poder" <tanel.poder.003_at_mail.ee> To: <greg_at_structureddata.org> Cc: <oracle-l_at_freelists.org> Sent: Sunday, July 20, 2008 8:15 AM Subject: RE: Performance off "count(*)"

> Hi Greg,
>
>> -----Original Message-----
>> From: oracle-l-bounce_at_freelists.org
>> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Greg Rahn
>> Sent: Sunday, July 20, 2008 07:06
>> To: Tanel Poder
>> Cc: oracle-l_at_freelists.org
>> Subject: Re: Performance off "count(*)"
>>
>> I think we are saying the same thing: the count can be
>> determined by just the block header. No?
>>
>
> Nope, as far as I know (and have elaborated in other posts in this thread)
> the rowcount can *not* be detected by reading the block header only. The
> reason is that the row count field in block header includes also deleted
> rows and continued rowpieces of chained rows which you shouldn't count.
>
>
> --
> Regards,
> Tanel Poder
> http://blog.tanelpoder.com
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
> No virus found in this incoming message.
> Checked by AVG - http://www.avg.com
> Version: 8.0.138 / Virus Database: 270.5.2/1562 - Release Date: 19/07/2008
> 14:01
>
>
>

--
http://www.freelists.org/webpage/oracle-l