Re: Performance off "count(*)"

Greg,

I heard that many years ago - and it seemed reasonable. Then I started to doubt it, and wondered if it was one of those plausible legends.

I'd love to know if it were really true,

    on the plus side, there does seem to be a slight difference     in performance after a select count(*) conversion.

    on the minus side, a check of x$kcbsw shows the call to     be the same for count(*) as for count(nullable).

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: "Greg Rahn" <greg_at_structureddata.org> To: <Marco.Gralike_at_amis.nl> Cc: <oracle-l_at_freelists.org> Sent: Friday, July 18, 2008 10:44 PM Subject: Re: Performance off "count(*)"

> On Fri, Jul 18, 2008 at 10:28 AM, Marco Gralike <Marco.Gralike_at_amis.nl> wrote:
>>
>> Am I correct in my "small understanding" of Oracle that "count(*)" has
>> been optimized.
>
> A count(*) that uses TABLE ACCESS FULL access does not have to read
> each block in its entirety, it just reads the header to see how many
> rows are in the block. I guess you could call that "optimized".
>
>

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