Re: separate data/inidex

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.

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 - 14:17:13 CDT