Re: Index management

<snip>  

> Anyway, I thought the concept of dirty base table blocks was
> self-evident to those using Oracle, but since there seems to be some
> confusion let me digress into an explanation. When a table is created
> all of its blocks are in a sense "clean", that is, they contain no
> data. As Oracle writes data to the database tables, their blocks
> become "dirty", a reference no doubt to the "dirty buffers" the
> dirty-buffer writer writes to them (while the politically correct
> "database writer" has supplanted the original "dirty buffer writer" in
> the original database documentation in version 6 and earlier it was
> "dirty buffer writer".) So, a dirty block contains data, while a clean
> one does not.

</snip>

That wasn't clear to me, maybe because I didn't get involved with Oracle until v7.1. However, I'm not sure that I'll be referring to my data on disk as "dirty". It's good data. It's valid data. But thanks for the explanation of your term.

<snip>

> Now, as a block fills and then reaches pctused, it is taken off of the
> free list and is not eligible for any more insertions, however it can
> under go updates which can cause row chaining, but that is another
> issue.

</snip>

Didn't you mean row migration?

<snip>
> Add to this the various node splitting algorithms Oracle uses for
> non-sequential inserts and updates and you can easily see why
> clustering factor increases and can become out of sync with reality.
> An index rebuild coalesces nodes and aligns them with the underlying
> table. Now, in many cases this reduces the clustering factor, however,
> I have seem it stay the same (usually on truly random keys such as
> text, or concatenated columns), decrease (the desired outcome) or
> increase!

</snip>

Just to make sure that we are all talking about the same clustering factor....

According to the Oracle docs, the clustering factor "Indicates the amount of order of the rows in the table based on the values of the index". How does rebuilding an index alter the clustering factor? An ALTER INDEX REBUILD does not change the order of rows in the table. Pure and simple. And Howard has a nice example which illustrates this exact point.

Is it possible that your definition of "clustering factor" differs from the above? If so, can you please post your definition? If not, then I'm afraid that your statements are false.

Cheers,
Brian

-- 
===================================================================

Brian Peasland
dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"