Re: Performance off "count(*)"

This was my understanding as well: x$kcbsw shows the same number of blocks, because regardless of count(*) counting the rows in the block header or traversing the row chain in the block, the block itself must be read in both cases. What might be saved is the CPU spent traversing the block. That might be relevant for very small rows and/or very large blocks, with many rows per block?

The other thing I recall is that if the table has a defined primary key, the index supporting it will be scanned rather than reading all blocks.

But that's about all. There might be nuances for things like IOT's, clusters and so on.

-- 
Cheers
Nuno Souto
in sunny Sydney, Australia
dbvision_at_iinet.net.au


Jonathan Lewis wrote,on my timestamp of 19/07/2008 6:29 PM:


> 

> 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