Re: Index management

Mike Ault wrote:

> First let me say, excellent book! I suggest it to the PL/SQL
> developers I come across and use it to review, shall we say,
> suboptimal code?

I agree.

> 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.

Your verbiage may have been "common" back with version 6, I don't recall that but I'll make allowances for my memory perhaps being faulty. But no document finable at otn.oracle.com, tahiti.oracle.com, docs.oracle.com, etc. uses this language. Nor could I find it in any of my reference books. Including one written by you.

Perhaps much of the problem here is one of communication and I would like to suggest on behalf of myself, my students, and others that watch these exchanges, and a few that participate, that such language be brought current to version 9i or later.

> Obviously, unless it is corrupt, an index can only point to dirty blocks
> in a table.

Given that one wishes to "invent" new terminology. Why can't you use the terms used by Oracle and in standard usage by developers and DBAs? It would make this thread much more usable and useful.

Hence a "perfect" clustering factor would
> be equal or perhaps less than the number of dirty blocks, while for a
> very random index, it would be closer to the number of actual table
> entries (for some bitmap indexes, I have seen it greater than the
> number of rows.) Usually, clustering factor shouldn't be greater than
> the number of rows in the underlying table.
>
> 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. Once deletions from the block cause the available space to drop
> below pctused and it is placed back on the free list for more inserts.
> This insertion back on the free list could be hours, days or some
> greater time later than the last time it was used for inserts. This
> means the index nodes that are referencing its inserted rows are most
> likely way across the index tree from the original ones that hold most
> of its entries.

Freelist? I don't think anything I've been doing has used freelists for years.

> 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!

Can you create a demo we can try to see this?
>
> However, I may have stated things unclearly, the goal in index
> rebuilding is not to reduce clustering factor, that is actually a
> desired by-product, the goal is to ensure that the index is properly
> aligned with the underlying table and that its entries are not sparse.
> Clustering factor ratios are just one of several indicators that can
> tell you an index needs to be investigated.
>
> Mike

Once again ... could you create a demo that would show this?

Thanks.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)