Re: Index management

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1084141988.550010_at_yasure...
> David Williams wrote:
>
> > I assume he means a dirty block in the table is a data (as opposite
to
> > index)
> > block which has changed since the last index rebuild. Presumably if
the
> > number (and hence percentage) of
> >
> > a) new blocks (i.e. rows put into the index in a sub-optimal manner)
> > b) deleted blocks which leave sub-optimal free space in an index
> > i.e. leave free space in a block which could be occupied by other
> > index entries using optimal placement.
> > c) updated blocks which have have rows moved and hence non-optimal
> > placment of index entries.
> >
>
> Assuming you are correct ... and that involves a lot of assuming ...
> you are misusing the phrase "dirty block". I have no idea how a row

   I am an Informix dba who is posting in an Oracle group. I do not know    your terminology but I do not btree index structures.

> can be put into an index in a sub-optimal manner or even what
> "sub-optimal" actually means. I can't see what is sub-optimal about

   sub-optimal means having all the index entries in as few blocks as possible

   to read/update/delete the blocks required whilst still allowing room for expected

   growth. Having to read nore index blocks to satisfy a query is not optimal.

   If the index entries you need to read can fit in less blocks than less reads are

   done and it is faster yes?

   If two index entries are in the same block than less writes are done when you

   update both rows at the same time, yes?

   If the index blocks are such that range scans can be done using sequential

   rather than random disk reads than it is faster yes?

> free space in an index block? Is that space less optimal than having
> no space when an insert comes along with a block that belongs in the
> block, etc.?

   Is depends on how many rows you will insert. If inserting no more rows    or few rows (e.g. customer table where you do not get many new customers    per month and most of you business is repeat business than you need a small

   percentage free than a phone records table for a phone company!). If this is a

   table that get no more rows (e.g. prices table where prices only change once

   per year) than yes having no free space is optimal).

>
> In short ... we have a huge amount of imprecise language being bandied
> about by people who have yet to produce demos that supports their
> contentions.
>
> Which means to me that those of us trying to learn from these "experts"
> are still in the dark. So lets stop making assumptions and get
> clarifications and demos.

    It is subjective and every case needs to be considered independently.     You need to use some general rules and the skill is in applying them to     specific cases. We need a few examples but do not assume the hugh     speedups you get from contrived examples will apply to you. Getting     a small speedup from lots of little tweaks can add up to a lot -    compound interest. Index rebuilds are just part of it.

   What else would you using idle time on the machine for? Running seti     on all your servers...or sitting idle doing nothing? Of course even this     advice needs to be balanced. Using every free second is not wise     as well.

> --
> 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)
>
Received on Sun May 09 2004 - 17:55:20 CDT