Re: Question about tablespace datafile setup

This is one of those statements that seems to get quoted as infallible gospel from time to time.

Unfortunately it isn't necessarily a good thing.

Imagine:

    Busy table X has 4 indexes.

It is reasonably likely that the branch levels of the indexes are fully buffered, so a query to table X using an index will hit one physical I/O on the index leaf and one physical I/O on the table. Spreading indexes to 'one disc' and tables to another seems a reasonable balance.

But -
If is insert a row into table X, I will update a leaf block from 4 different indexes. If I insert 10 rows into table X I will probably write all 10 rows into the same table block one after the other - I could well update 10 different leaf blocks in each of 4 different indexes. The subsequent I/O done by oracle will be 1 write to the 'tables disc' and 40 writes to the 'indexes disc'

The old 'indexes in one place, tables in another' does need a little extra thought.

Nevertheless as you indicate, it is always useful to separate table I/O from index I/O by putting different types of object into different tablespaces - any strategy which takes little effort but helps you identify significant I/O patterns is a good thing.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Brian Wheeler wrote in message <388A9B9D.D22C5DEA_at_iname.com>...

>At a minimum, put your index tables in a tablespace on one disk and your
>data tables on another disk.
Received on Sun Jan 23 2000 - 12:40:27 CST