Re: separate data/inidex

"simon" <onlysimon_at_hotmail.com> wrote in message news:tm5rcuonkpav9lgvdldmguosjonn2sbbvi_at_4ax.com...
> On Sun, 28 Apr 2002 03:04:54 +1000, "Howard J. Rogers"
> <dba_at_hjrdba.com> wrote:
>
> direct quotes from oracle docs (8.1.6):
>
> "Split indexes and tables into separate tablespaces to minimize disk
> head movement and parallelize I/O. Both reads happen faster because
> one disk head is on the index data and the other is on the table
> data."
>
> "You can create an index using an existing index as the data source.
> Creating an index in this manner allows you to change storage
> characteristics or move to a new tablespace. Rebuilding an index based
> on an existing data source also removes intra-block fragmentation..."
>
> lifespan has a lot to do with updates. lets say there is a bitmap
> index on a CHAR(1) column. The table has a million rows and the whole
> column is just one value. A series of updates occur and this column
> now has a cardinality of 10.. then more updates occur and cardinality
> goes back to 1. Now I admit this isn't a great application for bitmap
> indexes but the lifespans will be very different.
>

True enough: it's a lousy application of bitmap indexes, which shouldn't be in sight nor sound of that sort of DML! But that aside....

I still don't quite see what any of this has to do with wanting to separate the index from the table however. Having done the subject to death recently, it should be apparent that the changes to the index do not compete with the changes to the table, since they are not done in parallel.

Your example is actually a brilliant one demonstrating why separation of data and indexes is generally pointless: Given the horrible lack of concurrency when updating bitmap indexes, any performance penalties you might conceivably get from housing the index in the same tablespace as its table will be utterly insignificant compared with the shambolic performance occasioned by the update of the bitmap index itself.

Incidentally, this thread also discussed the, er, somewhat misleading nature of the Oracle documentation for 8.1.6 -specifically, that paragraph you quoted above. It was wrong when it was written, is wrong now, and finally got corrected in the 9i documentation (in which version, of course, indexes and heap tables haven't changed their nature at all) which is much less precise about the issue, mentioning only that "contending I/O" in general needs to be eliminated by creating separate tablespaces -they've dropped the specific reference to tables v. indexes. And quite right, too.

Regards
HJR
> simon
>
> >Since fragmentation need never happen these days, that leaves "extent
> >lifespan". Quite how an index extent would live longer or lesser than its
> >corresponding table extent beats me. Maybe you could elaborate.
> >
> >HJR
> >
> >"simon" <onlysimon_at_hotmail.com> wrote in message
> >news:cs7lcuoue1of1bsg7462vlbtqhgpfi5gmb_at_4ax.com...
> >> On 21 Apr 2002 11:08:16 -0700, Thomas Kyte <tkyte_at_oracle.com> wrote:
> >>
> >> >
> >> >
> >> >The theory behind putting index and data on separate devices goes like
> >this:
> >> >
> >> >"you read the index, then you read the data. We'd like to leave the
head
> >> >positioned in the index structure just so -- so that the next read on
it
> >doesn't
> >> >require head movement". (or something like that).
> >> >
> >>
> >> I don't think that is the case.
> >>
> >> Separate DATA and INDEX tablespaces because of fragmentation and
> >> extent lifespan.
> >>
> >> You could put these tablespaces on separate disks to spread disk I/O
> >> in a MULTI USER environment.
> >>
> >> It would be even better to spread both tablespaces over different
> >> disks to spread disk I/O.
> >>
> >> Simon (OCA)
> >
>
Received on Mon Apr 29 2002 - 17:01:11 CDT