Re: Index management

"David Williams" <djw_at_smooth1.fsnet.co.uk> wrote in message news:c7mcr9$o0j$1_at_newsg1.svr.pol.co.uk...

> > > a) new blocks (i.e. rows put into the index in a sub-optimal manner)

There is no such thing as a "sub-optimal manner" in the Oracle b-tree implementation since at least release 8i. Previous to that one there wasn't either. But there were a few nasty bugs here and there that could cause heaps of problems. Mostly gone.

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

They ARE occupied by other index entries using optimal placement...

> > > c) updated blocks which have have rows moved and hence non-optimal
> > > placment of index entries.

Repeat after me please: In Oracle, a move of a row in a data block will never ever cause a "non-optimal placement" of its corresponding entry in the index. The ONLY thing that can happen is for the rowid in the index to be updated to a new one. That is NOT "non-optimal" by ANY stretch of the imagination.

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

Cool. Welcome. I hope you will concede that b-trees a-la Informix are NOT the same as b-trees a-la Oracle?

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

That is controlled in Oracle by the index creation process. It's fixed and it has no progressive disorganization.

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

The only way that can really happen in Oracle is if the number of index branch levels increases. You need a LOT of inserts for that to happen in a significant manner. And I mean a *LOT*!

> If the index entries you need to read can fit in less blocks than less
> reads are
> done and it is faster yes?

The only way that can happen is if someone changes the allocation parameters for the index and re-creates it or if the length of the keys has changed. But none of that happens as a result of progressive degradation of the index.

>
> If two index entries are in the same block than less writes are done when
> you
> update both rows at the same time, yes?

Sure. And it ain't gonna change over time. Unless your key size has changed DRAMATICALLY.

> If the index blocks are such that range scans can be done using
> sequential
> rather than random disk reads than it is faster yes?

Yes. Absolutely. Compactness. And re-building an index gives you squat guarantee it will happen. You have to do a LOT more than just rebuild to ensure that.

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

Yes, it CAN affect the index compactness. At build time. Not during normal use. Once you define the per block free space, it is the same for ever new block. It ain't gonna get worse because you pumped a few million rows into the table...

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

Yes, but the problem is that we all doubt that an index re-build provides ANY speed improvement, let alone small increments.

IF you also change the NATURE of the index, then YES INDEED you may see an improvement. That is the case when you change the number or order of columns in a concatenated index. Or you may also change the space allocation parameters for the index to make it more compact. Or even the actual columns being indexed.

Sure. But that is NOT the rebuild we're talking about. That is design tuning, which can be done at ANY time in ANY database and have good to excellent performance results. The "index rebuild" that is in question here is the arbitrary ALTER INDEX REBUILD used as a "preventive" maintenannce strike by some DBAs and which serves no purpose whatsoever and achieves nothing, nowadays. Kinda like reorganising tables to reduce number of extents.

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

I'd settle for having ANY free time...
;)

Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam Received on Mon May 10 2004 - 06:12:27 CDT