Re: Performance off "count(*)"

Tanel,

I haven't checked this for several years, but I think the rowpointers for deleted rows get set to -1 (or some other special value) when the block gets cleaned out.

But you've definitely got me on the head row-pieces, I forgot to consider the effects of chained rows.

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: <jonathan_at_jlcomp.demon.co.uk>; <oracle-l_at_freelists.org> Sent: Saturday, July 19, 2008 1:49 PM Subject: RE: Performance off "count(*)"

> Hi Jonathan,
>
> The row index pointers remain there even for deleted rows (and block
> cleanout doesn't purge deleted rows as it just cleans out the ITL entries).
>
> And leaving deleted rows aside, there's still the issue of chained rows
> (continued row pieces). The only way (known to me) for figuring out how many
> rows in a block are head row pieces (and not continued pieces) is to check
> for H flag in individual row header. Otherwise we would overcount chained
> and migrated rows..
>
> So, unless there is a bit in every block header which says that "in this
> block all rows are actual not-deleted head rowpieces" I think there's no
> optimized way other than iterating through headers of all rowpieces in a
> block.
>
> --
> Regards,
> Tanel Poder
> http://blog.tanelpoder.com
>

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