RE: unexpected heavy buffered IO during partition bitmap index rebuild

| There is no "access path" effect, Oracle is doing a table scan, but
when it
| creates the index it needs the rowid it has just acquired and the data
from
| the migrated row AT THE SAME TIME, so it has to follow the pointer."

What got me starting up a worry thread on "access path" was the observation that the bitmap index rebuild on the old partition with zero migrated rows what doing nearly all direct path reads... vs. the new partition which (although 35% of rows are migrated) was nothing but buffered reads all over the place. Struggling to understand "what" little demon decision-maker in Oracle was deciding on that, and why... hence got interested in _small_table_threshold, blocks already in the buffer and other misc. points to consider why the decision to do one or the other was so different (at least in 11.2.0.3).

Oracle support seems to be telling me to just make the migrated rows go away and no problem. Ok, I agree... but it leaves me still wondering about the original problem... could 35% migrated rows (in our environment) have really lead to 285 times the IO? If so that is an electric fence that gives quite a (disproportionate) jolt! I would have thought (naively, I'm sure) that even with 100% of the rows migrated-and only one hop away-would have been at worst 2 times the IO to scan the table. In fact, after CTAS our recreated segment is 1/2 the size (in blocks) of the segment with 35% of its rows migrated. Seems like something is going astray in a Really Bad Way. However, this may be another one of those moments were it just doesn't pay to be curious beyond a certain point.

Looking forward to your code on calculating row size... perhaps I've missed it somewhere but I don't think I've seen that anywhere.

-john

</pre>This message is confidential, intended only for the named recipient(s) and may contain information that is privileged or exempt from disclosure under applicable law. If you are not the intended recipient(s), you are notified that the dissemination, distribution, or copying of this message is strictly prohibited. If you receive this message in error or are not the named recipient(s), please notify the sender by return email and delete this message. Thank you.

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