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
 



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

> From: oracle-l-bounce_at_freelists.org 

> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis

> Sent: Saturday, July 19, 2008 16:26

> To: oracle-l_at_freelists.org

> Subject: Re: Performance off "count(*)"

> 

> 

> Tanel,

> 

> I think it might be possible to:

>  check the clean SCN

>  check the transaction count

>  check that all transactions are committed and deduce from 

> this whether or not it was okay to count the number of rows 

> in the block from the "row index" area, excluding null pointers.

> 

> For blocks with a large enough number of rows this might be 

> more efficient than walking the row lengths.

> 

> 

> 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

> 


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