Re: Performance off "count(*)"
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 19 Jul 2008 14:34:04 +0100
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@xxxxxxx>
To: <jonathan@xxxxxxxxxxxxxxxxxx>; <oracle-l@xxxxxxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
