RE: Performance off "count(*)"

If there is something like "an optimized method" for "count(*)" based on the internals, wouldn't it be more cost effective to determine it based on the freelist info...

Van: Tanel Poder [mailto:tanel.poder.003_at_mail.ee] Verzonden: za 19-7-2008 8:53
Aan: greg_at_structureddata.org; Marco Gralike CC: oracle-l_at_freelists.org
Onderwerp: RE: Performance off "count(*)"

Hi Greg,

As far as I know the "nrow" in block header stores number of all row structures in a block, including deleted rows (with delete flag set in row header) and continued row pieces (chained rows) so Oracle still has to go to individual row header to determine whether to count it or not...

--
Regards,
Tanel Poder
http://blog.tanelpoder.com <http://blog.tanelpoder.com/> 




> -----Original Message-----

> From: oracle-l-bounce_at_freelists.org

> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Greg Rahn

> Sent: Saturday, July 19, 2008 05:44

> To: Marco.Gralike_at_amis.nl

> Cc: oracle-l_at_freelists.org

> 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".

>

>

> --

> Regards,

> Greg Rahn

> http://structureddata.org <http://structureddata.org/> 

> --

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

>

>





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